Summary: in this tutorial, we will introduce you to PL/SQL cursor. You will learn step by step how to use a cursor to loop through a set of rows and process each row individually.
Introducing to PL/SQL Cursor
When you work with Oracle database, you work with a complete set of rows returned from a SQL SELECT statement. However the application in some cases cannot work effectively with entire result set, therefore the database server needs to provide a mechanism for the application to work with one row or a subset of result set at a time. As the result, Oracle created PL/SQL cursor to provide these extensions.
A PL/SQL cursor is a pointer that points to the result set of a SQL query against database tables.
Working with PL/SQL Cursor
The following picture describes steps that you need to follow when you work with a PL/SQL cursor:
Let’s examine each steps in greater detail.
Declaring PL/SQL Cursor
CURSOR cursor_name [ ( [ parameter_1 [, parameter_2 ...] ) ] [ RETURN return_specification ] IS sql_select_statements [FOR UPDATE [OF [column_list]];
- First, you declare the name of cursor
CURSORkeyword . The name of the cursor can have up to 30 characters in length and follows the naming rules of identifiers in PL/SQL. It is important to note that cursor’s name is not a variable so you cannot use it as a variable such as assigning it to other cursor or using it in an expression. The
parameter2… are optional elements in the cursor declaration. These parameters allow you to pass arguments into the cursor. The
RETURN return_specificationis also an optional part.
- Second, you specify a valid SQL statement that returns a result set where the cursor points to.
- Third, you can indicate a list of columns that you want to update after the
FOR UPDATE OF. This part is optional so you can omit it in the CURSOR declaration.
Here is an example of declaring a cursor:
CURSOR cur_chief IS SELECT first_name, last_name, department_name FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id;
We retrieved data from employees and departments tables using
SELECT with INNER JOIN statement and set the
cur_chief cursor to this result set.
Opening a PL/SQL Cursor
After declaring a cursor, you can open it by using the following syntax:
OPEN cursor_name [ ( argument_1 [, argument_2 ...] ) ];
You have to specify the cursor’s name
cursor_name after the keyword
OPEN. If the cursor was defined with a parameter list, you need to pass corresponding arguments to the cursor.
When you OPEN the cursor, PL/SQL executes the SQL SELECT statement and identifies the active result set. Notice that the
OPEN action does not actually retrieve records from database. It happens in the
FETCH step. If the cursor was declared with
FOR UPDATE clause, PL/SQL locks all the records in the result set.
We can open the
cur_chief cursor as follows:
Fetching Records from PL/SQL Cursor
Once the cursor is open, you can fetch data from the cursor into a record that has the same structure as the cursor. Instead of fetching data into a record, you can also fetch data from the cursor to a list of variables.
The fetch action retrieves data and fills the record or the variable list. You can manipulate this data in memory. You can fetch the data until there is no record found in active result set.
The syntax of
FETCH is as follows:
FETCH cursor_name INTO record or variables
You can test the cursor’s attribute
%NOTFOUND to check if the fetch against the cursor is succeeded. The cursor has more attributes that we will cover in the next section.
We can use PL/SQL LOOP statement together with the
FETCH to loop through all records in active result set as follows:
LOOP -- fetch information from cursor into record FETCH cur_chief INTO r_chief; EXIT WHEN cur_chief%NOTFOUND; -- print department - chief DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' || r_chief.first_name || ',' || r_chief.last_name); END LOOP;
Closing PL/SQL Cursor
You should always close the cursor when it is no longer used. Otherwise, you will have a memory leak in your program, which is not expected.
To close a cursor, you use
CLOSE statement as follows:
And here is an example of closing the
A complete PL/SQL Cursor Example
The following is a complete example of cursor for printing a list of chief and name of departments as follows:
SET SERVEROUTPUT ON SIZE 1000000; DECLARE -- declare a cursor CURSOR cur_chief IS SELECT first_name, last_name, department_name FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id; r_chief cur_chief%ROWTYPE; BEGIN OPEN cur_chief; LOOP -- fetch information from cursor into record FETCH cur_chief INTO r_chief; EXIT WHEN cur_chief%NOTFOUND; -- print department - chief DBMS_OUTPUT.PUT_LINE(r_chief.department_name || ' - ' || r_chief.first_name || ',' || r_chief.last_name); END LOOP; -- close cursor cur_chief CLOSE cur_chief; END; /
PL/SQL Cursor Attributes
These are the main attributes of a PL/SQL cursor and their descriptions.
|cursor_name%ROWCOUNT||returns the number of records fetched from the cursor |
In this tutorial, you’ve learned how to use PL/SQL Cursor to loop through a set of rows with all necessary steps that need to be done including