This Oracle PL/SQL tutorial is one of a series showing you how to use the various features of PL/SQL and just as importantly how not to use them! In this tutorial we're going to look at using cursors in PL/SQL.
The first thing we need to do is to explain what a cursor actually is. In fact it's quite simple - a cursor is just a pointer to a memory area that stores information about the SQL statement.
For this reason there are two types of cursor - explicit cursors and implicit cursors. As the names suggest, explicit cursors are defined explicitly (using the keyword CURSOR) by you the programmer for SQL SELECT statements and implicit cursors are created implicity by the PL/SQL compiler for running DML statements (UPDATE, INSERT, DELETE) and for running SELECT statements that haven't been defined with an explicit cursor.
The major difference between explicit and implicit cursors is the degree of control. With implicit cursors there is none - opening, closing and fetching from the cursor is done automatically by Oracle. Whereas with explicit cursors you have full control over opening, closing and fetching from the cursor.
As already mentioned in this PL/SQL tutorial, implicit cursors can be used for SELECT statements and are always used for DML statements and you have no control over them. The cursor is opened at the point in time when the SQL statement is run by Oracle, the data is fetched if the statement is a query, and the cursor is closed as soon as the SQL statement finishes - all automatically.
You can however access the results of implicit cursors (such as how many rows were updated or whether or not any rows were found) as well as the data fetched from Oracle by an implicit cursor for aSELECT statement.
Let's look at an example of how to do this.
Here's a sample piece of Oracle PL/SQL code that uses an implicit cursor for a SELECT statement.
emp employees%ROWTYPE; -- holds data from database
/* open,fetch,close of implicit cursor performed automatically by Oracle */
SELECT * INTO emp
'Name: '||emp.first_name||' '||emp.last_name);
DBMS_OUTPUT.PUT_LINE('email: '|| emp.email);
DBMS_OUTPUT.PUT_LINE('salary: '|| emp.salary);
Note that the only variable declared was a record (emp) to hold the employee data obtained from the database and the only difference between our select statement and one we would issue using SQL*Plus is the INTO clause.
Oracle Tips & Tricks to SKYROCKET Your Career!
If you're not already a subscriber you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.
It's also worth noting that with an implicit PL/SQL cursor, if the SELECT statement doesn't find a match then the Oracle database raises a NO_DATA_FOUND exception. Also, if more than one row is found, a TOO_MANY_ROWS exception is raised. The way to avoid those exceptions stopping execution of your PL/SQL code is either to use explicit cursors or to use a loop.
This next example in our PL/SQL tutorial shows how an implicit PL/SQL cursor in a FOR LOOP makes it very easy to process multiple rows from a query and eliminates the need for a NO_DATA_FOUNDexception handler.
FOR emp IN (SELECT id, salary FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE('salary = '||emp.salary);
If the query doesn't find any matching rows in the Oracle database then the loop is not executed and if it finds more than one matching row in the database, then the loop is executed once for each row returned byte query. This obviously means that Oracle will never raise either the NO_DATA_FOUND exception or the TOO_MANY_ROWSexception when using a cursor for loop.
The other advantage of using a PL/SQL cursor for loop is that you don't need to declare a record variable to hold the results - it is declared for you automatically. However you can't access this variable outside the loop.
With SELECT statements we have the option of using implicit PL/SQL cursors or explicit PL/SQL cursors. For DML statements there's no such thing as an explicit cursor.
Let's continue our PL/SQL tutorial with an example of this.
SET salary = salary * 1.1
Note that for DML statements, no exception is raised if no records are found in the database matching the conditions in the where clause. Neither is an exception raised if more than one matching record is found by Oracle.
So how do we know if our implicit cursors using DML statements worked (in the sense of making any changes)?
Well, we could query the database to look for changes but that wouldn't be very efficient, fortunately Oracle provides us with a mechanism to determine the effects of our DML statements by enabling access to attributes of the cursors.
Let's expand our DML example to demonstrate this.
SET salary = salary * 1.1
IF SQL%FOUND THEN
SQL%ROWCOUNT||' rows updated');
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows updated');
The attributes of implicit PL/SQL cursors are %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN and these attributes are accessed by prefixing them with the name of the cursor - SQL.
Obviously SQL%FOUND and SQL%NOTFOUND are mutually exclusive. Also it is not necessary to test SQL%FOUND before accessing SQL%ROWCOUNT. If no rows were affected/returned by the SQL statement then SQL%ROWCOUNT will be zero.
These attributes are available for implicit cursors for both DML and SELECT statements but will always be NULL before any implicit cursor has been run, except for SQL%OPEN which is always FALSE.
Back to PL/SQL collections of records On to using explicit cursors