Working with cursors and dynamic queries in PL/SQL

Part 12 in a series of articles on understanding and using PL/SQL for accessing Oracle Database

PL/SQL is one of the core technologies at Oracle and is essential to leveraging the full potential of Oracle Database. PL/SQL combines the relational data access capabilities of the Structured Query Language with a flexible embedded procedural language, and it executes complex queries and programmatic logic run inside the database engine itself. This enhances the agility, efficiency, and performance of database-driven applications.

Steven Feuerstein, one of the industry’s best-respected and most prolific experts in PL/SQL, wrote a 12-part tutorial series on the language. Those articles, first published in 2011, have been among the most popular ever published on the Oracle website and continue to find new readers and enthusiasts in the database community. Beginning with the first installment, the entire series is being updated and republished; please enjoy!

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, the last in my 12-part PL/SQL tutorial, focuses on the most-common ways programmers execute SELECT statements. Those are

• Using the SELECT-INTO statement
• Fetching from an explicit cursor
• Using a cursor FOR loop
• Using EXECUTE IMMEDIATE for dynamic queries
• Using cursor variables

At the end of the article, you’ll see some quick tips to help you figure out which of these techniques you should use for different scenarios.

The SELECT-INTO cursor

SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is the following, 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.

SELECT select_list INTO variable_list FROM remainder_of_query;

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):
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  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;

  
  If there is a row in the employees table with ID 138, this block will display the last name of that employee. If there is no such row, the block will fail with an unhandled NO_DATA_FOUND exception. Assuming that a unique index is defined on the employee_id column, this block will never raise the TOO_MANY_ROWS exception.
• Fetch an entire row from the employees table for a specific employee ID:
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  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;

  
  Again, if an employee exists for that ID, the last name will be displayed. In this case, I declare a record based on the employees table and fetch all columns (with a SELECT *) into that record for the specified row.
• Fetch columns from different tables:
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  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 Table 2 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;

Table 2: Operations in the block at specified line numbers

Here are four things to keep in mind when working with explicit cursors:

• If the query does not identify any rows, Oracle Database will not raise NO_DATA_FOUND. Instead, the cursor_name%NOTFOUND attribute will return TRUE.
• Your query can return more than one row, and Oracle Database will not raise TOO_MANY_ROWS.
• When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.
• When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. It is still, however, a good idea to explicitly close the cursor yourself. If the cursor is moved to a package, you will have the now necessary CLOSE already in place. And if it is local, then including a CLOSE statement will also show other developers and your manager that you are paying attention.

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 perhttps://blogs.oracle.com/connect/post/bulk-processing-with-bulk-collect-and-forall" target="_blank">Bulk data processing with BULK COLLECT and FORALL in PL/SQL”). 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;

As you can see, instead of SELECT-INTO, I used EXECUTE IMMEDIATE-INTO and constructed the SELECT statement from the arguments passed to the function. Here’s an example of calling the function:

BEGIN
   DBMS_OUTPUT.put_line (
      single_number_value (
                'employees',
                'salary',
                'employee_id=138'));
END;

As with SELECT-INTO, EXECUTE IMMEDIATE-INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found.

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;

And here’s an example of calling the procedure for the standard employees table:

BEGIN
   show_number_values (
      'employees',
      'salary',
      'department_id = 10 
       order by salary desc');
END;

The following are the two rows of output:

4400

3200

A note of caution regarding dynamic SQL and the preceding examples in particular: Whenever you concatenate text to execute a dynamically executed statement, you run the risk of SQL injection. This occurs when a malicious user “injects,” or inserts into the statement, code that changes the behavior of that SQL statement.

For advice on avoiding SQL injection–based security breaches, check out “How to write SQL injection proof PL/SQL.”

Cursor variables

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:

• Pass a cursor variable back to the host environment that called the program unit—the result set can be “consumed” for display or other processing.
• Construct a result set inside a function and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.
• Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique.

A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article. Instead, I will show the basic syntax for working with cursor variables and identify situations in which you might consider using this feature.

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. And Table 3 describes the operations at specified line numbers.

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;

Table 3: Operations at specified line numbers

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;

As you can see, all the information about the query being opened is “hidden” behind the function header. You simply ask to get the “names for” a given table. The function picks the appropriate SELECT statement, opens the cursor variable for that statement, and then returns the variable pointing to that result set.

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, for example:

1. FETCH from the cursor (variable) INTO one or more variables (I can even FETCH-BULK COLLECT INTO with a cursor variable, populating a collection with multiple rows).
2. Check the %NOTFOUND attribute of the cursor variable to see if I am done fetching all rows.
3. CLOSE the cursor variable when done.

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;

And here is a block—virtually identical to the one that calls names_for, above—that displays all the salaries for employees in department 10:

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;

Move SELECT-INTOs into functions

PL/SQL developers frequently need to retrieve data for a single row in a table, specified (usually) by a primary key value, and they often find themselves writing the same primary key lookup again and again. A much better approach is to move each of your SELECT-INTO queries into a function whose sole purpose is to serve up the requested row. So instead of this

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;

You would first create a function

CREATE OR REPLACE FUNCTION row_for_employee_id (
   employee_id_in IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees e
    WHERE e.employee_id = 
       row_for_employee_id.employee_id_in;

   RETURN l_employee;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;

Then, the anonymous block for your primary key lookup would be

DECLARE
   l_employee   employees%ROWTYPE;
BEGIN
   l_employee := 
      row_for_employee_id (138);

   DBMS_OUTPUT.put_line (
      l_employee.last_name);
END;

Best of all, the next time you need to get a row from the employees table for an ID, you’ll just call the function.

There are two big advantages to this approach:

• Your productivity increases, because you can write less code and rely on prebuilt, pretested, reusable programs.
• If you ever need to change the way you look up that single row, you’ll make the change in one place (the “single point of definition”) and all programs that call the function will immediately use the improved version.

Note that I included in the function an exception handler that traps NO_DATA_FOUND and simply returns a NULL record. During execution of a SELECT-INTO, the absence of data is often not actually an error but, rather, just a data condition. So it is quite common to trap the exception and return an indicator that no row was found. (NULL is usually, but not necessarily, a good indicator of this state of affairs.) The programmer who calls the function has to decide how to treat the NO_DATA_FOUND condition.

Choosing the right way to query

You’ve seen 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:

• When you are fetching a single row, use SELECT-INTO or EXECUTE IMMEDIATE-INTO (if your query is dynamic). Do not use an explicit cursor or a cursor FOR loop.
• When you are fetching all the rows from a query, use a cursor FOR loop unless the body of the loop executes one or more DML statements (INSERT, UPDATE, DELETE, or MERGE). In such a case, you will want to switch to BULK COLLECT and FORALL.
• Use an explicit cursor when you need to fetch with BULK COLLECT, but limit the number of rows returned with each fetch.
• Use an explicit cursor when you are fetching multiple rows but might conditionally exit before all rows are fetched.
• Use a cursor variable when the query you are fetching from varies at runtime (but isn’t necessarily dynamic) and especially when you need to pass a result back to a non-PL/SQL host environment.
• Use EXECUTE IMMEDIATE to query data only when you cannot fully construct the SELECT statement while you write your code.

Dig deeper

• See the rest of the series.