create or replace procedure pr_zhaozhenlong_loop /* 名称:在存储过程中执行3种循环语句 功能:利用循环给表中插入数据 调用: begin -- Call the procedure pr_zhaozhenlong_strsql; end; 创建人:赵振龙 创建时间:2007-01-03 */ 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; /
转载于:https://www.cnblogs.com/coprince/p/3443219.html