oracle存储过程如何取动态字段内容(临时表)并汇总合并输出数据集
--1、创建函数get_sum_FYF001 获取合计的应发金额
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,FPeriodNum1 IN VARCHAR2 ,FPeriodNum2 IN VARCHAR2 )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' where FPeriodNum between '||FPeriodNum1||' and '||FPeriodNum2;
EXECUTE IMMEDIATE sql_str INTO result;
RETURN result;
END;
--2、创建函数get_sum_FSF002获取合计的实发金额
CREATE OR REPLACE FUNCTION get_sum_FSF002(table_name IN VARCHAR2,FPeriodNum1 IN VARCHAR2 ,FPeriodNum2 IN VARCHAR2 )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
sql_str:='SELECT Sum(FSF002) FROM '||table_name||' where FPeriodNum between '||FPeriodNum1||' and '||FPeriodNum2;
EXECUTE IMMEDIATE sql_str INTO result;
RETURN result;
END;
--测试:
SELECT FNUMBER,FNAME,FORGNUMBER,FORGNAME,
get_sum_FYF001(FHISTABLENAME,'201001','201002') FYF001,
get_sum_FSF002(FHISTABLENAME,'201001','201002') FSF002
FROM T_cmsScheme
WHERE FORGNUMBER='01.01.02';
--结果:
FNUMBER FNAME FORGNUMBER FORGNAME FYF001 FSF002
-------------------------------------------------------------------------------
A001 方案1 01.01.02 一分公司_本部_人事部 7400 7900
A002 方案2 01.01.02 一分公司_本部_人事部 6800 5500
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,FPeriodNum1 IN VARCHAR2 ,FPeriodNum2 IN VARCHAR2 )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
sql_str:='SELECT Sum(FYF001) FROM '||table_name||' where FPeriodNum between '||FPeriodNum1||' and '||FPeriodNum2;
EXECUTE IMMEDIATE sql_str INTO result;
RETURN result;
END;
--2、创建函数get_sum_FSF002获取合计的实发金额
CREATE OR REPLACE FUNCTION get_sum_FSF002(table_name IN VARCHAR2,FPeriodNum1 IN VARCHAR2 ,FPeriodNum2 IN VARCHAR2 )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
sql_str:='SELECT Sum(FSF002) FROM '||table_name||' where FPeriodNum between '||FPeriodNum1||' and '||FPeriodNum2;
EXECUTE IMMEDIATE sql_str INTO result;
RETURN result;
END;
--测试:
SELECT FNUMBER,FNAME,FORGNUMBER,FORGNAME,
get_sum_FYF001(FHISTABLENAME,'201001','201002') FYF001,
get_sum_FSF002(FHISTABLENAME,'201001','201002') FSF002
FROM T_cmsScheme
WHERE FORGNUMBER='01.01.02';
--结果:
FNUMBER FNAME FORGNUMBER FORGNAME FYF001 FSF002
-------------------------------------------------------------------------------
A001 方案1 01.01.02 一分公司_本部_人事部 7400 7900
A002 方案2 01.01.02 一分公司_本部_人事部 6800 5500