Mastering PL SQL Cursor: A Comprehensive Guide

PL/SQL cursors are essential tools for database developers. Cursor in PL/SQL allow you to manage and process data returned by SQL queries row by row. This article explores PL SQL cursor in detail.

What is a PL/SQL Cursor?

A PL SQL cursor is a pointer to a memory area. This area stores the result set of a SQL query. In essence, it lets you traverse the result set. You manipulate data row by row.

Therefore, cursors offer granular control over data processing.

Types of PL/SQL Cursors

PL SQL provides two primary cursor types:

• Implicit Cursors: SQL statement creates the implicit cursor automatically.
• Explicit Cursors: You define these manually(explicit) for data processing from sql query.

Implicit PL SQL Cursor

Oracle creates implicit cursors for all SQL statements. However, you cannot directly control them. They manage single-row operations.

For example, INSERT, UPDATE, and DELETE statements use implicit cursors.

Explicit PL SQL Cursor

You declare and manage explicit cursors. Consequently, you gain more control of the cursors. They handle multi-row queries. Furthermore, explicit cursors are vital for complex data manipulations.

PL SQL CURSOR Syntax

Syntax for declaring, opening, fetching from, and closing a PL/SQL cursor, along with explanations.

Declaring a Cursor

DECLARE
  CURSOR cursor_name [(parameter1 data_type, parameter2 data_type, ...)] IS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

• DECLARE: Keyword to start the declaration section.
• CURSOR cursor_name: Declares a cursor named cursor_name.
• [(parameter1 data_type, parameter2 data_type, …)]: Optional parameters for the cursor, allowing dynamic queries.
• SELECT column1, column2, … FROM table_name WHERE condition: The SQL query that the cursor will execute.

Opening a Cursor

BEGIN
  OPEN cursor_name [(value1, value2, ...)];

• OPEN cursor_name: Opens the cursor, executing the associated SELECT statement.
• [(value1, value2, …)]: If the cursor has parameters, you provide values here.

Fetching Data from a Cursor

FETCH cursor_name INTO variable1, variable2, ...;

• FETCH cursor_name INTO variable1, variable2, …: Retrieves a row from the result set and stores the column values into the specified variables.

Closing a Cursor

CLOSE cursor_name;

• CLOSE cursor_name: Releases the resources associated with the cursor.

Cursor Attributes

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ROWCOUNT
cursor_name%ISOPEN

• %FOUND: Returns TRUE if the last FETCH returned a row.
• %NOTFOUND: Returns TRUE if the last FETCH did not return a row.
• %ROWCOUNT: Returns the number of rows fetched so far.
• %ISOPEN: Returns TRUE if the cursor is1 currently open.  

Declaring an Explicit PL/SQL Cursor

You must declare a cursor before using it. This declaration specifies the SQL query.

First, you use the CURSOR keyword. Then, you name the cursor. Finally, you define the SELECT statement.

DECLARE
  CURSOR c_employees 
  IS
  SELECT employee_id, first_name, last_name
    FROM employees;
BEGIN
END;

In the above code snippet c_employees is the cursor name.

Opening a PL/SQL Cursor

After declaration, you must open the cursor. The open statement performs this action.

Specifically, the open statement executes the select query.

BEGIN
   OPEN c_employees;
END;

Thus, the result set becomes available for use through PL/SQL cursors for further data processing.

Fetching Data from a PL SQL Cursor

The FETCH statement retrieves rows from the PL SQL cursor. You store the retrieved data into variables.

Next, you use the FETCH statement repeatedly. Moreover, you process each row.

DECLARE
   CURSOR c_employees
   IS
   SELECT employee_id, first_name, last_name
     FROM employees;
   l_emp_id     employees.employee_id%TYPE;
   l_first_name employees.first_name%TYPE;
   l_last_name  employees.last_name%TYPE;
BEGIN
   OPEN c_employees;
   LOOP
      FETCH c_employees INTO v_emp_id, v_first_name, v_last_name;
      EXIT WHEN c_employees%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(l_first_name || ' ' || l_last_name);
   END LOOP;
   CLOSE c_employees;
END;

In the above example, data is fetched into l_emp_id, l_first_name, and l_last_name.

Closing a PL/SQL Cursor

You must close the PL SQL cursor after processing data. The CLOSE statement releases resources. Indeed, closing the cursor is critical for efficiency.

PL/SQL Cursor Attributes

PL SQL provides cursor attributes. These attributes provide information about the cursor’s state.

• %FOUND: Returns TRUE if a row was fetched successfully.
• %NOTFOUND: Returns TRUE if no row was fetched.
• %ROWCOUNT: Returns the number of rows fetched.
• %ISOPEN: Returns TRUE if the cursor is open.

These attributes help you control the cursor’s flow.

Using %FOUND and %NOTFOUND

You can use %FOUND and %NOTFOUND to check fetch status. For instance, you can use them in a WHILE/infinite loop.

BEGIN
   OPEN c_employees;
   LOOP
      FETCH c_employees INTO v_emp_id, v_first_name, v_last_name;
      EXIT WHEN c_employees%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(l_first_name || ' ' || l_last_name);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Total employees fetched: ' ||c_employees%ROWCOUNT);
   CLOSE c_employees;
END;

Using %ROWCOUNT

%ROWCOUNT provides the number of fetched rows. Therefore, you can track the progress. As shown in the above example c_employees%ROWCOUNT used to print the total employees fetched by the cursor.

Using %ISOPEN

%ISOPEN checks if the cursor is open. Consequently, you prevent errors.

IF c_employees%ISOPEN THEN
   CLOSE c_employees;
END IF;

PL/SQL Cursor with Parameters

In the example here you saw no parameter used during PL SQL cursor declaration. You can pass parameters to the SQL query in the cursor. This allows you to create dynamic query conditions.

First, you declare parameters in the cursor declaration. Then, you pass values when opening the cursor.

DECLARE
   CURSOR c_employees(p_dept_id 
           employees.department_id%TYPE) 
   IS
   SELECT employee_id, first_name, last_name
     FROM employees
    WHERE department_id = p_dept_id;
   l_emp_id employees.employee_id%TYPE;
   l_first_name employees.first_name%TYPE;
   l_last_name employees.last_name%TYPE;
BEGIN
   OPEN c_employees(90);
   LOOP
      FETCH c_employees INTO v_emp_id, v_first_name, 
          v_last_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || 
       v_last_name);
   END LOOP;
   CLOSE emp_cursor;
END;

Here, p_dept_id is the parameter.

Cursor FOR Loops

Cursor FOR loops simplify cursor processing operation in PL/SQL. They automatically open, fetch, and close cursors. Instead of manual handling, you use a FOR loop. You can get more details of PL/SQL for loop in our earlier article.

DECLARE
   CURSOR c_employees 
   IS
   SELECT employee_id, first_name, last_name
     FROM employees;
BEGIN
   FOR emp_rec IN c_employees 
   LOOP
      DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
   END LOOP;
END;

This loop implicitly handles cursor operations.

PL/SQL Cursor Variables (REF CURSOR)

Cursor variables are pointers to result sets. They offer flexibility in handling result sets. Specifically, they are useful for dynamic queries.

DECLARE
  TYPE emp_cur_type IS REF CURSOR;
  emp_cv emp_cur_type;
  v_emp_id employees.employee_id%TYPE;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
BEGIN
  OPEN emp_cv FOR SELECT employee_id, first_name, last_name FROM employees;
  LOOP
    FETCH emp_cv INTO v_emp_id, v_first_name, v_last_name;
    EXIT WHEN emp_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name);
  END LOOP;
  CLOSE emp_cv;
END;
/

Thus, you can pass cursor variables as parameters.

Nested PL SQL Cursor

Nested PL SQL cursor use one cursor within another. This allows for complex data relationships.

For example, you can fetch employee details for each department.

DECLARE
  CURSOR c_depts IS
    SELECT department_id, department_name
    FROM departments;
  CURSOR c_employees(p_dept_id departments.department_id%TYPE) IS
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = p_dept_id;
BEGIN
   FOR dept_rec IN c_depts 
   LOOP
      DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name);
      FOR emp_rec IN c_employees(dept_rec.department_id) 
      LOOP
         DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.first_name || ' ' || 
          emp_rec.last_name);
      END LOOP;
   END LOOP;
END;

This approach enables hierarchical data processing.

PL SQL Cursor Best Practices

• Close cursors promptly to free up resources.
• Use cursor attributes for better control.
• Leverage cursor FOR loops to simplify operations.
• Parameterize cursors for dynamic queries.
• Avoid fetching all rows into memory at once to save resources.
• Handle exceptions during cursor operations.
• Use cursor variables for greater flexibility.
• Optimize your SQL statements within cursors for performance.

Common PL SQL Cursor Errors

1. Cursor not open: Attempting to fetch data without opening the cursor causes errors. Always ensure the cursor is open before performing any operations.
2. Fetch after close: Trying to fetch data after closing the cursor leads to an error. Make sure the cursor remains open during data retrieval.
3. Incorrect variable types: Mismatched data types between cursor variables and the columns they fetch can cause errors. Always verify data types before assignment.
4. Missing EXIT conditions: Forgetting to include an exit condition in loops can cause infinite fetching. Use EXIT WHEN with %NOTFOUND to prevent this.
5. Resource leaks due to unclosed cursors: Failing to close cursors after use can consume system resources. Always include a CLOSE statement.

Advanced PL/SQL Cursor Techniques

Bulk Collect

Use BULK COLLECT to fetch multiple rows at once, reducing context switches between SQL and PL/SQL engines.

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emp_data emp_table;
BEGIN
  SELECT * BULK COLLECT INTO v_emp_data FROM employees;
  FOR i IN 1..v_emp_data.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_data(i).first_name || ' ' || v_emp_data(i).last_name);
  END LOOP;
END;

FORALL

The FORALL statement allows bulk DML operations, improving performance when inserting or updating large datasets.

DECLARE
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emp_data emp_table;
BEGIN
  SELECT * BULK COLLECT INTO v_emp_data FROM employees;
  FORALL i IN 1..v_emp_data.COUNT
  UPDATE employees SET salary = salary * 1.10 WHERE employee_id = v_emp_data(i).employee_id;
  DBMS_OUTPUT.PUT_LINE('Salaries updated for selected employees');
END;

Pipelined Table Functions

Pipelined table functions return result sets row by row, which is efficient for large data volumes.

Dynamic SQL with Cursors

Dynamic SQL allows building queries at runtime. Using cursors with dynamic SQL increases flexibility, especially for complex and variable queries.

DECLARE
  TYPE ref_cursor IS REF CURSOR;
  c_employees ref_cursor;
  v_first_name employees.first_name%TYPE;
BEGIN
   OPEN c_employees FOR 'SELECT first_name FROM employees WHERE department_id = :dept' USING 10;
   LOOP
      FETCH c_employees INTO v_first_name;
      EXIT WHEN c_employees%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_first_name);
  END LOOP;
  CLOSE c_employees;
END;

Conclusion

PL SQL cursor are powerful tools. They allow you to manipulate data row by row. Therefore, they are essential for complex database operations. Moreover, mastering cursors enhances your PL/SQL development skills. Finally, practicing cursor techniques leads to efficient and robust applications.