sql - 获取 VARRAY 集合中元素的索引

Question

这看起来很简单，但我在文档中找不到答案。

与嵌套表一样，我们可以将 TABLE() 与 COLUMN_VALUE 伪列一起用于 varrays。

create or replace type NUMBER_ARRAY as VARRAY(10) of NUMBER;

create table TAB_WITH_ARRAY(
  ID    NUMBER,
  VALS  NUMBER_ARRAY)    

insert into TAB_WITH_ARRAY
select 1, NUMBER_ARRAY(1,2)
from dual
union all
select 2, NUMBER_ARRAY(1,2,3,4,5)
from dual

select t.id, c.column_value
from TAB_WITH_ARRAY   t,
     table(t.vals)   c

但是，与嵌套表不同，VARRAY 是有序集合类型，我想保留该顺序。有没有办法在SQL中不仅获取值而且获取每个元素的索引？

---

是的，在我的测试中输出的顺序是正确的，我可以只使用 ROW_NUMBER 和我的主表的 PARTITION BY 主键来生成索引，但是经验告诉我们除非手动指定，否则我不依赖于排序。

那么有没有内置的方法来访问数组中元素的索引？

Answer 1

使用ROW_NUMBER()解析函数:

SELECT t.id,
       c.COLUMN_VALUE,
       ROW_NUMBER() OVER ( PARTITION BY t.ROWID ORDER BY ROWNUM ) AS idx
FROM   TAB_WITH_ARRAY t
       CROSS JOIN
       TABLE( t.vals ) c

或者，从 Oracle 12c 开始，使用 OUTER APPLY 和 ROWNUM:

SELECT t.id,
       v.*
FROM   tab_with_array t
       OUTER APPLY (
         SELECT v.COLUMN_VALUE,
                ROWNUM AS idx
         FROM   TABLE( t.vals ) v
       ) v

其中，对于您的示例数据，两个输出:

ID | COLUMN_VALUE | IDX-: | -----------: | --: 1 |            1 |   1 1 |            2 |   2 2 |            1 |   1 2 |            2 |   2 2 |            3 |   3 2 |            4 |   4 2 |            5 |   5

db<> fiddle here