Use of Cursors in PL/SQL Programs
A cursor, either explicit or implicit, is used to handle the result set of a SELECT
statement.
As a programmer, you can declare an explicit cursor to manage queries that return multiple rows of data. PL/SQL declares and opens an implicit cursor for any SELECT
statement that is not associated with an explicit cursor.
Note:
In TimesTen, any operation that ends your transaction closes all cursors associated with the connection. This includes any COMMIT
or ROLLBACK
statement and any DDL statement. This results in autocommits of DDL statements. See Differences in TimesTen: Transaction Behavior.
The following example shows basic use of a cursor. See Examples Using Cursors for additional information and examples. Also see PL/SQL REF CURSORs.
Declare a cursor c1
to retrieve the last name, salary, hire date, and job class for the employee whose employee ID is 120:
Command> DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id FROM employees
WHERE employee_id = 120;
--declare record variable that represents a row
--fetched from the employees table
employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor
-- and use it to fetch data into employee_rec
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
CLOSE c1;
END;
/
Employee name: Weiss
PL/SQL procedure successfully completed.