Oracle存储过程中执行3种循环语句

本文转载自：http://hi.baidu.com/wkcs520/blog/item/7e06a0514fe3f9868d543027.html

 

create or replace procedure pr_zhaozhenlong_loop
/*
名称：在存储过程中执行3种循环语句
功能：利用循环给表中插入数据
调用：
       begin
         -- Call the procedure
         pr_zhaozhenlong_strsql;
       end;
*/
is
      i int;
begin
      i :=1;
      loop
          insert into tb_zhaozhenlong(rpt_date ,dept_id,item,qty) values(to_date('2007-01-01','yyyy-MM-dd'),'D'||i,'I'||i,round(i*100/3,3));
          exit when i =10;
          i :=i+1;
      end loop;
      --
      i :=1;
      while i<=5 loop
          insert into tb_zhaozhenlong(rpt_date ,dept_id,item,qty) values(to_date('2007-01-02','yyyy-MM-dd'),'D'||i,'I'||i,round(i*200/3,3));
          i :=i+1;
      end loop;
      --如果指定了reverse选项，则循环控制变量会自动减1，否则自动加1
      for j in reverse   1..10 loop
          --insert into tb_zhaozhenlong(rpt_date ,dept_id,item,qty) values(to_date('2007-01-03','yyyy-MM-dd'),'D'||j,'I'||j,round(j*300/3,3));
          insert all --first，不会被重复插入
          when i <> 2 then into tb_zhaozhenlong(rpt_date ,dept_id,item,qty)
          else into tb_temp_zhaozhenlong(rpt_date ,dept_id,item,qty)--如果两个表结构完全一样，则列举不用列名
          select to_date('2007-01-02','yyyy-MM-dd')as rpt_date,'D'||j as dept_id,'I'||j as item,round(j*300/3,3) as qty from dual;
      end loop;
      commit;

      --？？？？？？？
      <<outer_zzl>>--？？？？？？
      for x in   1..10 loop
          <<inner_zzl>>
          for y in 1..100 loop
          i :=x*y;
          exit outer_zzl when i=500;
          exit when i =300;
          end loop inner_zzl;
          --<<inner_zzl>>
      end loop outer_zzl;
      --<<outer_zzl>>
end；
/