oracle sql 子游标_ORACLE动态游标及动态SQL使用实例

create or replace procedure AAA(table_val in varchar2,table_result in varchar2)

is

--游标结果集

t_fproductid varchar2(50);

t_fproductid_fnumber varchar2(50);

--动态游标

TYPE My_CurType IS REF CURSOR;

CUR_1 My_CurType;

--结果表字段定义

FIDENTITYID number; --主键

FPRODUCTID_FNUMBER varchar2(100); --产品编码

FPRODUCTID_FNAME varchar2(300); --产品名称

FSPECIFICATION varchar2(300); --规格

FBASICUNITFIELD_FNAME varchar2(100); --单位

FCURRCOMPLETEQTY number(23,10); --入库数量

Fyuancailiao_cengben number(23,10); --原材料单位成本

Fyuancailiao_zonge number(23,10); --原材料总额

Fbaozhuang_cengben number(23,10); --包材单位成本

Fbaozhuang_zonge number(23,10); --包材总额

Fenery_cengben number(23,10); --动力单位成本

Fenery_zonge number(23,10); --动力总额

Fzhijie_cengben number(23,10); --工资单位成本

Fzhijie_zonge number(23,10); --工资总额

Fzhejiu_cengben number(23,10); --折旧单位成本

Fzhejiu_zonge number(23,10); --折旧总额

Fzulin_cengben number(23,10); --租赁费单位成本

Fzulin_zonge number(23,10); --租赁费总额

Fhuayan_cengben number(23,10); --化验费单位成本

Fhuayan_zonge number(23,10); --化验费总额

Fpaiwu_cengben number(23,10); --排污费单位成本

Fpaiwu_zonge number(23,10); --排污费总额

Fyunsu_cengben number(23,10); --运输费单位成本

Fyunsu_zonge number(23,10); --运输费总额

Fqita_cengben number(23,10); --其它(含仓储费)单位成本

Fqita_zonge number(23,10); --其它(含仓储费)总额

Fweiliechu_cengben number(23,10); --制造费用单位成本

Fweiliechu_zonge number(23,10); --制造费用总额

Fcurrcostamount number(23,10); --单位成本

Fcurrcompleteamount number(23,10); --总成本

--标识变量

loop_state number:=0;

--汇总字段

t_FIDENTITYID number; --主键

tt_FPRODUCTID_FNUMBER varchar2(100); --产品编码

t_FPRODUCTID_FNAME varchar2(300); --产品名称

t_FSPECIFICATION varchar2(300); --规格

t_FBASICUNITFIELD_FNAME varchar2(100); --单位

t_FCURRCOMPLETEQTY number(23,10); --入库数量

t_Fyuancailiao_cengben number(23,10); --原材料单位成本

t_Fyuancailiao_zonge number(23,10); --原材料总额

t_Fbaozhuang_cengben number(23,10); --包材单位成本

t_Fbaozhuang_zonge number(23,10); --包材总额

t_Fenery_cengben number(23,10); --动力单位成本

t_Fenery_zonge number(23,10); --动力总额

t_Fzhijie_cengben number(23,10); --工资单位成本

t_Fzhijie_zonge number(23,10); --工资总额

t_Fzhejiu_cengben number(23,10); --折旧单位成本

t_Fzhejiu_zonge number(23,10); --折旧总额

t_Fzulin_cengben number(23,10); --租赁费单位成本

t_Fzulin_zonge number(23,10); --租赁费总额

t_Fhuayan_cengben number(23,10); --化验费单位成本

t_Fhuayan_zonge number(23,10); --化验费总额

t_Fpaiwu_cengben number(23,10); --排污费单位成本

t_Fpaiwu_zonge number(23,10); --排污费总额

t_Fyunsu_cengben number(23,10); --运输费单位成本

t_Fyunsu_zonge number(23,10); --运输费总额

t_Fqita_cengben number(23,10); --其它(含仓储费)单位成本

t_Fqita_zonge number(23,10); --其它(含仓储费)总额

t_Fweiliechu_cengben number(23,10); --制造费用单位成本

t_Fweiliechu_zonge number(23,10); --制造费用总额

t_Fcurrcostamount number(23,10); --单位成本

t_Fcurrcompleteamount number(23,10); --总成本

begin

OPEN CUR_1 FOR 'select distinct t.fproductid,max(t.fproductid_fnumber) fproductid_fnumber from '||table_val||' t group by t.fproductid';

LOOP

FETCH CUR_1 INTO t_fproductid,t_fproductid_fnumber;

EXIT WHEN CUR_1%NOTFOUND;

--主键

loop_state:=loop_state+1;

FIDENTITYID:=loop_state;

--物料信息

execute immediate 'select to_char(max(t.fproductid_fnumber)),to_char(max(t.fproductid_fname)),to_char(max(t.fspecification)),to_char(max(t0.fname)) from '||table_val||' t,T_BD_UNIT_l t0 where t.fbasicunitid=t0.funitid and t0.flocaleid=''2052'' and t.fproductid='''||t_fproductid||'''' into FPRODUCTID_FNUMBER,FPRODUCTID_FNAME,FSPECIFICATION,FBASICUNITFIELD_FNAME ;

--入库数量

execute immediate 'select sum(FCURRCOMPLETEQTY) from '||table_val||' t where t.fproductid='||t_fproductid into FCURRCOMPLETEQTY;

--直接原材料

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00001_SYS''' into Fyuancailiao_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00001_SYS''' into Fyuancailiao_zonge;

--包装材料

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00067''' into Fbaozhuang_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00067''' into Fbaozhuang_zonge;

--能源动力

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00068''' into Fenery_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00068''' into Fenery_zonge;

--直接工资，奖金等

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber in(''CBXM00069'',''CBXM00070'',''CBXM00071'',''CBXM00072'',''CBXM00073'',''CBXM00074'',''CBXM00075'',''CBXM00076'')' into Fzhijie_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber in(''CBXM00069'',''CBXM00076'')' into Fzhijie_zonge;

--折旧费用

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00080''' into Fzhejiu_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00080''' into Fzhejiu_zonge;

--租赁费

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00081''' into Fzulin_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00081''' into Fzulin_zonge;

--化验费

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00092''' into Fhuayan_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00092''' into Fhuayan_zonge;

--排污费

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00093''' into Fpaiwu_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00093''' into Fpaiwu_zonge;

--运输费

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00088''' into Fyunsu_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00088''' into Fyunsu_zonge;

--其他

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00094''' into Fqita_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00094''' into Fqita_zonge;

--未列出

execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber not in (''CBXM00001_SYS'',''CBXM00067'',''CBXM00068'',''CBXM00069'',''CBXM00076'',''CBXM00080'',''CBXM00081'',''CBXM00092'',''CBXM00093'',''CBXM00088'',''CBXM00094'')' into Fweiliechu_cengben;

execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber not in (''CBXM00001_SYS'',''CBXM00094'')' into Fweiliechu_zonge;

--本期单位成本

execute immediate 'select sum(t.Fcurrcostamount) from '||table_val||' t where t.fproductid='''||t_fproductid||'''' into Fcurrcostamount;

--本期完工成本

execute immediate 'select sum(t.Fcurrcompleteamount) from '||table_val||' t where t.fproductid='''||t_fproductid ||'''' into Fcurrcompleteamount;

--插入结果表

execute immediate 'insert into '||table_result||'(

FIDENTITYID,FPRODUCTID_FNUMBER,FBASICUNITFIELD_FNAME,FCURRCOMPLETEQTY,Fyuancailiao_cengben,Fyuancailiao_zonge,Fbaozhuang_cengben,Fbaozhuang_zonge,Fenery_cengben,Fenery_zonge,Fzhijie_cengben,Fzhijie_zonge,Fzhejiu_cengben,Fzhejiu_zonge,Fzulin_cengben,Fzulin_zonge,Fhuayan_cengben,Fhuayan_zonge,Fpaiwu_cengben,Fpaiwu_zonge,Fyunsu_cengben,Fyunsu_zonge,Fqita_cengben,Fqita_zonge,Fweiliechu_cengben,Fweiliechu_zonge,Fcurrcostamount,Fcurrcompleteamount

)

values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30)'

using

FIDENTITYID,Fcurrcompleteamount;

commit;

END LOOP;

CLOSE CUR_1;

execute immediate 'select sum(FCURRCOMPLETEQTY),sum(Fyuancailiao_cengben),sum(Fyuancailiao_zonge),sum(Fbaozhuang_cengben),sum(Fbaozhuang_zonge),sum(Fenery_cengben),sum(Fenery_zonge),sum(Fzhijie_cengben),sum(Fzhijie_zonge),sum(Fzhejiu_cengben),sum(Fzhejiu_zonge),sum(Fzulin_cengben),sum(Fzulin_zonge),sum(Fhuayan_cengben),sum(Fhuayan_zonge),sum(Fpaiwu_cengben),sum(Fpaiwu_zonge),sum(Fyunsu_cengben),sum(Fyunsu_zonge),sum(Fqita_cengben),sum(Fqita_zonge),sum(Fweiliechu_cengben),sum(Fweiliechu_zonge),sum(Fcurrcostamount),sum(Fcurrcompleteamount) from '||table_result into t_FCURRCOMPLETEQTY,t_Fyuancailiao_cengben,t_Fyuancailiao_zonge,t_Fbaozhuang_cengben,t_Fbaozhuang_zonge,t_Fenery_cengben,t_Fenery_zonge,t_Fzhijie_cengben,t_Fzhijie_zonge,t_Fzhejiu_cengben,t_Fzhejiu_zonge,t_Fzulin_cengben,t_Fzulin_zonge,t_Fhuayan_cengben,t_Fhuayan_zonge,t_Fpaiwu_cengben,t_Fpaiwu_zonge,t_Fyunsu_cengben,t_Fyunsu_zonge,t_Fqita_cengben,t_Fqita_zonge,t_Fweiliechu_cengben,t_Fweiliechu_zonge,t_Fcurrcostamount,t_Fcurrcompleteamount;

t_FIDENTITYID:=loop_state+1;

tt_FPRODUCTID_FNUMBER:='合计';

--插入结果表

execute immediate 'insert into '||table_result||'(

FIDENTITYID,:30)'

using

t_FIDENTITYID,tt_FPRODUCTID_FNUMBER,t_FPRODUCTID_FNAME,t_FSPECIFICATION,t_FBASICUNITFIELD_FNAME,t_FCURRCOMPLETEQTY,t_Fcurrcompleteamount;

commit;

end P_DC_SummaryOfProductCost;