Let's take a look at the different ways you can define and use cursors (pointers to SQL result sets) in PL/SQL, including: implicit cursor, explicit cursor, cursor expressions, cursor variables, DBMS_SQL cursor handles.
Check out the LiveSQL script that demonstrates the points made in this post.
SELECT-INTO (aka, Implicit Cursor)
The SELECT-INTO statement is called an implicit cursor, because we write the SELECT statement, and the PL/SQL engine takes care of all the cursor operations for us implicitly: open, fetch, close.
SELECT-INTO is the best (smallest amount of code, best performance) way to fetch a single row.
Here's an example, based on the standard HR schema.
PACKAGE BODY employee_mgr IS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN hr.employees%ROWTYPE IS onerow_rec hr.employees%ROWTYPE; BEGIN SELECT * INTO onerow_rec FROM employees WHERE employee_id = employee_id_in; RETURN onerow_rec; END; END;SELECT-INTO famously can raise (at least) two exception:
- NO_DATA_FOUND - zero rows were found for the specified WHERE clause.
- TOO_MANY_ROWS - two or more rows were found for the specified WHERE clause.
So usually when I write functions that fetch single rows (or individual column values from a row), I write something more like this:
PACKAGE BODY employee_mgr IS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN hr.employees%ROWTYPE IS onerow_rec hr.employees%ROWTYPE; BEGIN SELECT * INTO onerow_rec FROM employees WHERE employee_id = employee_id_in; RETURN onerow_rec; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN /* Replace with your logging routine, such as logger.log_error */ log_error ('employee_mgr.onerow', 'Too many rows for '|| employee_id_in); END; END;SELECT-INTO Documentation
CURSOR xxx IS (aka, Explicit Cursor)
With an explicit cursor, you associate a SELECT statement with an cursor that you declare in a declaration section or package specification. You can even define a parameter list for your cursor, just as you would a function. Then you get to control all aspects or cursor operation: open, fetch, close. If that's what you want. Generally do not use explicit cursors for single row lookups; implicits are simpler and faster.
But because you have total control, you also have lots of flexibility, Takes a look at some examples. First, I create and populate a table:
CREATE TABLE endangered_species ( common_name VARCHAR2 (100), species_name VARCHAR2 (100) ) / BEGIN INSERT INTO endangered_species VALUES ('Amur Leopard', 'Panthera pardus orientalis'); INSERT INTO endangered_species VALUES ('Hawksbill Turtle', 'Eretmochelys imbricata'); INSERT INTO endangered_species VALUES ('Javan Rhino', 'Rhinoceros sondaicus'); COMMIT; END;I can then define and use an explicit cursor as follows:
DECLARE CURSOR species_cur IS SELECT * FROM endangered_species ORDER BY common_name; l_species species_cur%ROWTYPE; BEGIN OPEN species_cur; FETCH species_cur INTO l_species; CLOSE species_cur; END;Notice that I define my record based on the cursor, not the underlying table. That way, if the SELECT list ever changes in the cursor's query, I don't have to change the record declaration. It's anchored back to the cursor.
Then I open, fetch, close. That block shows the basic operations, but a reminder: if all you are doing is fetching a single row, once, like this, use SELECT-INTO.
There is, however, more that I can do. Here's an example of a cursor with a parameter:
DECLARE CURSOR species_cur (filter_in IN VARCHAR2) IS SELECT * FROM endangered_species WHERE species_name LIKE filter_in ORDER BY common_name; l_species species_cur%ROWTYPE; BEGIN OPEN species_cur ('%u%'); FETCH species_cur INTO l_species; CLOSE species_cur; /* Use same cursor a second time, avoiding copy-paste of SQL */ OPEN species_cur ('%e%'); FETCH species_cur INTO l_species; CLOSE species_cur; /* I can even use it in a cursor FOR loop */ FOR rec IN species_cur ('%u%') LOOP DBMS_OUTPUT.PUT_LINE (rec.common_name); END LOOP; END;So you can see that I can reuse my explicit cursor, but I cannot reuse an implicit cursor in the same way. With an implicit, I would have to put the SELECT-INTO inside a function and call the function. But, hey, that's what I recommend you do anyway! :-)
Another nice feature of the explicit cursor is that you can declare the cursor in the specification, but not show the SQL. Then you define the cursor (including the SELECT) in the package body - as shown in next step. This way, you hide the details of the query and "force" the user of the cursor to simply rely on its documented specification.
CREATE PACKAGE species_pkg IS CURSOR species_cur RETURN endangered_species%ROWTYPE; END; / CREATE PACKAGE BODY species_pkg IS CURSOR species_cur RETURN endangered_species%ROWTYPE IS SELECT * FROM endangered_species ORDER BY common_name; END; /Explicit Cursor Documentation
Cursor FOR Loop
The cursor FOR Loop is one of my favorite PL/SQL features. No need to open, fetch, close. Just tell the PL/SQL engine you want to work with each row returned by the query. Plus, with your optimization level set to 2 (the default) or higher, this code is automatically optimized to return 100 rows with each fetch (similar to BULK COLLECT).
You can embed the SELECT statement within the loop header, or you can declare an explicit cursor and reference it by name in the loop header. That means you can, again, parameterize the cursor and reuse it in multiple loops.
BEGIN FOR rec IN ( SELECT * FROM endangered_species ORDER BY common_name) LOOP DBMS_OUTPUT.put_line (rec.common_name); END LOOP; END; / DECLARE CURSOR species_cur IS SELECT * FROM endangered_species ORDER BY common_name; PROCEDURE start_conservation_effort IS BEGIN DBMS_OUTPUT.put_line ('Remove human presence'); END; BEGIN FOR rec IN species_cur LOOP DBMS_OUTPUT.put_line (rec.common_name); END LOOP; FOR rec IN species_cur LOOP start_conservation_effort; END LOOP; END; /Things to keep in mind with cursor FOR loops:
- Never use a cursor FOR loop to fetch a single row. It's a lazy way to avoid declaring the INTO variable or record, and that's bad lazy.
- If you need to iterate through rows of data but then conditionally exit the loop under certain data conditions, use a WHILE or simple loop with an explicit cursor. Why? Because...
- Any kind of FOR loop is saying, implicitly, "I am going to execute the loop body for all iterations defined by the loop header." (N through M or SELECT) Conditional exits means the loop could terminate in multiple ways, resulting in code that is hard to read and maintain.
A cursor variable is, well, just that: a variable pointing back to a cursor/result set. Some really nice aspects of cursor variables, demonstrated in this package: you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL); you can pass the cursor variable as a parameter or function RETURN value (specifically: you can pass a cursor variable back to a host language like Java for consumption).
Here's a package and script that demonstrates several cursor variable features
CREATE OR REPLACE PACKAGE refcursor_pkg IS /* Use this "strong" REF CURSOR to declare cursor variables whose queries return data from the endangered_species table. */ TYPE endangered_species_t IS REF CURSOR RETURN endangered_species%ROWTYPE; /* Use a "weak" REF CURSOR to declare cursor variables whose queries return any number of columns. Or use the pre-defined SYS_REFCURSOR, see example below. */ TYPE weak_t IS REF CURSOR; FUNCTION filtered_species_cv (filter_in IN VARCHAR2) RETURN endangered_species_t; /* Return data from whatever query is passed as an argument. */ FUNCTION data_from_any_query_cv (query_in IN VARCHAR2) RETURN weak_t; /* Return data from whatever query is passed as an argument. But this time, use the predefined weak type. */ FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2) RETURN SYS_REFCURSOR; END refcursor_pkg;
CREATE OR REPLACE PACKAGE BODY refcursor_pkg IS FUNCTION filtered_species_cv (filter_in IN VARCHAR2) RETURN endangered_species_t IS l_cursor_variable endangered_species_t; BEGIN IF filter_in IS NULL THEN OPEN l_cursor_variable FOR SELECT * FROM endangered_species; ELSE OPEN l_cursor_variable FOR SELECT * FROM endangered_species WHERE common_name LIKE filter_in; END IF; RETURN l_cursor_variable; END filtered_species_cv; FUNCTION data_from_any_query_cv (query_in IN VARCHAR2) RETURN weak_t IS l_cursor_variable weak_t; BEGIN OPEN l_cursor_variable FOR query_in; RETURN l_cursor_variable; END data_from_any_query_cv; FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2) RETURN SYS_REFCURSOR IS l_cursor_variable SYS_REFCURSOR; BEGIN OPEN l_cursor_variable FOR query_in; RETURN l_cursor_variable; END data_from_any_query_cv2; END refcursor_pkg;
DECLARE l_objects refcursor_pkg.endangered_species_t; l_object endangered_species%ROWTYPE; BEGIN l_objects := refcursor_pkg.filtered_species_cv ('%u%'); LOOP FETCH l_objects INTO l_object; EXIT WHEN l_objects%NOTFOUND; DBMS_OUTPUT.put_line (l_object.common_name); END LOOP; CLOSE l_objects; END;
/Bottom line: Once you've got a cursor variable, you can use all the familiar features of explicit cursors with them: fetch, close, check cursor attribute values.
Cursor Variable Documentation
A cursor expression, in essence, converts a subquery (SELECT statement) into a nested cursor (cursor variable).
Switching back to the HR schema, notice my use of CURSOR inside the all_in_one_cur. My INTO can then deposit the set return by CURSOR directly into a cursor variable defined in my PL/SQL block. I can then use standard cursor processing to fetch rows from that cursor variable.
CREATE OR REPLACE PROCEDURE cursor_expression_demo (location_id_in NUMBER) IS
/* Notes on CURSOR expression: 1. The query returns only 2 columns, but the second column is a cursor that lets us traverse a set of related information. 2. Queries in CURSOR expression that find no rows do NOT raise NO_DATA_FOUND. */ CURSOR all_in_one_cur IS SELECT l.city, CURSOR (SELECT d.department_name, CURSOR (SELECT e.last_name FROM hr.employees e WHERE e.department_id = d.department_id) AS ename FROM hr.departments d WHERE l.location_id = d.location_id) AS dname FROM hr.locations l WHERE l.location_id = location_id_in; department_cur SYS_REFCURSOR; employee_cur SYS_REFCURSOR; v_city hr.locations.city%TYPE; v_dname hr.departments.department_name%TYPE; v_ename hr.employees.last_name%TYPE; BEGIN OPEN all_in_one_cur; LOOP FETCH all_in_one_cur INTO v_city, department_cur; EXIT WHEN all_in_one_cur%NOTFOUND; -- Now I can loop through deartments and I do NOT need to -- explicitly open that cursor. Oracle did it for me. LOOP FETCH department_cur INTO v_dname, employee_cur; EXIT WHEN department_cur%NOTFOUND; -- Now I can loop through employee for that department. -- Again, I do need to open the cursor explicitly. LOOP FETCH employee_cur INTO v_ename; EXIT WHEN employee_cur%NOTFOUND; DBMS_OUTPUT.put_line (v_city || ' ' || v_dname || ' ' || v_ename); END LOOP; /* Not necessary; automatically closed with CLOSE all_in_one_cur See comment by Albert below. CLOSE employee_cur; */ END LOOP; /* Not necessary; automatically closed with CLOSE all_in_one_cur. See comment by Albert below. CLOSE department_cur; */ END LOOP; CLOSE all_in_one_cur; END; / BEGIN cursor_expression_demo (1700); END; /Cursor expressions are also commonly used in streaming and pipelined table functions. Here's an example:
INSERT INTO tickertable SELECT * FROM TABLE (stockpivot (CURSOR (SELECT * FROM stocktable)))Cursor Expression Documentation
DBMS_SQL Cursor Handle
Most dynamic SQL requirements can be met with EXECUTE IMMEDIATE (native dynamic SQL). Some of the more complicated scenarios, however, like method 4 dynamic SQL (variable number of elements in SELECT list and/or variable number of bind variables) are best implemented by DBMS_SQL. You allocate a cursor handle and then all subsequent operations reference that cursor handle. Here's an example:
CREATE OR REPLACE PROCEDURE show_common_names (table_in IN VARCHAR2) IS l_cursor PLS_INTEGER := DBMS_SQL.open_cursor (); l_feedback PLS_INTEGER; l_name endangered_species.common_name%TYPE; BEGIN DBMS_SQL.parse (l_cursor, 'select common_name from ' || table_in, DBMS_SQL.native); DBMS_SQL.define_column (l_cursor, 1, 'a', 100); l_feedback := DBMS_SQL.execute (l_cursor); DBMS_OUTPUT.put_line ('Result=' || l_feedback); LOOP EXIT WHEN DBMS_SQL.fetch_rows (l_cursor) = 0; DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_name); DBMS_OUTPUT.put_line (l_name); END LOOP; DBMS_SQL.close_cursor (l_cursor); END; / BEGIN show_common_names ('ENDANGERED_SPECIES'); END; /
Almost all code in this blog post may be accessed and executed on LiveSQL.