1 create or replace procedure NW_DelYW(iOPERATION_ID number, 2 sUserID varchar2) is 3 sCurDJBH yw_operation_link.djbh%type; 4 cursor table_yw(ywid yw_operation.id%type) is 5 select * from yw_operation_link t1 where t1.operation_id = ywid; 6 begin 7 for dr in table_yw(iOPERATION_ID) loop 8 sCurDJBH := dr.djbh; 9 --取得opercationid 10 /* select t1.operation_id 11 into sOperationID 12 from yw_operation_link t1 13 where t1.djbh = sCurDJBH;*/ 14 15 --写日志 16 insert into log_zfywinfo 17 (DJBH, 18 DJDL, 19 DJXL, 20 DLMC, 21 XLMC, 22 SLR, 23 SLRID, 24 SQRXM, 25 FWZL, 26 ZFRQ, 27 ZFRID, 28 zfr) 29 select distinct sCurDJBH, 30 t4.id, 31 t3.id, 32 t4.name, 33 t3.name, 34 t1.slry, 35 t1.slryid, 36 t1.SQRXM, 37 t1.zl, 38 sysdate, 39 sUserID, 40 (select tt.name from pw_user tt where tt.id=sUserID) 41 from yw_operation t1 42 join yw_operation_link t2 43 on t2.operation_id = t1.ID 44 join BUSINESS_TYPE t3 45 on t3.id = t1.business_id 46 join BUSINESS_CLASS t4 47 on t4.id = t3.parent_id 48 where t1.ID = dr.operation_id; 49 exception 50 when others then 51 rollback; 52 dbms_output.put_line(sqlerrm); 53 end NW_DelYW;
Oracle使用cursor 游标循环添加删除更新。