Cursors in PL/SQL

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

There are two types of cursors in PL SQL
1) Implicit cursor
2) Explicit cursor

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

The attributes of the cursors

The attributes of the implicit cursors can return information about DML and DDL execution commands such as INSERT, UPDATE, DELETE, SELECT INTO, COMMIT or ROLLBACK.

% FOUND

Until SQL data manipulation is executed, the attribute % FOUND is NULL. So, % FOUND is TRUE if the command type of INSERT, UPDATE, or DELETE affects one or more records from the database or SELECT INTO returns one or more recordings.

% ISOPEN

Oracle closes the cursor automatically after execution of commands. As a result,% ISOPEN becomes FALSE.

% NOTFOUND

% NOTFOUND is the opposite attribute of % FOUND.
% NOTFOUND is TRUE if the INSERT, UPDATE, or DELETE your registration does not affect any of the database or SELECT INTO does not return any registration. Otherwise it is FALSE.

% ROWCOUNT

% ROWCOUNT returns the number of records affected by one of the commands that INSERT, UPDATE, or DELETE, or the number of records affected by the SELECT INTO. % ROWCOUNT is 0 if the command INSERT, UPDATE, or DELETE does not affect any registration, or SELECT INTO does not return any record.



For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;