Whats the best way to write a cursor in PLSQL

Question:
For years now (and in the first two editions of your book, Oracle PL/SQL Programming), you have been telling us that we should always use explicit cursors and avoid implicit ones because they are always slower. Why did you change your mind in the third edition of your book, and what is your current advice about the type of cursors to use?
Answer:
Asim, I will answer those questions in the reverse order. But first a quick review of the basic options for writing cursors:
• Implicit (static) cursor: commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• l_title magazine.title%TYPE;
• BEGIN
• SELECT title
• INTO l_title
• FROM magazine
• WHERE author = author_in;
• END;
• Explicit (static) cursor: in which you explicitly declare your cursor, and then explicitly perform each operation on that cursor yourself. This form will not raise NO_DATA_FOUND or TOO_MANY_ROWS; you decide for yourself if those conditions are actually errors.
• CREATE OR REPLACE PROCEDURE show_title (author_in IN magazine.author%TYPE)
• IS
• CURSOR title_cur
• IS
• SELECT title
• FROM magazine
• WHERE author = author_in;
•
• title_rec title_cur%ROWTYPE;
• BEGIN
• OPEN title_cur;
• FETCH title_cur INTO title_rec;
• CLOSE title_cur;
• END;
•
• Cursor FOR loop: another type of implicit cursor, but intended for use with multi-row queries (you could use it for a single row query). It opens the cursor, fetches each row within a loop, and then closes the cursor:
• BEGIN
• FOR title_rec IN (SELECT title
• FROM magazine)
• LOOP
• calculate_magazine_sales (title_rec.title);
• END LOOP;
• END;
• BULK COLLECT INTO: introduced in Oracle8i, BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
• DECLARE
• TYPE title_aat IS TABLE OF magazine.title%TYPE
• INDEX BY BINARY_INTEGER;
•
• l_titles title_aat;
• BEGIN
• SELECT title
• BULK COLLECT INTO l_titles
• FROM magazine;
• END;
•
• Dynamic variations of the same: you can use DBMS_SQL or the newer Native Dynamic SQL to execute queries in PL/SQL that are constructed at run-time. I won't go into dynamic SQL in any more detail now; perhaps (likely) it will surface in a future Q&A.
You can download the examples for each of the above cursors here.
Now, here is my advice regarding the kinds of cursors to use when fetching data from Oracle in PL/SQL programs:
• Whenever possible, use BULK COLLECT—it offers dramatic performance gains. In Oracle9i Release 2, you can even use BULK COLLECT to fetch multiple rows directly into a collection of records. Of course, when you want/need to fetch just a single row, BULK COLLECT doesn't make sense. So in those cases...
• Whatever kind of cursor you decide to use to retrieve a single row of data, hide that cursor behind a function interface. In other words, ahem, I don't really care which form of cursor you use. Choose the one that performs best and/or is most easily maintained—and then stuff it behind a function. By placing a layer of PL/SQL code between your application logic and the underlying SQL, you give yourself the latitude to change the implementation of the query without affecting your application code.
• Implicit cursors are often faster than explicit cursors (although I imagine that in most situations, the performance difference won't make much difference to the user), so you should certainly consider using them—as long as you place the SELECT INTO inside a function.
• If the table from which you are querying is small and static (or perhaps very rarely changes and then only when users are not on the system), consider caching that table in a package-level PL/SQL collection. Your query function can then retrieve data from the collection (a PGA-based cache) rather than from the SGA—and it will be significantly faster.
• In a special case, please place your SELECT FROM dual statements inside a function. The use of dual is generally a workaround to access SQL functionality not yet natively available in PL/SQL. The most common example of this is
• SELECT my_sequence.NEXTVAL
• INTO l_new_key
FROM dual
I cannot call my_sequence.NEXTVAL in PL/SQL, so I embed the reference to it inside SQL. I use dual because I want to increment my sequence by just one value, and dual (theoretically) has just a single row.
But don't expose this logic in your application; put it inside its own function. For one thing, we generally assume just a single row and don't check for TOO_MANY_ROWS. As many developers have learned, this can be a dangerous assumption. Second, someday Oracle may let us reference NEXTVAL directly in our PL/SQL code. If we don't hide the logic, we are then stuck with this "mythological" code forever. (Read my column in the September/October 2004 issue of Oracle Magazine for more on this topc.)
Finally, if you would like to get lots more detail about cursor management and reuse in PL/SQL code, please check out Bryn Llewellyn's excellent article, "Cursor Reuse in PL/SQL Static SQL."