Oracle遍历

一.遍历数组

create or replace procedure coco3 as

 type t_Test is table of varchar2(10);

 test t_test:=t_test('A','B','C','D','E','F','G','H','J','K','L');

begin

  for i in 1 .. test.count loop

    dbms_output.put_line(test(i));

  end loop;

end;

二.遍历游标

方式一:

create or replace procedure CoCo2 as

cursor cur_n is select * from users where user_id like 'EDP%';    

v_row users%rowtype;

begin

  open  cur_n; 

  loop  

  fetch cur_n into v_row;  

  exit when   cur_n%notfound;  

   dbms_output.put_line('user_id:'||v_row.user_id||'name:'||v_row.name);

  end loop;  

 close cur_n;

end ;

方式二:

create or replace procedure CoCo as

cursor cur_name is select user_id,name as usr_name from users where user_id like 'EDP%';

begin

  for cur in cur_name loop

    begin

     dbms_output.put_line(cur.user_id||'-'||cur.usr_name);

    end;

   end loop;

end ;

三:遍历数据表

create or replace procedure coco4 as

 type t_users is table of users%rowtype;

 v_user t_users;

begin

  select *  bulk collect into v_user from users where user_id like 'EDP%';

  if v_user.count>0 then

    for i in 1 .. v_user.count loop

      dbms_output.put_line('haha:'||v_user(i).name);

    end loop;

  end if;

end;