By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,454 Members | 1,804 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Oracle REF CURSOR - 2

debasisdas
Expert 5K+
P: 8,127
Cursor Variable Returning %ROWTYPE
-----------------------------------------------------------

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  3. tmp_cv TmpCurTyp;TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
  4. emp_cv EmpCurTyp;
  5. BEGIN
  6. NULL;
  7. END;
Cursor Variable Returning %TYPE
---------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. dept_rec dept%ROWTYPE;
  3. TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
  4. dept_cv DeptCurTyp;
  5. BEGIN
  6. NULL;
  7. END;
  8.  
Cursor Variable Returning Record Type
-----------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE EmpRecTyp IS RECORD (
  3. employee_id NUMBER,
  4. last_name VARCHAR2(30),
  5. salary NUMBER(7,2));
  6. TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
  7. emp_cv EmpCurTyp;
  8. BEGIN
  9. NULL;
  10. END;
Passing Cursor Variables As Parameters
--------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  3. ct EmpCurTyp;
  4. PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
  5. person emp%ROWTYPE;
  6. BEGIN
  7. dbms_output.put_line('-----');
  8. dbms_output.put_line('Here are the names from the result set:');
  9. LOOP
  10. FETCH emp_cv INTO person;
  11. EXIT WHEN emp_cv%NOTFOUND;
  12. dbms_output.put_line('Name = ' || person.ename ||' ' || person.job);
  13. END LOOP;
  14. END;
  15. BEGIN
  16. OPEN ct FOR SELECT * FROM emp WHERE ROWNUM < 11;
  17. process_emp_cv(ct);
  18. CLOSE ct;
  19. OPEN ct FOR SELECT * FROM emp WHERE ename LIKE 'A%';
  20. process_emp_cv(ct);
  21. CLOSE ct;
  22. END;
  23.  
another sample code
================
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  3. emp_cv EmpCurTyp;
  4. rr emp%rowtype;
  5. BEGIN
  6. IF NOT emp_cv%ISOPEN THEN
  7. OPEN emp_cv FOR SELECT * FROM emp;
  8. END IF;
  9. loop
  10. fetch emp_cv into rr;
  11. dbms_output.put_line(rr.ename||' '||rr.job);
  12. exit when emp_cv%notfound;
  13. end loop;
  14. CLOSE emp_cv;
  15. END;
Sample Program Showing Fetching from a Cursor Variable into a Record
================================================== =====
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  3. emp_cv EmpCurTyp;
  4. emp_rec emp%ROWTYPE;
  5. BEGIN
  6. OPEN emp_cv FOR SELECT * FROM emp WHERE sal < 3000;
  7. LOOp
  8. FETCH emp_cv INTO emp_rec;
  9. dbms_output.put_line('Name = ' || emp_rec.ename || ' ' ||emp_rec.job);
  10. EXIT WHEN emp_cv%NOTFOUND;
  11. END LOOP;
  12. CLOSE emp_cv;
  13. END;
  14.  
Also check Oracle REF CURSOR - 3
May 29 '07 #1
Share this Article
Share on Google+