Oracle - use of parameterized cursor - April 30, 2009 at
20:00 PM by Amit Satpute
Explain how to make use of parameterized cursor in Oracle.
A parameter makes the cursor more reusable.
A parameter avoids scoping problems.
Example:
Cursor without parameters:
cursor find_id is
select id, name from emp;
Parameterized cursor
cursor find_id is
select id, name, dept from emp
where dept =βITβ
Generalizing a cursor:
DECLARE
CURSOR cursor_emp (dept_in VARCHAR2)
IS
SELECT name, dept,
last_used_date
FROM emp
WHERE dept =
dept_in;
emp_rec cursor_emp%ROWTYPE;
BEGIN
OPEN cursor_emp (:emp.dept);
FETCH cursor_emp INTO
emp_rec;
In the previous example, we passed a hard-coded value (IT) as a parameter to the
where.
In this example, we generalized it. So whenever we open a cursor with an
argument, the select is parsed and the output is limited to the conditions that
apply.
Also read
What a SELECT FOR UPDATE cursor represent?, What WHERE CURRENT OF clause does in
a cursor?, Can you pass a parameter to a cursor?, Explain the functioning of
CURSOR FOR LOOP with example., Define Simple/Explicit , Parametric and
Internal/Implicit cursor.............
Normal cursors fall under the category of static cursors while REF cursors are
dynamic.........
A cursor variable works like pointer in C. It is used to hold address of an
item rather than the item itself. Cursor variables can be used to hold
different values at run time............
Oracle cursors
Oracle form
Oracle security
Oracle
system privilege
Oracle
object privileges
|
