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

时间:2021-04-23 06:01:04
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
/