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

来源：互联网发布：unix与windows 编辑：程序博客网时间：2024/05/29 13:25

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

0 0