通过存储过程操作临时表实现对表的增删改

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

转载于:https://www.cnblogs.com/xf1224/p/4475366.html