灵活利用配置表，制作报表

最新推荐文章于 2022-09-22 00:46:20 发布

zhouruijie2007

最新推荐文章于 2022-09-22 00:46:20 发布

阅读量1.2k

点赞数

文章标签：报表 table report sql

本文链接：https://blog.csdn.net/zhouruijie2007/article/details/1303143

版权

create or replace procedure zhou_test as
sSql       varchar(2000);
TABLE_NAME varchar(60);
V_col_name varchar(60);
V_col_SQL varchar(1000);
-----根据我的配置表走
CURSOR sv_col IS
    select col_name, col_sql from zhou.sv_sc_report;
begin
----首先生成报表(初始化)（cno,cname）
table_name := 'REPORT_SC';
exist_table(table_name);
sSql := 'create TABLE zhou.REPORT_SC as    select CNO,CNAME FROM zhou.C';
EXECUTE IMMEDIATE sSql;
sSql := 'alter table REPORT_SC add   primary key(CNO)';
EXECUTE IMMEDIATE sSql;
----循环游标(遍历配置表)分别求每个条件的字段
OPEN sv_col;
loop
    FETCH sv_col
      INTO V_col_name, V_col_SQL;
    EXIT WHEN sv_col%NOTFOUND;

    table_name := 'SC_TEMP1';
    exist_table(table_name);
    sSql := 'create TABLE SC_TEMP1 as    select CNO,count(*) NUM,avg(grade) avg_num from SC WHERE   ' ||
            V_COL_SQL || ' GROUP BY CNO ';
    EXECUTE IMMEDIATE sSql;
    sSql := 'alter table SC_TEMP1 add   primary key(CNO)';
    EXECUTE IMMEDIATE sSql;
    table_name := 'SC_TEMP2';
    exist_table(table_name);
    sSql := 'create TABLE SC_TEMP2 as    select A.*,B.NUM ' || V_col_name ||
            '人数,b.avg_num ' || V_col_name ||
            '平均分 from REPORT_SC A,SC_TEMP1 B WHERE   A.CNO=B.CNO(+) ';
    EXECUTE IMMEDIATE sSql;
    sSql := 'alter table SC_TEMP2 add   primary key(CNO)';
    EXECUTE IMMEDIATE sSql;
    table_name := 'REPORT_SC';
    exist_table(table_name);
    sSql := 'RENAME SC_TEMP2 TO REPORT_SC';
    EXECUTE IMMEDIATE sSql;
    commit;

END LOOP;
close SV_COL;