Implicit CURSORs – Resilient Software and PL/SQL

0 Comments

Implicit CURSORs

When Oracle executes a SQL statement using SELECT, UPDATE, etc., it automatically creates an implicit CURSOR. This CURSOR is managed internally and Oracle reveals only a limited amount of information about it. For example, you can get the numbers of rows affected by the query using the following command:

SQL
%
ROWCOUNT

If you want to know if your query affected at least one row, you can use the following:

SQL
%
FOUND

If you run a query, such as SELECT * FROM <TABLE_NAME>, you can then follow it with something like this:

SET
 
SERVEROUTPUT
 
ON
;

DECLARE

var_rows
 
NUMBER
 
:
=
 
0
;

TYPE
 
temp_type
 
is
 
TABLE
 
of
 
TEMP
%
ROWTYPE
;

new_temp
 
temp_type
;

BEGIN
select
*
bulk
 
collect
 
into
 
new_temp
 
from
 
temp
;
var_rows
:
=
SQL
%
ROWCOUNT
;
dbms_output
.
put_line
(
‘Number of rows affected: ‘
 
||
 
SQL
%
ROWCOUNT
);

END
;

This allows you to see the numbers of rows affected by the SQL statement.

Explicit CURSORs

The other type of CURSOR is the explicit variety, which allows a lot of interaction with its lifecycle from within PL/SQL code. For example, you can do the following with an explicit CURSOR:

OPEN the CURSOR for use.

FETCH data from the CURSOR into variables.

CLOSE the CURSOR (an important step in cleaning up after your code has run).

Explicit cursors take care of a lot of the heavy lifting involved when accessing the database. Cursors also give you type safety. You’ll see more on this later, but for the moment, the main point to note is that a knowledge of cursors and CURSOR management is a crucial part of learning PL/SQL.

Back to the CURSORs example

With that in mind about cursors, the following lines from Example 1-3 should now make a little more sense:

  
CURSOR
 
c1
is
     
SELECT
ename
,
empno
,
sal
FROM
emp
ORDER
BY
sal
 
DESC
;
— start with the highest paid employee

The result of this is to create an explicit CURSOR called c1. c1 contains one or more rows that have been read from the emp table by way of the SELECT query. The last line simply orders the resultant rows. So, how do we use the CURSOR? The first thing we need to do is open the CURSOR, i.e., OPEN c1. We then loop and repeatedly transfer the current contents of the CURSOR into the three variables:
FOR
 
i
 
IN
 
1
..
5
LOOP
FETCH
 
c1
INTO
 
my_ename
,
my_empno
,
my_sal
;
EXIT
WHEN
c1
%
NOTFOUND
;

Notice the use of c1%NOTFOUND to exit the loop when all the constituent data has been processed.

The FETCH occurs for each row in the CURSOR. That is, I reuse the variables on each cycle of the containing loop. Finally, I insert the variable values into the temp table, followed by committing the changes to the database as follows:

INSERT
INTO
temp
VALUES
(
my_sal
,
my_empno
,
my_ename
);

COMMIT

After this, I repeat the loop for the next row in the CURSOR. When all the CURSOR data has been processed, the loop ends and I close the CURSOR.

Example 1-3 contains some powerful PL/SQL content. It’s pretty concise code, and it’s almost certainly better to do this type of processing in PL/SQL rather than attempting to do this type of work in SQL script. This is because PL/SQL provides good error and exception handling, which we’ll see in detail in the upcoming chapters. The PL/SQL in Example 1-3 reveals a lot of the programming power and there’s more to come, so stay tuned.

Leave a Reply

Your email address will not be published. Required fields are marked *