This is the fifth in our series of introductory Oracle PLSQLtutorials showing you how to use it to improve database performance and make your life easier as an Oracle developer or DBA.
This article explores the use of explicit cursors in Oracle PLSQL.
Up until now in this tutorial we've looked at data types, language elements, the pros and cons of using Oracle PLSQLand a brief example of using it in the real world as well as implicit cursors. Now it's time to look at the use of explicit cursors.
First of all we need to define an explicit cursor and describe the differences between implicit and explicit cursors. An explicit cursor is a named query that is defined as a cursor in PL/SQL by use of
the keyword CURSOR . Notice I said named query. UPDATE, DELETE and INSERT statements always use an implicit cursor (because they are not named).
The difference between explicit and implicit cursors in Oracle PLSQLis that explicit cursors give you complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched. Also, fetching from an explicit cursor will never raise a NO_DATA_FOUND or a TOO_MANY_ROWS exception.
In fact with an explicit cursor you can continue fetching past the last row of data for ever without any exception being raised! Which begs the question: how do you know when to stop fetching from a cursor if Oracle will let you carry on fetching from the result set when you've already had all the data ? The answer is that we need to use one or other of the cursor attributes FOUND or NOTFOUND (there are another two cursor attributes: ROWCOUNT and ISOPEN but we can't use these to tell if there are any more rows to fetch).
When using implicit cursors in Oracle PL/SQL, we access the cursor attributes by prefixing the attribute name with "SQL%". To access the cursor attributes of explicit cursors in Oracle PL/SQL the SQL keyword is replaced by the cursor name. For example, when using an explicit cursor MYCURSOR, after every fetch we can check whether we have reached the end of the result set by checking that MYCURSOR%FOUND remains TRUE or that MYCURSOR%NOTFOUND remains FALSE.
Another advantage of using explicit named cursors in PL/SQL is that they can be parameterised just like any procedure or function so that the actual variables used when the cursor is executed may change with every invocation. Also with an explicit cursor you can fetch the whole row into a PL/SQL record variable instead of into individual variables as you have to do with implicit cursors.
Let's look at an example, to put this all together. Let's suppose we need
a query to return the department name, the salary and the employee name of
each of our employees. Unless we have only one employee in our company we'll
need to use either a CURSOR FOR LOOP
(which we'll cover in the next article) or explicitly OPEN,
FETCH from and CLOSE
an explicit cursor. We would declare the cursor like this:-.
.
.
<preceding data declarations>
1.
CURSOR emp_details IS
2.
SELECT emp.name, emp.salary, dept.name FROM emp, dept 3.
WHERE emp.dept_id = dept.id;
4.
emp_detail_rec emp_details%ROWTYPE;
<more data declarations>
.
.
.
Let's dissect this snippet of PL/SQL quickly, to explain what's going on.
The first line of interest (line 1) starts with the word CURSOR.
This tells Oracle that you're declaring an explicit cursor. As this is a type
of variable, the same rules apply to the naming of cursors as for other variables
in Oracle PL/SQL. In this case we haven't parameterised the cursor, but to
do so we would just treat it as if it were a function or procedure and specify
the parameters after the name. Note that cursor parameters are always IN (i.e.
read only). The results are always returned in one or more variables or a
PL/SQL record
Lines 2 and 3 form the SQL query that this cursor implements (just a simple one in this case). The next line (line 4) declares a PLSQL record to hold the results of the query. Note this structure holds only one row of results, we could declare a PLSQLcollection (table) to hold all the results if we wanted to, but in this case we won't.
Now that we've declared our cursor, we need to use it. How do we do this ? Simple, first we open it, next we fetch from it, finally when we've finished we close it. Here's an example.
OPEN emp_details;
FETCH emp_details INTO emp_detail_rec;
CLOSE emp_details;
In this little section of Oracle PL/SQL code, we've opened the cursor, fetched just one row from it and then closed it. This raises several important points about explicit cursors function in PLSQL: first, we can fetch as many or as few rows as we like (or even none) - we have complete control over the cursor- in this case we fetched just one row; second, emp_detail_rec remains available for use even after we've closed the cursor; third, as mentioned earlier we don't need an exception handler to catch a NO_DATA_FOUND or a TOO_MANY_ROWS exception as Oracle won't generate either of them.
To fetch more than one row, we would need to either put the fetch statement in a loop or to repeat the FECTH statement once for each row we want to fetch. Obviously the latter is only practical if we know in advance exactly how may rows we need to fetch and that number is small (3 at the most) otherwise using a loop would result in less code.
Let's change the PLSQLexample slightly to demonstrate the use of a simple loop.
OPEN emp_details;
LOOP
FETCH emp_details
INTO emp_detail_rec;
EXIT WHEN emp_details%NOTFOUND;
END LOOP;
CLOSE emp_details;
Notice we used the cursor attribute NOT FOUND to determine when to exit from the loop (EXIT WHEN emp_details%NOTFOUND).
That is one way to use cursors in a loop, the next part of this tutorial looks at the use of cursor for loops which require less coding as Oracle does all the work for us. See Part 6 for details.
---------------------------------------
Looking for more Oracle tips and tricks ? Just subscribe to our ezine and benefit from concise, clear, technical info. covering all aspects of Oracle databases,
Contact us with your questions and we'll answer them in a future newsletter.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.