oracle 条件控制 case when

格式一:
CASE  条件表达式
  WHEN 条件表达式结果1 THEN 
     语句段1
  WHEN 条件表达式结果2 THEN
     语句段2
  ......
  WHEN 条件表达式结果n THEN
     语句段n
  [ELSE 条件表达式结果]
END;

格式二
CASE 
  WHEN 条件表达式1 THEN
     语句段1
  WHEN 条件表达式2 THEN
     语句段2
  ......
  WHEN 条件表达式n THEN 
     语句段n
  [ELSE 语句段]
END;

例子1 对应格式一：

 

create or replace procedure p_test_casewhen is
  V_grade     char(1) := 'A';
  V_appraisal VARCHAR2(20);
begin
  V_appraisal := CASE v_grade
                   WHEN 'A' THEN
                    'Excellent'
                   WHEN 'B' THEN
                    'Very Good'
                   WHEN 'C' THEN
                    'Good'
                   ELSE
                    'No such grade'
                 END;
  DBMS_OUTPUT.PUT_LINE('Grade:' || v_grade || '  Appraisal: ' ||
                       v_appraisal);
end p_test_casewhen;

例子2 对应格式二

 

 

create or replace procedure p_test_casewhen is
  v_name      emp.ename%TYPE;
  v_job       emp.job%TYPE;
  v_sal       emp.sal%TYPE;
  v_sal_raise NUMBER(3, 2);
begin
  SELECT ename, job, sal
    INTO v_name, v_job, v_sal
    FROM emp
   WHERE empno = '7698';
  CASE
    WHEN v_job = 'CLERK' THEN
      IF v_sal < 3000 THEN
        v_sal_raise := .08;
      ELSE
        v_sal_raise := .07;
      END IF;
    WHEN v_job = 'SALESMAN' THEN
      IF v_sal < 4000 THEN
        v_sal_raise := .06;
      ELSE
        v_sal_raise := .05;
      END IF;
    WHEN v_job = 'MANAGER' THEN
      IF v_sal < 3500 THEN
        v_sal_raise := .04;
      ELSE
        v_sal_raise := .03;
      END IF;
    ELSE
      DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: ' || v_job);
  END CASE;
  if v_sal_raise is not null then
    DBMS_OUTPUT.PUT_LINE(v_name || '的岗位是' || v_job || '、的工资是' || v_sal ||
                         '、工资涨幅是' || v_sal_raise);
  end if;
end p_test_casewhen;