线性化 Oracle 嵌套表

发布于 2024-10-05 02:10:35 字数 602 浏览 3 评论 0原文

如何线性化嵌套表，该表本身也包含嵌套表（注意：内部表可能具有不同的大小）。假设我有以下类型声明：

CREATE OR REPLACE TYPE VECTOR
IS
  TABLE OF NUMBER;

CREATE OR REPLACE TYPE TABLE_OF_VECTOR
IS
  TABLE OF VECTOR;

以及以下 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;

有没有一种方法可以线性化 number_table 并将其所有值作为一个连续的数字列表存储在 result_vector 中？我想结束：

result_vector == vector(23, 4, 2222, 22222222, 2, 1, 766, 2, 2, 1, 5)

原文

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 want to end up with:

result_vector == vector(23, 4, 2222, 22222222, 2, 1, 766, 2, 2, 1, 5)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情徒 2024-10-12 02:10:35

确实如此，但并不漂亮。

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;

因此 table(...) 部分将 table_of_vector 视为“常规”表，其中有一列名为“COLUMN_VALUE”。然后，我们将其视为另一个我称为 B 的表。

SELECTed 表达式获取构成“A”表中的“B”表的所有单独数字，并将它们聚合到一个集合中（使用 COLLECT）。最后，我明确地将集合转换为 VECTOR 类型。

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.

回复收藏 0 原文

清君侧 2024-10-12 02:10:35

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

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

回复收藏 0 原文

~没有更多了~