oracle存储过程使用游标对多表操作例子

create or replace procedure UpdateTempInventoryM(
DateMM in VARCHAR2,
HasBl in VARCHAR2,--是否包含借出借入
HasQty in VARCHAR2, --是否包含领料预留
COMWhere in VARCHAR2, --公司名称
Warehouse in VARCHAR2--仓库代号
)


as


isql varchar2(4000);--groud by
countsql varchar(400);
blcount NUMBER(10);




--定义游标 调增单
CURSOR s_aq IS SELECT b.billuser,a.INVENTORYBINNUM,a.UNIT,a.QTY,a.BIN, a.AJTYPE,a.COST FROM S_AQ a right join m_aq b on a.aj_no=b.aj_no
where to_char(ADD_MONTHS(b.BILLDATE,0), 'YYYYMM')=DateMM AND a.aj_id='AJ' AND B.billuser is not null
AND https://www.360docs.net/doc/ed5330456.html,pany like '%'||COMWhere||'' AND a.warehouseid like '%'||Warehouse||'';

--定义游标 借入借出
CURSOR s_bl IS SELECT b.bw_id,a.INVENTORYBINNUM,a.BIN,a.UNIT,a.QTY,a.AMT,a.QTY_RQ ,a.QTY_BACK FROM s_borrow a right join m_borrow b on a.bw_no=b.bw_no
where to_char(ADD_MONTHS(b.BILLDATE,0), 'YYYYMM')=DateMM AND b.billuser is not null
AND https://www.360docs.net/doc/ed5330456.html,pany_in like '%'||COMWhere||'' AND a.warehouseid like '%'||Warehouse||'';


--定义游标 领料预留
CURSOR s_rq IS SELECT b.canceluser,b.canceldate,a.INVENTORYBINNUM,a.BIN,a.UNIT,a.qtyrq FROM S_RQ a right join m_rq b on a.rq_no =b.rq_no
where to_char(ADD_MONTHS(b.BILLDATE,0), 'YYYYMM')=DateMM AND b.billuser is not null AND b.canceluser is null
AND https://www.360docs.net/doc/ed5330456.html,pany like '%'||COMWhere||'' AND a.warehouseid like '%'||Warehouse||'';


--定义游标 出入库
CURSOR s_rk IS SELECT b.rk_id,a.INVENTORYBINNUM,a.BIN,a.amt,a.UNIT,a.qty FROM S_RK a right join m_rk b on a.rk_no =b.rk_no
where to_char(ADD_MONTHS(b.billdate,0), 'YYYYMM')=DateMM AND b.billuser is not null
AND https://www.360docs.net/doc/ed5330456.html,panyid like '%'||COMWhere||'' AND a.warehouseid like '%'||Warehouse||'';


--GROUP BY后的数据
CURSOR temp_inventorybinm IS select InventoryBinNum, MAX(UNIT) UNIT,MAX(BIN) BIN, SUM(QTY) QTY, SUM(COST) COST,SUM(QTY1LEND) QTY1LEND,SUM(QTYBORROW) QTYBORROW,SUM(QTYSQ) QTYSQ,Max(MM)MM,Max(YY)yy from TMP_INVENTORYM GROUP BY InventoryBinNum;



BEGIN


-- 遍历调增单游标
for cur in s_aq loop
if cur.Ajtype ='+' then --调增
begin
insert into tmp_inventorym(inventorybinnum,UNIT,QTY,BIN,COST,MM,YY)values
(cur.inventorybinnum,cur.UNIT,cur.QTY,cur.BIN, cur.COST*cur.qty,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;--提交
end;
else --调减
begin
insert into tmp_inventorym(inventorybinnum,UNIT,QTY,BIN,COST,mm,YY)values(cur.inventorybinnum,cur.UNIT,-cur.QTY,cur.BIN, -cur.COST,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;--提交
end;
end if;

end loop;
--close s_aq;--关闭调增单游标
if HasBl='True' then --用户勾选了包含借入借出

begin
-- 遍历调借出借入游标
for bl in s_bl loop
if bl.BW_ID='BW' or bl.BW_ID='LB' then --借入或还入
begin
insert into tmp_inventorym(inventorybinnum,BIN,UNIT,QTYSQ,QTY,QTYBORROW,COST,mm,yy)values

(bl.INVENTORYBINNUM,bl.BIN,bl.UNIT,bl.QTY_RQ ,bl.QTY,bl.QTY_BACK,bl.AMT,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;
end;

else --借出或还出
begin
insert into tmp_inventorym(inventorybinnum,BIN,UNIT,qty,QTYSQ ,QTY1LEND,COST,mm,yy)
values(bl.INVENTORYBINNUM,bl.BIN,bl.UNIT,-bl.qty,bl.QTY_RQ ,bl.QTY_BACK,bl.AMT,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;
end;


end if;
end loop;
--close s_bl;--关闭借出借入游标
end;
end if;
-- 遍历调领料预留游标

if HasQty='True' then --用户勾选了包含领料预留
begin
for rq in s_rq loop

insert into tmp_inventorym(inventorybinnum,BIN,UNIT,QTYSQ ,mm,yy)values(rq.INVENTORYBINNUM,rq.BIN,rq.UNIT,rq.qtyrq,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));

COMMIT;
end loop;
--close s_rq;--关闭料预留游标
end;
end if;

-- 遍历出入库游标

for rk in s_rk loop

--判断如果是RK数量金额为+否则为-
if rk.rk_id='RK' then
begin

insert into tmp_inventorym(inventorybinnum,BIN,UNIT,qty,cost,mm,yy )values
(rk.INVENTORYBINNUM,rk.BIN,rk.UNIT,rk.qty,RK.AMT,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;
end;
else
begin
insert into tmp_inventorym(inventorybinnum,BIN,UNIT,qty,cost,mm,yy)values
(rk.INVENTORYBINNUM,rk.BIN,rk.UNIT,-rk.qty,-rk.amt,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
COMMIT;
end;
end if;
end loop;
-- close s_rk;--关闭入库游标


for tym in temp_inventorybinm loop
--查看INVENTORYBINM有没有上个月的记录，有就更新否则添加
countsql:='select Count(*) from INVENTORYBINM where InventoryBinNum='||tym.InventoryBinNum||'';
EXECUTE IMMEDIATE countsql into blcount;--使用动态SQL语句来执行
if blcount>0 then

begin
update temp_inventorybinm set UNIT=tym.UNIT,BIN=tym.BIN,QTY=tym.QTY,COST=tym.COST,QTY1LEND=tym.QTY1LEND,QTYBORROW=tym.QTYBORROW,QTYSQ=tym.QTYSQ where InventoryBinNum=tym.InventoryBinNum;
COMMIT;
end;
else
begin
insert into temp_inventorybinm(InventoryBinNum,UNIT,BIN,QTY,COST,QTY1LEND,QTYBORROW,QTYSQ,Mm,yy)values(tym.InventoryBinNum,tym.UNIT,tym.BIN,tym.QTY,tym.COST,tym.QTY1LEND,tym.QTYBORROW,tym.QTYSQ,to_char(ADD_MONTHS(sysdate,-1), 'mm'),to_char(ADD_MONTHS(sysdate,0), 'yy'));
end;
end if;
end loop;
--close inventorym;--关闭inventorym游标

delete from tmp_inventorym;
COMMIT;
END UpdateTempInventoryM;


--CALL UpdateInventoryM('','True','True','','');