In its simplest form, you can think of a cursor as a pointer to the results of a query run against one or more tables 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:
Then I can fetch rows from it:
FETCH employee_cur INTO employee_rec;
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 R.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 or implicit view 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 containing 3 columns, then the cursor functions as a pointer into those 20 rows.
Some Data Retrieval Terms
You have lots of options in PL/SQL for executing SQL, ...