how to append values to a oracle type

how can I append (insert) 3 or 4 different values to an oracle type and then later open it up for a cursor.

For example (pseudo):

insert into mytype select 1 from dual;
insert into mytype select 3 from dual;
insert into mytype select 5 from dua开发者_Go百科l;

open cursor_1 for select * from table(mytype);

Is this possible to do in pl/sql?

I know this is trivial and can be combined into one query but my real need is to have different queries and keep appending the results to mytype.

Assuming you mean you have a custom SQL type (presumably a nested table type), and a PL/SQL variable of that type: I don't believe you can INSERT into it, and I don't think you can SELECT into it in a way that would append to the collection.

You can select into a scalar variable, then append it to the collection procedurally.

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.

Manipulating PL/SQL collections is a lot easier since 10g, which gave us some SET operators we can use with them.

As you know, to employ the TABLE() function means we have to use a SQL type...

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

Type created.

SQL>

The following block populates a collection with some numbers, which it uses in a FOR loop. Then it executes a another query to populate a second collection. The second collection is added to the first collection using the MULTISET UNION syntax. Unlike the the SQL UNION operator, this implementation does not winnow duplicates (we can use MULTISET UNION DISTINCT for that). The code finishes off by looping through the first collection again, to prove that it contains both sets of numbers.

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>