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 :

4 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
  2. Hello Dayakar,

    Your writing shines! There is no room for gibberish here clearly you have explained about Oracle PL/SQL Implicit Cursor Attributes. Keep writing!


    I have a number of tables with partitions by month.
    Each database logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

    My first script where I input the actual table works fine - as in it will drop the partitions which meet the criteria (see below)

    Code (SQL):
    DECLARE
    v_sql varchar2(500);
    v_date DATE;
    v_partition_name user_tab_partitions.partition_name%TYPE;
    v_high_value user_tab_partitions.high_value%TYPE;
    CURSOR c1 IS SELECT PARTITION_NAME, HIGH_VALUE
    FROM user_tab_partitions WHERE TABLE_NAME='TEST_YFS_BAT_LOC_H' AND PARTITION_NAME!='P0';
    BEGIN
    OPEN c1;
    LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    IF v_date < (sysdate - 30) THEN
    v_sql := 'alter table TEST_YFS_BAT_LOC_H drop partition ' || v_partition_name;
    EXECUTE immediate v_sql;
    END IF;
    END LOOP;
    CLOSE c1;
    END;
    /
    But I want to be able to drop all partitions from all tables belonging to this schema. So how can I do something similar which goes through all the tables and partitions and does the same thing?

    I am executing this script as the schema owner with 100+ tables


    But great job man, do keep posted with the new updates.

    Kind Regards,
    Kevin

    ReplyDelete
  3. Hey,


    I learnt so much in such little time about #topic. Even a toddler could become smart reading of your amazing articles.
    I have requirement like

    select COL1,Col2 from A
    minus
    Select COL1,COL2 from B;

    both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.



    But nice Article Mate! Great Information! Keep up the good work!


    Kind Regards,

    ReplyDelete
  4. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete