Cursor Attributes

Cursor Attributes:

• There are four attributers available in PL/SQL that can be applied to cursor.
• Cursor attributes are appended to a cursor name in a  PL/SQL block, similar to %TYPE and %ROWTYPE.

The attributes are

1. %FOUND
2. %NOTFOUND
3. %ISOPEN
4. %ROWCOUNT

%FOUND:

• Boolean attribute that evaluates to TRUE If the most recent SQL statement affects one or more rows.

%NOTFOUND:

• Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.

%ISOPEN:

• Boolean attribute that evaluates to TRUE if the cursor is open else evaluates to FALSE.

%ROWCOUNT:

• Number of rows affected by the most recent SQL statement (an integer value).

SQL>var rows_deleted varchar2(20)

SQL>DECLARE
    v_Empno emp.empno%TYPE:=7788;
    BEGIN
    DELETE FROM emp
    WHERE empno=v_Empno;
    :rows_deleted:=(SQL%ROWCOUNT||’row deleted.’);
    END;

Write a PL/SQL Program to print the BIND variable.
SQL>DECLARE
    v_Job Emp.Job%TYPE:=’&Job’;
    BEGIN
    UPDATE emp
    SET sal=sal+1000
    WHERE job=v_Job;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||’Rows were updated.’);
    IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE(‘Data Not Found So, No updation.’);
    END IF;
    END;