oracle中SYS.ODCIVARCHAR2LIST(）实现行转列

原创已于 2022-04-18 16:24:36 修改 · 2.6k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #数据库

于 2021-09-06 15:11:08 首次发布

数据库学习专栏收录该内容

20 篇文章

订阅专栏

本文介绍了Oracle中如何使用SYS.ODCIVARCHAR2LIST类型进行行转列操作，特别指出该功能在9i及以上版本可用，而在9i版本中可通过创建自定义TYPE实现。此外，还提供了两种其他方法实现行转列：1) 利用CONNECT BY子句解析字符串；2) 使用正则表达式在10G及以上版本进行转换。这些方法对于处理字符集数据尤其有用。

SYS.ODCIVARCHAR2LIST：

SELECT COLUMN_VALUE  FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));

Oracle 10G 以上版本才支持SYS.ODCIVARCHAR2LIST，其实SYS.ODCIVARCHAR2LIST只不过是一个TYPE，
所以在9I版本中可以通过创建一个TYPE来使用该功能：

CREATE OR REPLACE TYPE MY_ODCIVARCHAR2LIST AS VARRAY(32767) OF VARCHAR2(4000);

SELECT COLUMN_VALUE  FROM TABLE(MY_ODCIVARCHAR2LIST('1','2','3','4','5'));

总结：(1)Table函数将数组里的内容通过SQL语句查询出来；
(2)ODCIVARCHAR2LIST 在9I 及以上版本中均可使用。在9I中可通过创建TYPE，10G及以上直接使用SYS.ODCIVARCHAR2LIST；
(3)ODCIVARCHAR2LIST 适用于字符集，不适用单个字符串，如果是单个字符串，可以通过参考如下方法实现。

其他方法实现行转列（仅供参考）

(1) 利用CONNECT BY （使用9I，10G,11G，12C）

WITH T AS (SELECT  '1,2,3,4,5' AS STR FROM DUAL)
SELECT  STR1  
FROM ( SELECT  DISTINCT
                SUBSTR(T.CA,INSTR(T.CA, ',', 1, C.LV) + 1,
                       INSTR(T.CA, ',', 1, C.LV + 1) -(INSTR(T.CA, ',', 1, C.LV) + 1)) AS STR1
       FROM (SELECT ',' || STR || ',' AS CA,LENGTH(STR || ',') -NVL(LENGTH(REPLACE(STR, ',')), 0) AS CNT FROM T) T,
            (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 9) C
       WHERE C.LV <= T.CNT
       ORDER BY STR1);

(2).正则表达式（使用10G及以上版本）

WITH TEST AS (SELECT  '1,2,3,4,5' AS STR FROM DUAL)
SELECT DISTINCT REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)
FROM TEST
CONNECT BY ROWNUM <= 5;