Question: Why are PL/SQL
ref cursors important to PL/SQL performance?
Answer: The ref cursor is a
"pointer" data types that allows you to quickly reference any cursor result
(usually an internal PL/SQL table array) with data values kept in super-fast
RAM.
I'm a big fan of Dr.
Hall, one of the world's top PL/SQL programming guru's, and I'm referencing his
wonderful book "PL/SQL
Tuning" where he has many ref cursor examples and ref cursor tips.
Definition of a ref cursor
The name "ref cursor" can be confusing because a ref cursor
variable is not a cursor, but a variable that points to a cursor. In
essence, a ref cursor allows you to store the result of a "bulk collect" from a
table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then
reference the array with the ref cursor as a pointer. Below is a simple
example of a ref cursor.
Once you load a PL/SQL table (an in-RAM
array), a ref cursor allows you to reference the array within your PL/SQL code
without having to pass the array to a new procedure or function. Instead
of passing the "baggage" of the actual RAM array, you simple pass the ref
cursor, which points to the array values.
PL/SQL ref cursor references:
