ITPUB论坛－专业的IT技术社区

标题: 刚发现的利用SYS.ODCIVARCHAR2LIST进行列转行 [打印本页]

作者: cj402444206 时间: 2010-9-8 14:41
标题: 刚发现的利用SYS.ODCIVARCHAR2LIST进行列转行
众大牛们已经总结了行列转换的若干方法。今天发现了一种新的方法(

)，和大家分享下。
1.SYS.ODCIVARCHAR2LIST：
SELECT COLUMN_VALUE  FROM TABLE(SYS.ODCIVARCHAR2LIST('1','2','3','4','5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
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'));
COLUMN_VALUE
--------------------------------------------------------------------------------
1
2
3
4
5
但是，当'1','2','3','4','5'  作为一个字符串('1,2,3,4,5')就没有办法转换了：
SELECT COLUMN_VALUE  FROM TABLE(MY_ODCIVARCHAR2LIST('1,2,3,4,5'));
COLUMN_VALUE
--------------------------------------------------------------------------------
1,2,3,4,5

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

欢迎大家讨论，提出更多更好的方法~~

参考----------------------------------------------------------------
2.其他方法实现列转行（大牛们早已经总结，仅供参考）
(1) 利用CONNECT BY （使用9I，10G,11G）
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;

[ 本帖最后由 cj402444206 于 2010-9-8 16:02 编辑 ]

作者: dingjun123 时间: 2010-9-8 15:36
这个三思几年前就发现了

作者: bell6248 时间: 2010-9-8 15:38
这东西用起来可不可靠，是不是在官方文档上公布呢？

作者: dingjun123 时间: 2010-9-8 15:39

原帖由 bell6248 于 2010-9-8 15:38 发表
这东西用起来可不可靠，是不是在官方文档上公布呢？

明显不是，呵呵

作者: cp2833 时间: 2010-9-8 16:09
学习一下

作者: newkid 时间: 2010-9-8 22:07
没啥用，因为不能用绑定变量。

作者: 老妹你真浪 时间: 2010-9-9 08:07
对呀感觉用处不大还是用'[^,]+'

作者: dnxqw2013 时间: 2019-4-9 10:35
9年的帖子了

作者: newkid 时间: 2019-4-10 03:17
SYS.ODCIVARCHAR2LIST 就是一个预定义的 VARRAY而已，你要对它初始化就要先有一系列数组元素，它并不会替你执行任何字符串拆分的工作。

欢迎光临 ITPUB论坛－专业的IT技术社区 (http://www.itpub.net/)