在Oracle中追加用户定义集合类型

Question

我希望在对象引用变量中保留所有插入的行，但是没有找到任何方法可以实现。有人能够检查我的脚本并进行更正吗？

create or replace type "GT_OBJECT_TYPE_REL_IDS" as object (OBJECT_ID number, OBJECT_TYPE varchar2(64));
/
create or replace type "T_OBJECT_TYPE_REL_IDS" as table of "GT_OBJECT_TYPE_REL_IDS";
/
VARIABLE CURS REFCURSOR;
/
DECLARE
   v_OBJECT_TYPE_REL_IDS T_OBJECT_TYPE_REL_IDS;
BEGIN
   --Explicit Constructor
   SELECT GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT')
   BULK   COLLECT INTO v_OBJECT_TYPE_REL_IDS
   FROM   DUAL;

   SELECT GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE')
   BULK   COLLECT INTO v_OBJECT_TYPE_REL_IDS
   from   DUAL;
   OPEN :curs FOR SELECT * FROM TABLE (v_OBJECT_TYPE_REL_IDS);
end;
/
PRINT :CURS

当我执行脚本时，它显示的最终输出为：

OBJECT_ID   OBJECT_TYPE

11          COLLEGE

我需要输出如下：

OBJECT_ID   OBJECT_TYPE

10          STUDENT
11          COLLEGE

如何保留所有插入的记录？

- user_27

Answer 1

您可以全部使用PL/SQL完成，无需切换到SQL范围：

DECLARE
   -- Initialise the collection
   v_OBJECT_TYPE_REL_IDS T_OBJECT_TYPE_REL_IDS := T_OBJECT_TYPE_REL_IDS();
BEGIN
   -- Extend the collection by the default amount (1 element)
   v_OBJECT_TYPE_REL_IDS.EXTEND;
   -- Set the first element to be your student type
   v_OBJECT_TYPE_REL_IDS(1) := GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT');

   -- Extend the collection by 1 element
   v_OBJECT_TYPE_REL_IDS.EXTEND(1);
   -- Set the last element to be your college type
   v_OBJECT_TYPE_REL_IDS(v_OBJECT_TYPE_REL_IDS.COUNT) := GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE');

   OPEN :curs FOR SELECT * FROM TABLE (v_OBJECT_TYPE_REL_IDS);
END;
/

你也可以在构造函数中完成它：

DECLARE
   -- Initialise the collection
   v_OBJECT_TYPE_REL_IDS T_OBJECT_TYPE_REL_IDS := T_OBJECT_TYPE_REL_IDS(
       GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT'),
       GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE')
     );
BEGIN
   OPEN :curs FOR SELECT * FROM TABLE (v_OBJECT_TYPE_REL_IDS);
END;
/

如果你想使用 SQL，那么你不需要使用集合类型，只需直接使用 UNION ALL 填充游标的行：

BEGIN
   OPEN :curs FOR
     SELECT GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT') FROM DUAL UNION ALL
     SELECT GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE') FROM DUAL;
END;
/

或者，使用集合：

BEGIN
   OPEN :curs FOR
     SELECT *
     FROM TABLE(
       T_OBJECT_TYPE_REL_IDS(
         GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT'),
         GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE')
       )
     );
END;
/

那么，使用前两个版本之一，您可以完全避免游标和PL/SQL。也就是说：

SELECT *
FROM TABLE(
  T_OBJECT_TYPE_REL_IDS(
    GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT'),
    GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE')
  )
);

或者，要合并两个集合：

DECLARE
  v_OBJECT_TYPE_REL_IDS T_OBJECT_TYPE_REL_IDS;
BEGIN
  --Explicit Constructor
  v_OBJECT_TYPE_REL_IDS := T_OBJECT_TYPE_REL_IDS( GT_OBJECT_TYPE_REL_IDS (10, 'STUDENT') );

  -- Merge in SQL using MUTLISET UNION ALL
  SELECT v_OBJECT_TYPE_REL_IDS
         MULTISET UNION ALL
         T_OBJECT_TYPE_REL_IDS( GT_OBJECT_TYPE_REL_IDS (11, 'COLLEGE') )
  INTO v_OBJECT_TYPE_REL_IDS
  FROM   DUAL;

  OPEN :curs FOR SELECT * FROM TABLE (v_OBJECT_TYPE_REL_IDS);
END;
/

- MT0

Answer 2

您正在用第二行覆盖第一行。使用单个select和UNION ALL一起加载它。

SELECT * BULK COLLECT
INTO v_OBJECT_TYPE_REL_IDS
FROM (
    SELECT GT_OBJECT_TYPE_REL_IDS(10, 'STUDENT')
    FROM DUAL  UNION ALL
    SELECT GT_OBJECT_TYPE_REL_IDS(11, 'COLLEGE')
    FROM DUAL
   );

此外，如果将其转换为PIPELINED FUNCTION，则无需使用CURSOR变量。您只需运行SELECT * FROM TABLE(fn_pipelinedfunc);即可。

- Kaushik Nayak