A為表名 A_TR為臨時(shí)表（操作完自動(dòng)刪除里面數(shù)據(jù)），下面是存儲(chǔ)過程代碼：

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; /

通過存儲(chǔ)過程操作臨時(shí)表實(shí)現(xiàn)對(duì)表的增刪改