How do I linearize a nested table, which in itself also contains nested tables (note: the inner tables could be of different size). Suppose I've got the following type declarations:
CREATE OR REPLACE TYPE VECTOR
IS
TABLE OF NUMBER;
CREATE OR REPLACE TYPE TABLE_OF_VECTOR
IS
TABLE OF VECTOR;
And the following snippet of PL/SQL:
DECLARE
number_table TABLE_OF_VECTOR;
result_vector VECTOR;
BEGIN
number_table := table_of_vector(vector(23, 4, 2222, 22222222),
vector(2, 1, 766, 2), vector(2, 1, 5));
END;
Is there a way I can linearize number_table and store all of its values in result_vector as one continuous list of numbers? I wan开发者_如何学Got to end up with:
result_vector == vector(23, 4, 2222, 22222222, 2, 1, 766, 2, 2, 1, 5)
It does, but it isn't pretty.
select cast(collect(b.column_value) as vector)
from table(table_of_vector(
vector(23, 4, 2222, 22222222),
vector(2, 1, 766, 2),
vector(2, 1, 5))) a,
table(a.column_value) b;
So the table(...) a part treats the table_of_vector as a 'regular' table with a column with the name "COLUMN_VALUE". We then treat that as another table that I've called B.
The SELECTed expression takes all the individual numbers that made up the 'B' tables in the 'A' table and aggregates them into a collection (using COLLECT). Finally, I explicitly cast the collection as VECTOR type.
DECLARE
number_table TABLE_OF_VECTOR;
result_vector VECTOR:=vector();
BEGIN
number_table := table_of_vector(vector(23, 4, 2222, 22222222),
vector(2, 1, 766, 2), vector(2, 1, 5));
for i in number_table.first .. number_table.last loop
for j in number_table(i).first .. number_table(i).last loop
result_vector.extend();
result_vector(result_vector.count):=number_table(i)(j);
end loop;
end loop;
for i in 1.. result_vector.count loop
dbms_output.put_line(result_vector(i));
end loop;
END;
/
精彩评论