编者按：
长期坚持做完一件小事也是不容易的
【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)
系列文章目录
Oracle PL/SQL例2：处理查询的结果行（基础循环）
Oracle PL/SQL例4：文字（Literals）换行的处理
Oracle PL/SQL例6：声明变量/常量
使用PL/SQL发邮件相关的Oracle ACL (Access Control List)
文章目录
前言
Oracle PL/SQL基础语法学习16：CASE Expression（CASE表达式）
CASE Expression（CASE表达式）介绍
1. 简单CASE表达式
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
WHEN value3 THEN result3
...
ELSE default_result
END;复制
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>复制
2. 搜索CASE表达式
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
...
ELSE default_result
END;复制
--使用以下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;复制
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;
/
复制
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;
/复制
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;
/
复制
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>复制
参考连接
总结
文章转载自SQL和数据库技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
