PL/SQL Implicit Cursor

Published on August 18 2015 by LakshmiSaahul,Dhana Royal

PL/SQL Implicit Cursor

Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor.
Implicit cursor scope you can get information from cursor by using session attributes until another SELECT statement or DML statement execute.

Implicit Cursor Attributes

%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT

SQL%ISOPEN--   Oracle engine automatically open the cursor
If cursor open return TRUE otherwise return FALSE.
SQL%FOUND   If SELECT statement return one or more rows or DML statement (INSERT, UPDATE, DELETE) affect one or more rows
If affect return TRUE otherwise return FALSE.
If not execute SELECT or DML statement return NULL.
SQL%NOTFOUND   If SELECT INTO statement return no rows and fire no_data_found PL/SQL exception before you can check SQL%NOTFOUND.
If not affect the row return TRUE otherwise return FALSE.
SQL%ROWCOUNT   Return the number of rows affected by a SELECT statement or DML statement (insert, update, delete).
If not execute SELECT or DML statement return NULL.

Example Code

SQL>set serveroutput on
SQL>edit implicit_cursor
BEGIN
UPDATE emp_information SET emp_dept='Web Developer'
WHERE emp_name='Saulin';

   IF SQL%FOUND THEN
       dbms_output.put_line('Updated - If Found');
   END IF;

   IF SQL%NOTFOUND THEN
       dbms_output.put_line('NOT Updated - If NOT Found');
   END IF;

   IF SQL%ROWCOUNT>0 THEN
       dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
   ELSE
       dbms_output.put_line('NO Rows Updated Found');
   END;

Advertising