如何将值附加到 oracle 类型

Question

如何将 3 或 4 个不同的值附加（插入）到一个 oracle 类型，然后用游标打开它。

例如（伪）：

insert into mytype select 1 from dual;
insert into mytype select 3 from dual;
insert into mytype select 5 from dual;

open cursor_1 for select * from table(mytype);

这可以在 pl/sql 中做到吗？

我知道这是微不足道的，可以合并到一个查询中，但我真正的需要是进行不同的查询并不断将结果附加到 mytype。

Answer 1

假设您的意思是您有一个自定义 SQL 类型（大概是嵌套表类型）和该类型的 PL/SQL 变量：我不相信您可以 INSERT 到其中，而且我也不相信认为您可以以附加到集合的方式SELECT进入它。

您可以选择一个标量变量，然后按程序将其附加到集合中。

SQL> create type mytype as table of integer;
  2  /

Type created.

SQL> set serveroutput on
SQL> l
  1  declare
  2    mytable  mytype := mytype();
  3    cursor_1 sys_refcursor;
  4    x  integer;
  5    procedure append_to_table( t IN OUT mytype, y IN INTEGER)
  6      is
  7      begin
  8        t.extend();
  9        t(t.COUNT) := y;
 10      end append_to_table;
 11  begin
 12    select 1 into x from dual;
 13    append_to_table( mytable, x );
 14    select 3 into x from dual;
 15    append_to_table( mytable, x );
 16    select 5 into x from dual;
 17    append_to_table( mytable, x );
 18    open cursor_1 for select * from table(cast(mytable as mytype));
 19    fetch cursor_1 into x;
 20    dbms_output.put_line(x);
 21    fetch cursor_1 into x;
 22    dbms_output.put_line(x);
 23    fetch cursor_1 into x;
 24    dbms_output.put_line(x);
 25    close cursor_1;
 26* end;
SQL> /
1
3
5

PL/SQL procedure successfully completed.

Answer 2

自 10g 以来，操作 PL/SQL 集合变得更加容易，它为我们提供了一些可以与它们一起使用的 SET 运算符。

如您所知，使用 TABLE() 函数意味着我们必须使用 SQL 类型...

SQL> create or replace type nums_nt as table of number
  2  /

Type created.

SQL>

以下块用一些数字填充集合，并在 FOR 循环中使用这些数字。然后它执行另一个查询来填充第二个集合。使用 MULTISET UNION 语法将第二个集合添加到第一个集合。与 SQL UNION 运算符不同，此实现不会消除重复（我们可以使用 MULTISET UNION DISTINCT 来实现）。该代码通过再次循环第一个集合来结束，以证明它包含两组数字。

SQL> set serveroutput on
SQL>
SQL> declare
  2      master_nos nums_nt;
  3      fresh_nos nums_nt;
  4  begin
  5
  6      dbms_output.put_line ('get some numbers, print some names');
  7
  8      select id
  9      bulk collect into master_nos
 10      from t23
 11      where name not in ( select upper(name) from t_doctors )
 12      and name not in ( select upper(name) from t_kids );
 13
 14      for r in ( select t23.name
 15                 from t23
 16                      join ( select * from table(master_nos)) sq
 17                             on t23.id = sq.column_value
 18               )
 19      loop
 20          dbms_output.put_line (r.name);
 21      end loop;
 22
 23      dbms_output.put_line ('get more numbers, print all names');
 24
 25      select id
 26      bulk collect into fresh_nos
 27      from t23
 28      where name in ( select upper(name) from t_doctors );
 29
 30      master_nos := master_nos
 31                      MULTISET UNION
 32                   fresh_nos;
 33
 34      for r in ( select t23.name
 35                 from t23
 36                      join ( select * from table(master_nos)) sq
 37                             on t23.id = sq.column_value
 38               )
 39      loop
 40          dbms_output.put_line (r.name);
 41      end loop;
 42
 43  end;
 44  /
get some numbers, print some names
CAT
PINNER BLINN
LORAX
MR KNOX
FOX IN SOCKS
get more numbers, print all names
CAT
PINNER BLINN
LORAX
MR KNOX
FOX IN SOCKS
DR SINATRA
DR FONZ

PL/SQL procedure successfully completed.

SQL>