Sunday, February 26, 2012

Cursors 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.


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.


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


% 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 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);
UPDATE employee
SET salary = salary + 1000;
dbms_output.put_line('None of the salaries where updated');
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

No comments:

Post a Comment