<span style="font-size:18px;">begin
if &var > 10 and &var <= 50 then
dbms_output.put_line('输入值大于10');
elsif &var > 50 then
dbms_output.put_line('输入值大于50');
else
dbms_output.put_line('不在范围中');
end if;
end;
--case搜索语句 （默认搜索true，当when满足true时执行。当case false时，搜索false，当when为false时执行）
begin
case true
when &var < 10 and &var > 0 then
dbms_output.put_line('范围是0~10');
when &var < 50 and &var > 10 then
dbms_output.put_line('范围是10~50');
when &var < 100 and &var > 50 then
dbms_output.put_line('范围是50~100');
else
dbms_output.put_line('不在范围中');
end case;
end;
--简单case语句（等于比较）
declare var1 varchar2(30);
begin
var1 := &var;
case var1
when '男' then
dbms_output.put_line('性别为男');
when '女' then
dbms_output.put_line('性别为女');
else
dbms_output.put_line('不在范围中');
end case;
end;
--数值for循环
begin
for i in 1..10 Loop
dbms_output.put_line('The index value is ['||i||']');
end loop;
end;
--显示游标for循环(i相当于一条记录，能直接通过 i.字段名获得对应的字段值)
declare
cursor cur is select * from SCOTT.EMP t;
begin
for i in cur loop
dbms_output.put_line('The name is ['||i.ename||']');
end loop;
end;
--隐式游标for循环(i相当于一条记录，能直接通过 i.字段名获得对应的字段值)
begin
for i in (select * from SCOTT.EMP t) loop
if i.ename = 'JONES' then
exit;
end if;
dbms_output.put_line('The name is ['||i.ename||']');
end loop;
dbms_output.put_line('循环结束');
end;
--简单循环
declare
emp_entity SCOTT.EMP%rowtype; --定义一行的数据类型 emp_name SCOTT.EMP.ENAME%type;
cursor cur is select * from SCOTT.EMP t;
begin
open cur;
loop
fetch cur into emp_entity;
dbms_output.put_line('The name is ['||emp_entity.ename||']');
exit when cur%notfound; --exit when放在循环最后执行，达到类似repeat until循环的效果
end loop;
close cur;
end;
-- exit when的用法
declare
emp_name SCOTT.EMP.ENAME%type;--根据表的指定字段的数据类型定义变量
cursor cur is select t.ename from SCOTT.EMP t;
begin
open cur;
loop
fetch cur into emp_name;
exit when emp_name ='wanli'; --条件放前面，先判断后执行
dbms_output.put_line('The name is ['||emp_name||']');
end loop;
close cur;
end;
--if exit的用法 continue和continue when跳过当前索引值是11g的新功能
declare
emp_name SCOTT.EMP.ENAME%type;
cursor cur is select t.ename from SCOTT.EMP t;
begin
open cur;
loop
fetch cur into emp_name;
if emp_name = 'wanli' then
exit;
else
dbms_output.put_line('The name is ['||emp_name||']');
end if;
end loop;
close cur;
end;
--while循环
declare
emp_name SCOTT.EMP.ENAME%type;
cursor cur is select t.ename from SCOTT.EMP t;
begin
open cur;
while cur%isopen loop
fetch cur into emp_name;
if cur%notfound then
close cur; --关闭游标，不能通过while循环的条件判断，自然结束循环
end if;
dbms_output.put_line('The name is ['||emp_name||']');
end loop;
end;
</span>
