行变列构建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;
/

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