Implicit vs. Explicit Cursor: Which Should You Use?
"Hmmm," you might be saying to yourself, "a conversation about SQL queries in a post on conditional logic? That's strange." Ah, but maybe not so strange.
Join the DZone community and get the full member experience.
Join For FreeMy post on Conditional Logic in PL/SQL generated some interesting discussion concerning whether to use an implicit cursor (SELECT-INTO) or an explicit cursor (OPEN-FETCH-CLOSE) when fetching a single row of data.
"Hmmm," you might be saying to yourself, "a conversation about SQL queries in a post on conditional logic? That's strange."
Ah, but maybe not so strange. Because as one person put it in the comments:
Yes, this is true. You can use exception handling as a kind of conditional branching logic. But should you? Generally, no. It's a good idea to keep application logic out of exception sections. Developers don't expect or look for business logic in those sections. They are, after all, for dealing with exceptions: errors. So if you put lots of code in EXCEPTION WHEN clauses, they can be easily overlooked, making your code harder to debug and enhance.
But that question — and the recommendation to avoid the exception section — then led to other comments that led to a question we circle back to over and over again in the world of Oracle Database programming:
If I am fetching (at most) a single row, should I use an implicit cursor (SELECT-INTO) or an explicit cursor (OPEN-FETCH-CLOSE)?
How do those questions link up? Quite simply, if you use SELECT-INTO to fetch a single row, then if no row is found, the PL/SQL engine raise the NO_DATA_FOUND exception. If more than one row is found, TOO_MANY_ROWS is raised. Let me show you that before I continue (all code in this post available in LiveSQL).
First of all, for this blog post, I will work with the following table and data:
CREATE TABLE not_much_stuff (n NUMBER)
/
INSERT INTO not_much_stuff
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < 11
/Here's a block of code using implicit cursors and trapping exceptions, and displaying the error:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
BEGIN
DBMS_OUTPUT.put_line ('No rows found:');
BEGIN
SELECT n
INTO my_n
FROM not_much_stuff
WHERE n = -1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
DBMS_OUTPUT.put_line ('Too many rows found:');
BEGIN
SELECT n
INTO my_n
FROM not_much_stuff
WHERE n BETWEEN 1 AND 10;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
END;
/
No rows found:
ORA-01403: no data found
Too many rows found:
ORA-01422: exact fetch returns more than requested number of rowsSo, that's how implicit cursors work when it comes to an outcome that is anything but "got you your one row."
OK, back to the question of which to use: implicit or explicit?
As is so often, and frustratingly, the case, the answer is: it depends.
And the "depends" has to do with both performances of the two approaches and specific context in which you are writing the single row fetch code. So first...
Which Is Faster?
In each of the sections below (again, available on LiveSQL for you to run yourselves), I check the performance of implicit and explicit cursors for both finding a single row successfully and not finding any rows.
Note: In the code below, I assign values to the my_n variable and then display its value at the end of the block to ensure that the optimizer doesn't "zero out" my code because it doesn't actually do anything. Smart optimizer. And yes, the PL/SQL optimizer works on anonymous blocks as well as stored program units.
No data found, implicit cursor:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
BEGIN
FOR indx IN 1 .. 100000
LOOP
BEGIN
SELECT n
INTO my_n
FROM not_much_stuff
WHERE N = -1;
my_n := 100;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
my_n := 100;
END;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:06.372One row found, implicit cursor:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
BEGIN
FOR indx IN 1 .. 100000
LOOP
BEGIN
SELECT n
INTO my_n
FROM not_much_stuff
WHERE N = 1;
my_n := 100;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
my_n := 100;
END;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:04.703No data found, explicit cursor:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
CURSOR stuff_cur
IS
SELECT n
FROM not_much_stuff
WHERE n = -1;
BEGIN
FOR indx IN 1 .. 100000
LOOP
OPEN stuff_cur;
FETCH stuff_cur INTO my_n;
IF stuff_cur%NOTFOUND
THEN
my_n := 100;
END IF;
CLOSE stuff_cur;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:04.703One row found, explicit cursor:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
CURSOR stuff_cur
IS
SELECT n
FROM not_much_stuff
WHERE n = 1;
BEGIN
FOR indx IN 1 .. 100000
LOOP
OPEN stuff_cur;
FETCH stuff_cur INTO my_n;
IF stuff_cur%FOUND
THEN
my_n := 100;
END IF;
CLOSE stuff_cur;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:05.209So, the takeaway from these relatively superficial but reliably consistent performance comparisons:
- When the query successfully finds just one row,
SELECT-INTOis the most efficient approach. - When the query fails to find a row, the explicit cursor is more efficient.
The reason is simple, With the explicit cursor, you avoid the raising of an exception. And in PL/SQL, the raising and handling of an exception is relatively expensive. Combine that overhead with the desire to avoid application logic in the exception and you can see why developers would say in that same blog post:
And so now we come to the crux of the "it depends" answer to "Which should I use?"
Is your query usually going to successfully find a single row or not?
If you expect the query to find just one row most of the time, use SELECT-INTO but wrap it in its own function or nested block and trap NO_DATA_FOUND. In the handler, don't write application code; instead, set a variable's value so that the enclosing block can determine the next appropriate step.
If you expect the query to fail to find a row a lot of the time, then consider using an explicit cursor and the %NOTFOUND cursor attribute to identify a "no data found" scenario. Then take appropriate action.
Here are some patterns based on the above recommendation and the standard HR employees table.
1. Implicit Cursor Inside a Nested Block
I narrow the propagation of the NO_DATA_FOUND exception and then take appropriate action. Here, I simply ignore the problem and let the subsequence IF statement say, "We're done if no row was found." You might call an error logging procedure if a row really should have been there — and then re-raise the exception.
PROCEDURE do_stuff_with_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_name employees.last_name%TYPE;
BEGIN
BEGIN
SELECT last_name
INTO l_name
FROM employees e
WHERE e.employee_id = do_stuff_with_employee.employee_id_in;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/* log the error if this really is an error or let it go... */
l_name := NULL;
END;
IF l_name IS NOT NULL
THEN
/* continue with application logic */
NULL;
END IF;
END;2. Implicit Cursor Inside a Nested Subprogram
I move all that code into its own nested subprogram (or you could put it in a package so it could be used by more than one block). Again, you need to decide what you want to do about NO_DATA_FOUND.
PROCEDURE do_stuff_with_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_name employees.last_name%TYPE;
FUNCTION emp_name (employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
IS
l_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_name
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/* log the error if this really is an error or let it go... */
RETURN NULL;
END;
BEGIN
l_name := emp_name (employee_id_in);
IF l_name IS NOT NULL
THEN
/* continue with application logic */
NULL;
END IF;
END;3. Explicit Cursor Unconcerned With Too Many Rows
With the explicit cursor, I fetch once and then proceed. I don't have to worry about NO_DATA_FOUND being raised, and the IF statement ensures that I do nothing if no (non-null) value was returned.
PROCEDURE do_stuff_with_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_name employees.last_name%TYPE;
CURSOR name_cur
IS
SELECT last_name
FROM employees e
WHERE e.employee_id = do_stuff_with_employee.employee_id_in;
BEGIN
OPEN name_cur;
FETCH name_cur INTO l_name;
CLOSE name_cur;
IF l_name IS NOT NULL
THEN
/* continue with application logic */
NULL;
END IF;
END;4. Explicit Cursor That Checks for Too Many Rows
But perhaps I need to know if there were > 1 rows found. In this case, fetch a second time and then raise TOO_MANY_ROWS if a row was found. Else, continue on.
PROCEDURE do_stuff_with_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_name employees.last_name%TYPE;
l_name2 employees.last_name%TYPE;
CURSOR name_cur
IS
SELECT last_name
FROM employees e
WHERE e.employee_id = do_stuff_with_employee.employee_id_in;
BEGIN
OPEN name_cur;
FETCH name_cur INTO l_name;
FETCH name_cur INTO l_name2;
IF name_cur%FOUND
THEN
CLOSE name_cur;
RAISE TOO_MANY_ROWS;
ELSE
CLOSE name_cur;
END IF;
IF l_name IS NOT NULL
THEN
/* continue with application logic */
NULL;
END IF;
END;As is so often the case in programming and life in the real world, a very simple (or simply phrased) question can lead to a complicated, nuanced answer. That is the case with fetching single rows. You need to understand the context and the patterns of data being evaluated by the cursor. From that, you make your determination.
But I will close with this very simple piece of advice: Opt for the implicit cursor (SELECT-INTO) as your default choice, and then switch to the explicit cursor only when needed.
Ha! I lied. I am not done. A developer posted this comment on LinkedIn:
I never, ever useOPEN/FETCHexcept for declared cursor types. Why don't you mention cursor loops? Cursor loops are more succinct, have a well-defined scope, and are much clearer to read.
So let's take a look at using a cursor FOR loop (CFL) instead of SELECT-INTO or OPEN-FETCH-CLOSE. A CFL is another kind of implicit cursor, since you provide the SELECT (which could be defined as an explicit cursor, just to make things confusing) and Oracle implicitly opens, fetches from, and closes the cursor. If you are not very familiar with cursor FOR loops, check out the doc.
CFLs are designed (intended) for fetching multiple rows from a cursor. But you could use it to fetch just a single row. Should you?
First, from a performance standpoint, CFLs are very efficient.
Cursor FOR loop, no rows found:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
BEGIN
FOR indx IN 1 .. 100000
LOOP
FOR rec IN (SELECT n
FROM not_much_stuff
WHERE n = -1)
LOOP
my_n := rec.n;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:04.560Cursor FOR loop, one row found:
SQL> DECLARE
my_n not_much_stuff.n%TYPE;
BEGIN
FOR indx IN 1 .. 100000
LOOP
FOR rec IN (SELECT n
FROM not_much_stuff
WHERE n = 1)
LOOP
my_n := rec.n;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line (my_n);
END;
/
Elapsed: 00:00:04.685CFLS compare very favorably to the SELECT-INTO performance. And you don't have to worry about NO_DATA_FOUND or TOO_MANY_ROWS being raised. If the CFL finds no rows, it does nothing. If it finds more than one, it fetches each of them.
What, me? Worry? Well, maybe you should. See, that is also kind of the downside of CFLs. It "hides" those different states of your data. That's fine if, say:
- You know with 100% certainty that there will always only be one row, and/or....
- You don't care if you happen to fetch more than one row, and/or....
- You don't care if you don't fetch any rows.
Sadly, there is so little we can point to in our lives, in our code, in our databases that lends itself to 100% certainty. And that's one of the reasons I do not use CFLs for single row fetches.
Another reason I avoid them is that I like my code to be, as much as possible, self-documenting (and believe me: I fail regularly to achieve this!), to tell a story that is easy to follow and easy to modify when user requirements change.
Use of a loop implies that you may — and are expecting to — iterate multiple times.
Use of a cursor FOR loop implies that you may — and are expecting to — fetch more than one row.
To use a CFL when you know — and when the SELECT statement clearly reflects — that at most one row will ever be fetched seems to me to invite confusion and possible problems for future developers working with this code.
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments