sql - 转换为表格后如何获取 VARRAY 项目的索引

Question

在以下示例中，我创建了一个包含 3 个项目的 VARRAY。

TEST@XE> select t1, t2.* from
  2  (select 'X' as t1 from dual UNION select 'Y' from dual) t1,
  3  table (sys.odcivarchar2list('a', 'b', 'c'))             t2;

T1  COLUMN_VALUE
--- --------------------
X   a
X   b
X   c
Y   a
Y   b
Y   c

我想要以下输出:

T1  INDEX COLUMN_VALUE
--- ----- --------------------
X   1     a
X   2     b
X   3     c
Y   1     a
Y   2     b
Y   3     c

请注意 sys.odcivarchar2list预定义为 VARRAY(32767) OF VARCHAR2(4000); .

Answer 1

据我所知，没有保证可以正常工作的纯 SQL 解决方案。您可能需要创建一个 PL/SQL 函数来将 VARCHAR2 的 VARRAY 转换为对象的 VARRAY。

即使对于下面的 PL/SQL 解决方案，也很难说它保证有效。我在 PL/SQL Language Reference 中找不到任何内容明确表示构造函数中项目的顺序将始终与索引顺序匹配。但是这些例子暗示顺序是保留的，如果它不是真的，它会导致我现在可能会遇到的各种奇怪的错误..

请注意，我的示例可能不适用于嵌套表。从手册:

"When you store and retrieve a varray from the database, its indexes and element order remain stable." ... "The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database."

SQL> create or replace type varchar2_with_index as object
  2  (
  3     id number,
  4     value varchar2(4000)
  5  );
  6  /

Type created.

SQL> create or replace type varchar2_with_index_varray as
  2     varray(32767) of varchar2_with_index;
  3  /

Type created.

SQL> create or replace function add_index(p_list in sys.ODCIVarchar2List
  2  ) return varchar2_with_index_varray as
  3     v_new_list varchar2_with_index_varray := varchar2_with_index_varray();
  4  begin
  5     for i in 1 .. p_list.count loop
  6             v_new_list.extend;
  7             v_new_list(v_new_list.count) := varchar2_with_index(i, p_list(i));
  8     end loop;
  9     return v_new_list;
 10  end;
 11  /

Function created.

SQL> column value format a6
SQL> select t1, t2.* from
  2  (select 'X' as t1 from dual UNION select 'Y' from dual) t1,
  3  table (add_index(sys.odcivarchar2list('a', 'b', 'c')))  t2;

T         ID VALUE
- ---------- ------
X          1 a
X          2 b
X          3 c
Y          1 a
Y          2 b
Y          3 c

6 rows selected.