Oracle underlying stored procedures and cursors

One, a stored procedure with a cursor

　　There are two types of cursors as arguments:

　　1. Declaring the system cursor type Sys_refcursor

1) The cursor acts as a parameter to the stored procedure:

--stored procedures with cursorsCREATE OR REPLACE PROCEDUREPro_emp_in (Emp_cur sys_refcursor) asv_name EMP. Ename%TYPE; V_sal EMP. SAL%TYPE;BEGINLOOPFETCHEmp_cur intoV_name, V_sal; Dbms_output. Put_Line ('Name:' ||V_name|| 'Sal:' ||v_sal); EXIT  whenEmp_cur%NOTFOUND; ENDLOOP;END;

To invoke a stored procedure with a cursor:

-- calling a stored procedure with a cursor DECLARE   emp_cur sys_refcursor; BEGIN  OPEN  for    SELECT  from EMP;   = emp_cur);   CLOSE emp_cur; END;

　　

2) stored procedure with output cursors

-- sys_refcursor type of system cursor with output parameters CREATE OR REPLACE PROCEDURE  as BEGIN  OPEN  for    SELECT  from EMP E;   -- and query statement binding END;

To call a stored procedure with an output cursor:

--calling a stored procedure with a cursor with an output typeDECLAREv_name EMP. Ename%TYPE; V_sal EMP. SAL%TYPE;    Emp_cur Sys_refcursor; --declaring system cursor typesBEGINpro_emp (Emp_cur=emp_cur); LOOPFETCHEmp_cur intoV_name, V_sal; Dbms_output. Put_Line ('Name:' ||V_name|| 'Sal:' ||v_sal); EXIT  whenEmp_cur%NOTFOUND; ENDLOOP; CLOSEemp_cur;END;

2. Declare a custom cursor type.

　　

Oracle underlying stored procedures and cursors