Oracle 如何将值列表从一个过程传递到另一个过程

第一步是获取数据。
第二是将在步骤1中获取的值列表传递给另一个过程。

这是表

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));
复制

这就是我所尝试的，

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; 
/
复制

您不能从过程中返回值。只有一个函数。所以

RETURN C_EMP;
复制

在P1的末尾会导致编译错误。

另外，您正在遍历P1中的emp行，将它们分配给out变量。但是这些中的每一个都只能存储一个值。循环的每次迭代都会覆盖之前的内容。所以任何调用它的东西只会看到最后一个值。

有多种方法可以克服这一点，包括:

Return the ref 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;
/
复制

然后，无论调用f循环通过游标，获取行并关闭它。

Return an array

在过程中获取结果时，将其加载到数组中。当你把它们都收集完后，把它还回来。

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;
/
复制

您可以将过程更改为返回emp_tab的函数，如ref cursor示例所示。

哪个更好？

一如既往，这取决于...

从ref游标中获取结果可能会导致更多的数据库往返，具体取决于您编写客户端代码的方式。而返回一个数组可以一次获得所有内容。

当然，如果结果集是 “大” 这可能是一个问题。你可能会耗尽PGA的记忆!

使用ref光标，您可以控制获得的行数。因此，您可以确保每个fetch仅获得足够少的行数。如果您想根据到目前为止的情况在某些条件点停止获取，这也可能很方便。