Working with CursorsBy Steven Feuerstein
Part 12 in a series of articles on understanding and using PL/SQL
The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database. You must, of course, use the SQL language to access tables, and each time you do so, you use a cursor to get the job done. A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements. This article focuses on the most-common ways programmers execute SELECT statements in PL/SQL, namely
At the end of the article, I offer some quick tips to help you figure out which of these techniques you should use for different scenarios.
SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is
SELECT select_list INTO variable_list FROM remainder_of_query;
where remainder_of_query contains the list of tables or views, the WHERE clause, and other clauses of the query. The number and types of elements in the variable_list must match those of the select_list.
If the SELECT statement identifies more than one row to be fetched, Oracle Database will raise the TOO_MANY_ROWS exception. If the statement doesn’t identify any rows to be fetched, Oracle Database will raise the NO_DATA_FOUND exception.
Here are some examples of using SELECT-INTO:
Get the last name for a specific employee ID (the primary key in the employees table):
DECLARE l_last_name employees.last_name%TYPE; BEGIN SELECT last_name INTO l_last_name FROM employees WHERE employee_id = 138; DBMS_OUTPUT.put_line ( l_last_name); END;
Fetch an entire row from the employees table for a specific employee ID:
DECLARE l_employee employees%ROWTYPE; BEGIN SELECT * INTO l_employee FROM employees WHERE employee_id = 138; DBMS_OUTPUT.put_line ( l_employee.last_name); END;
Fetch columns from different tables:
DECLARE l_last_name employees.last_name%TYPE; l_department_name departments.department_name%TYPE; BEGIN SELECT last_name, department_name INTO l_last_name, l_department_name FROM employees e, departments d WHERE e.department_id=d.department_id AND e.employee_id=138; DBMS_OUTPUT.put_line ( l_last_name || ' in ' || l_department_name); END;
In this case, I need more than one column value but not all the column values in either or both of the tables. So I declare two variables and fetch the two column values into those variables.
What happens if the list of variables in the INTO clause does not match the SELECT list of the query? You will see one of the error messages shown in Table 1.
Table 1: Possible error messages if INTO and SELECT lists do not match
Fetching from Explicit Cursors
A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised).
You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.
Suppose I need to write a block that fetches employees in ascending salary order and gives them a bonus from a total pool of funds by calling the assign_bonus procedure, whose header is
PROCEDURE assign_bonus ( employee_id_in IN employees.employee_id%TYPE, bonus_pool_io IN OUT INTEGER)
Each time assign_bonus is called, the procedure subtracts the bonus given from the total and returns that reduced total. When that bonus pool is exhausted, it stops fetching and commits all changes.
Listing 1 includes a block that uses an explicit cursor to implement this logic, and it describes the operations in the block at specified line numbers.
Code Listing 1: Block and description of explicit cursor implementation
1 DECLARE 2 l_total INTEGER := 10000; 3 4 CURSOR employee_id_cur 5 IS 6 SELECT employee_id 7 FROM plch_employees 8 ORDER BY salary ASC; 9 10 l_employee_id employee_id_cur%ROWTYPE; 11 BEGIN 12 OPEN employee_id_cur; 13 14 LOOP 15 FETCH employee_id_cur INTO l_employee_id; 16 EXIT WHEN employee_id_cur%NOTFOUND; 17 18 assign_bonus (l_employee_id, l_total); 19 EXIT WHEN l_total <= 0; 20 END LOOP; 21 22 CLOSE employees_cur; 23 END;
Here are some things to keep in mind when working with explicit cursors:
Using the Cursor FOR Loop
The cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With a cursor FOR loop, the body of the loop is executed for each row returned by the query.
The following block uses a cursor FOR loop to display the last names of all employees in department 10:
BEGIN FOR employee_rec IN ( SELECT * FROM employees WHERE department_id = 10) LOOP DBMS_OUTPUT.put_line ( employee_rec.last_name); END LOOP; END;
You can also use a cursor FOR loop with an explicitly declared cursor:
DECLARE CURSOR employees_in_10_cur IS SELECT * FROM employees WHERE department_id = 10; BEGIN FOR employee_rec IN employees_in_10_cur LOOP DBMS_OUTPUT.put_line ( employee_rec.last_name); END LOOP; END;
The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).
Best of all, Oracle Database automatically optimizes cursor FOR loops to perform similarly to BULK COLLECT queries (covered in “Bulk Processing with BULK COLLECT and FORALL,” in the September/October 2012 issue of Oracle Magazine). So even though your code looks as if you are fetching one row at a time, Oracle Database will actually fetch 100 rows at a time—and enable you to work with each row individually.
Dynamic Queries with EXECUTE IMMEDIATE
Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.
Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement. And querying data is the easiest dynamic SQL operation of all!
You can fetch a single row or multiple rows. Here is a generic function that fetches the value of a numeric column in any table, for the specified WHERE clause:
CREATE OR REPLACE FUNCTION single_number_value ( table_in IN VARCHAR2, column_in IN VARCHAR2, where_in IN VARCHAR2) RETURN NUMBER IS l_return NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT ' || column_in || ' FROM ' || table_in || ' WHERE ' || where_in INTO l_return; RETURN l_return; END;
BEGIN DBMS_OUTPUT.put_line ( single_number_value ( 'employees', 'salary', 'employee_id=138')); END;
You can also use EXECUTE IMMEDIATE to fetch multiple rows of data, which means that you will populate a collection, so you must use BULK COLLECT. The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause:
CREATE OR REPLACE PROCEDURE show_number_values ( table_in IN VARCHAR2, column_in IN VARCHAR2, where_in IN VARCHAR2) IS TYPE values_t IS TABLE OF NUMBER; l_values values_t; BEGIN EXECUTE IMMEDIATE 'SELECT ' || column_in || ' FROM ' || table_in || ' WHERE ' || where_in BULK COLLECT INTO l_values; FOR indx IN 1 .. l_values.COUNT LOOP DBMS_OUTPUT.put_line (l_values (indx)); END LOOP; END;
BEGIN show_number_values ( 'employees', 'salary', 'department_id = 10 order by salary desc'); END;
For advice on avoiding SQL injection–based security breaches, check out “How to Write SQL Injection Proof PL/SQL".
A cursor variable is, as you might guess from its name, a variable that points to a cursor or a result set. Unlike with an explicit cursor, you can pass a cursor variable as an argument to a procedure or a function. There are several excellent use cases for cursor variables, including the following:
Cursor variables can be used with either embedded (static) or dynamic SQL. Listing 2 includes the names_for function, which returns a cursor variable that fetches either employee or department names, depending on the argument passed to the function.
Code Listing 2: Block and description of the names_for function, which returns a cursor variable
1 CREATE OR REPLACE FUNCTION names_for ( 2 name_type_in IN VARCHAR2) 3 RETURN SYS_REFCURSOR 4 IS 5 l_return SYS_REFCURSOR; 6 BEGIN 7 CASE name_type_in 8 WHEN 'EMP' 9 THEN 10 OPEN l_return FOR 11 SELECT last_name 12 FROM employees 13 ORDER BY employee_id; 14 WHEN 'DEPT' 15 THEN 16 OPEN l_return FOR 17 SELECT department_name 18 FROM departments 19 ORDER BY department_id; 20 END CASE; 21 22 RETURN l_return; 23 END names_for;
Here is a block that uses the names_for function to display all the names in the departments table:
DECLARE l_names SYS_REFCURSOR; l_name VARCHAR2 (32767); BEGIN l_names := names_for ('DEPT'); LOOP FETCH l_names INTO l_name; EXIT WHEN l_names%NOTFOUND; DBMS_OUTPUT.put_line (l_name); END LOOP; CLOSE l_names; END;
Once the cursor variable has been opened and passed back to the block, I use the same code with a cursor variable that I would use with an explicit cursor:
The OPEN-FOR statement is unique to cursor variables and enables me to specify at runtime, without having to switch to dynamic SQL, which data set will be fetched through the cursor variable.
Nevertheless, you can use OPEN-FOR with a dynamic SELECT statement. Here is a very simple example:
CREATE OR REPLACE FUNCTION numbers_from ( query_in IN VARCHAR2) RETURN SYS_REFCURSOR IS l_return SYS_REFCURSOR; BEGIN OPEN l_return FOR query_in; RETURN l_return; END numbers_from;
DECLARE l_salaries SYS_REFCURSOR; l_salary NUMBER; BEGIN l_salaries := numbers_from ( 'select salary from employees where department_id = 10'); LOOP FETCH l_salaries INTO l_salary; EXIT WHEN l_salaries%NOTFOUND; DBMS_OUTPUT.put_line (l_salary); END LOOP; CLOSE l_salaries; END;
Choosing the Right Way to Query
This article has shown that the PL/SQL language offers many different ways, ranging from the simplest SELECT-INTO implicit query to the much more complicated cursor variable, to use cursors to fetch data from relational tables into local variables.
Here are some guidelines to help you decide which technique to use: