如何在Oracle PL / SQL Procedure中使用集合并对其进行初始化？

由 发布于 oraclearraysplsql
 收藏 
DECLARE
    CURSOR cur IS
    SELECT *
    FROM bigdata.controlmcopy;

    TYPE controlmtable IS
        TABLE OF bigdata.controlmcopy%rowtype;
    v controlmtable;

    PROCEDURE ptable(
        formalarray OUT controlmtable
    )AS
    BEGIN
        FOR i IN 1..500 LOOP
            dbms_output.put_line(formalarray(i).jobname);
        END LOOP;
    END;

BEGIN
    OPEN cur;
    FETCH cur BULK COLLECT INTO v LIMIT 500;
    ptable(v);
    CLOSE cur;
END;

嗨，我想知道我需要在哪里初始化我的嵌套表'v'以及out参数模式如何与集合一起工作。 我收到此错误。

Error report -
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 15
ORA-06512: at line 22
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
           was referenced (where an initialized collection is needed)
           without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
           or whole-object assignment.
评论
神经
神经

我没有您的桌子，所以我改用了Scott的EMP。应该是这样的（我将您所做的错误标记为评论）：

SQL> set serveroutput on;
SQL> DECLARE
  2      CURSOR cur IS
  3      SELECT *
  4      FROM emp;
  5
  6      TYPE controlmtable IS
  7          TABLE OF emp%rowtype;
  8      v controlmtable := controlmtable();        --> initialized like this
  9
 10      PROCEDURE ptable(
 11          formalarray IN controlmtable           --> IN, not OUT
 12      )AS
 13      BEGIN
 14          FOR i IN 1..formalarray.count LOOP     --> not "500" as hardcoded value but array.COUNT
 15              dbms_output.put_line(formalarray(i).job);
 16          END LOOP;
 17      END;
 18
 19  BEGIN
 20      OPEN cur;
 21      FETCH cur BULK COLLECT INTO v LIMIT 500;
 22      ptable(v);
 23      CLOSE cur;
 24  END;
 25  /
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

PL/SQL procedure successfully completed.

SQL>
点赞
评论