求助oracle存储过程高手：多表连接取动态表的动态字段（多个，字段名由其它表取）之和。

--方法：
--还是采用我之前回你那个帖子思路，
-- 应发工资绩效工资  岗位工资各种津贴补贴补助
--各自通过函数来求
--具体如下：--这里强调一点你的错误！
--临时表3 T_DB_9983第一个字段名称FPeriodNum 应该是错的，
--跟你临时表1，2期间编号名称FPeriodID不一致，这样在动态取应发工资等时候会报错
--下面测试，这里我已经将临时表3 T_DB_9983第一个字段名称FPeriodNum改成 FPeriodID--1、创建'应发工资'函数get_sum_FYF001 获取合计的应发金额
--考虑应发工资有可能不是每个临时表都会有的字段，如后面的'绩效工资'等
--处理方法如下：
CREATE OR REPLACE FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
     BEGIN
          sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                   'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;          --Dbms_Output.put_line(sql_str);
          EXECUTE IMMEDIATE sql_str INTO result;
     EXCEPTION WHEN OTHERS THEN
          result:=0;
     END ;
     RETURN result;
EXCEPTION WHEN OTHERS THEN
     Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ;
END;--2、创建'绩效工资'函数get_sum_FJX002 获取合计的绩效工资金额
--这里需要注意一点就是：
--只有临时表T_DB_8872中才有绩效工资字段FJX002,无法满足所有临时表有此字段，
--故，我采用异常形式来取临时表中无该字段'绩效工资'
CREATE OR REPLACE FUNCTION get_sum_FJX002(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
     BEGIN
          sql_str:='SELECT Sum(FJX002) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                   'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
          --Dbms_Output.put_line(sql_str);
          EXECUTE IMMEDIATE sql_str INTO result;
     EXCEPTION WHEN OTHERS THEN
          result:=0;
     END ;
     RETURN result;
EXCEPTION WHEN OTHERS THEN
     Raise_Application_Error('-20002','Error: get_sum_FJX002 !') ;
END;
--3、创建'岗位工资'函数get_sum_FGW003 获取合计的岗位工资金额
--处理思路跟上面'绩效工资' get_sum_FJX002处理一样。
CREATE OR REPLACE FUNCTION get_sum_FGW003(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
RETURN NUMBER
IS
result NUMBER;
sql_str VARCHAR2(2000);
BEGIN
     BEGIN
          sql_str:='SELECT Sum(FGW003) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                   'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
          --Dbms_Output.put_line(sql_str);
          EXECUTE IMMEDIATE sql_str INTO result;
     EXCEPTION WHEN OTHERS THEN
          result:=0;
     END ;
     RETURN result;
EXCEPTION WHEN OTHERS THEN
     Raise_Application_Error('-20003','Error: get_sum_FGW003 !') ;
END;--4、创建'各种津贴补贴补助'函数get_sum_FtmpFld 获取合计的各种津贴补贴补助金额
--这个稍微麻烦点，处理思路:
--由于你各种补贴的字段名称，在T_cmpItem中获取，因此采用循环处理各种补贴
CREATE OR REPLACE FUNCTION get_sum_FtmpFld(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
RETURN NUMBER
IS
result  NUMBER;  --返回的最终各种补贴结果
tmp     NUMBER;  --记录单个补贴的中间变量
sql_str VARCHAR2(2000);  --动态SQL拼接变量
BEGIN
     tmp:=0;
     result:=0;
     --Dbms_Output.put_line('tmp: '||tmp||'  result: '||result);
     FOR rs IN (select DISTINCT fnumber from T_cmpItem
                where Fname like '%补贴%' or Fname like '%津贴%' or Fname like '%补助%')
     LOOP
         BEGIN
              --Dbms_Output.put_line('fnumber: '||rs.fnumber);
              sql_str:='SELECT Sum('||rs.fnumber||') FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO tmp;
         EXCEPTION WHEN OTHERS THEN
              tmp:=0;
         END ;
         --Dbms_Output.put_line('tmp: '||tmp);
         result:=result+tmp;
         --Dbms_Output.put_line('result: '||result);
     END LOOP;
     RETURN result;
EXCEPTION WHEN OTHERS THEN
     Raise_Application_Error('-20004','Error: get_sum_FtmpFld !') ;
END;
--测试：
SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,
       get_sum_FYF001(a.FHisTableName,201001,201002) FYF001,
       get_sum_FJX002(a.FHisTableName,201001,201002) FJX002,
       get_sum_FGW003(a.FHisTableName,201001,201002) FGW003,
       get_sum_FtmpFld(a.FHisTableName,201001,201002)FtmpFld
FROM  T_cmsScheme a left join T_OrgAdmin b
ON a.ForgID=b.FID
WHERE b.ForgNumber like '01.01%'--结果：
FNUMBER  FNAME   FORGNUMBER   FORGNAME                FYF001   FJX002    FGW003   FTMPFLD
-------------------------------------------------------------------------------------------------
A001     方案1   01.01.02     一分公司_本部_人事部    7400     0         2500     930
A002     方案2   01.01        一分公司_本部           6800     5500      1500     300

--非要用过程来实现，可以将4个函数写在过程中，调用就可以了
--如下：存储过程实现
CREATE OR REPLACE PROCEDURE get_sum_Info(v_ForgNumb IN VARCHAR2 ,
                                         Form_FPeriodNum IN VARCHAR2 ,
                                         To_FPeriodNum IN VARCHAR2 ,
                                         resultSet OUT sys_refcursor )
IS
  --4个函数function的申明和实现：
    FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'应发工资'函数get_sum_FYF001 获取合计的应发金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ;
    END;    FUNCTION get_sum_FJX002(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'绩效工资'函数get_sum_FJX002 获取合计的绩效工资金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FJX002) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20002','Error: get_sum_FJX002 !') ;
    END;    FUNCTION get_sum_FGW003(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'岗位工资'函数get_sum_FGW003 获取合计的岗位工资金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FGW003) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20003','Error: get_sum_FGW003 !') ;
    END;    FUNCTION get_sum_FtmpFld(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'各种津贴补贴补助'函数get_sum_FtmpFld 获取合计的各种津贴补贴补助金额
    */
    IS
    result  NUMBER;  --返回的最终各种补贴结果
    tmp     NUMBER;  --记录单个补贴的中间变量
    sql_str VARCHAR2(2000);  --动态SQL拼接变量
    BEGIN
        tmp:=0;
        result:=0;
        --Dbms_Output.put_line('tmp: '||tmp||'  result: '||result);
        FOR rs IN (select DISTINCT fnumber from T_cmpItem
                    where Fname like '%补贴%' or Fname like '%津贴%' or Fname like '%补助%')
        LOOP
            BEGIN
                  --Dbms_Output.put_line('fnumber: '||rs.fnumber);
                  sql_str:='SELECT Sum('||rs.fnumber||') FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                          'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
                  --Dbms_Output.put_line(sql_str);
                  EXECUTE IMMEDIATE sql_str INTO tmp;
            EXCEPTION WHEN OTHERS THEN
                  tmp:=0;
            END ;
            --Dbms_Output.put_line('tmp: '||tmp);
            result:=result+tmp;
            --Dbms_Output.put_line('result: '||result);
        END LOOP;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20004','Error: get_sum_FtmpFld !') ;
    END;--存储过程begin：
BEGIN
    OPEN resultSet FOR
         'SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,
                get_sum_FYF001(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FYF001,
                get_sum_FJX002(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FJX002,
                get_sum_FGW003(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FGW003,
                get_sum_FtmpFld(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||')FtmpFld
         FROM  T_cmsScheme a left join T_OrgAdmin b
         ON a.ForgID=b.FID
         WHERE b.ForgNumber like '''||v_ForgNumb||'%''';

END;

--上面过程中调用函数，错了，应该拼接，好像这样：
--你测试下，上面单个函数实现我已测试了的，过程实现我没测试CREATE OR REPLACE PROCEDURE get_sum_Info(v_ForgNumb IN VARCHAR2 ,
                                         Form_FPeriodNum IN VARCHAR2 ,
                                         To_FPeriodNum IN VARCHAR2 ,
                                         resultSet OUT sys_refcursor )
IS .....
.....--存储过程begin：
BEGIN
    OPEN resultSet FOR
         'SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,'
                ||get_sum_FYF001(||'a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FYF001,'
                ||get_sum_FJX002(||'a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FJX002,'
                ||get_sum_FGW003(||'a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FGW003,'
                ||get_sum_FtmpFld(||'a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||')FtmpFld '
         ||'FROM  T_cmsScheme a left join T_OrgAdmin b
         ON a.ForgID=b.FID
         WHERE b.ForgNumber like '''||v_ForgNumb||'%''';

END;

哇，gelyon 真高手！！非常感谢，我得慢慢消化一下先。

gelyon真实个乐于助人的好孩子

可以说是通用 ---- 建表数据
create table T_cmsScheme
(fnumber varchar2(5),fname varchar2(10),ForgID varchar2(10),
fhistablename varchar2(10))insert into T_cmsScheme
select 'A001','方案1','0lk1', 'T_DB_4500' from dual union all
select 'A002','方案2' ,'0ka2', 'T_DB_8872' from dual union all
select 'A003','方案3', '0jh3','T_DB_9983' from dualcreate table T_OrgAdmin
(ForgID varchar2(10),forgnumber varchar2(10),forgname varchar2(50))insert into T_OrgAdmin
select '0lk1','01.01.02','一分公司_本部_人事部' from dual union all
select '0ka2' ,'01.01','一分公司_本部' from dual union all
select '0jh3', '01.02' ,'二分公司_本部' from dual
create table T_cmpItem
(fid varchar2(10),fnumber varchar2(10),fname varchar2(10))insert into T_cmpItem
select 'THC1','FSAL001','节日补助' from dual union all
select 'TDS1','FSAL001','节日补助' from dual union all
select 'TWW3','FSAL001','节日补助' from dual union all
select 'THC2','FSAL002','电脑补助' from dual union all
select 'TSD3','FSAL002','电脑补助' from dual union all
select 'THC3','FSAL003','交通补贴' from dual union all
select 'THC4','FSAL004','岗位津贴' from dual union all
select 'TKL3','FSAL004','岗位津贴' from dual union all
select 'THC5','FSAL005','职称津贴' from dual union all
select 'THC6','FSAL220','物业费' from dual union all
select 'TWE3','FSAL220','物业费' from dual union all
select 'THC7','FSAL221','卫生费' from dual union all
select 'THC8','FSAL225','房租费' from dual union all
select 'THC9','FSAL302','加班费' from dual create table T_cmpPeriod
(fid varchar2(10),FPeriodNum number,FPeriodName number)

insert into T_cmpPeriod
select 'PKL1',201001,201001 from dual union all
select 'PJK2',201002,201002 from dual union all
select 'PGD3',201003,201003 from dual union all
select 'PYR4',201004,201004 from dual union all
select 'PEW5',201005,201005 from dual create table T_DB_4500
(FPeriodID varchar2(10),FYF001 number,FSAL004 number,
FSAL001 number,FSAL003 number,FGW003 number,FSAL225 number)insert into T_DB_4500
select 'PKL1',1000, 80,  50, 100, 500, 200 from dual union all
select 'PKL1',1400, 100, 50, 100, 600, 140 from dual union all
select 'PJK2',2000, 100, 50, 100, 1000, 200 from dual union all
select 'PJK2',3000, 50,  50, 100, 400, 200 from dual
create table T_DB_8872
(FPeriodID varchar2(10),FYF001 number,FJX002 number,
FGW003 number,FSAL001 number,FSAL004 number,FSAL221 number,FSAL225 number)insert into T_DB_8872
select 'PJK2', 4000, 3000, 1000, 50, 100, 10, 200 from dual union all
select 'PJK2', 2800, 2500, 500, 50, 100, 10, 200 from dual union all
select 'PGD3', 1000, 1200, 600, 50, 100, 10, 200 from dual union all
select 'PYR4', 1200, 1300, 400, 50, 50, 10, 200 from dual union all
select 'PEW5' ,2000, 2200, 200, 50, 50, 10, 200 from dual create table T_DB_9983
(FPeriodID varchar2(10), FYF001 number,FSF002 number,FSAL005 number,
FSAL003 number,FSAL221 number,FSAL004 number)
insert into T_DB_9983
select 'PJK2',3000, 2000, 10, 40, 10, 50 from dual union all
select 'PJK2',2300, 2500, 20, 50, 15, 50 from dualcommit
  CREATE global temporary TABLE TMP2
   (    FNUMBER VARCHAR2(20),
        FNAME VARCHAR2(20),
        FORGNUMBER VARCHAR2(20),
        FORGNAME VARCHAR2(20),
        FYF001 NUMBER,
        FJX002 NUMBER,
        FGW003 NUMBER,
        FTMPFLD NUMBER
   ) on commit preserve rows

---过程代码
create or replace procedure p_tmp2(v_ForgNumber varchar2,FPrdNumFrom number,FPrdNumTo number,cur out sys_refcursor)
as
str1 varchar2(1000);
str2 varchar2(1000);
str varchar2(3000);
str3 varchar2(500);
begin
for i in (select a.FNUMBER,a.FNAME,b.FORGNUMBER,b.FORGNAME,FHisTableName from T_cmsScheme a,T_OrgAdmin b where a.forgid=b.forgid and instr(b.forgnumber,'01.01',1)>0)
loop
for j in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
loop
str1:=str1||','||'sum('||j.cname||')  '||j.cname;
end loop;
for h in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
loop
str3:=str3||','||h.cname;
end loop;
for l in
(select k.COLUMN_NAME cname2 from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  in(select distinct Fnumber from T_cmpItem where instr(Fname,'贴',1)>0 or Fname like '%补助%'))
loop
str2:=str2||'+'||l.cname2;
end loop;
str:='select '''||i.FNUMBER||''','''||i.FNAME||''','''||i.FORGNUMBER||''','''||i.FORGNAME||''','||ltrim(str1,',')||',sum('||ltrim(str2,'+')||')
from '||i.fhistablename||' a,T_cmpPeriod b where b.fid=a.FPeriodID and b.FPeriodNum between '||FPrdNumFrom||' and '||FPrdNumTo;
execute immediate 'insert into tmp2(FNUMBER,FNAME,FORGNUMBER,FORGNAME,'||ltrim(str3,',')||',FtmpFld)  '||str;
str1:='';
str3:='';
str2:='';
commit;
end loop;
open cur for 'select * from tmp2';
end;
---运行过程
SQL> create or replace procedure p_tmp2(v_ForgNumber varchar2,FPrdNumFrom number,FPrdNumTo number,cu
r out sys_refcursor)
  2  as
  3  str1 varchar2(1000);
  4  str2 varchar2(1000);
  5  str varchar2(3000);
  6  str3 varchar2(500);
  7  begin
  8  for i in (select a.FNUMBER,a.FNAME,b.FORGNUMBER,b.FORGNAME,FHisTableName from T_cmsScheme a,T_O
rgAdmin b where a.forgid=b.forgid and instr(b.forgnumber,'01.01',1)>0)
  9  loop
10  for j in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName an
d k.column_name<>'FPERIODID'
11  and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
12  loop
13  str1:=str1||','||'sum('||j.cname||')  '||j.cname;
14  end loop;
15  for h in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName an
d k.column_name<>'FPERIODID'
16  and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
17  loop
18  str3:=str3||','||h.cname;
19  end loop;
20  for l in
21  (select k.COLUMN_NAME cname2 from user_tab_cols k where k.table_name=i.FHisTableName and k.colu
mn_name<>'FPERIODID'
22  and k.COLUMN_NAME  in(select distinct Fnumber from T_cmpItem where instr(Fname,'贴',1)>0 or Fna
me like '%补助%'))
23  loop
24  str2:=str2||'+'||l.cname2;
25  end loop;
26  str:='select '''||i.FNUMBER||''','''||i.FNAME||''','''||i.FORGNUMBER||''','''||i.FORGNAME||''',
'||ltrim(str1,',')||',sum('||ltrim(str2,'+')||')
27  from '||i.fhistablename||' a,T_cmpPeriod b where b.fid=a.FPeriodID and b.FPeriodNum between '||
FPrdNumFrom||' and '||FPrdNumTo;
28  execute immediate 'insert into tmp2(FNUMBER,FNAME,FORGNUMBER,FORGNAME,'||ltrim(str3,',')||',Ftm
pFld)  '||str;
29  str1:='';
30  str3:='';
31  str2:='';
32  commit;
33  end loop;
34  open cur for 'select * from tmp2';
35  end;
36  /过程已创建。
SQL> col FNUMBER format a10
SQL> col FNAME format a10
SQL> col FORGNUMBER format a15
SQL> set linesize 150SQL> var cur refcursor
SQL> exec p_tmp2('01.01',201001,201002,:cur)PL/SQL 过程已成功完成。SQL> print curFNUMBER    FNAME      FORGNUMBER      FORGNAME                 FYF001     FJX002     FGW003    FTMPFLD
---------- ---------- --------------- -------------------- ---------- ---------- ---------- --------
A001       方案1      01.01.02        一分公司_本部_人事部       7400                  2500        930
A002       方案2      01.01           一分公司_本部              6800       5500       1500        300SQL> select * from tmp2
  2  /FNUMBER    FNAME      FORGNUMBER      FORGNAME                 FYF001     FJX002     FGW003    FTMPFLD
---------- ---------- --------------- -------------------- ---------- ---------- ---------- --------
A001       方案1      01.01.02        一分公司_本部_人事部       7400                  2500        930
A002       方案2      01.01           一分公司_本部              6800       5500       1500        300

回复内容一个用户只允许连续回复3次。
--解释方案依然采用临时表加动态拼接 user_tab_cols （以及嵌套循环拼接)--花了快一个小多时（主要建表看数据费时间);
--昨天回去帮你想了想，只有借用user_tab_cols 才能拼接出--而你要是把 3、薪酬项目表：T_cmpItem 设计好点就不要这么花时间
---你自己去琢磨吧

下次提问得把建表数据代码全部贴上来不贴谁有这么多的时间来解决 csdn 又不发工资呵呵

--今天测试了下存储过程，
--之前我的10楼过程是对的，不需要拼接，下面是测试：
--存储过程实现：

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> --创建过程:
SQL> edit
Wrote file afiedt.buf  1  CREATE OR REPLACE PROCEDURE get_sum_Info(v_ForgNumb IN VARCHAR2 ,
  2                                           Form_FPeriodNum IN VARCHAR2 ,
  3                                           To_FPeriodNum IN VARCHAR2 ,
  4                                           resultSet OUT sys_refcursor )
  5  IS
  6    --4个函数function的申明和实现：
  7      FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VA
  8      RETURN NUMBER
  9      /*
10      * 创建'应发工资'函数get_sum_FYF001 获取合计的应发金额
11      */
12      IS
13      result NUMBER;
14      sql_str VARCHAR2(2000);
15      BEGIN
16          BEGIN
17                sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=
18                        'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
19                --Dbms_Output.put_line(sql_str);
20                EXECUTE IMMEDIATE sql_str INTO result;
21          EXCEPTION WHEN OTHERS THEN
22                result:=0;
23          END ;
24          RETURN result;
25      EXCEPTION WHEN OTHERS THEN
26          Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ;
27      END;
28      FUNCTION get_sum_FJX002(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VA
29      RETURN NUMBER
30      /*
31      * 创建'绩效工资'函数get_sum_FJX002 获取合计的绩效工资金额
32      */
33      IS
34      result NUMBER;
35      sql_str VARCHAR2(2000);
36      BEGIN
37          BEGIN
38                sql_str:='SELECT Sum(FJX002) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=
39                        'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
40                --Dbms_Output.put_line(sql_str);
41                EXECUTE IMMEDIATE sql_str INTO result;
42          EXCEPTION WHEN OTHERS THEN
43                result:=0;
44          END ;
45          RETURN result;
46      EXCEPTION WHEN OTHERS THEN
47          Raise_Application_Error('-20002','Error: get_sum_FJX002 !') ;
48      END;
49      FUNCTION get_sum_FGW003(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VA
50      RETURN NUMBER
51      /*
52      * 创建'岗位工资'函数get_sum_FGW003 获取合计的岗位工资金额
53      */
54      IS
55      result NUMBER;
56      sql_str VARCHAR2(2000);
57      BEGIN
58          BEGIN
59                sql_str:='SELECT Sum(FGW003) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=
60                        'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
61                --Dbms_Output.put_line(sql_str);
62                EXECUTE IMMEDIATE sql_str INTO result;
63          EXCEPTION WHEN OTHERS THEN
64                result:=0;
65          END ;
66          RETURN result;
67      EXCEPTION WHEN OTHERS THEN
68          Raise_Application_Error('-20003','Error: get_sum_FGW003 !') ;
69      END;
70      FUNCTION get_sum_FtmpFld(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN V
71      RETURN NUMBER
72      /*
73      * 创建'各种津贴补贴补助'函数get_sum_FtmpFld 获取合计的各种津贴补贴补助金额
74      */
75      IS
76      result  NUMBER;  --返回的最终各种补贴结果
77      tmp     NUMBER;  --记录单个补贴的中间变量
78      sql_str VARCHAR2(2000);  --动态SQL拼接变量
79      BEGIN
80          tmp:=0;
81          result:=0;
82          --Dbms_Output.put_line('tmp: '||tmp||'  result: '||result);
83          FOR rs IN (select DISTINCT fnumber from T_cmpItem
84                      where Fname like '%补贴%' or Fname like '%津贴%' or Fname like '%补助%')
85          LOOP
86              BEGIN
87                    --Dbms_Output.put_line('fnumber: '||rs.fnumber);
88                    sql_str:='SELECT Sum('||rs.fnumber||') FROM '||table_name||' a left join T_cmpPeriod b o
89                            'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
90                    --Dbms_Output.put_line(sql_str);
91                    EXECUTE IMMEDIATE sql_str INTO tmp;
92              EXCEPTION WHEN OTHERS THEN
93                    tmp:=0;
94              END ;
95              --Dbms_Output.put_line('tmp: '||tmp);
96              result:=result+tmp;
97              --Dbms_Output.put_line('result: '||result);
98          END LOOP;
99          RETURN result;
100      EXCEPTION WHEN OTHERS THEN
101          Raise_Application_Error('-20004','Error: get_sum_FtmpFld !') ;
102      END;
103  --存储过程begin：
104  BEGIN
105      OPEN resultSet FOR
106           'SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,
107                  get_sum_FYF001(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FYF001,
108                  get_sum_FJX002(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FJX002,
109                  get_sum_FGW003(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FGW003,
110                  get_sum_FtmpFld(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FtmpFld
111           FROM  T_cmsScheme a left join T_OrgAdmin b
112           ON a.ForgID=b.FID
113           WHERE b.ForgNumber like '''||v_ForgNumb||'%''';
114* END;
115  /Procedure created.SQL> --测试:
SQL> var cur refcursor
SQL> exec get_sum_Info('01.01',201001,201002,:cur);PL/SQL procedure successfully completed.SQL> print cur
FNUMBER  FNAME   FORGNUMBER   FORGNAME                FYF001   FJX002    FGW003   FTMPFLD
------- -------  -----------  ---------------------  --------  --------  -------  ------------
A001     方案1   01.01.02     一分公司_本部_人事部    7400     0         2500     930
A002     方案2   01.01        一分公司_本部           6800     5500      1500     300SQL>

---过程代码   整理说明下
create or replace procedure p_tmp2(v_ForgNumber varchar2,FPrdNumFrom number,FPrdNumTo number,cur out sys_refcursor)
as
str varchar2(3000);
str1 varchar2(1000);
str2 varchar2(1000);
str3 varchar2(500);
begin
for i in (select a.FNUMBER,a.FNAME,b.FORGNUMBER,b.FORGNAME,FHisTableName from T_cmsScheme a,T_OrgAdmin b where a.forgid=b.forgid and instr(b.forgnumber,v_ForgNumber,1)>0)
loop
for j in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
loop
str1:=str1||','||'sum('||j.cname||')  '||j.cname;
----拼接应发工资绩效工资岗位工资
end loop;
for h in (select k.COLUMN_NAME cname from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  not in(select distinct Fnumber from T_cmpItem ))
loop
str3:=str3||','||h.cname;
---拼接插入临时表的字段
end loop;
for l in
(select k.COLUMN_NAME cname2 from user_tab_cols k where k.table_name=i.FHisTableName and k.column_name<>'FPERIODID'
and k.COLUMN_NAME  in(select distinct Fnumber from T_cmpItem where instr(Fname,'贴',1)>0 or Fname like '%补助%'))
loop
str2:=str2||'+'||l.cname2;
---拼接各种津贴补贴补助
end loop;
str:='select '''||i.FNUMBER||''','''||i.FNAME||''','''||i.FORGNUMBER||''','''||i.FORGNAME||''','||ltrim(str1,',')||',sum('||ltrim(str2,'+')||')
from '||i.fhistablename||' a,T_cmpPeriod b where b.fid=a.FPeriodID and b.FPeriodNum between '||FPrdNumFrom||' and '||FPrdNumTo;
execute immediate 'insert into tmp2(FNUMBER,FNAME,FORGNUMBER,FORGNAME,'||ltrim(str3,',')||',FtmpFld)  '||str;
--动态插入临时表str1:='';
str3:='';
str2:='';
---不重复碟拼
commit;
end loop;
open cur for 'select * from tmp2';
--这步可要可不要返回结果集
end;

呵呵，非常感谢二位的热心帮助！我初接触oracle，所以还不知道怎么建测试数据脚本。看了wkc168
的代码，知道以后怎么做了，再次非常感谢。我还在调试，现实中的数据字段及内容都与以上不同的，要做些小调。等调完后我给二位分。

建议这个贴子加精呀！wkc168 和gelyon真的很棒！

以上就是我执行的过程和结果，还有分析过程，我这没问题啊，是不是建表脚本不一样？明天我给你贴一下我上周六给你处理时，当时的建表和数据SQL脚本。

我知道原因了，我仔细看了我19楼回复的代码，发现右边有些长了的没截取完，可能你复制到你那里就少了很多东西，
也许是当时我在SQLPLUS下右边copy掉了，没截取完，明天给你重新发下代码
不好意思。

--sql建表和初始数据脚本--1、薪酬方案表名：
DROP TABLE T_cmsScheme;
SELECT * FROM T_cmsScheme;
CREATE TABLE T_cmsScheme AS
WITH T_cmsScheme AS(
--方案编号方案名称单位ID 历史记录表
--Fnumber Fname ForgID FHisTableName
SELECT 'A001' Fnumber,'方案1' Fname,'Olk1' ForgID,'T_DB_4500' FHisTableName FROM dual UNION ALL
SELECT 'A002','方案2','OKA2','T_DB_8872' FROM dual UNION ALL
SELECT 'A003','方案3','OJH3','T_DB_9983' FROM dual
)
SELECT * FROM T_cmsScheme


--2、组织单位表名：T_OrgAdmin
--单位ID 单位编码单位名称
--FID ForgNumber ForgName
DROP TABLE T_OrgAdmin;
SELECT * FROM T_OrgAdmin;
CREATE TABLE T_OrgAdmin AS
WITH T_OrgAdmin AS(
SELECT 'Olk1'FID,'01.01.02'ForgNumber,'一分公司_本部_人事部'ForgName FROM dual UNION ALL
SELECT 'OKA2','01.01','一分公司_本部' FROM dual UNION ALL
SELECT 'OJH3','02.01','二分公司_本部' FROM dual
)
SELECT * FROM T_OrgAdmin
--3、薪酬项目表：
DROP TABLE T_cmpItem;
SELECT * FROM T_cmpItem ;
CREATE TABLE T_cmpItem AS
WITH T_cmpItem AS(
--FID Fnumber Fname
SELECT 'THC1'FID,'FSAL001'Fnumber,'节日补助'Fname FROM dual UNION ALL
SELECT 'TDS1','FSAL001','节日补助' FROM dual UNION ALL
SELECT 'TWW3','FSAL001','节日补助' FROM dual UNION ALL
SELECT 'THC2','FSAL002','电脑补助' FROM dual UNION ALL
SELECT 'TSD3','FSAL002','电脑补助' FROM dual UNION ALL
SELECT 'THC3','FSAL003','交通补贴' FROM dual UNION ALL
SELECT 'THC4','FSAL004','岗位津贴' FROM dual UNION ALL
SELECT 'TKL3','FSAL004','岗位津贴' FROM dual UNION ALL
SELECT 'THC5','FSAL005','职称津贴' FROM dual UNION ALL
SELECT 'THC6','FSAL220','物业费'   FROM dual UNION ALL
SELECT 'TWE3','FSAL220','物业费'   FROM dual UNION ALL
SELECT 'THC7','FSAL221','卫生费'   FROM dual UNION ALL
SELECT 'THC8','FSAL225','房租费'   FROM dual UNION ALL
SELECT 'THC9','FSAL302','加班费'   FROM dual
)
SELECT * FROM T_cmpItem--4、薪资期间：
DROP TABLE T_cmpPeriod;
SELECT * FROM T_cmpPeriod;
CREATE TABLE T_cmpPeriod AS
WITH T_cmpPeriod AS(
--期间ID 期间编号期间名称
--FID FPeriodNum FPeriodName
SELECT 'PKL1'FID,201001 FPeriodNum,'201001' FPeriodName FROM dual UNION ALL
SELECT 'PJK2',201002,'201002' FROM dual UNION ALL
SELECT 'PGD3',201003,'201003' FROM dual UNION ALL
SELECT 'PYR4',201004,'201004' FROM dual UNION ALL
SELECT 'PEW5',201005,'201005' FROM dual
)
SELECT * FROM T_cmpPeriod
--5、方案1临时表名：
DROP TABLE T_DB_4500;
SELECT * FROM T_DB_4500;
CREATE TABLE T_DB_4500 AS
WITH T_DB_4500 AS(
--薪资期间ID 应发工资岗位津贴节日补助交通补贴岗位工资房租费
--FPeriodID   FYF001    FSAL004   FSAL001 FSAL003 FGW003    FSAL225
SELECT 'PKL1'FPeriodID,1000 FYF001,80 FSAL004,50 FSAL001,100 FSAL003,500 FGW003,200 FSAL225 FROM dual UNION ALL
SELECT 'PKL1',   1400, 100, 50,   100, 600, 140 FROM dual UNION ALL
SELECT 'PJK2', 2000, 100, 50, 100, 1000, 200 FROM dual UNION ALL
SELECT 'PJK2', 3000, 50 ,  50, 100, 400, 200 FROM dual
)
SELECT * FROM T_DB_4500
--6、方案2临时表名：
DROP TABLE T_DB_8872;
SELECT * FROM T_DB_8872;
CREATE TABLE T_DB_8872 AS
WITH T_DB_8872 AS(
--薪资期间ID 应发工资绩效工资岗位工资节日补助岗位津贴卫生费   房租费
--FPeriodID   FYF001    FJX002    FGW003    FSAL001 FSAL004    FSAL221 FSAL225
SELECT 'PJK2'FPeriodID,4000 FYF001,3000 FJX002,1000 FGW003,50 FSAL001,100 FSAL004,10 FSAL221,200 FSAL225 FROM dual UNION ALL
SELECT 'PJK2', 2800, 2500, 500, 50, 100, 10, 200 FROM dual UNION ALL
SELECT 'PGD3', 1000, 1200, 600, 50, 100, 10, 200 FROM dual UNION ALL
SELECT 'PYR4', 1200, 1300, 400, 50, 50, 10, 200 FROM dual UNION ALL
SELECT 'PEW5', 2000, 2200, 200, 50, 50, 10, 200 FROM dual
)
SELECT * FROM T_DB_8872
--7、方案3临时表名：
DROP TABLE T_DB_9983;
SELECT * FROM T_DB_9983;
CREATE TABLE T_DB_9983 AS
WITH T_DB_9983 AS(
--期间编号期间名称     应发工资实发工资职称津贴交通补贴卫生费   岗位津贴
--FPeriodNum FPeriodName FYF001 FSF002   FSAL005 FSAL003 FSAL221 FSAL004
SELECT 'PJK2'FPeriodID,'201002'FPeriodName,3000 FYF001,2000 FSF002,10 FSAL005,40 FSAL003,10 FSAL221, 50 FSAL004 FROM dual UNION ALL
SELECT 'PJK2', '201002', 2300, 2500, 20, 50, 15, 50 FROM dual
)
SELECT * FROM  T_DB_9983
--存储过程实现
CREATE OR REPLACE PROCEDURE get_sum_info(v_ForgNumb IN VARCHAR2 ,
                                         Form_FPeriodNum IN VARCHAR2 ,
                                         To_FPeriodNum IN VARCHAR2 ,
                                         resultSet OUT sys_refcursor )
IS
  --4个函数function的申明和实现：
    FUNCTION get_sum_FYF001(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'应发工资'函数get_sum_FYF001 获取合计的应发金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FYF001) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20001','Error: get_sum_FYF001 !') ;
    END;
    FUNCTION get_sum_FJX002(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'绩效工资'函数get_sum_FJX002 获取合计的绩效工资金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FJX002) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20002','Error: get_sum_FJX002 !') ;
    END;
    FUNCTION get_sum_FGW003(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'岗位工资'函数get_sum_FGW003 获取合计的岗位工资金额
    */
    IS
    result NUMBER;
    sql_str VARCHAR2(2000);
    BEGIN
        BEGIN
              sql_str:='SELECT Sum(FGW003) FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                      'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
              --Dbms_Output.put_line(sql_str);
              EXECUTE IMMEDIATE sql_str INTO result;
        EXCEPTION WHEN OTHERS THEN
              result:=0;
        END ;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20003','Error: get_sum_FGW003 !') ;
    END;
    FUNCTION get_sum_FtmpFld(table_name IN VARCHAR2,Form_FPeriodNum IN VARCHAR2 ,To_FPeriodNum IN VARCHAR2  )
    RETURN NUMBER
    /*
    * 创建'各种津贴补贴补助'函数get_sum_FtmpFld 获取合计的各种津贴补贴补助金额
    */
    IS
    result  NUMBER;  --返回的最终各种补贴结果
    tmp     NUMBER;  --记录单个补贴的中间变量
    sql_str VARCHAR2(2000);  --动态SQL拼接变量
    BEGIN
        tmp:=0;
        result:=0;
        --Dbms_Output.put_line('tmp: '||tmp||'  result: '||result);
        FOR rs IN (select DISTINCT fnumber from T_cmpItem
                    where Fname like '%补贴%' or Fname like '%津贴%' or Fname like '%补助%')
        LOOP
            BEGIN
                  --Dbms_Output.put_line('fnumber: '||rs.fnumber);
                  sql_str:='SELECT Sum('||rs.fnumber||') FROM '||table_name||' a left join T_cmpPeriod b on a.FPeriodID=b.FID '||
                          'WHERE b.FPeriodNum between '||Form_FPeriodNum||' and '||To_FPeriodNum;
                  --Dbms_Output.put_line(sql_str);
                  EXECUTE IMMEDIATE sql_str INTO tmp;
            EXCEPTION WHEN OTHERS THEN
                  tmp:=0;
            END ;
            --Dbms_Output.put_line('tmp: '||tmp);
            result:=result+tmp;
            --Dbms_Output.put_line('result: '||result);
        END LOOP;
        RETURN result;
    EXCEPTION WHEN OTHERS THEN
        Raise_Application_Error('-20004','Error: get_sum_FtmpFld !') ;
    END;
--存储过程begin：
BEGIN
    OPEN resultSet FOR
         'SELECT a.Fnumber,a.Fname,b.ForgNumber,b.ForgName,
                get_sum_FYF001(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FYF001,
                get_sum_FJX002(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FJX002,
                get_sum_FGW003(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FGW003,
                get_sum_FtmpFld(a.FHisTableName,'||Form_FPeriodNum||','||To_FPeriodNum||') FtmpFld
         FROM  T_cmsScheme a left join T_OrgAdmin b
         ON a.ForgID=b.FID
         WHERE b.ForgNumber like '''||v_ForgNumb||'%''';
END;
/
SQL> --测试:
SQL> var cur refcursor
SQL> exec get_sum_Info('01.01',201001,201002,:cur);PL/SQL procedure successfully completed.SQL> print cur
FNUMBER  FNAME   FORGNUMBER   FORGNAME                FYF001   FJX002    FGW003   FTMPFLD
------- -------  -----------  ---------------------  --------  --------  -------  ------------
A001     方案1   01.01.02     一分公司_本部_人事部    7400     0         2500     930
A002     方案2   01.01        一分公司_本部           6800     5500      1500     300SQL>

回gelyon有两个疑问：
1）存储过程中调用内置函数，按你的写法是会出错的。我用pl/sql dev
2）存储过程三个参数都是字符型的，你测试时为什么第二第三个参数不加单引号？

调试易

求助oracle存储过程高手：多表连接取动态表的动态字段（多个，字段名由其它表取）之和。

解决方案 »