oracle - 循环

loop循环：
create or replace procedure pro_test_loop is
i number;
begin
i:=0;
loop
i:=i+1;
dbms_output.put_line(i);
if i>5 then
exit;
end if;
end loop;
end pro_test_loop;

while循环：
create or replace procedure pro_test_while is
i number;
begin
i:=0;
while i<5 loop
i:=i+1;
dbms_output.put_line(i);
end loop;
end pro_test_while;

for循环1：
create or replace procedure pro_test_for is
i number;
begin
i:=0;
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end pro_test_for;

 

for in loop:

for c_ag in (select a.agentid,
                        a.recommendagentid,
                        getagentstatus(a.agentstatus) agentstatus,
                        a.agentcode,
                        a.agentname,
                        a.agentlevelcode,
                        a.hiredate
                   from agenthis a
                  where a.bizyearmonth = p_month
                    and a.agentstatus = 'AGENTSTATUS_09'
                    and a.branchid = p_branchid
                    and (a.agentcode =p_agentcode or p_agentcode is null)
                    and (rownum <= v_num or v_num = 0)
                 
                 ) loop

//代码

end loop;

 

for loop 替代Cursor

我们在Oracle存储过程中需要遍历一张表，应该怎样做。我想大多少的人第一个念头就是Cursor。

　　比如：

　　create or replace procedure StudyCursor(

　　resulst out integer

　　) is

　　v_tablename varchar(30);

　　v_tabletype varchar(11);

　　cursor mycursor is select * from cat;

　　begin

　　open mycursor;

　　loop

　　fetch mycursor into v_tablename,v_tabletype;

　　null; --you can use tablename and v_tabletype

　　end loop;

　　close mycursor;

　　end StudyCursor;

　　最近在看代码是，发现其实我们还有一个更方便的方法就是使用for　in　loop …　end loop

　　create or replace procedure StudyFor(

　　resulst out integer

　　) is

　　begin

　　for emm in(select * from cat) loop

　　null;　--you can use emm.table_name and emm.table_type

　　end loop;

　　return ;

　　end StudyFor;

　　是不是更方便，我要使用的查询结果，只需使用emm.table_name和emm.table_type即可。

 

 

goto语句和标签

declare
  v_applicantionno varchar2(50);
begin

  for cur_me in (select *
                   from (select * from persist_test a order by ac06 desc)
                  where rownum < 10) loop
    begin
      if (cur_me.ac06 is not null) then
        v_applicantionno := cur_me.ac06;
      else
        goto end_insert;
      end if;
    exception
      when others then
        dbms_output.put_line('出错' || cur_me.ac06 || sqlerrm);
    end;
  
    begin
      insert into persist_test1 (ac06) values (v_applicantionno);
    exception
      when others then
        dbms_output.put_line(cur_me.ac06 || '插入出错');
    end;
  
    <<end_insert>>
    dbms_output.put_line(cur_me.ac06 || '对应的数据为空');
  end loop;
exception
  when others then
    dbms_output.put_line(sqlerrm || '全局出错');
end;

 

不常用：

------example 1: loops with IF statement-----------  
 set serveroutput on  
 declare   
     v_counter binary_integer := 0;  
     begin  
       loop  
         --increment loop counter by one  
         v_counter := v_counter + 1;  
         DBMS_OUTPUT.put_line('v_counter = '||v_counter);  
           
         --if exit condition yields true exit the loop  
         if v_counter  = 5 then  -- the same as : exit when v_counter = 5;  
           exit;  
         end if;  
           
         end loop;  
         --control resumes here  
         dbms_output.put_line('Done...');  
       end;  
        
 ------example 2: loops with EXIT WHEN conditions-----------  
 set serveroutput on  
 declare  
     v_course           course.courser_no%type :430;  
     v_instructor_id    instructor.instructor_id%type :102;  
     v_sec_num          section.section_no%type :=0;  
 begin  
     loop  
       --increment section number by one  
       v_sec_num := v_sec_num + 1;  
       insert into section   
              (section_no,course_no,section_no,instructor_id,  
              created_date, created_by,modified_date,  
              modified_by)  
        values  
              (section_id_seq.nextval,v_course,v_sec_num,  
              v_instructor_id,SYSDATE,USER,sysdate,user);  
        --if number of section added is four exit the loop  
        exit when v_sec_num = 4;  
      end loop;  
        
      --control resumes here  
      commit;  
 end;  
   
 --------exiample 3: loops with WHILE Loops condition---------  
 set serveroutput on   
 declare  
     c_counter binary_integer := 1;  
     v_sum     number :=0;  
     begin  
       while v_counter <= 10 loop  
            v_sum := v_sum + v_counter;  
            dbms_output.put_line('current sum is: '||v_sum);  
              
            -- increment loop counter by one  
            v_counter := v_counter + 1;  
       end loop;  
         
       -- control resumes here  
       dbms_output.put_line('the sum of integers between 1 '||  
                            'and 10 is: '||v_sum);  
 end;  
   
 ---------example 4: loop with For Loops----------  
 set serveroutput on  
 declare  
     v_factorial number := 1;  
 begin  
     --the loop counter is defined implicitly by the loop  
     --Therefore,before the loop ,the loop counter is undefined and has no value;  
     for v_counter in 1..10 loop  
       v_factorial := v_factorial * v_counter;  
       end loop;  
         
       --countrol resume here  
       dbms_output.put_line('factorial of ten is :'||v_factorial);  
 end;