Use of Cursors Attributes with Implicit Cursor

--====================================================

--Cursor Attributes  :-

--====================================================

SQL%ISOPEN Attribute: this attribute checkes wheather cursor Is the Cursor Open or not and return boolean value TRUE OR FALSE respectively.

SQL%FOUND Attribute:  Were Any Rows Affected and return boolean value TRUE OR FALSE respectively.

SQL%NOTFOUND Attribute: Were No Rows Affected  and return boolean value TRUE OR FALSE respectively.

SQL%ROWCOUNT Attribute: How Many Rows Were Affected or not  and return number of rows are affected by this DML statements.

SQL%BULK_ROWCOUNT : Getting Number of Rows Affected by FORALL Statement

SQL%BULK_EXCEPTIONS : Handling FORALL Exceptions After FORALL Statement Completes

--====================================================

-- Use of Cursors Attributes with Implicit cursor :-( an implicit cursor is also called a SQL cursor )

/*

An implicit cursor is a session cursor that is constructed and managed by PL/SQL.

PL/SQL opens an implicit cursor every time you run a SELECT or DML statement.

You cannot control an implicit cursor, but you can get information from its attributes.

Syntax:-

SQLattribute :- SQLattribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQLattribute is NULL.

*/

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81			20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82			20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	10	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD		7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10

--====================================================

--Example  SQL%FOUND Implicit Cursor Attribute | it return :- Boolean Value

select * from XX_EMP_1

DECLARE

department_no NUMBER:=30;

BEGIN

  DELETE FROM XX_EMP_1

  WHERE deptNO = department_no;

  IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE ( 'all rows Delete succeeded for department number ' || department_no );

  ELSE

    DBMS_OUTPUT.PUT_LINE ('No Rows found for department number ' || department_no);

  END IF;

 

END;

OUTPUT:-

all rows Delete succeeded for department number 30

No Rows found for department number 30

--====================================================

--Example  SQL%NOTFOUND Implicit Cursor Attribute | it return :- Boolean Value

select * from XX_EMP_2

DECLARE

department_no NUMBER:=30;

BEGIN

  DELETE FROM XX_EMP_2

  WHERE deptNO = department_no;

  IF SQL%NOTFOUND THEN

    DBMS_OUTPUT.PUT_LINE ('No Rows found for department number ' || department_no);

  ELSE

    DBMS_OUTPUT.PUT_LINE ( 'all rows Delete succeeded for department number ' || department_no );   

  END IF;

END;

OUTPUT:-

No Rows found for department number 30

all rows Delete succeeded for department number 30

--====================================================

--Example  SQL%ROWCOUNT Implicit Cursor Attribute | it return :- Number Value

select * from XX_EMP_3

DECLARE

department_no NUMBER:=30;

BEGIN

  DELETE FROM XX_EMP_3

  WHERE deptNO = department_no;

    DBMS_OUTPUT.PUT_LINE ('Total '|| SQL%ROWCOUNT|| ' Number of  rows Deleted for department number :- ' || department_no );   

 

END;

OUTPUT:-

Total 6 Number of  rows Deleted for department number :- 30

--====================================================

--Example  SQL%ISOPEN Implicit Cursor Attribute  | it return :- Boolean Value

SQL%ISOPEN always returns FALSE, because an implicit cursor always closes after its associated statement runs.