Explicit cursors
In
PL/SQL, all SQL statements except the select statement create an implicit cursor. That is: the cursor is not explicitly declared. For select statements, an explicit cursor can be declared. Such an explicit cursor can be thought of as a pointer to a record in a table or record set that can be moved forward within this table or record set.
An explit cursor is declared and used, for example, like so:
declare
cursor explic_cur is
select table_name
from user_tables;
begin
for r in explic_cur loop
dbms_output.put_line(r.table_name);
end loop;
end;
/
Note: not all select statements in PL/SQL are explicit. For example, the following creates in implicit cursor:
declare
cnt number;
begin
select count(*) into cnt
from user_tables;
dbms_output.put_line(
'There are ' || cnt || ' tables in your schema'
);
end;
/
Cached cursors
The
initialization parameter session_cached_cursors
specifies how many cursors are held open for a session. Such cached cursors are stored in the session cursor cache.
If a new cursor needs to me moved into the session cursor cache, the cursor that hasn't been used for the longest time will be removed from the session cursor cache.
The higher the value of
session_cached_cursors
is set to a high value, the more the
Shared Pool might become fragmented.