Oracle存储过程之insert的使用，含游标的使用
lidongliang 2013-01-17
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);
