Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 23 July 2017

Oracle PL/SQL Implicit Cursor Attributes


Implicit Cursors

PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of   rows, including queries that return only one row
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually
We can refer to the most recent implicit cursor by the name SQL
Implicit Cursors - Attributes

%FOUND Attribute: used to check whether a DML Statement has affected one or many rows

Until the DML or SELECT statement is executed, it yields NULL
Yields TRUE if the DML affects one or more rows, or a SELECT INTO statement returns one or more rows.
Otherwise yields FALSE.

%ISOPEN Attribute: always FALSE for Implicit Cursors

Yields TRUE if the cursor is open else returns FALSE. 
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND Attribute: used to check whether a DML statement has failed to change rows


%NOTFOUND is the logical opposite of %FOUND
Yields TRUE if the DML statement affects no rows, or a SELECT INTO statement returns no rows
Otherwise it yields FALSE

%ROWCOUNT Attribute: used to find out how many rows are affected so far


%ROWCOUNT yields the number of rows affected by the DML statement, or returned by a SELECT INTO statement
Yields 0 if the DML statement affected no rows, or a SELECT INTO statement returned no rows
Implicit Cursor: Example 1
         Using SQL%ROWCOUNT 

 DECLARE
   v_tot_rows   NUMBER (3);
BEGIN
   DELETE FROM emp
         WHERE deptno = 10;
   /* PL/SQL will use an implicit cursor to process the above statement */
   v_tot_rows := SQL%ROWCOUNT;
   /* Implicit Attribute %ROWCOUNT is used to find the number of  rows affected by the DELETE command */
   DBMS_OUTPUT.put_line('Total records deleted : ' || v_tot_rows);
END;

Implicit Cursor: Example 2
       Using SQL%FOUND
DECLARE
   v_empno   NUMBER (4) := &eno;
BEGIN
   DELETE FROM emp
         WHERE empno = v_empno;
   IF SQL%FOUND
   THEN
      /* Making use of %FOUND to find out whether at least one row was affected by the DELETE statement */
      DBMS_OUTPUT.put_line('Delete successful');
   END IF;
END;
Share this article :

0 comments:

Post a Comment