Oracle Consulting and Training
For Better, Faster, Smarter,
Oracle PL/SQL Tutorial Part 5 - using explicit cursors
This is part 5 of our Oracle PLSQL tutorial to take you from beginner to expert. Part 4 covered implicit
cursors so it’s now time to look at explicit cursors which are required whenever your query may return
more than one row.
First of all we need to define an explicit cursor and then describe the differences between implicit and
Definition of an explicit cursor
An explicit cursor is a named query that is defined n
PL/SQL by use of the keyword CURSOR. Note only
queries can be named and therefore be explicit cursors.
UPDATE, DELETE and INSERT statements always use
an implicit cursor (because they are not named).
Differences between explicit and implicit
cursors in PL/SQL
Other than the fact that explicit cursors have a name and
implicit cursors don’t, the major difference between the
two types of cursor is that explicit cursors give you
complete control over fetching data from your Oracle
database, including the number of rows returned. Using an
explicit cursor also ensures that the database will never
raise either a NO_DATA_FOUND exception or a TOO_MANY_ROWS exception. Another advantage of
using explicit 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.
Controlling explicit cursors
With implicit cursors you have no control over the opening and closing of the cursor and fetching data as
Oracle handles all this for us. With an explicit cursor you have to code specific calls to open, close and
fetch from the cursor (except when using a cursor for loop, which we'll cover in the next article). You also
have complete control over how many rows are fetched from the result set by your application. You can
fetch none, one, some or all of the rows in the result set.
In fact with an explicit cursor you can continue to fetch from the cursor after the last row of data has been
returned from the result set without any exception being raised, so after every fetch you need to check
whether any data was returned. This is done by testing either of the cursor attributes FOUND or
NOTFOUND which are both Boolean. The other two cursor attributes are ROWCOUNT and ISOPEN, but
neither of these will tell us if there are any more rows to fetch. Both implicit and explicit cursors have these
attributes, but when using implicit cursors we rarely need to access these attributes.
When using implicit cursors in Oracle PL/SQL, we access the cursor attributes by prefixing the attribute
name with SQL%. To access the attributes of explicit cursors the keyword SQL is replaced by the cursor
name. For example, when using an explicit cursor MYCRSR, after every fetch we can check whether we
have reached the end of the result set by checking that MYCRSR%FOUND remains TRUE or that
MYCRSR%NOTFOUND remains FALSE.
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 () 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
SELECT last_name, salary, department_name
FROM employees d, departments d
6.. emp_detail emp_details%ROWTYPE;
<more data declarations>.
Let's dissect this snippet of PL/SQL 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 PL/SQL. In this case the cursorr
doesn’t have any parameters 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 as a PL/SQL record
Lines 2-4 form the SQL query that this cursor implements (just a simple one in this case). Line 5 declares a
PL/SQL record to hold the results of the query. Note this structure holds only one row of results, we could
declare a PL/SQL collection (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.
FETCH emp_details INTO emp_detail;
In this little section of code, we've opened the cursor, fetched just one row from it and then closed it. This
raises several important points about how explicit cursors function in PL/SQL: firstly, 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; secondly, emp_detail remains available for use even after we've closed the cursor;
thirdly, 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 just need to either put the fetch statement in a loop or to repeat the FETCH
statement once for each row we want to retrieve. 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 example slightly to demonstrate the use of a simple loop.
FETCH emp_details INTO emp_detail;
EXIT WHEN emp_details%NOTFOUND;
Notice we used the cursor attribute NOTFOUND to determine when to exit from the loop (EXIT WHEN
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.
PL/SQL tutorial Part 4
PL/SQL tutorial Part 6
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information,
take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you
make your Oracle systems faster and more reliable and save you hours searching for information.
Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
Need help with Oracle? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.