CASE 表达式

---------格式一---------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;

 

例4:

1 DECLARE 2   V_grade char(1) := UPPER('&p_grade'); 3   V_appraisal VARCHAR2(20); 4 BEGIN 5   V_appraisal := 6   CASE v_grade 7     WHEN 'A' THEN 'Excellent' 8     WHEN 'B' THEN 'Very Good' 9     WHEN 'C' THEN 'Good'10     ELSE 'No such grade'11   END;12   DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'  Appraisal: '|| v_appraisal);13 END;

 

例5:

 

1 DECLARE 2    v_first_name employees.first_name%TYPE; 3    v_job_id employees.job_id%TYPE; 4    v_salary employees.salary%TYPE; 5    v_sal_raise NUMBER(3,2); 6 BEGIN 7    SELECT first_name,   job_id,   salary INTO 8           v_first_name, v_job_id, v_salary 9    FROM employees WHERE employee_id = &emp_id;10    CASE11       WHEN v_job_id = 'PU_CLERK' THEN12          IF v_salary < 3000 THEN v_sal_raise := .08;13          ELSE v_sal_raise := .07;14          END IF;15       WHEN v_job_id = 'SH_CLERK' THEN16          IF v_salary < 4000 THEN v_sal_raise := .06;17          ELSE v_sal_raise := .05;18          END IF;19       WHEN v_job_id = 'ST_CLERK' THEN20          IF v_salary < 3500 THEN v_sal_raise := .04;21          ELSE v_sal_raise := .03;22          END IF;23       ELSE24          DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id);25    END CASE;26    DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id27                                     ||'、的工资是'||v_salary28                                     ||'、工资涨幅是'||v_sal_raise);29 END;