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.