create or replace procedure dp_CopySmlOpt
(ai_BOptID int,
ai_SOptID int,
as_newSOptName varchar2,
ri_newSOptID out int) is
cursor cur_OForms is
select OFormID from D_OForm where SOptID = ai_SOptID;
li_OFormID int;
li_newOFormID int;
--业务环节表、业务流程表、环节表单表
cursor cur_Steps is
select OStepID from D_OStep where SOptID = ai_SOptID ;
li_OStepID int;
li_newOStepID int;
li_oflowid int;
li_TableID number(10);
--业务表单主键值的前后对照
TYPE T_S is record
(
old int,
new int
);
TYPE T_a IS TABLE OF T_S INDEX BY BINARY_INTEGER;
--定义表变量，暂存数据
--业务流程
Flow_rec D_OFlow%rowtype;
TYPE T_b IS TABLE OF D_OFlow%rowtype INDEX BY BINARY_INTEGER;
--环节表单
SFlow_rec D_SForm%rowtype;
TYPE T_c IS TABLE OF D_SForm%rowtype INDEX BY BINARY_INTEGER;
li_count1 int:=1;
li_count2 int:=1;
li_count3 int:=1;
li_i int;li_j int;
T_Flow T_b;
T_OldNew T_a;
T_SFlow T_c;
e_error exception;
begin
--新增业务小类
select SOptID.nextval into ri_newSOptID FROM DUAL;
insert into D_SmlOpt(SOptID,BOptID, LDTypeID, NumID, SOName, OTLimit, InsProc,
AppProc, PigProc, DelProc, OOrder, Remark, State)
select ri_newSOptID,ai_BOptID, LDTypeID, NumID, as_newSOptName, OTLimit, InsProc,
AppProc, PigProc, DelProc, OOrder+1, Remark, State
from D_SmlOpt where SOptID = ai_SOptID;
if sqlcode<>0 then
raise e_error;
end if;
--业务收费定义表
insert into D_OChr(OChrID,SOptID, CTypeID, OCOrder)
select ri_newSOptID,ri_newSOptID, CTypeID, OCOrder
from D_OChr
where SOptID = ai_SOptID;
if sqlcode<>0 then
raise e_error;
end if;
--业务收件表
insert into D_ORecv(ORecvID,SOptID, RecvID, OROrder, Need, Remark, State)
select ri_newSOptID,ri_newSOptID, RecvID, OROrder, Need, Remark, State
from D_ORecv
where SOptID = ai_SOptID;
if sqlcode<>0 then
raise e_error;
end if;
open cur_OForms;
fetch cur_oforms into li_OFormID;
while cur_oforms%FOUND loop
select OFormID.NEXTVAL INTO li_newOFormID FROM DUAL;
insert into D_OForm(OFormID,SOptID, FormID, ListText, FitName, FDisplay, DftForm,
OFOrder, Remark, State)
select li_newOFormID,ri_newSOptID, FormID, ListText, FitName, FDisplay, DftForm,
OFOrder, Remark, State
from D_OForm where OFormID = li_OFormID;
if sqlcode<>0 then
close cur_oforms;
raise e_error;
end if;
t_Oldnew(li_count1).old := Li_Oformid;
T_OLDNEW(li_count1).new := li_newoformid;
li_count1 := li_count1 + 1;
fetch cur_oforms into li_OFormID;
end loop;
close cur_oforms;
for Flow_Rec in (select * from D_OFlow where SOptID = ai_SOptID)loop
T_flow(li_count2):=Flow_Rec;
li_count2:=li_count2+1;
end loop;
open cur_Steps;
fetch cur_steps into li_OStepID;
while cur_steps%FOUND LOOP
select OStepID.nextval into li_newostepid from dual;
insert into D_OStep(OStepID,SOptID, AttID, SName, STLimit, SType, DoType,
DoID, SetDate, DelDate, NeedExam, ExamIndex, BtnDisp, OSORDER,
Center, Remark, State)
select li_newOStepID,ri_newSOptID, AttID, SName, STLimit, SType, DoType,
DoID, SetDate, DelDate, NeedExam, ExamIndex, BtnDisp, OSORDER,
Center, Remark, State
from D_OStep where OStepID = li_OStepID;
if sqlcode<>0 then
close cur_steps;
raise e_error;
end if;
--生成临时的环节表单表
for SFlow_Rec in (select * from D_SForm where OStepID = li_OStepID) loop
T_Sflow(li_count3):=SFlow_Rec;
li_count3:=li_count3+1;
end loop;
--更新临时的业务流程表
for li_i in 1..li_count2 loop
if T_flow(li_i).pstepid = li_ostepid then
T_flow(li_i).pstepid := li_newostepid;
end if;
if T_flow(li_i).nstepid = li_ostepid then
T_flow(li_i).nstepid := li_newostepid;
end if;
end loop;
fetch cur_steps into li_OStepID;
end loop;
close cur_steps;
--环节表单表
for li_i in 1..li_count3 loop
for li_j in 1..li_count1 loop
--下面这句报错,怎么回事?******************************************
if T_OLDNEW(li_j).old = T_SFLOW(li_i).OFormID then
T_SFLOW(li_i).OFormID := T_OLDNEW(li_j).new;
end if;
--****************************************************************
end loop;
select SFormID.nextval into li_TableID FROM DUAL;
insert into D_SForm(SFormID,OStepID, OFormID, FormID, ListText, FitName, FDisplay,
FieldCtrl, WNeed, WCan, BtnDisp, DftForm, CanPrint, SFOrder, Remark, State)
values(li_TableID,T_SFLOW(li_i).OStepID, T_SFLOW(li_i).OFormID, T_SFLOW(li_i).FormID,
T_SFLOW(li_i).ListText, T_SFLOW(li_i).FitName, T_SFLOW(li_i).FDisplay,
T_SFLOW(li_i).FieldCtrl, T_SFLOW(li_i).WNeed, T_SFLOW(li_i).WCan,
T_SFLOW(li_i).BtnDisp, T_SFLOW(li_i).DftForm, T_SFLOW(li_i).CanPrint,
T_SFLOW(li_i).SFOrder, T_SFLOW(li_i).Remark, T_SFLOW(li_i).State);
end loop;
--业务流程表
for li_i in 1..li_count2 loop
select OFlowID.nextval into li_TableID FROM DUAL;
insert into D_OFlow(OFlowID,SOptID, PStepID, NStepID, FType, Caption, GType, GIndex, Operator,
Con1, Con2, SetDate, DelDate, MidPts, Center, Remark, State)
values(li_TableID,ri_newSOptID,T_FLOW(li_i).PStepID, T_FLOW(li_i).NStepID, T_FLOW(li_i).FType,
T_FLOW(li_i).Caption, T_FLOW(li_i).GType, T_FLOW(li_i).GIndex, T_FLOW(li_i).Operator,
T_FLOW(li_i).Con1, T_FLOW(li_i).Con2, T_FLOW(li_i).SetDate, T_FLOW(li_i).DelDate,
T_FLOW(li_i).MidPts, T_FLOW(li_i).Center, T_FLOW(li_i).Remark, T_FLOW(li_i).State);
end loop;
commit;
exception
when e_error then
rollback;
when others then
rollback;
end dp_CopySmlOpt ;
