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

---推荐使用过程，关键是采用了临时表

--函数



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

---过程









----建立临时表

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 cur



FNUMB 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

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;

--你这个可以不用那么复杂，只需要创建两个函数来分别获取合计的应发金额和合计的实发金额就可以了

--下面是我的测试过程，请参考：

--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 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;