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;
end ma_test;
/