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 :

1 comments:

  1. Hi Dayakar,

    Allow me to show my gratitude bloggers. You guys are like unicorns. Never seen but always spreading magic. Your content is yummy. So satisfied.


    I've tried installing Patch above for SQL Server 2008 R2 in a Windows Server 2012 R2 and I've received the following message when I just start the installation:
    Unable to generate a temporary class (result=1). error CS0016: Could not write to output file 'c:\Install_TEMP\AppData\Local\Temp\2\ny0nmqmd.dll' -- 'Could not execute CVTRES.EXE.'
    Followed by:
    CVTRES.EXE
    The program can't start

    because MSVCR80.dll is missing from your computer. Try reinstalling the program to fix this problem.





    Super likes !!! for this amazing post. I thinks everyone should bookmark this.


    Obrigado,
    Irene Hynes

    ReplyDelete