行變列構建sql語句

很久以前做開發時候寫的儲存過程.

CREATE OR REPLACE PROCEDURE sp_test (
   p_tablename     IN   CHAR, --表名
   p_columnname    IN   CHAR, --行變列的條件列(在此例中是xbie00)
   p_columnout     IN   CHAR, --需要彙總的列(此例是rs0000)
   p_columnwhere   IN   CHAR --根據那個列來變(此例是zrc000)
)
IS
   TYPE cur_typ IS REF CURSOR;

   c_1           cur_typ; --動態遊標
   v_temptable   VARCHAR2 (50); --臨時表
   v_sql         VARCHAR2 (200); --SQL語句
   v_count       NUMBER; --總數
   v_outsql      VARCHAR2 (300); --生成的sql語句
   v_temp        VARCHAR2 (200); --臨時變數
   v_xbie00      CHAR (1); --性別
BEGIN
   BEGIN
      EXECUTE IMMEDIATE    'drop table '
                        || p_columnname
                        || '_temp'; --Drop臨時表
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   v_temptable :=    p_columnname
                  || '_temp';
   v_sql :=    'create table '
            || v_temptable
            || ' as select '
            || p_columnname
            || ' from '
            || p_tablename
            || ' group by '
            || p_columnname
            || ' order by '
            || p_columnname;
   DBMS_OUTPUT.put_line (v_sql); --生成臨時表(注意: 使用者需要有create any table 系統許可權
   EXECUTE IMMEDIATE v_sql;
   v_sql :=
             'select count(distinct '
          || p_columnname
          || ') from '
          || p_tablename; --有多少性別
   DBMS_OUTPUT.put_line (v_sql);
   EXECUTE IMMEDIATE v_sql
      INTO v_count;
   v_outsql := 'select t1.zrc000,';
   v_sql :=    'select '
            || p_columnname
            || ' from '
            || p_tablename
            || ' group by '
            || p_columnname; --構建SqL語句
  
/*
   v_sql :=    'select '
            || v_temp
            || p_columnname
            || ' ),from '
            || p_tablename
            || ' group by '
            || p_columnname;
*/
   OPEN c_1 FOR v_sql;

   LOOP
      FETCH c_1 INTO v_xbie00; --取性別
      EXIT WHEN c_1%NOTFOUND;
      v_temp :=    '(select '
                || p_columnout
                || ' from '
                || p_tablename
                || ' where '
                || p_columnname
                || ' = '
                || v_xbie00
                || ' and '
                || p_columnwhere
                || ' = t1.'
                || p_columnwhere
                || ' ),'; --對某個性別生成sql語句
      v_outsql :=    v_outsql
                  || v_temp; --構建sql語句
   END LOOP;

   CLOSE c_1;
   v_outsql := SUBSTR (v_outsql, 1,   LENGTH (TRIM (v_outsql))
                                    - 1); --把最後的','去掉
   v_outsql :=    v_outsql
               || ' from (select '
               || p_columnwhere
               || ' from '
               || p_tablename
               || ' group by '
               || p_columnwhere
               || ' ) t1'; --最後的sql部分

   DELETE      t_sql;

   INSERT INTO t_sql
               (str_sql)
        VALUES (v_outsql); --插入表中

   -- 清除臨時表     
   BEGIN
      EXECUTE IMMEDIATE    'drop table '
                        || p_columnname
                        || '_temp'; --Drop臨時表
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20637,    'error : '
                                       || v_sql);
END;
/