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 explicit cursors.

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 2. SELECT last_name, salary, department_name 3. FROM employees d, departments d 4. WHERE e.departmnet_id=d.departmnet_id; 5. 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. OPEN emp_details; FETCH emp_details INTO emp_detail; CLOSE emp_details; 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. OPEN emp_details; LOOP FETCH emp_details INTO emp_detail; EXIT WHEN emp_details%NOTFOUND; END LOOP; CLOSE emp_details Notice we used the cursor attribute NOTFOUND 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. 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 View our privacy policy 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 linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.