create or replace procedure testProc (topId in number,priId in number,flowType in varchar2)
as
declare
pri_id integer;
fieldtitle varchar2(20);
fieldname varchar2(20);
fieldvalue varchar2(4000);
old_priid number;
lorder integer;
begin
cursor mycursor is
select a.PRI_ID,c.fieldtitle ,d.FIELDNAME,d.fieldvalue,0 as old_priid ,
c.lorder from oaflowinstance a,flow b,flowfield c,oaflowfield d
where a.TOPORGID = b.COMPANYID and a.FLOWTYPE = b.FLOWTYPE
and b.flowid = c.flowid and a.PRI_ID = d.PRI_ID and a.TOPORGID = topId
and b.FLOWTYPE = flowType and c.FIELDNAME = d.FIELDNAME
and d.PRI_ID = priId order by c.lorder;
begin
for c_mycursor in mycursor loop
pri_id := c_mycursor.pri_id;
fieldtitle := c_mycursor.fieldtitle;
fieldname := c_mycursor.fieldname;
fieldvalue := c_mycursor.fieldvalue;
old_priid := c_mycursor.old_priid;
lorder := c_mycursor.lorder;
insert into oafilefield (id,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder) values (seq_id_law.nextval,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder);
end loop;
end;
end testProc;
as
declare
pri_id integer;
fieldtitle varchar2(20);
fieldname varchar2(20);
fieldvalue varchar2(4000);
old_priid number;
lorder integer;
begin
cursor mycursor is
select a.PRI_ID,c.fieldtitle ,d.FIELDNAME,d.fieldvalue,0 as old_priid ,
c.lorder from oaflowinstance a,flow b,flowfield c,oaflowfield d
where a.TOPORGID = b.COMPANYID and a.FLOWTYPE = b.FLOWTYPE
and b.flowid = c.flowid and a.PRI_ID = d.PRI_ID and a.TOPORGID = topId
and b.FLOWTYPE = flowType and c.FIELDNAME = d.FIELDNAME
and d.PRI_ID = priId order by c.lorder;
begin
for c_mycursor in mycursor loop
pri_id := c_mycursor.pri_id;
fieldtitle := c_mycursor.fieldtitle;
fieldname := c_mycursor.fieldname;
fieldvalue := c_mycursor.fieldvalue;
old_priid := c_mycursor.old_priid;
lorder := c_mycursor.lorder;
insert into oafilefield (id,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder) values (seq_id_law.nextval,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder);
end loop;
end;
end testProc;
解决方案 »
- AWR报表的问题
- 高人帮忙看个牛的存贮过程更新出现错误问题!!!!!
- 请问一个rowtype变量或者多列的游标是否知道自身包含的那些列的列名?
- 看似简单,却又复杂的CONVERT问题 (Oracle 10g)
- 各位好啊。大家帮我看看这个sql怎么优化,帮帮忙啊,
- 关于database link 连接的问题
- 一个存储过程有错误帮我看看啊,谢谢
- power designer中有字段相同的时候,改变1张表中那个字段,另张表的也变,怎样不改变啊?
- 关于ORA-00600错误的问题。
- 升级庆祝,放分,请zjcop (柳风) 来,我送50
- 查询语句写不出来了,达人们帮帮忙,出出招!
- 请教高手,怎么样在where条件里带判断语句
fetch c_mycursor into pri_id,
fieldtitle,
fieldname,
fieldvalue,
old_priid,
lorder
exit when c_mycursor%notfound;
insert into .................
loop
fetch mycursor into pri_id,
fieldtitle,
fieldname,
fieldvalue,
old_priid,
lorder
exit when c_mycursor%notfound;
insert into oafilefield (id,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder) values (seq_id_law.nextval,pri_id,fieldtitle,fieldname,fieldvalue,old_priid,lorder);
end loop;
close mycursor ;
end;