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.