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