Oracle 静态 动态游标的使用

declare

      ida varchar(200); 

      a varchar(200);

      b varchar(200);

      c varchar(200);

      i number(20);

      err number(20) default 0;

      type cs is ref cursor return test%rowtype; --定义强类型游标

      mycursor cs;  -- 声明动态强类型游标变量

      m test%rowtype; 

      CURSOR mycursor2 is select * from test;

--       test1Cursor Cursor for select * from test;

begin 

  ida:= '13140';

  i := 13139;

 -- select gp.rugr_lruname into a from R_T_RUGROUP gp where gp.rugr_id in (select sp.rumesarearugr_id from  R_S_RuMembership sp where sp.saru_id = i and sp.rumes_state = 1);

 -- select gp.rugr_lruname into b from R_T_RUGROUP gp where gp.rugr_id in (select sp.rumescityrugr_id from  R_S_RuMembership sp where sp.saru_id = i and sp.rumes_state = 1);

 -- select gp.rugr_lruname into c from R_T_RUGROUP gp where gp.rugr_id in (select sp.rumesprovincerugr_id from  R_S_RuMembership sp where sp.saru_id = i and sp.rumes_state = 1); 

  err:=0;  

  while i<13144 loop

          insertCard(i,'aaaa',err);

          if err != 0 then

            dbms_output.put_line('inser err'||err||' i='||i);            

          end if;

          if SQL%FOUND then

              dbms_output.put_line('ok ok ok ok');

          end if; 

          i:=i+1;                    

  end loop;

  commit;

  Dbms_Output.put_line('总出错数'||err);

  Dbms_output.put_line('区级:'||a);  Dbms_output.put_line('市级:'||b);  Dbms_output.put_line('省级:'||c);    

  open mycursor for select * from test;

 

  insert into test values(2113,'1111');

  loop

      fetch mycursor into m;

      exit when mycursor%NOTFOUND;

      dbms_output.put_line(m.id);

   end loop;  

   

   

   for m in mycursor2

     loop

       dbms_output.put_line(m.id || m.name);

     end loop;

   

  /*

  lookCard(mycursor);

  insert into test values(1112,'1111');

  commit;

    loop

      fetch mycursor into m;

      exit when mycursor%NOTFOUND;

      dbms_output.put_line(m.id);

    end loop;  

    */     

end;

/

--select * from test ;

/*

create or replace procedure lookCard(

       cur out sys_refcursor       

)is 

begin

     open cur for select * from test; 

end;

/

*/

--select * from test; 

/*

create or replace  procedure insertCard(

       id in number,

       con in varchar,

       err in out varchar 

)is

begin

  insert into test values(id,con);

  err:=0;

  EXCEPTION 

            WHEN dup_val_on_index THEN 

                 err:=err+1;

                 dbms_output.put_line('出错');

end;

/

*/

/*

create table test (

       id number(20),

       name varchar2(200)

)

alter table test add constraint aa unique(id);

*/

转载于:https://my.oschina.net/u/723981/blog/292043