oracle利用游标添加数据库,Oracle存储过程之insert的使用，含游标的使用

Oracle存储过程之insert的使用，含游标的使用

body部分：

-- 获取指定计划的指定阶段的指定修改次数的计划类型编号和名称 by shiwei  --变更阶段使用

procedure proc_sy_syjs_getAProChange(proID in jwxt.sy_syjs_audit.FProID%type,

historyStageID in number,

tab out t_cursor) is

curStage number;

begin

curStage:=-1;

select max(jt.fstageid) into curStage

from jwxt.SY_SYJS_PROITEM jt

where jt.fproid=proID

and jt.zt=1;

if(curStage = 1) then

-----初次变更时

insert into jwxt.SY_SYJS_PROITEM( fproid,fstageid, fmodifyid, fcoursecode, fcoursename, fitemid, fitemname, ftype, fstyle, frequirement,

flabmemnum, fgroupnum, fdevicename, fmodel, fhavenum, fobsonum, fbuynum, funitprice)

select t.fproid,t.fstageid+1,0, t.fcoursecode,t.fcoursename,t.fitemid,t.fitemname,t.ftype,t.fstyle,t.frequirement,

t.flabmemnum,t.fgroupnum,t.fdevicename,t.fmodel,t.fhavenum,t.fobsonum,t.fbuynum,t.funitprice

from jwxt.SY_SYJS_PROITEM t

where t.fproid=proID

and t.fstageid='1'

and t.zt='1';

commit;

else

----2次及以上  变更时 复制数据

insert into jwxt.SY_SYJS_PROITEM( fproid,fstageid, fmodifyid, fcoursecode, fcoursename, fitemid, fitemname, ftype, fstyle, frequirement,

flabmemnum, fgroupnum, fdevicename, fmodel, fhavenum, fobsonum, fbuynum, funitprice)

select t.fproid,t.fstageid,t.fmodifyid+1, t.fcoursecode,t.fcoursename,t.fitemid,t.fitemname,t.ftype,t.fstyle,t.frequirement,

t.flabmemnum,t.fgroupnum,t.fdevicename,t.fmodel,t.fhavenum,t.fobsonum,t.fbuynum,t.funitprice

from jwxt.SY_SYJS_PROITEM t

where t.fmodifyid=(select max(mt.fmodifyid) from jwxt.SY_SYJS_PROITEM mt

where mt.fproid=proID

and mt.fstageid='2'

and mt.zt='1'

)

and t.fproid=proID

and t.fstageid='2'

and t.zt='1';

commit;

end if;

open tab for

select t.FProTypeID, t.FProTypeName from jwxt.sy_syjs_audit t

where FModifyID=(select max(t.fmodifyid) from jwxt.sy_syjs_proitem td

where td.fproid=proID and td.fstageid=historyStageID

and td.zt='1')

and t.fproid=proID and t.fstageid=historyStageID  and t.zt='1';

end;

头文件：

procedure proc_sy_syjs_getAProChange(proID in jwxt.sy_syjs_audit.FProID%type,

historyStageID in number,

                                              tab out t_cursor);