update_cma.sql

学习PL/SQL时收集的一些经典的SQL源代码

create or replace procedure update_cma
(grade1		in	number)
is
   query_str 	varchar2(4000);
   cursor c1 is select id,seqno from cma where grade=grade1 order by id;
   dummy c1%rowtype; 
   i		number(11);
begin
   open c1;
   fetch  c1  into   dummy;
   i:=0;
   while c1%found  loop
   	i:=i+1;
   	query_str:='update cma set seqno='||i||' where id='||dummy. id;
   	 EXECUTE IMMEDIATE query_str;
      	 fetch c1 into dummy;
   end loop;
   commit;
   close c1;
exception
   WHEN OTHERS THEN  
   	rollback;
end;
/

create table cma(
id	number(4),
grade	number(2),
seqno	number(4),
name	varchar2(20));

insert into cma values(1,1,3,'aaaa');
insert into cma values(2,2,6,'ttttt');
insert into cma values(3,1,9,'bbbb');
insert into cma values(4,3,12,'hhhhh');
insert into cma values(5,2,4,'ddddd');
insert into cma values(6,3,1,'kkkkk');
insert into cma values(7,1,7,'llll');
insert into cma values(8,2,9,'ooooo');
insert into cma values(9,3,123,'pppp');
insert into cma values(10,1,43,'fffff');
commit;

 select id,grade,seqno from cma order by grade,seqno;
 
 exec update_cma(1);