Oracle数据库PL/SQL学习笔记二——基础控制语句

<span style="font-size:18px; font-family: Arial, Helvetica, sans-serif;">--简单的if判断</span>

<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>