行转列问题研究

--行转列问题研究：
1、正则表达式法：
SELECT LEVEL,REGEXP_SUBSTR('XY,YZ,OK','[^,]+',1,LEVEL) AS
  VALUE_STR FROM DUAL CONNECT BY LEVEL<=LENGTH('XY,YZ,OK')-LENGTH(REPLACE('XY,YZ,OK',','))+1;
 
2、常规函数：
SELECT LEVEL,
  SUBSTR(INLIST,
     INSTR(INLIST,',',1,LEVEL)+1,
     INSTR(INLIST,',',1,LEVEL+1)
     -INSTR(INLIST,',',1,LEVEL)-1)
   AS VALUE_STR
   FROM (SELECT ','||'XY,YZ,OK'||',' AS INLIST
     FROM DUAL)
   CONNECT BY LEVEL<=
     LENGTH('XY,YZ,OK')-LENGTH(REPLACE('XY,YZ,OK',',',''))+1;
    
3、上下文与视图法：
1)创建上下文：
CREATE OR REPLACE CONTEXT INLIST_CTX USING SET_INLIST_CTX_PRC;
2)建立与上下文创建相关的过程
CREATE OR REPLACE PROCEDURE SET_INLIST_CTX_PRC(P_VAL IN VARCHAR2)
  /**
   ||程序说明：
   ||上下文INLIST_CTX属性名为STR
   ||p_val为属性对应的值
   **/
 AS
 BEGIN
   DBMS_SESSION.SET_CONTEXT('INLIST_CTX','STR',P_VAL);
 END;
 /
3)建立视图：
--创建动态视图，让SYS_CONTEXT动态给视图传常参数，只需要将前面语句中的绑定变量:str改为
--SYS_CONTEXT('INLIST_CTX','STR')就可以了
CREATE OR REPLACE VIEW V_INLIST
AS
  SELECT LEVEL AS LEV,
  SUBSTR(INLIST,
    INSTR(INLIST,',',1,LEVEL)+1,
    INSTR(INLIST,',',1,LEVEL+1)
    - INSTR(INLIST,',',1,LEVEL)-1)
  AS VALUE_STR
  FROM (SELECT ','||SYS_CONTEXT('INLIST_CTX','STR')||','
          AS INLIST
        FROM DUAL)
  CONNECT BY LEVEL<=
  LENGTH(SYS_CONTEXT('INLIST_CTX','STR')) 
  -LENGTH(REPLACE(SYS_CONTEXT('INLIST_CTX','STR'),',',''))+1;
4)测试：
CALL SET_INLIST_CTX_PRC('ab,bc,cd');
SELECT * FROM V_INLIST;

CALL SET_INLIST_CTX_PRC('x,y,z');
SELECT * FROM V_INLIST;

4、集合构造伪表法：
1)创建嵌套表：
CREATE OR REPLACE TYPE VARCHAR2_TT AS TABLE
 OF VARCHAR2(1000);
2)创建函数：
CREATE OR REPLACE FUNCTION F_STR2LIST(IN_STR IN VARCHAR2,IN_DELIMITER IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2_TT
/************************************
||程序说明：将按指定分隔的字符串转为嵌套表类型变量返回
||输入变量：
||            in_str 字符串，如'a,b,c'
||            in_delimiter 分隔符，默认是逗号
||输出变量：
||            varchar2_tt类型，嵌套表
************************************/
AS
  V_STR     VARCHAR2(32767) DEFAULT IN_STR||IN_DELIMITER;
  V_RESULT  VARCHAR2_TT := VARCHAR2_TT();
  I         NUMBER;
BEGIN

  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(USERENV('CLIENT_INFO')+1);  --用来测试函数的调用次数。
  LOOP
    EXIT WHEN V_STR IS NULL;
    i:=INSTR(V_STR,IN_DELIMITER);
    V_RESULT.EXTEND;
    V_RESULT(V_RESULT.COUNT):=TRIM(SUBSTR(V_STR,1,i-1));
    V_STR:=SUBSTR(V_STR,i+1);
  END LOOP;
  RETURN V_RESULT;
END;
/
4)测试：
CALL DBMS_APPLICATION_INFO.set_client_info(0);
SELECT * FROM TABLE(F_STR2LIST('a,b,c'));
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME IN
  (SELECT /*+ cardinality(tab,5) */ * FROM TABLE(CAST(F_STR2LIST('ALL_DFYH_TABLE,ASAS')
    AS VARCHAR2_TT)) TAB); 
SELECT USERENV('CLIENT_INFO') FROM DUAL;

5、自定义聚合函数法：
0)创建类型：
CREATE OR REPLACE TYPE T_CAT AS OBJECT
( UNION_MC VARCHAR2(4000),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_CAT) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE
  (
    SELF  IN OUT T_CAT,
    VALUE IN VARCHAR2
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE
  (
    SELF        IN T_CAT,
    RETURNVALUE OUT VARCHAR2,
    FLAGS       IN NUMBER
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE
  (
    SELF IN OUT T_CAT,
  CTX2 IN T_CAT
 ) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY T_CAT IS
 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_CAT) RETURN NUMBER IS
 BEGIN
  SCTX := T_CAT('');
  RETURN ODCICONST.SUCCESS;
 END;

 MEMBER FUNCTION ODCIAGGREGATEITERATE
 (
  SELF  IN OUT T_CAT,
  VALUE IN VARCHAR2
 ) RETURN NUMBER IS
 BEGIN
  SELF.UNION_MC := SELF.UNION_MC || VALUE || ',';
  RETURN ODCICONST.SUCCESS;
 END;

 MEMBER FUNCTION ODCIAGGREGATETERMINATE
 (
  SELF        IN T_CAT,
  RETURNVALUE OUT VARCHAR2,
  FLAGS       IN NUMBER
 ) RETURN NUMBER IS
 BEGIN
  RETURNVALUE := TRIM(TRAILING ',' FROM SELF.UNION_MC);
  RETURN ODCICONST.SUCCESS;
 END;

 MEMBER FUNCTION ODCIAGGREGATEMERGE
 (
  SELF IN OUT T_CAT,
  CTX2 IN T_CAT
 ) RETURN NUMBER IS
 BEGIN
  RETURN ODCICONST.SUCCESS;
 END;
END;
1)创建函数：
CREATE OR REPLACE FUNCTION CATSTR(V_MC VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING T_CAT;
2)测试：
SELECT CATSTR(COLUMN_NAME) FROM USER_TAB_COLS WHERE TABLE_NAME='DFGLDXSJ5061';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20709695/viewspace-706029/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20709695/viewspace-706029/