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

Following are implicit cursor attributes,

Cursor Attribute	Cursor Variable	Description
%ISOPEN	SQL%ISOPEN	Oracle engine automatically open the cursor If cursor open return TRUE otherwise return FALSE.
%FOUND	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.
%NOTFOUND	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.
%ROWCOUNT	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.

Syntax

cursor_attribute ::=
    { 
        cursor_name | 
        cursor_variable_name | 
        :host_cursor_variable_name
    }
    % {FOUND | ISOPEN | NOTFOUND | ROWCOUNT}

Explanation

cursor_name cursor_name identifies the current scope which are previously declared.

cursor_variable_name cursor variable or parameter identifies the current scope which are previously declared.

host_cursor_variable_name host_cursor_variable_name must be prefixed with a colon. Host cursor variable datatype must be compatible with the PL/SQL cursor variable.

Implicit Cursor Example

Following one emp_information table:

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	Zenia Sroll	Web Developer	42k

Now above employee information table update the employee name 'Saulin' department 'Program Developer' update to 'Web Developer'.

Example

implicit_cursor.sql

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;
/

Result

SQL>@implicit_cursor
Updated - If Found
1 Rows Updated

PL/SQL procedure successfully operation.