Cursor Overview

Suppose you have a database;
you can have access to your data through a query as:
SELECT […your cols…] FROM […your tables…] WHERE […your conditions…];
However, from a PL/SQL program, is not sufficient to run query, you need to store the data somewhere to use them.
There are two alternatives:
1) if the result of a query is a single line, you can use a variable, with a syntax of this type:
 
SELECT […your cols…] INTO […your variable…] FROM […your tables…] WHERE […your conditions…];
2) if the result of the query consists of multiple lines, you need to use a cursor.
There are 2 types of cursors: explicit and implicit.
 
————————————————————————————————————————
 
EXPLICIT CURSORS
To handle explicit cursors, there are four steps:
1–DECLARATION
2–OPENING*
3-FETCHING*
4–CLOSING*
 
1-declaration
you must declare the cursor in declarative area of your code.
 
DECLARE
CURSOR cur_name IS
SELECT […your cols…]
FROM […your tables…]
WHERE […your conditions…];
 
2-opening
you executes the previous declared query and identify the result type using OPEN statement.
 
OPEN cur_name;
 
3-fetching
you recover data – one row at a time – and you insert query output into one or more variables using FETCH statement;
for recover more rows you may insert FETCH statement in a loop.
 
FETCH cur_name INTO […your vars…]
 
4-closing
you release cursor using CLOSE statement at the end.
 CLOSE cur_name;
*Attention:
opening, fetching and closing statements can be omitted where you use cursor into a FOR loop (see example #3).
 
————————————————————————————————————————
 
IMPLICIT CURSOR
 
Implicit cursor is opened by Oracle without an associated declared cursor.
It happens when Oracle must process a DML statement (for example, a SQL query)
We can’t use OPEN, FETCH and CLOSE statements for this type of cursor.
 
————————————————————————————————————————
CURSOR ATTRIBUTES
 
to manage explicit cursors you can use these attributes:
 
%ISOPEN                            (boolean) it’s TRUE when your cursor is open
%NOTFOUND                       (boolean) it’s TRUE when the last FETCH can’t return a row
%FOUND                               (boolean) it’s TRUE until the last FETCH can’t return a row
%ROWCOUNT                       (numeric) total number of rows returned until now
 
to manage implict cursors you can use these attributes:
 
SQL%ISOPEN                    (boolean) it’s always FALSE because implicit cursors are closed after execution
SQL%FOUND                       (boolean) it’s TRUE if last command affected rows
SQL%NOTFOUND               (boolean) it’s TRUE if last command didn’t affect rows
SQL%ROWCOUNT               (numeric) total number of rows returned by last command
 
————————————————————————————————————————
 
CURSOR EXAMPLES
 
example 1 – explicit cursor, full statements
 
DECLARE
var_firstname     VARCHAR(20);
var_lastname      VARCHAR(20);
var_salary        people.salary%TYPE;
CURSOR cur_person IS
SELECT first_name, last_name, salary
FROM people;
BEGIN
OPEN cur_person;
LOOP
FETCH cur_person INTO var_firstname, var_lastname, var_salary;
EXIT WHEN cur_person%NOTFOUND;
/* …do something… */
END LOOP;
CLOSE cur_person;
END;
/
 
example 2 – explicit cursor, full statements with %ROWTYPE Record
 
DECLARE
CURSOR cur_person IS
SELECT first_name, last_name, salary
FROM people;
person_rec cur_person%ROWTYPE;
BEGIN
OPEN cur_person;
LOOP
FETCH cur_person INTO person_rec;
EXIT WHEN cur_person%NOTFOUND;
/* …do something… */
END LOOP;
CLOSE cur_person;
END;
/
 example 3 – explicit cursor, FOR loop
 DECLARE
total_sal people.salary%TYPE := 0;
CURSOR cur_person IS
SELECT first_name, last_name, salary
FROM people;
BEGIN
FOR single_person IN cur_person LOOP
total_sal := total_sal + single_person.salary;
END LOOP;
/* …do something… */
DBMS_OUTPUT.PUT_LINE (‘amount: ‘ || total_sal);
END;
/
example 4 – implicit cursor
 
DECLARE
max_salary  NUMBER(6) := 3000;
BEGIN
DELETE FROM people WHERE salary > max_salary;
DBMS_OUTPUT.PUT_LINE (‘Record deleted: ‘ || TO_CHAR(SQL%ROWCOUNT));
END;
/