分支控制语句

4.1.1 if-then-else
declare
  v_count number(10) :=0;
  v_empno number(4) := 7888;
begin
  select count(1) into v_count
  from emp where empno = v_empno;
  if v_count = 0
  then
    insert into emp
           (empno,ename,job1,hiredate,sal,deptno)
     values(v_empno,'张三','经理',trunc(sysdate),100,30);
  else
    update emp
      set ename='张三',job1='经理',
          hiredate =trunc(sysdate),
          sal =1000,
          deptno =30
      where empno =v_empno;
  end if;
  commit;
exception
  when others
  then
    dbms_output.put_line(sqlerrm);
end;

declare 
  v_sal  number(7,2);
  v_deptno number (2);
  v_job  varchar2(9);
begin
  select deptno,v_job,sal 
    into v_deptno,v_job,v_sal from emp
    where empno= :empno;--变量绑定
  if v_deptno =20
  then
    if v_job = 'CLERK'
    then
      v_sal := v_sal*(1+0.12);
    elsif v_job='ANALYST'
    then
      v_sal := v_sal*(1+0.19);
    end if
  else 
    dbms_output.put_line('仅部门编号为20的员工才能加薪!');
  end if
end;

4.1.2 if-then-elsif语句
declare 
  v_character  char(1) := &tmpVar;
begin
  if v_character ='A'
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif v_character = 'B'
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif v_character = 'C'
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif v_character = 'D'
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  else
    dbms_output.put_line('不是A-D之间的字符');
  end if;
end;
/

create or replace procedure sp_strcomp(p1 varchar2) --用存储过程来处理
as
  v_character  varchar2(2):=p1;
begin
  if (v_character ='A')
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif ( v_character = 'B')
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif ( v_character = 'C')
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  elsif ( v_character = 'D')
  then
    dbms_output.put_line('当前输出字符串:'||v_character);
  else
    dbms_output.put_line('不是A-D之间的字符');
  end if;
end;
exec sp_strcomp('E');

4.1.3 case语句
declare
  v_job varchar2(30);
  v_empno number(4) := $empno;
begin
  select job into v_job from emp where empno=v_empno;
  case v_job
    when 'CLERK'
    then
      update emp set sal = sal * (1+0.15)
      where empno = v_empno;
      dbms_output.put('为普通职员加薪15%');
    when 'ANALYST'
    then
      update emp set sal = sal *(1+0.18)
      where empno =v_empno;
       dbms_output.put('为分析人员加薪18%');
    when 'MANAGER'
    then 
      update emp set sal =sal*(1+0.20)
      where empno = v_empno;
        dbms_output.put('为管理人员加薪20%');
    when 'SALESMAN'
    then 
      update emp set sal = sal *(1+0.22);
      where empno = v_empno;
        dbms_output.put('为销售人员加薪22%');
    else  --在case语句中如果不加else语句 会触发ora-6592异常
      dbms_output.put('员工职级不在加薪行列。');
  end case; 
end;

declare
  v_sal  number(10,2);
  v_empno  number(10) :=&empno;
begin
  select sal into v_sal from emp where empno = v_empno;
  case --搜索型case语句示例
    when v_sal between 1000 and 1500
    then 
      dbms_output.put('员工级别：初级职员');
    when v_sal between 1500 and 3000
    then
      dbms_output.put('员工级别：中级职员');
    when v_sal between 3000 and 5000
    then 
      dbms_output.put('员工级别：高级职员');
    else
      dbms_output.put('员工级别：不在级别范围之内');
  end case;
end;