oracle - 附加到 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

如何保留所有插入的记录

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')
  )
);

或者，到 merge two collections :

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