create or replace procedure RKDJWZXZ
(
MaterialOIDList in varchar2, --仓库物资编码OID序列
StorageAccountOID in number, --仓库帐务OID
StorageCodeOID in number, --仓库编码OID
AccountBookOID in number, --账本OID
AccountBookName in varchar2, --账本名称
AccountCode in varchar2, --帐务编码
HandlerOID in number, --操作人OID
HandlerName in number, --操作人姓名
AccountOID in number, --帐套OID
State in varchar2, --状态
AnyError out varchar2 --返回存储过程是否执行成功
)
is
v_WL_CKZWOID number(12); --仓库帐务OID
v_KCL number(12,2); --库存量
v_JHJG number(12,2); --库存单价
v_YSDBH varchar2(30); --验收单编号
v_WL_YSDMXOID number(12); --验收单明细OID
v_WL_YSD number(12); --验收主表OID
v_WL_CKWZBMOID number(12); --仓库物资编码OID
v_WL_WZMC varchar2(200); --物资名称
v_WL_GG varchar2(200); --规格
v_WL_DW varchar2(30); --计量单位
v_YSSL number(12,2); --验收数量
v_DJ number(12,2); --单价
v_XQBMOID number(12); --需求部门OID
v_XQBZOID number(12); --需求班组OID
v_WL_CJHXBOID number(12); --采购计划明细OID
v_WL_CJSXBOID number(12); --需求计划明细OID
v_WL_LXXQJHXBOID number(12); --零星采购明细OID
v_SYFXOID number(12); --使用方向OID
v_WL_NBWZBM varchar2(30); --仓库物资内部编码
v_CZ varchar2(100); --材质
v_TH varchar2(100); --图号
v_WL_CKZWRKOID number(12); --仓库帐务入库OID
v_Countnum integer; --临时统计
v_ReturnOIDList varchar2(30); --自定义函数返回的值
cursor MaterialInfo is select OID,WL_YSD,WL_CKWZBMOID,WL_WZMC,WL_GG,WL_DW,YSSL,DJ,XQBMOID,XQBZOID from wl_ysdmx where oid in(MaterialOIDList);
begin
AnyError := 'HaveNoError';
begin
/* 1:------当没有主表信息的情况下要增加主表记录------ */
if StorageAccountOID = 0 then
select s_pm.Nextval into v_WL_CKZWOID from dual; --得到仓库帐务的最新OID
insert into WL_ZWRKZB(OID,WL_CKZBOID,WL_ZBMC,WL_ZWBH,ZZROID,ZZR,ZZSJ,WL_ZTOID,STATE) --给主表增加一条记录
values(v_WL_CKZWOID,AccountBookOID,AccountBookName,AccountCode,HandlerOID,HandlerName,sysdate,AccountOID,State);
else
v_WL_CKZWOID := StorageAccountOID;
end if;
/* 1:------添加入库登记子表信息------ */
open MaterialInfo;
fetch MaterialInfo into v_WL_YSDMXOID,v_WL_YSD,v_WL_CKWZBMOID,v_WL_WZMC,v_WL_GG,v_WL_DW,v_YSSL,v_DJ,v_XQBMOID,v_XQBZOID;
while MaterialInfo % found
loop
select KCL into v_KCL from wl_ckwzinfo where WL_CKWZBMOID = WL_CKWZBMOID; --取得库存量
select JHJG into v_JHJG from wl_ckwzinfo where WL_CKWZBMOID = WL_CKWZBMOID; --取得库存价格
select BH into v_YSDBH from wl_ysd where OID = v_WL_YSD; --获得验收单主表编号
v_ReturnOIDList := GetOIDList(v_WL_YSDMXOID); --利用自定义函数得到采购计划明细或者是零星采购明细
if Instr(v_ReturnOIDList,'WL_CJHXB',1,1) <> 0 then
v_WL_CJHXBOID := Substr(v_ReturnOIDList,9,length(v_ReturnOIDList)); --标记返回的是采购计划明细OID
v_WL_LXXQJHXBOID := 0;
else
v_WL_LXXQJHXBOID := Substr(v_ReturnOIDList,12,length(v_ReturnOIDList)); --标记返回的是零星采购明细OID
v_WL_CJHXBOID := 0;
end if;
/* 2:------添加入库登记子表信息------ */
select s_pm.Nextval into v_WL_CKZWRKOID from dual; --得到仓库帐务入库登记OID
insert into WL_CKZWRKMX(oid,Wl_Ckwzbmoid,WL_ZWRKZB,wl_wzmc,WL_GG,WL_DW,YSDSL,RZSL,YDFPSL,WDFPSL,RZZJE,WL_YSDBH,WL_YSDMXOID,KCL,KCJE,XQBMOID,XQBZOID,WL_CJHXBOID,WL_LXXQJHXBOID,WL_ZTOID,CKCS)
values(v_WL_CKZWRKOID,v_WL_CKWZBMOID,v_WL_CKZWOID,v_WL_WZMC,v_WL_GG,v_WL_DW,v_YSSL,v_YSSL,0.0,0.0,v_DJ * v_YSSL,v_YSDBH,v_WL_YSDMXOID,v_KCL+v_YSSL,(v_KCL+v_YSSL) * v_JHJG,nvl(v_XQBMOID,0),nvl(v_XQBZOID,0),v_WL_CJHXBOID,v_WL_LXXQJHXBOID,AccountOID,0);
/* 2:------添加入库登记子表信息------ */
/* 3:------更新[仓库物资归属]情况表------ */
if v_WL_CJHXBOID <> 0 then
select nvl(WL_CJSXBOID,0) into v_WL_CJSXBOID from WL_CJHXB where OID = v_WL_CJHXBOID;
select SYFXOID into v_SYFXOID from ckwzbmcjsxb where OID = v_WL_CJSXBOID;
else
v_WL_CJSXBOID := 0;
v_SYFXOID := 0; --[零星采购]标记使用方向为0
end if;
select nvl(WL_NBWZBM,'') into v_WL_NBWZBM from wl_ckwzinfo where WL_CKWZBMOID=v_WL_CKWZBMOID; --内部物资编码
select nvl(CZ,'') into v_CZ from wl_ckwzinfo where WL_CKWZBMOID=v_WL_CKWZBMOID; --材质
select nvl(TH,'') into v_TH from wl_ckwzinfo where WL_CKWZBMOID=v_WL_CKWZBMOID; --图号
if v_SYFXOID = 7071 then --当v_SYFXOID = 7071时表示最初需求是用来补库
select count(*) into v_Countnum from wl_wzgs where WL_CKWZBMOID=v_WL_CKWZBMOID;
if v_Countnum > 0 then --当存在相同的记录时,则更新库存情况
update WL_WZGS set SL = SL + v_YSSL where WL_CKWZBMOID=v_WL_CKWZBMOID;
else
insert into WL_WZGS(OID,WL_CKWZBMOID,WL_CKBMOID,WL_CKZBOID,WL_NBWZBM,SL,WL_WZMC,WL_GG,WL_DW,WL_CZ,WL_TH,XQBMOID,XQBZOID,WL_ZTOID,WL_CJSXBOID,WL_LXCGMXOID,WL_CKZWRKMXOID,WL_YSDMXOID,GRJG,GRSL,SYFXOID,GRSJ)
values(s_pm.Nextval,v_WL_CKWZBMOID,StorageCodeOID,AccountBookOID,v_WL_NBWZBM,v_YSSL,v_WL_WZMC,v_WL_GG,v_WL_DW,v_CZ,v_TH,nvl(v_XQBMOID,0),nvl(v_XQBZOID,0),AccountOID,0,0,v_WL_CKZWRKOID,v_WL_YSDMXOID,v_DJ,v_YSSL,7071,sysdate);
end if;
else
insert into WL_WZGS(OID,WL_CKWZBMOID,WL_CKBMOID,WL_CKZBOID,WL_NBWZBM,SL,WL_WZMC,WL_GG,WL_DW,WL_CZ,WL_TH,XQBMOID,XQBZOID,WL_ZTOID,WL_CJSXBOID,WL_LXCGMXOID,WL_CKZWRKMXOID,WL_YSDMXOID,GRJG,GRSL,SYFXOID,GRSJ)
values(s_pm.Nextval,v_WL_CKWZBMOID,StorageCodeOID,AccountBookOID,v_WL_NBWZBM,v_YSSL,v_WL_WZMC,v_WL_GG,v_WL_DW,v_CZ,v_TH,nvl(v_XQBMOID,0),nvl(v_XQBZOID,0),AccountOID,v_WL_CJSXBOID,v_WL_LXXQJHXBOID,v_WL_CKZWRKOID,v_WL_YSDMXOID,v_DJ,v_YSSL,v_SYFXOID,sysdate);
end if;
/* 3:------更新[仓库物资归属]情况表------ */
/* 4:------更新[更新验收单明细]表------ */
update wl_ysdmx set YRKSL = v_YSSL where OID = v_WL_YSDMXOID;
/* 4:------更新[更新验收单明细]表------ */
/* 5:------更新[用户最新消息]表------ */
/* 5:------更新[用户最新消息]表------ */
/* 6:------更新[库存]表------ */
update WL_CKWZBM set KCL = v_KCL + v_YSSL,KCJE = (v_KCL+v_YSSL) * v_JHJG where OID=v_WL_CKWZBMOID;
/* 6:------更新[库存]表------ */
end loop;
close MaterialInfo; --关闭游标
commit
exception
when others then
AnyError := 'HaveError';
end ;
end RKDJWZXZ;
因为我的存储过程中,对多个表进行了操作,我要用事务一次提交,而我用上面的语句提示下面的错误
PROCEDURE WLTEST.RKDJWZXZ 编译错误
错误: PL/SQL: ORA-02185: COMMIT 后面跟的标记不是 WORK
行: 123
文本: when others then
错误: PL/SQL: SQL Statement ignored
行: 122
文本: exception