Working with Cursors in Oracle PL/SQL

In this article I’ll explain several ways Cursors are used in PL/SQL. For basic details on cursors and its types please refer this blog.

There are multiple ways we can used SELECT statement in PL/SQL:

1. Using SELECT-INTO statement – Implicit Cursor: This is the simplest and best way to fetch single row result from a SELECT statement. Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that. The syntax is as follows:

DECLARE
	var_col1 VARCHAR2(100):= 'N';
	var_col2 VARCHAR2(100):= 'N';
BEGIN	
	SELECT col1,
	       col2
	INTO   var_col1,
	       var_col2
	FROM   table_name;
END;

The number and type of columns in variable list must match with that of the SELECT list. There are two possible scenarios where this query may go wrong. If the SELECT statement fetches more than one rows then it will throws TOO_MANY_ROWS exception. If the SELECT statement does not fetches any rows then NO_DATA_FOUND exception will be raised.

2. Fetching from explicit cursor: Here we explicitly declare a cursor and then perform an OPEN, FETCH and CLOSE the cursor and shown in the below code snippet.

DECLARE
    CURSOR cur_data IS
    (SELECT columns
     FROM   table
    );
	var_col1 cur_data%ROWTYPE;
BEGIN
	OPEN cur_data;
	LOOP
		FETCH  cur_data
		INTO   var_col1
		EXIT WHEN cur_data%NOTFOUND;
	END LOOP;
	
	CLOSE cur_data;
END;

When we declare a cursor in a package and the cursor is opened, it will always stay open until we explicitly close it or the session is terminated. But when the cursor is declared in a declaration section, Oracle Database will also automatically close it when the block in which it is declared terminates.

3. Using Cursor FOR loop: Using a cursor FOR loop, the body of the loop is executed for each row returned by the query as shown in the below code snippet.

DECLARE
    CURSOR cur_data IS
    (SELECT columns
     FROM   table
    );
BEGIN
	FOR col_rec IN cur_data
	LOOP
		DBMS_OUTPUT.PUT_LINE(col_rec.columns);
	END LOOP;
END;

4. Using Bulk Collect: We use cursor to process many rows of data. Using cursor will have performance issue while dealing with huge amount of data because cursor fetches one row at a time. Database has two engines the PL/SQL engine and the SQL engine. PL/SQL statements are run by the PL/SQL statement executor and SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

Using BULK COLLECT we can retrieve multiple rows with a single fetch, thereby improving the speed of data retrieval. Insert BULK COLLECT before the INTO keyword of your fetch operation, and then supply one or more collections after the INTO keyword to make use of bulk processing for queries as shown in the below example:

DECLARE
	TYPE info_student IS TABLE OF VARCHAR2(30);
	s_name info_student;
	BEGIN
		SELECT stud_name BULK COLLECT INTO s_name from student;
		FOR idx IN 1..s_name.COUNT
		LOOP
			DBMS_OUTPUT.PUT_LINE(idx||'-'||s_name(idx));
		END LOOP;
END;

BULK COLLECT can be used with all three types of collections: associative arrays, nested tables, and varrays. If we are fetching lots of rows, the collection that is being filled could consume too much session memory and raise an error. To help you avoid such errors, Oracle Database offers a LIMIT clause for BULK COLLECT.

5. Using Cursor Variables: A cursor variable is a variable that references to a cursor. It enables passing the result of a query between PL/SQL programs. Without a cursor variable, you have to fetch all data from a cursor, store it in a variable e.g., a collection, and pass this variable as an argument. With a cursor variable, you simply pass the reference to that cursor.

CREATE OR REPLACE FUNCTION get_names(
      in_manager_id IN employees.manager_id%TYPE)
   RETURN SYS_REFCURSOR
AS
   c_direct_reports SYS_REFCURSOR;
BEGIN
   OPEN c_direct_reports FOR 
   SELECT first_name
   FROM   employees 
   WHERE  manager_id = in_manager_id;

   RETURN c_direct_reports;
END;

Which way to use when?

• When we are fetching a single row, use SELECT-INTO or EXECUTE IMMEDIATE-INTO.
• When we 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, we will want to switch to BULK COLLECT and FORALL.
• Use an explicit cursor when we need to fetch with BULK COLLECT, but limit the number of rows returned with each fetch.
• Use an explicit cursor when we are fetching multiple rows but might conditionally exit before all rows are fetched.
• Use a cursor variable when the query we are fetching from varies at runtime.

That’s all about this article.

If you like this article, please like, comment, and share.

Request you to subscribe to my YouTube channel for regular updates.

Further readings:

Oracle PL/SQL Cursor: Implicit and Explicit

Creating Procedures, Functions, and Packages in PL/SQL

Error and Exception Handling In Oracle PL/SQL

Different types of Loops in Oracle PL/SQL

Creating schema and adding roles in an Oracle Autonomous Database