A为表名 A_TR为临时表（操作完自动删除里面数据），下面是存储过程代码：
create or replace procedure A_UPDATE(p_id in varchar2)
as
user_err exception; -- user exception
user_err_code varchar2(200); -- contents of user error
err_sqlcode varchar2(100); -- err code
proc_name varchar2(30) := 'a_update';
tran_name varchar2(30) := 'a_tr_tbl';
-- a_tr_tbl transaction
cursor c_atr is select * from a_tr_tbl where USERID = p_id for update;
atr_rec a_tr_tbl%rowtype;
-- a_tbl
cursor c_a is select * from a_tbl where name= atr_rec.name
AND id= atr_rec.id for update;
a_rec a_tbl%rowtype;
begin
open c_atr;
fetch c_atr into atr_rec;
while c_atr%found loop
-- a_tbl insert
insert into a_tbl values (atr_rec.name,
atr_rec.id,
atr_rec.desc
); --FREE_SPACE
end if;
-- a_tbl update
open c_a;
fetch c_a into a_rec;
if c_a%notfound then
user_err_code := 'update a_tbl not found';
raise user_err;
end if;
update a_tbl set name= atr_rec.name,
desc= atr_rec.desc
where current of c_a;
close c_a;
end if;
-- a_tbl delete
open c_a;
fetch c_a into a_rec;
if c_a%notfound then
user_err_code := 'delete a_tbl not found';
raise user_err;
end if;
delete from a_tbl where current of c_a;
close c_a;
end if;
delete from a_tr_tbl where current of c_atr;
fetch c_atr into atr_rec;
end loop;
close c_atr;
exception
when user_err then
rollback;
insert into err_tbl values(p_id, ' ', proc_name, user_err_code, sysdate, tran_name);
when others then
rollback;
err_sqlcode := sqlcode || SQLERRM;
insert into err_tbl values(p_id, ' ', proc_name, err_sqlcode, sysdate, tran_name);
end A_UPDATE; /
