oracle控制语句

--控制语句
--1
declare
v_sal number(6,2);
begin
select sal into v_sal from emp
where lower(ename)=lower('&&name');
if v_sal<2000 then
update emp set sal=v_sal+200
where lower(ename)=lower('&name');
end if;
end;

--2
declare
v_sal emp.sal%type;
v_name emp.ename%type;
begin
select ename,sal into v_name, v_sal from emp where empno=&no;
case
when v_sal<=1000 then
update emp set comm=100 where ename=v_name;
when v_sal<=2000 then
update emp set comm=80 where ename=v_name;
when v_sal<=6000
then update emp set comm=50 where ename=v_name;
end case;

end;

--循环
--基本循环
create table temp(a int);

declare
i int:=1;
begin
loop
insert into temp(a) values(i);
exit when i=10;
i:=i+1;

end loop ;
end;

--while循环

declare
i int:=10;
begin
while i<=20 loop
insert into temp values(i);
i:=i+1;
end loop ;
end;

--for 循环
begin
for i in 20..30 loop
insert into temp values(i);
end loop ;
end;
--嵌套循环
declare
result int;
begin
<<outer>>
for i in 1..100 loop
<<inner>>
for j in 1..100 loop
result:=i*j;
exit outer when result=1000;
exit when result=500;
end loop inner;
dbms_output.put_line(result);
end loop outer;
dbms_output.put_line(result);
end;

--顺序控制语句
--go to
declare
i int:=1;
begin
loop
insert into temp values(i);
if i>=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;