Oracle PL/SQL基础语法学习16：CASE Expression（CASE表达式）

SQLplusDB

发布于 2023-08-17 09:12:39

2050

发布于 2023-08-17 09:12:39

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

前言

PL/SQL是Oracle数据库中的一种嵌入式语言，其功能强大，可以进行存储过程和函数的编写，帮助开发者快速高效地处理数据库操作。

最好的学习是实践加上看官方文档。官方文档中的代码例更是精华和重点所在。 IT行业如此，编程如此，Oracle PL/SQL的学习更是如此。

本系列将以《Database PL/SQL Language Reference》的PL/SQL代码例为主线进行介绍。

Oracle PL/SQL基础语法学习16：CASE Expression（CASE表达式）

CASE Expression（CASE表达式）介绍

在Oracle PL/SQL中，CASE表达式（CASE Expression）是一个非常强大且常用的工具，可以用于在SQL或PL/SQL中执行基于条件的操作。CASE表达式类似于IF-THEN-ELSE语句，但使用起来更加灵活，易于阅读和编写。

CASE表达式包含两种形式，一种是简单形式，一种是搜索形式。下面将分别介绍这两种形式的写法、语法以及使用方法。

1. 简单CASE表达式

简单CASE表达式是指在给定的表达式上执行基于等式的比较，如果表达式等于某个值，则执行某个操作。它的基本语法如下：

CASE expression
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   WHEN value3 THEN result3
   ...
   ELSE default_result
END;

其中，expression表示需要比较的表达式，value1、value2、value3等表示需要比较的值，result1、result2、result3等表示各个值相等时的结果，default_result表示当expression不等于任何值时的默认结果。

下面通过一个简单的例子来说明如何使用简单CASE表达式：

SQL> show user
USER 为 "HR"
SQL> SELECT first_name, last_name,department_id，
  2  CASE department_id
  3     WHEN 10 THEN 'Accounting'
  4     WHEN 20 THEN 'Sales'
  5     WHEN 30 THEN 'Finance'
  6     ELSE 'Other'
  7  END department_name
  8  FROM employees where rownum <2;

FIRST_NAME                               LAST_NAME                                          DEPARTMENT_ID DEPARTMENT_NAME
---------------------------------------- -------------------------------------------------- ------------- --------------------
Steven                                   King                                                          90 Other

SQL>

※测试使用的Oracle提供的HR示例数据库。

以上SQL语句将查询employees表中每个员工的first_name、last_name以及对应的部门名称department_name。CASE表达式的作用是将每个部门ID转换为对应的部门名称。如果部门ID等于10，则使用Accounting作为部门名称，如果部门ID等于20，则使用Sales作为部门名称，如果部门ID等于30，则使用Finance作为部门名称，否则使用Other作为部门名称。

2. 搜索CASE表达式

搜索CASE表达式是指在给定的表达式上执行基于不等式的比较，如果表达式满足给定的条件，则执行相应操作。它的基本语法如下：

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   WHEN condition3 THEN result3
   ...
   ELSE default_result
END;

其中，condition1、condition2、condition3等表示需要比较的条件，result1、result2、result3等表示满足对应条件时的操作结果，default_result表示当expression不满足任何条件时的默认结果。

下面通过一个简单的例子来说明如何使用搜索CASE表达式：

--使用以下SQL语句创建测试表
CREATE TABLE student_scores (
  student_name VARCHAR2(100),
  score NUMBER
);

INSERT INTO student_scores VALUES ('Tom', 80);
INSERT INTO student_scores VALUES ('Jack', 92);
INSERT INTO student_scores VALUES ('Lucy', 65);
INSERT INTO student_scores VALUES ('Mike', 74);
INSERT INTO student_scores VALUES ('Mary', 55);
commit;

--运行这个PL/SQL语句
set serverout on
DECLARE
   grade CHAR(1);
BEGIN
   SELECT
      CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         WHEN score >= 60 THEN 'D'
         ELSE 'F'
      END INTO grade
   FROM student_scores
   WHERE student_name = 'Tom';
   
   DBMS_OUTPUT.PUT_LINE('Tom Grade is: ' || grade);
END;

以上PL/SQL代码将查询student_scores表中Tom的成绩，然后将其转换为字母成绩，并输出转换结果。如果Tom的成绩大于等于90分，则输出A，如果Tom的成绩在80~89分之间，则输出B，以此类推。

输出结果：

SQL> DECLARE
  2     grade CHAR(1);
  3  BEGIN
  4     SELECT
  5        CASE
  6           WHEN score >= 90 THEN 'A'
  7           WHEN score >= 80 THEN 'B'
  8           WHEN score >= 70 THEN 'C'
  9           WHEN score >= 60 THEN 'D'
 10           ELSE 'F'
 11        END INTO grade
 12     FROM student_scores
 13     WHERE student_name = 'Tom';
 14
 15     DBMS_OUTPUT.PUT_LINE('Tom Grade is: ' || grade);
 16  END;
 17  /
Tom Grade is: B

PL/SQL 过程已成功完成。

官方文档BOOLEAN表达式代码例

Example 2-50 Simple CASE Expression

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Fair'
      WHEN 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;
    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/

这段PL/SQL程序代码表示将一个字符类型的成绩grade转换为对应的评价appraisal。程序首先声明了两个变量：grade和appraisal。在声明grade变量时，将其初值赋为’B’。appraisal的类型为VARCHAR2(20)。然后程序使用了CASE语句，根据grade的值，依次将对应的评价赋值给appraisal。在使用CASE语句时，所有情况都不满足时候，输出’No such grade’。

由于grade的初值为’B’，因此在这个例子中，appraisal将被赋值为’Very Good’。程序使用DBMS_OUTPUT.PUT_LINE函数将评价结果输出到控制台上。

输出结果：

SQL> set serverout on
SQL> DECLARE
  2    grade CHAR(1) := 'B';
  3    appraisal VARCHAR2(20);
  4  BEGIN
  5    appraisal :=
  6      CASE grade
  7        WHEN 'A' THEN 'Excellent'
  8        WHEN 'B' THEN 'Very Good'
  9        WHEN 'C' THEN 'Good'
 10        WHEN 'D' THEN 'Fair'
 11        WHEN 'F' THEN 'Poor'
 12        ELSE 'No such grade'
 13      END;
 14      DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
 15  END;
 16  /
Grade B is Very Good

PL/SQL 过程已成功完成。

SQL>

Example 2-51 Simple CASE Expression with WHEN NULL

DECLARE
  grade CHAR(1); -- NULL by default
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
  CASE grade
    WHEN NULL THEN 'No grade assigned'
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Very Good'
    WHEN 'C' THEN 'Good'
    WHEN 'D' THEN 'Fair'
    WHEN 'F' THEN 'Poor'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/

这段PL/SQL程序代码表示将一个字符类型的成绩grade转换为对应的评价appraisal。程序首先声明了两个变量：grade和appraisal。grade的默认值为NULL，appraisal的类型为VARCHAR2(20)。然后程序使用了CASE语句，根据grade的值，依次将对应的评价赋值给appraisal。在CASE语句中需要使用WHEN NULL THEN的结构来处理grade值为NULL的情况。在程序中声明变量时，并不会为变量赋初值，因此在这段代码中，grade的初值为NULL。最后，程序使用DBMS_OUTPUT.PUT_LINE函数将评价结果输出到控制台上。

输出结果：

SQL> DECLARE
  2    grade CHAR(1); -- NULL by default
  3    appraisal VARCHAR2(20);
  4  BEGIN
  5    appraisal :=
  6    CASE grade
  7      WHEN NULL THEN 'No grade assigned'
  8      WHEN 'A' THEN 'Excellent'
  9      WHEN 'B' THEN 'Very Good'
 10      WHEN 'C' THEN 'Good'
 11      WHEN 'D' THEN 'Fair'
 12      WHEN 'F' THEN 'Poor'
 13      ELSE 'No such grade'
 14    END;
 15    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
 16  END;
 17  /
Grade  is No such grade

PL/SQL 过程已成功完成。

SQL>

Example 2-52 Searched CASE Expression

DECLARE
  grade      CHAR(1) := 'B';
  appraisal  VARCHAR2(120);
  id         NUMBER  := 8429862;
  attendance NUMBER := 150;
  min_days   CONSTANT NUMBER := 200;
  
  FUNCTION attends_this_school (id NUMBER)
    RETURN BOOLEAN IS
  BEGIN
    RETURN TRUE;
  END;
BEGIN
  appraisal :=
  CASE
    WHEN attends_this_school(id) = FALSE
      THEN 'Student not enrolled'
    WHEN grade = 'F' OR attendance < min_days
      THEN 'Poor (poor performance or bad attendance)'
    WHEN grade = 'A' THEN 'Excellent'
    WHEN grade = 'B' THEN 'Very Good'
    WHEN grade = 'C' THEN 'Good'
    WHEN grade = 'D' THEN 'Fair'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE
    ('Result for student ' || id || ' is ' || appraisal);
END;
/

这段PL/SQL程序代码包含了一些函数和常量。

程序首先声明了五个变量：grade、appraisal、id、attendance和min_days。 grade和appraisal的作用和之前一样，分别表示学生的成绩和对应的评价。id表示学生的学号，attendance表示学生的出勤天数，min_days是一个常量，表示学生应该出席的最小天数。

程序还声明了一个名为attends_this_school的函数。这个函数的作用是判断一个给定的学号是否存在于学校的数据库中。在本例中，它仅仅返回了TRUE，因为我们假设所有的学号都是存在的。

然后程序使用了CASE语句，根据学生的成绩和出勤情况来判断对应的评价。如果学生没有在学校数据库中，则评价为"Student not enrolled"。如果学生的成绩为F，或者出勤天数不足min_days，则评价为"Poor (poor performance or bad attendance)“。否则，根据学生成绩的不同，评价为"Excellent”、“Very Good”、“Good"或者"Fair”。

程序使用DBMS_OUTPUT.PUT_LINE函数将评价结果输出到控制台上，并包含了学生的学号。

输出结果：

SQL> DECLARE
  2    grade      CHAR(1) := 'B';
  3    appraisal  VARCHAR2(120);
  4    id         NUMBER  := 8429862;
  5    attendance NUMBER := 150;
  6    min_days   CONSTANT NUMBER := 200;
  7
  8    FUNCTION attends_this_school (id NUMBER)
  9      RETURN BOOLEAN IS
 10    BEGIN
 11      RETURN TRUE;
 12    END;
 13  BEGIN
 14    appraisal :=
 15    CASE
 16      WHEN attends_this_school(id) = FALSE
 17        THEN 'Student not enrolled'
 18      WHEN grade = 'F' OR attendance < min_days
 19        THEN 'Poor (poor performance or bad attendance)'
 20      WHEN grade = 'A' THEN 'Excellent'
 21      WHEN grade = 'B' THEN 'Very Good'
 22      WHEN grade = 'C' THEN 'Good'
 23      WHEN grade = 'D' THEN 'Fair'
 24      ELSE 'No such grade'
 25    END;
 26    DBMS_OUTPUT.PUT_LINE
 27      ('Result for student ' || id || ' is ' || appraisal);
 28  END;
 29  /
Result for student 8429862 is Poor (poor performance or bad attendance)

PL/SQL 过程已成功完成。

SQL>