Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


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 Code

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

Example Result

SQL>@implicit_cursor
Updated - If Found
1 Rows Updated

PL/SQL procedure successfully operation.