René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback

January 8, 2006: On emulating tables with nested tables  
In PL/SQL, tables can be emulated with nested tables. A nested table type is created with the
create type type_name as
table of dependand_type_name command. Basically, this command creates a type (here with the name type_name) that can store
0, 1 or more instances of dependand_type_name. To make theory a bit more clear, this is demonstrated in the following
I create a type that can store one number (
a ) and one varchar2(15) (b ). This type is the dependend
type for the nested table type I am going to create later.
As this type functions like a row in the nested table, I call the type row_type:
create type row_type as object ( a number, b varchar2(15) ) /
With this type, I am now ready to create the nested table type that is going to function like a table, hence the name
table_type. It functions like a table because it can store 0, 1 or more instance of row_type.
create type table_type as table of row_type /
The following procedure sum_group_by_b takes one argument whose name is
t and whose type is table_type. The variable
t can now be turned into a in memory table by putting the t into a table(t) . This construct allows then to
select from this in memory table as though it were an ordinary table. It must be noted, however, that, unfortunatly, only select statements
can be performed on the table(t) construct, deletes, updates or insertes are not possible.
create procedure sum_group_by_b(t in table_type) is begin for r in ( select sum(a) sum_a, b from table(t) group by b ) loop dbms_output.put_line(rpad(r.b, 15)  to_char(r.sum_a, '9999')); end loop; end sum_group_by_b; /
As can be seen, this procedure groups the values in t by b and sums a for each group and prints the sum along with b's value using
dbms_output.
Let's see this procedure in action. I call the function and pass it an instance of table_type that is created with
the table_type's constructor. A type's constructor has the same name as the type, so, the constructor looks like
table_type( .... ) .
In the case of nested tables, a constructor takes any number of instances of the dependend type. Here, these instances, in turn, are created
using the row_type constructor, that has, again, the same name as the type: namely row_type . Unlike nested tables, this
constructor exactly takes two arguments that correspond to the types attributes a and b .
begin sum_group_by_b ( table_type ( row_type( 25, 'eighty' ), row_type( 2, 'nine' ), row_type(1000, 'four thousand'), row_type( 3, 'nine' ), row_type( 4, 'nine' ), row_type(3000, 'four thousand'), row_type( 35, 'eighty' ), row_type( 20, 'eighty' ) ) ); end; /
The output of the procedure is:
eighty 80 nine 9 four thousand 4000 Emulating an insert
I have already mentioned that I can not do an
insert on a nested table. This was a bit inaccurate, I can, but it's not so straightforward
as it would be with ordinary tables and the SQL insert statement.
The following (ordinary heap) table stores the data that will be inserted (appended) to the nested table:
create table table_a_b_c ( a number, b varchar2(15), c number );
Some values are filled into the table:
insert into table_a_b_c values (1, '*', 1); insert into table_a_b_c values (2, '*', 5); insert into table_a_b_c values (3, '#', 1); insert into table_a_b_c values (8, '?', 9); insert into table_a_b_c values (9, '#', 2); insert into table_a_b_c values (7, '!', 3); insert into table_a_b_c values (4, '#', 2); insert into table_a_b_c values (6, '#', 5);
Before I can go on, I have to create (or add) a
map member to the row_type. It is unclear to me, why I have to do that,
but if I don't, I get a:
14/3 PLS00801: internal error [*** ASSERT at file pdw4.c, line 2076; Type 0x07B8B260 has no MAP method.; APPEND_FROM_TABLE__RENE__P__75470[14, 3]] alter type row_type add map member function m return number invalidate;
The following procedure receives a table_type to which some rows from the table table_a_b_c (actually the columns a and b only)
are appended (or inserted). The parameter
c specifies the select criteria for the rows.
It works in two parts. The first part fills t_temp with the selected rows using
bulk collect . Again, the rows that are filled
into t_temp are created using the constructor row_type(...) .
The second part actually appends the data found in t_temp to t. The magical keywords are
multiset union all .
t is an in out parameter, so the caller will find the result in its t passed to the procedure.
create procedure append_from_table ( t in out table_type, c in number ) is t_temp table_type; begin select row_type(a, b) bulk collect into t_temp from table_a_b_c t where t.c = append_from_table.c; t := t multiset union all t_temp; end append_from_table; / show errors
Using this procedure, I append the rows from table_a_b_c whose c is equal to 1 or equal to 5 and pass the resulting nested table to
sum_group_by_b:
declare t table_type := table_type(); begin append_from_table(t, 1); append_from_table(t, 5); sum_group_by_b(t); end; / * 3 # 9 LinksMore on OracleThis is an on Oracle article. The most current articles of this series can be found here.
