that block of code demonstrates is that a ref
cursor can be opened with either a query you
construct at runtime or a query that is predetermined at compile time. The statement
open l_cursor for 'select from emp';
demonstrates that the query defining the
ref cursor L_CURSOR can be truly dynamic—
constructed entirely at runtime. You could
replace'select from emp'with
any string—any PL/SQL variable of a string
type—and be able to open the ref cursor
at runtime.
The other two open calls for L_CURSOR in
Listing 1 demonstrate that the query associated with the ref cursor can be dynamic
at runtime. Depending on what day of the
month it is, the ref cursor opens the static
query SELEC T FROM DEPT or SELEC T *
FROM DUAL. The ref cursor is not bound
to a fixed definition at compile time as the
regular cursor C is. No matter how many
times you run the code, the query associated
with cursor C will be static and constant.
Now, looking at the second point, a ref
cursor can be passed to another PL/SQL
routine or returned to a client, whereas a
regular cursor must be directly addressed
(not passed) and cannot be returned to a
client application. You can easily see what
this means for a ref cursor. The following is a
procedure that accepts a ref cursor as input,
fetches a row from it, and prints out the row.
create or replace
procedure
p( l_cursor in out sys_refcursor )
is
Code Listing 3: Report comparing performance of regular and ref cursors
SQL> declare
2 l_rec t%rowtype;
3 l_cursor sys_refcursor;
4 begin
5 for x in
6 ( select from t regular )
7 loop
8 null;
9 end loop;
10
11 open l_cursor
12 for
13 select *
14 from t refcur;
15
16 loop
17 fetch l_cursor
18 into l_rec;
19 exit when
20 l_cursor%notfound;
21 end loop;
22 close l_cursor;
23 end;
24 /
PL/SQL procedure successfully completed.
TKPROF report
SELECT FROM T REGULAR
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 722 0.23 0.23 0 1748 072198
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total 724 0.23 0.23 0 1748 072198
SELECT FROM T REFCUR
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch72199 0.40 0.42 0 72203 072198
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total72201 0.40 0.42 0 72203 072198
l_rec all_users%rowtype;
begin
fetch l_cursor into l_rec;
if (sql%notfound)
then
return;
end if;
dbms_output.put_line
( 'data in procedure = ' ||
l_rec.username );
end;
/
Then, using SQL*Plus, I’ll define a ref
cursor host variable in the “program”:
SQL> variable x refcursor
And last I’ll run the code in Listing 2, which
will dynamically open a ref cursor, pass it to a
PL/SQL subroutine, and then return it to the
client program for further processing.
As you can see, the first row was fetched
by the PL/SQL routine and printed out, and
the remaining four rows were fetched and
printed by the client program, not PL/SQL.
Those two differentiators show the flex-
ibility of a ref cursor over a regular cursor,
so that might beg the question “Why use
regular cursors at all?” The answer is twofold:
performance and ease of programming. Let’s
look at performance first, starting with the
facts that a regular cursor will be cached open
by PL/SQL and that a ref cursor cannot and
will not be. In other words, a parsing penalty
is involved in using a ref cursor. Again, I can
observe this easily by coding a simple routine
that will open a regular cursor, fetch from it,
close it, and then do the same to a ref cursor.
Here is a routine to do that:
