Lesson 1

By now, those of you who have worked with real-world business applications may begin to wonder how you can manipulate data sequentially from the database within your PL/SQL block. Oracle provides you with cursors to do so. A cursor is a work area that Oracle uses to keep track of which record is currently being processed in multiple-record queries. The result set that a query returns may consist of any number of records that satisfy the search conditions of the query. When a query returns multiple records, you can define a cursor explicitly to:

1. Process beyond the first record that the query returns
2. Keep track of which record is currently being processed

In this module, we will first delve into what a cursor does. Next, you will learn about the different types of cursors. In addition, you will build an explicit cursor. Finally, you will learn about some advanced techniques to use cursors with a FOR loop.

By the end of this module, you will know how to:

1. Define a cursor
2. Review implicit and explicit cursors
3. Define the basic structure of a cursor block
4. Build a cursor block
5. Use a cursor FOR loop to process records

In the next lesson, you will begin learning about cursors, their advantages, and their disadvantages.

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.
Important: Be aware that in TimesTen^[1], any operation that ends your transaction closes all cursors associated with the connection. This includes any COMMIT or ROLLBACK statement. This also includes any DDL statement executed when PL/SQL is enabled, because the DDLCommitBehavior connection must be set to 0 (Oracle Database behavior) if PL/SQL is enabled. This results in autocommits of DDL statements.
Example 6-1: Using a cursor to retrieve information about an employee.
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.

[1]TimesTen: TimesTen is an in-memory relational database with persistence to disk and standard transaction semantics.