Monday, March 16, 2015

Oracle REF Cursor Example

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno      IN     emp.deptno%TYPE,
                                        p_recordset      OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN p_recordset FOR
        SELECT ename, empno, deptno
          FROM emp
         WHERE deptno = p_deptno
      ORDER BY ename;
END get_emp_rs;

-- The resulting cursor can be referenced from PL/SQL as follows.
DECLARE
   l_cursor   SYS_REFCURSOR;
   l_ename    emp.ename%TYPE;
   l_empno    emp.empno%TYPE;
   l_deptno   emp.deptno%TYPE;
BEGIN
   GET_EMP_RS (p_deptno => 30, p_recordset => l_cursor);

   LOOP
      FETCH l_cursor INTO l_ename, l_empno, l_deptno;

      EXIT WHEN l_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (l_ename || ' | ' || l_empno || ' | ' || l_deptno);
   END LOOP;

   CLOSE l_cursor;

END;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect