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:
- 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.
- 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.
- 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;
- 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.
- 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.