When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it.
In its simplest form, you can think of a cursor as a pointer into a table in the database. For example, the following cursor declaration associates the entire employee table with the cursor named employee_cur:
CURSOR employee_cur IS SELECT * FROM employee;
Once I have declared the cursor, I can open it:
And then I can fetch rows from it:
FETCH employee_cur INTO employee_rec;
and, finally, I can close the cursor:
In this case, each record fetched from this cursor represents an entire record in the employee table. You can, however, associate any valid SELECT statement with a cursor. In the next example I have a join of three tables in my cursor declaration:
DECLARE CURSOR joke_feedback_cur IS SELECT J.name, R.laugh_volume, C.name FROM joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND J.joker_id = C.joker_id; BEGIN ... END;
Here, the cursor does not act as a pointer into any actual table in the database. Instead, the cursor is a pointer into the virtual table represented by the SELECT statement (SELECT is called a virtual table because the data it produces has the same structure as a table -- rows and columns -- but it exists only for the duration of the execution of the SQL statement). If the triple-join returns 20 rows, each row containing the three columns in the preceding example, then the cursor functions as a pointer into those 20 rows.
You have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor. Generally, there are two types of SQL that you can execute in PL/SQL: static and dynamic. SQL is static if the content of the SQL statement is determined at compile time. A SQL statement is dynamic if it is constructed at runtime and then executed.
Dynamic SQL is made possible in PL/SQL only through the use of the DBMS_SQL built-in package (see Appendix C, Built-In Packages ). All other forms of SQL executed inside a PL/SQL program represent static SQL; these forms of cursors are the focus of the remainder of this chapter.
Even within the category of static SQL, we have further differentiation. With the advent of PL/SQL Release 2.3, you can choose between two distinct types of cursor objects:
The cursor variable is one of the newest enhancements to PL/SQL and will be unfamiliar to most programmers. Cursor variables act as references to cursor objects. As a true variable, a cursor variable can change its value as your program executes. The variable can refer to different cursor objects (queries) at different times. You can also pass a cursor variable as a parameter to a procedure or function. Cursor variables are discussed later in this chapter.
Static PL/SQL cursors have been available since PL/SQL Version 1. The static version of cursors "hardcodes" a link between the cursor name and a SELECT statement. The static cursor itself comes in two flavors: implicit and explicit.
You, the programmer, define your own explicit cursors in your code. You must use an explicit cursor when you need to retrieve more than one row of data at a time through a SELECT statement. You can then use the cursor to fetch these rows one at a time. The set of rows returned by the query associated with an explicit cursor is called the active set or result set of the cursor. The row to which the explicit cursor points is called the current row of the result set.
The bulk of this chapter is devoted to the management of static, explicit cursors. All information about cursor variables is localized in Section 6.12, "Cursor Variables" . Any references to PL/SQL cursors and cursor characteristics outside of that section will pertain to static cursors.
Figure 6.1 shows how some of these different operations are used to fetch information from the database into your PL/SQL program.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.