Oracle（22）pl/sql编程控制结构语句

末蓝、 2024-02-17 21:02 9阅读 0赞

①条件语句if..then/if\_then\_else

-------------- 单if——then ---------------
    create or replace procedure pro1(v_in_empno varchar2) is
    v_sal emp.sal%type;
    begin
      select sal into v_sal from emp where empno = v_in_empno;
      if v_sal<2000 then
        update emp set sal=sal*1.1 where empno=v_in_empno;
      end if;
    end;
    -------------- 多重if—then-elsif—then ---------------
    create or replace procedure pro1(v_in_empno varchar2) is
    v_job emp.job%type;
    begin
      select job into v_job from emp where empno = v_in_empno;
      if v_job='PRESIDENT' then
        update emp set sal=sal+1000 where empno=v_in_empno;
      elsif v_job='MANAGER' then
        update emp set sal=sal+500 where empno=v_in_empno;
      else
        update emp set sal=sal+200 where empno=v_in_empno;
      end if;
    end;

②循环结构

----- 循环添加n条记录 到表users5-----------
    -----------loop基本循环
    create or replace procedure pro1(v_in_ename varchar2, n number) is
    --定义需要的变量
    v_empno users5.id%type:=1;
    
    begin
      loop
        exit when n<=0;
        --执行添加任务
        insert into users5 values(v_empno, v_in_ename);
        exit when v_empno = n;
        --v_empno自增
        v_empno:=v_empno+1;
      end loop;
    end;
    -----------while...loop循环
    create or replace procedure pro1(v_in_ename varchar2, n number) is
    --定义需要的变量
    v_empno users5.id%type:=1;
    
    begin
      while v_empno <= n loop
        --执行添加任务
        insert into users5 values(v_empno, v_in_ename);
        --v_empno自增
        v_empno:=v_empno+1;
      end loop;
    end;
    -----------for i in reverse begin_i..end_i loop循环
    create or replace procedure pro1(v_in_ename varchar2, n number) is
    --定义需要的变量
    v_empno users5.id%type:=1;
    
    begin
      for v_empno in reverse 1..n loop
        --执行添加任务
        insert into users5 values(v_empno, v_in_ename);
      end loop;
    end;

③顺序控制语句 goto null（null语句主要为了提高代码的可读性，什么都不做）

declare
    i number:=1;
    begin
      <<start_loop>>   ---标号
      loop
        dbms_output.put_line('输出i=' || i);
        if i=12 then
          goto end_loop;
        end if;
    
        i:= i + 1;
        if i=10 then
          goto start_loop;
        else
          null;
        end if;
      end loop;
      <<end_loop>>
      dbms_output.put_line('循环结束');
    end;