oracle存储过程如何取动态字段内容（临时表）并汇总合并输出数据集？？

Question

本帖最后由 bswendy 于 2010-11-30 16:18:58 编辑

Answer 1

-----你编译下试下看呢..可能要调整下那条语句。
create or replace procedure helpBswendy(ForgNum    number,
                                        FPeriodNum number,
                                        resultSet  out sys_refcursor o_ret number,
                                        o_msg      varchar2) as
  v_FHisTableName varchar2(200);
  v_sql           varchar2(2000);
begin
  select FHisTableName
    into v_FHisTableName
    from T_cmsScheme
   where ForgNumber := ForgNum;
  v_sql := 'select a.Fnumber,a.Fname,a.ForgNumber,a.ForgName,sum(b.FYF001) FYF001,sum(FSF002) FSF002 from T_cmsScheme a,' ||
           v_FHisTableName || ' b where b.FPeriodNum=' || FPeriodNum ||
           'and a.ForgNumber=' || ForgNum ||
           ' group by a.ForgNumber,a.Fnumber';
  open for resultSet;
exception
  when others then
    o_ret := sqlcode;
    o_msg := sqlerrm(sqlcode);
end;

Answer 2

--你这个可以不用那么复杂，只需要创建两个函数来分别获取合计的应发金额和合计的实发金额就可以了
--下面是我的测试过程，请参考：
--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

Answer 3

create or replace procedure p_result(ForgN varchar2,FPrdNumFrom number,FPrdNumTo number,cur out sys_refcursor)
as
str varchar2(1000);
begin
str:='select Fnumber,Fname,ForgNumber,ForgName,'||'(select sum(FYF001) FYF001,sum(FYF002) FYF002 sum from '||'FHisTableName
 where FPeriodNum between '||FPrdNumFrom||'  and  '||FPrdNumTo||')'||
' from T_cmsScheme  where ForgNumber='''||ForgN||'''';
open cur for str;
end;

Answer 4

谢谢楼上高手，但
 v_sql := 'select a.Fnumber,a.Fname,a.ForgNumber,a.ForgName,sum(b.FYF001) FYF001,sum(FSF002) FSF002 from T_cmsScheme a,' ||
           v_FHisTableName || ' b where b.FPeriodNum=' || FPeriodNum ||
           'and a.ForgNumber=' || ForgNum ||
           ' group by a.ForgNumber,a.Fnumber';是不是调整这条语句呢？怎么感觉有点不对呢？

Answer 5

---过程
----建立临时表
create global temporary table tmp2(fnumber varchar2(5),fname varchar2(10),forgnumber varchar2(10),
forgname varchar2(50),fhistablename varchar2(10),FYFsum1 number,FYFsum2 number) on commit preserve rows;
--过程
create or replace procedure p_result(ForgN varchar2,FPrdNumFrom number,FPrdNumTo number,cur out sys_refcursor)
as
str varchar2(1000);
num number;
begin
for i in(select fhistablename from T_cmsScheme where forgnumber=ForgN) loop
str:='select fnumber,fname,forgnumber,forgname,fhistablename,'||'(select sum(FYF001) FYF001 from '||i.fhistablename||' where FPeriodNum between '||FPrdNumFrom||' and
'||FPrdNumTo||'),(select sum(FYF002) FYF002 from '||i.fhistablename||' where FPeriodNum between '||FPrdNumFrom||' and '||FPrdNumTo||')'||' from T_cmsScheme where
fhistablename='''||i.fhistablename||'''';
dbms_output.put_line(str);
execute immediate 'insert into tmp2  '||str;
commit;
end loop;
open cur for 'select * from tmp2';
end;
SQL> /过程已创建。SQL> var cur refcursor
SQL> exec p_result('01.01.02',201001,201002,:cur)PL/SQL 过程已成功完成。SQL> print curFNUMB FNAME      FORGNUMBER FORGNAME                                           FHISTABLEN    FYFSUM1    FYFSUM2
----- ---------- ---------- -------------------------------------------------- ---------- ----------
A001  方案1      01.01.02   一分公司_本部_人事部                               T_DB_4500        7400       7900
A002  方案2      01.01.02    一分公司_本部_人事部                              T_DB_8872        6800       5500

Answer 6

---推荐使用过程，关键是采用了临时表
--函数create or replace function fun_sum(v_tb T_cmsScheme.Fhistablename%type,fnofrom number,fnoto number) return varchar2
as
num varchar2(100);
str varchar2(1000);
begin
str:='select sum(FYF001)'||'||'',''||'||'sum(FYF002) from '||v_tb||' where FPeriodName between '||fnofrom||' and '||fnoto;
execute immediate str into num;
return num;
end;
SQL> col FORGNAME format a30
SQL> col f_sum1 format a10
SQL> col f_sum2 format a10
SQL> 
SQL> select fnumber,fname,forgnumber,forgname,fhistablename,
  2  substr(fun_sum(Fhistablename,201001,201002),1,instr(fun_sum(Fhistablename,201001,201002),',',1)-1) f_sum1,
  3  substr(fun_sum(Fhistablename,201001,201002),instr(fun_sum(Fhistablename,201001,201002),',',1)+1) f_sum2
  4  from T_cmsScheme
  5  where forgnumber='01.01.02'
  6  /
 
FNUMBER FNAME      FORGNUMBER FORGNAME                       FHISTABLENAME F_SUM1     F_SUM2
------- ---------- ---------- ------------------------------ ------------- ---------- ----------
A001    方案1      01.01.02   一分公司_本部_人事部            T_DB_4500     7400       7900
A002    方案2      01.01.02    一分公司_本部_人事部           T_DB_8872     6800       5500

Answer 7

谢谢各位，我要结贴了，问题都已经解决！
分给wkc168和gelyon ，两位给我的帮助最大，谢谢！