将数据传递到另一个集合对象(oracle)中的集合

Question

我创建了一个集合来传递数据并在过程中使用：

Create or Replace type Search_Type AS Object(
ID Number(38),
SF_ID Varchar(25),
PF_ID Varchar(25),
C_ID SYS.ODCIVARCHAR2LIST
);
Create or Replace Type  Search_Array AS Table of Search_Type;

我调用的程序如下：

SET SERVEROUTPUT ON  
DECLARE
v_S_ID     Search_Array;
v_C_ID     SYS.ODCIVARCHAR2LIST;
BEGIN
v_S_ID := Search_Array();
v_O_ID := SYS.ODCIVARCHAR2LIST();
v_O_ID.Extend(2);
v_O_ID(1) := '4';
v_O_ID(2) := '5';
v_S_ID.EXTEND(4);
v_S_ID(1) := Search_Type(1,'2','3',v_C_ID);
FOR i IN v_S_ID.first..v_S_ID.last LOOP dbms_output.put_line(--'Iteration: '
    --   || i ||
       v_S_ID(i).id
       || ', '
       || v_S_ID(i).SF_ID
       || ', '
       || v_S_ID(i).PF_ID
       || ', '
       || v_S_ID(i).C_ID.count);
END LOOP;
END;

我得到以下错误：

Error report -
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 17
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.

Answer 1

您的代码中缺少了我在以下代码中作为内联注释提到的一些内容：

SQL> SET SERVEROUT ON
SQL> DECLARE
2  V_S_ID   SEARCH_ARRAY;
3  V_C_ID   SYS.ODCIVARCHAR2LIST;
4  V_O_ID   SYS.ODCIVARCHAR2LIST; -- added this
5  BEGIN
6  V_S_ID      := SEARCH_ARRAY();
7  V_O_ID      := SYS.ODCIVARCHAR2LIST();
8  V_C_ID      := SYS.ODCIVARCHAR2LIST(); -- added this
9  V_O_ID.EXTEND(2);
10  V_O_ID(1)   := '4';
11  V_O_ID(2)   := '5';
12  V_S_ID.EXTEND(1);  -- CHANGED THIS FROM 4 TO 1
13  V_S_ID(1)   := SEARCH_TYPE(
14  1,
15  '2',
16  '3',
17  V_C_ID
18  );
19  FOR I IN V_S_ID.FIRST..V_S_ID.LAST LOOP DBMS_OUTPUT.PUT_LINE(--'Iteration: '
20                                                                            --   || i ||
21  V_S_ID(I).ID
22                                                               || ', '
23                                                               || V_S_ID(I).SF_ID
24                                                               || ', '
25                                                               || V_S_ID(I).PF_ID
26                                                               || ', '
27                                                               || V_S_ID(I).C_ID.COUNT);
28  END LOOP;
29
30  END;
31  /
1, 2, 3, 0
PL/SQL procedure successfully completed.
SQL>