Cursor Variable Returning %ROWTYPE
-----------------------------------------------------------
-
DECLARE
-
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
-
tmp_cv TmpCurTyp;TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
-
emp_cv EmpCurTyp;
-
BEGIN
-
NULL;
-
END;
Cursor Variable Returning %TYPE
---------------------------------------------------
-
DECLARE
-
dept_rec dept%ROWTYPE;
-
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
-
dept_cv DeptCurTyp;
-
BEGIN
-
NULL;
-
END;
-
Cursor Variable Returning Record Type
-----------------------------------------------------------
-
DECLARE
-
TYPE EmpRecTyp IS RECORD (
-
employee_id NUMBER,
-
last_name VARCHAR2(30),
-
salary NUMBER(7,2));
-
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
-
emp_cv EmpCurTyp;
-
BEGIN
-
NULL;
-
END;
Passing Cursor Variables As Parameters
--------------------------------------------------------------
-
DECLARE
-
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
-
ct EmpCurTyp;
-
PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
-
person emp%ROWTYPE;
-
BEGIN
-
dbms_output.put_line('-----');
-
dbms_output.put_line('Here are the names from the result set:');
-
LOOP
-
FETCH emp_cv INTO person;
-
EXIT WHEN emp_cv%NOTFOUND;
-
dbms_output.put_line('Name = ' || person.ename ||' ' || person.job);
-
END LOOP;
-
END;
-
BEGIN
-
OPEN ct FOR SELECT * FROM emp WHERE ROWNUM < 11;
-
process_emp_cv(ct);
-
CLOSE ct;
-
OPEN ct FOR SELECT * FROM emp WHERE ename LIKE 'A%';
-
process_emp_cv(ct);
-
CLOSE ct;
-
END;
-
another sample code
================
-
DECLARE
-
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
-
emp_cv EmpCurTyp;
-
rr emp%rowtype;
-
BEGIN
-
IF NOT emp_cv%ISOPEN THEN
-
OPEN emp_cv FOR SELECT * FROM emp;
-
END IF;
-
loop
-
fetch emp_cv into rr;
-
dbms_output.put_line(rr.ename||' '||rr.job);
-
exit when emp_cv%notfound;
-
end loop;
-
CLOSE emp_cv;
-
END;
Sample Program Showing Fetching from a Cursor Variable into a Record
================================================== =====
-
DECLARE
-
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
-
emp_cv EmpCurTyp;
-
emp_rec emp%ROWTYPE;
-
BEGIN
-
OPEN emp_cv FOR SELECT * FROM emp WHERE sal < 3000;
-
LOOp
-
FETCH emp_cv INTO emp_rec;
-
dbms_output.put_line('Name = ' || emp_rec.ename || ' ' ||emp_rec.job);
-
EXIT WHEN emp_cv%NOTFOUND;
-
END LOOP;
-
CLOSE emp_cv;
-
END;
-
Also check
Oracle REF CURSOR - 3 
