The first step is to fetch the data.
Second is to pass the list of values fetched in step 1 to another procedure.
This is the table
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
And this is what i tried,
CREATE OR REPLACE PROCEDURE P1(
EMPNO OUT EMP.EMPNO%type,
ENAME OUT EMP.ENAME%type,
DEPTNO OUT EMP.DEPTNO%type)
AS
C_EMP SYS_REFCURSOR;
C_EM VARCHAR2(200);
BEGIN
C_EM:='SELECT EMPNO,ENAME,DEPTNO FROM EMP';
OPEN C_EMP FOR C_EM;
LOOP
FETCH C_EMP into EMPNO,ENAME,DEPTNO;
EXIT WHEN C_EMP%notfound;
END LOOP;
CLOSE C_EMP;
RETURN C_EMP;
END;
/
CREATE OR REPLACE PROCEDURE P2 (e_EMP IN SYS_REFCURSOR,
E_EMPNO OUT EMP.EMPNO%type,
E_ENAME OUT EMP.ENAME%type,
E_DEPTNO OUT EMP.DEPTNO%type)
AS
BEGIN
LOOP
FETCH e_EMP INTO E_EMPNO,E_ENAME,E_DEPTNO;
EXIT WHEN e_EMP%NOTFOUND;
END LOOP;
CLOSE e_EMP;
END;
/
You can't return a value from a procedure. Only a function. So
RETURN C_EMP;
at the end of P1 causes a compilation error.
Also, you're looping through the emp rows in P1, assigning them to the out variables. But each of these can only store a single value. Each iteration of the loop overwrites what was there before. So anything that calls it will only see the last value.
There are various ways you can overcome this, including:
Return the ref cursorChange the procedure to a function, strip out the fetching and just return the cursor:
create or replace function f
return sys_refcursor as
cur sys_refcursor;
begin
open cur for
select empno,ename,deptno from emp;
return cur;
end f;
/
Then whatever calls f loop through the cursor, fetching the rows and closing it.
Return an arrayWhen fetching the results in the procedure, load them into an array. Then return that when you've collected them all.
create or replace type emp_obj as object (
empno number(4),
ename varchar2(10),
deptno number(2));
/
create or replace type emp_tab as table of emp_obj;
/
create or replace procedure p ( emps out emp_tab ) as
begin
select emp_obj(empno,ename,deptno)
bulk collect into emps
from emp;
end p;
/
You could change the procedure to a function returning emp_tab as in the ref cursor example.
Which is better?
As always, it depends...
Fetching the results from the ref cursor may lead to more round trips to the DB, depending on how you write your client code. Whereas returning an array gets everything in one go.
Of course, if the result set is "large" this could be a problem. You may run out of PGA memory!
With the ref cursor you control how many rows you get. So you can ensure each fetch only gets a small enough number of rows. This could also be handy if you want to stop fetching at some conditional point based on what you've got so far.