What are Cursors?
A cursor is a temporary work area created in
the system memory when a SQL statement is executed. A cursor contains
information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the
database, and manipulate this data. A cursor can hold more than one
row, but can process only one row at a time. The set of rows the cursor
holds is called the
active set.
There are two types of cursors in PL/SQL:
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.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
Implicit Cursors:
When you execute DML statements like DELETE, INSERT,
UPDATE and SELECT statements, implicit statements are created to
process these statements.
Oracle provides few attributes called as implicit cursor
attributes to check the status of DML operations. The cursor attributes
available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE
statements the cursor attributes tell us whether any rows are affected
and how many have been affected.
When a SELECT... INTO statement is
executed in a PL/SQL Block, implicit cursor attributes can be used to
find out whether any row has been returned by the SELECT statement.
PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
Attributes
|
Return Value
|
Example
|
%FOUND
|
The return value is
TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at
least one row and if SELECT ….INTO statement return at least one
row. |
SQL%FOUND
|
| The return value is
FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect
row and if SELECT….INTO statement do not return a row. |
| %NOTFOUND |
The return value is
FALSE, if DML statements like INSERT, DELETE and UPDATE at least one
row and if SELECT ….INTO statement return at least one row. |
SQL%NOTFOUND
|
| The return value is
TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect
even one row and if SELECT ….INTO statement does not return a row. |
| %ROWCOUNT |
Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT |
SQL%ROWCOUNT |
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;
In the above PL/SQL Block, the salaries of all the
employees in the ‘employee’ table are updated. If none of the
employee’s salary are updated we get a message 'None of the salaries
where updated'. Else we get a message like for example, 'Salaries for
1000 employees are updated' if there are 1000 rows in ‘employee’ table.
