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 b.company 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 b.company_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 b.company 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 b.companyid 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