通用存储过程

CREATE OR REPLACE PROCEDURE DZZH_TJ(TABLENAME VARCHAR2,QXTNAME VARCHAR2,QXTYPETNAME  VARCHAR2,ORDERVALUE VARCHAR2,cur_out  out sys_refcursor) IS

  V_SQL  VARCHAR2(4000);--区县、型统计，及区县类型总计；SQl

  V_SQL1 VARCHAR2(4000);--类型总计；SQl

  V_SQL2 VARCHAR2(4000);--统计类型合并SQL

  V_SQL3 VARCHAR2(4000);--各区县类型统计信息；SQl

  V_SQL4 VARCHAR2(4000);--各类型的统计总数；SQl

  V_SQL5 VARCHAR2(4000);--获取所有类型；SQL

  ZHTYPE varchar2(4000);--统计类型变量；

  CURSOR_1 sys_refcursor;

  BEGIN

      V_SQL3:='select '||QXTNAME||' as XZQ,'||QXTYPETNAME||' as ZHTYPE,count('||QXTYPETNAME||') SUMBYTYPE

                       from '||TABLENAME||'

                       group by '||QXTNAME||','||QXTYPETNAME||'';

      V_SQL4:='select '||QXTYPETNAME||' as ZHTYPE, count('||QXTYPETNAME||')as SUMBYTYPE

                      from '||TABLENAME||'   group by '||QXTYPETNAME||' order by ZHTYPE';

      V_SQL5:='select '||QXTYPETNAME||' as ZHTYPE from '||TABLENAME||'  group by '||QXTYPETNAME||' order by '||QXTYPETNAME||'';

      V_SQL := 'SELECT XZQ';

      V_SQL1 := 'SELECT N''合计'' as XZQ';

 

       open CURSOR_1 for V_SQL5;
         loop

           fetch CURSOR_1 into ZHTYPE;

           exit when CURSOR_1%notfound;

           V_SQL := V_SQL   || ',' || 'SUM(DECODE(ZHTYPE,''' || ZHTYPE || ''',SUMBYTYPE,null)) AS ' || ZHTYPE ;

           V_SQL1 := V_SQL1 || ',' || 'SUM(DECODE(ZHTYPE,''' || ZHTYPE || ''',SUMBYTYPE,null)) AS ' || ZHTYPE ;

          END LOOP;

            V_SQL  := V_SQL  ||  ' , sum(SUMBYTYPE) as SUMTYPE     FROM ('||V_SQL3||') GROUP BY XZQ';

            V_SQL1 := V_SQL1 ||  ' , sum(SUMBYTYPE) as SUMTYPE     FROM ('||V_SQL4||')  GROUP BY null';

            V_SQL2 :='select * from ('||V_SQL||' union '||V_SQL1||')  '||ORDERVALUE||'';

      close CURSOR_1;

      open cur_out for V_SQL2;

    END;