Вопрос по sql – Как получить индекс элементов VARRAY после преобразования в таблицу

4

В следующем примере я создаю VARRAY с 3 элементами.

[email protected]> 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);.

Ваш Ответ

3   ответа
1
 select t1, row_number() over ( partition by t1 order by t1), t2.* from
 (select 'X' as t1 from dual UNION select 'Y' from dual) t1,
 table (sys.odcivarchar2list('a', 'b', 'c'))             t2;
На самом деле, я неправильно понял. Вы хотите фактические индексы в массиве. Я думаю, что мое решение будет работать, если записи массива гарантированно будут возвращены в порядке индекса.
2

нет чистого решения SQL, которое гарантированно будет работать. Возможно, вам понадобится создать функцию PL / SQL для преобразования VARRAY из VARCHAR2 в VARRAY объектов.

Даже для приведенного ниже решения PL / SQL трудно сказать, что оно гарантированно работает. Я ничего не могу найти вСправочник по языку PL / SQL это явно говорит, что порядок элементов в конструкторе всегда будет соответствовать порядку индекса. Но примеры подразумевают, что порядок сохраняется, и если он не соответствует действительности, это вызовет всевозможные странные ошибки, с которыми я, вероятно, столкнулся бы сейчас ...

Обратите внимание, что мой пример можетnot работать для вложенных таблиц. Из руководства:

"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.
Error: User Rate Limit Exceeded HAL 9000
0

почему никто не придумал это, поэтому я отвечаю на свой вопрос

select t1, t2.* from 
(select 'X' as t1 from dual UNION select 'Y' from dual) t1, 
(select ROWNUM rn, COLUMN_VALUE from table (sys.odcivarchar2list('a', 'b', 'c'))) t2

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

Однако остается вопрос, гарантированно ли это работает на 100%?

Похожие вопросы