《Oracle 入门教程》第 09 篇 CASE 表达式

Oracle 中的 CASE 表达式可以根据不同条件产生不同的结果,实现类似于编程语言中的 IF-THEN-ELSE 逻辑功能。例如,根据员工的 KPI 计算相应的涨薪幅度,根据考试成绩评出优秀、良好、及格等。

COALESCE 和 NULLIF 函数是两个简写形式的 CASE 表达式。另外,Oracle 还提供了专有的 NVL、NVL2 以及 DECODE 函数。

9.1 CASE 表达式

CASE 表达式支持两种形式:简单 CASE 表达式和搜索 CASE 表达式。

9.1.1 简单 CASE 表达式

简单 CASE 表达式的语法如下:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

表达式的计算过程如下图所示:

simple case
首先计算 expression 的值;然后依次与 WHEN 列表中的值(value1,value2,…)进行比较,找到第一个相等的值并返回对应的结果(result1,result2,…);如果没有找到相等的值,返回 ELSE 中的默认结果;如果此时没有指定 ELSE,返回 NULL 值。

以下语句使用简单 CASE 表达式将员工的部门编号显示为相应的部门名称:

SELECT emp_name AS "员工姓名",
       CASE dept_id
         WHEN 1 THEN '行政管理部'
         WHEN 2 THEN '人力资源部'
         WHEN 3 THEN '财务部'
         WHEN 4 THEN '研发部'
         WHEN 5 THEN '销售部'
         WHEN 6 THEN '保卫部'
         ELSE '其他部门'
       END AS "所在部门"
FROM employee;
员工姓名|所在部门 |
------|--------|
刘备   |行政管理部|
关羽   |行政管理部|
张飞   |行政管理部|
...
邓芝   |销售部   |
简雍   |销售部   |
孙乾   |销售部   |

首先,判断部门编号是否等于 1,等于就显示为“行政管理部”;否则,如果部门编号等于 2, 显示为“人力资源部”;依次类推;如果部门编号不等于 1 到 6 中的任何值,显示为“其他部门”。

CASE 表达式的一个常见应用就是实现表的行列转换。创建以下学生成绩表:

-- 创建成绩表 t_case,sname 为学生姓名,cname 为课程名称,score 为考试成绩
CREATE TABLE t_case(sname varchar(10), cname varchar(10), score int);

-- 插入测试数据
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '语文', 80);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '语文', 77);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '语文', 91);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '数学', 85);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '数学', 90);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '数学', 60);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '英语', 81);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '英语', 69);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '英语', 82);

该表中的数据如下:

SELECT *
FROM t_case;
sname |cname  |score|
------|-------|-----|
张三   |语文   |   80|
李四   |语文   |   77|
王五   |语文   |   91|
张三   |数学   |   85|
李四   |数学   |   90|
王五   |数学   |   60|
张三   |英语   |   81|
李四   |英语   |   69|
王五   |英语   |   82|

接下来我们利用 CASE 表达式将其转换为按列显示的形式:

SELECT sname,
       sum(CASE cname WHEN '语文' THEN score ELSE 0 END) AS "语文",
       sum(CASE cname WHEN '数学' THEN score ELSE 0 END) AS "数学",
       sum(CASE cname WHEN '英语' THEN score ELSE 0 END) AS "英语"
FROM t_case
GROUP BY sname;
sname |语文|数学|英语|
------|---|---|---|
张三   | 80| 85| 81|
李四   | 77| 90| 69|
王五   | 91| 60| 82|

第一个 CASE 表达式用于获取学生的语文成绩,cname 等于“语文”就返回考试成绩,不是“语文”就记为 0 分;第二个和第三个 CASE 表达式分别用于获取数学和英语成绩。然后,使用 SUM 汇总函数和 GROUP BY 分组操作将每个学生的成绩合并成一条记录。

简单 CASE 表达式使用的是等值比较(=),只能处理简单的逻辑。如果想要进行复杂的逻辑处理,例如根据考试成绩评出优秀、良好、及格等,或者判断表达式的值是否为空,就需要使用更加强大的搜索 CASE 表达式。

9.1.2 搜索 CASE 表达式

搜索 CASE 表达式的语法如下:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE default_result]
END

表达式的计算过程如下图所示:

search case
按照顺序依次计算每个分支中的条件(condition1,condition2,…),找到第一个结果为真的分支并返回相应的结果(result1,result2,…);如果没有任何条件为真,返回 ELSE 中的默认结果;如果此时没有指定 ELSE,返回 NULL 值。

所有的简单 CASE 表达式都可以替换为等价的搜索 CASE 表达式。我们可以将上一节的示例改写如下:

SELECT emp_name AS "员工姓名",
       CASE
         WHEN dept_id = 1 THEN '行政管理部'
         WHEN dept_id = 2 THEN '人力资源部'
         WHEN dept_id = 3 THEN '财务部'
         WHEN dept_id = 4 THEN '研发部'
         WHEN dept_id = 5 THEN '销售部'
         WHEN dept_id = 6 THEN '保卫部'
         ELSE '其他部门'
       END AS "所在部门"
FROM employee;

首先,判断部门编号等于 1 是否成立(为真),成立就显示为“行政管理部”;否则,判断部门编号等于 2 是否成立, 成立就显示为“人力资源部”;依次类推;如果部门编号不等于 1 到 6 中的任何值,显示为“其他部门”。

以下查询按照考试分数对成绩进行评价:

SELECT sname AS "学生姓名",
       cname AS "学科名称",
       CASE
         WHEN score >= 90 THEN '优秀'
         WHEN score >= 80 THEN '良好'
         WHEN score >= 70 THEN '中等'
         WHEN score >= 60 THEN '及格'
         ELSE '不及格'
       END AS "考试成绩"
FROM t_case;
学生姓名|学科名称|考试成绩|
-------|------|-------|
张三   |语文   |良好    |
李四   |语文   |中等    |
王五   |语文   |优秀    |
张三   |数学   |良好    |
李四   |数学   |优秀    |
王五   |数学   |及格    |
张三   |英语   |良好    |
李四   |英语   |及格    |
王五   |英语   |良好    |

CASE 表达式除了可以用于 SELECT 列表,也可以出现在其他子句中,例如 WHERE、GROUP BY、ORDER BY 等。以下语句使用 CASE 表达式实现了自定义的排序规则:

SELECT emp_name,
       CASE emp_name
         WHEN '刘备' THEN 1
         WHEN '关羽' THEN 2
         WHEN '张飞' THEN 3
         ELSE 99
       END AS num
FROM employee
ORDER BY CASE emp_name
           WHEN '刘备' THEN 1
           WHEN '关羽' THEN 2
           WHEN '张飞' THEN 3
           ELSE 99
         END;
EMP_NAME|NUM|
--------+---+
刘备     |  1|
关羽     |  2|
张飞     |  3|
孙丫鬟   | 99|
...
关平     | 99|

我们通过 CASE 表达式将“刘备”编号为 1,“关羽”编号为 2,“张飞”编号为 3,其他人员编号为 99。

9.2 COALESCE 函数

COALESCE(expr1, …) 函数返回参数中第一个非空的值,它的等价 CASE 表达式如下:

CASE
  WHEN expr1 IS NOT NULL THEN expr1
  WHEN expr2 IS NOT NULL THEN expr2
  WHEN expr3 IS NOT NULL THEN expr3
  ...
END

例如:

SELECT coalesce(NULL, NULL, 1), coalesce(NULL, NULL, NULL)
FROM dual;
COALESCE(NULL,NULL,1)|COALESCE(NULL,NULL,NULL)|
---------------------+------------------------+
                    1|                        |

9.3 NULLIF 函数

NULLIF(expr1,expr2) 函数的处理逻辑如下:如果表达式 expr1 和 expr2 相等,返回 NULL;否则,返回 expr1 的值。NULLIF 函数可以使用等价的 CASE 表达式进行表示:

CASE
  WHEN expr1 = expr2 THEN NULL
  ELSE expr1
END

NULLIF 函数的一个常见用途是防止除零错误:

-- 除零错误
SELECT 1 / 0
FROM dual;
SQL 错误 [1476] [22012]: ORA-01476: 除数为 0

SELECT 1 / NULLIF(0 , 0)
FROM dual;
1/NULLIF(0,0)|
-------------+
             |

第一个语句会产生除零错误,第二个语句返回 NULL。

9.4 NVL 函数

NVL(expr1, expr2) 函数,它用于返回第一个非空的参数值,相当于只有两个参数的 COALESCE 函数。

以下查询返回了员工的总收入:

SELECT emp_name,
       salary*12 + NVL(bonus, 0) AS "年收入"
FROM employee;
EMP_NAME|年收入 |
--------+------+
刘备     |370000|
关羽     |322000|
张飞     |298000|
诸葛亮   |296000|
黄忠     | 96000|
魏延     | 90000|
...

9.5 NVL2 函数

NVL2(expr1, expr2, expr3) 函数包含三个参数,如果第一个参数不为空,返回第二个参数的值;否则,返回第三个参数的值。以下查询同样返回员工的总收入:

SELECT emp_name,
       NVL2(bonus, salary*12 + bonus, salary*12) AS "年收入"
FROM employee;

9.6 DECODE 函数

DECODE 函数可以实现类似于简单 CASE 表达式的功能:

DECODE(expression, value1, result1, value2, result2, ...[, default_result ])

该函数依次比较表达式 expression 与 valueN 的值,如果找到相等的值就返回对应的 resultN;如果没有匹配到任何相等的值,返回默认结果 default_result;如果此时没有提供 default_result,返回 NULL 值。

以下语句利用 DECODE 函数将员工的部门编号显示为相应的名称:

SELECT emp_name,
       DECODE(dept_id, 1, '行政管理部',
                       2, '人力资源部',
                       3 ,'财务部',
                       4, '研发部',
                       5, '销售部',
                       6, '保卫部',
                          '其他部门') AS department
FROM employee;

该查询的结果与前文中的简单 CASE 表达式示例相同。DECODE 是 Oracle 专有函数,推荐大家使用标准的 CASE 表达式。

  • 5
    点赞
  • 23
    收藏
  • 打赏
    打赏
  • 2
    评论
【精华】Oracle从入门到精通pdf高清版: 清原版文档,非扫描件 一、SQL................................................... 1.1、基本概念:........................................... 1.2、数据库安全:......................................... 1.3、基本的SQL SELECT 语句................................ 1.4、SELECT语句........................................... 1、语法:................................................. 2、SQL语句说明:.......................................... 3、数字和日期都可以使用数学运算符建立表达式。............. 4、定义空(NULL)值....................................... 5、别名................................................... 6、spool +路径............................................ 7、连接操作符: || ....................................... 8、文本字符串............................................. 9、DISTINCT .............................................. 1.5、SQLPLUS 与 SQL 的关系................................ 1、SQLPLUS命令的功能: ................................... 2、查询 SQLPLUS 命令...................................... 3、SQLPLUSW 在 WINDOWS 下运行的分析器。................... 4、SQLPLUS 命令: ........................................ 1.6、单行函数............................................. 1、character字符类型函数: ............................... 2、number数字类型函数..................................... 3、时间类型函数:(date) ................................ 1.7、嵌套函数:........................................... 1. 通用函数:............................................. 2. 条件表达式:........................................... 3. 从多表中显示数据: .................................... 1.8、用字函数产生的总计................................... 1.9、子查询:............................................. 2.0、替换变量:........................................... 1.&....................................................... 2.&&...................................................... 2.1.环境变量:............................................ 2.2 格式化命令:.......................................... 2.3 做脚本文件的过程:.................................... 2.3 数据操作语句:........................................ 1. 插入................................................... 2. 删除................................................... 3. 更新................................................... 4. MERGE语句.............................................. 5. 事务(transaction): ................................. 2.4 创建和管理表.......................................... 1、表(TABLE)基本的存储单位,由行和列组成。.............. 2、方案:一个用户所有对象的命名集合。..................... 3、CTAS(子查询建表):................................... 4、截取:................................................. 资源来自网络,仅供学习! Oracle 从入门到精通 第 2 页,共 106 页 5、给表加注释:COMMENT ................................... 6、约束条件:............................................. 2.5.视图 (VIEW) ........................................ 2.6、序列:............................................... 2.7、索引:............................................... 2.8 控制用户的访问........................................ 1.数据库的安全性.......................................... 2.角色:.................................................. 3.使用集合操作............................................ 4.ORDER BY 子句:......................................... 5.GROUP BY 子句的增强..................................... 6.GROUPING 函数........................................... 2.9 高级子查询............................................ 1. 成对子查询:........................................... 2.层次查询................................................ 二、Management:.......................................... 1.Oracle的构件和组件...................................... 2.数据库的物理结构:...................................... 1.控制文件................................................ 2. 数据文件............................................... 3. 重做日志文件........................................... 4. data file 数据文件: .................................. 5. 作用:存放数据。....................................... 6. 数据文件大小可以扩展。 ................................ 7. tablespace 表空间:一个或多个数据文件的逻辑组成。 ..... 8. redo log file 重做日志文件............................. 9. control file 控制文件 ................................. 10. parameter file 初始化参数文件......................... 11. password file 口令文件................................ 12. archived log file 归档日志文件........................ 3.instance 实例/例程...................................... 4、进程结构............................................... 1. 用户进程:开始于数据库用户请求连接数据库 .............. 2. 服务进程:与ORA实例连接,开始于用户会话的建立。........ 3. 后台进程:当ORA实例启动时启动.......................... 1. DBWR 数据库写进程...................................... 2. LGWR 重作日志写进程.................................... 6.CKPT 检查点进程......................................... 7.ARCn 归档进程(可选) .................................. 8.LOGICAL STRUCTURE 逻辑结构 ............................. 5、OEM ORACLE 企业管理器.................................. 6.管理ORA实例............................................. 7.启动过程:.............................................. 1. NOMOUNT 实例启动阶段................................... 2. MOUNT 数据库装载阶段................................... 3. OPEN 打开数据库........................................ 资源来自网络,仅供学习! Oracle 从入门到精通 第 3 页,共 106 页 8.启动命令:.............................................. 1. 在关闭状态下执行....................................... 2. 切换命令:不能跳级切换................................. 3. 关闭过程与启动逆向; .................................. 9.监视诊断文件:.......................................... 10.BACKGROUND TRACE FILES 后台进程跟踪文件................ 11.user TRACE FILES 用户跟踪文件.......................... 12.创建数据库............................................. 1. 创建前的准备:......................................... 2. 创建方法:............................................. 13、UNIX 操作系统环境变量................................. 14、手动创建数据库........................................ 15、使用数据字典和动态性能视图............................ 1. 数据字典............................................... 2. 数据字典的分类:....................................... 3. 动态性能表:........................................... 16、维护重做日志文件...................................... 17、管理表空间和数据文件.................................. 18、表空间的空间管理(区的管理): ....................... 1、本地管理:............................................. 2、数据字典管理表空间: .................................. 3、存储参数:............................................. 4、表空间状态:........................................... 5、查看表空间信息:....................................... 6、重定义表空间的大小..................................... 7、操作表空间:........................................... 8、移动数据文件:......................................... 9、删除表空间:........................................... 19、存储结构和关系........................................ 1、段类型:............................................... 2、区:................................................... 3、数据库块............................................... 4、9I提供非标准块......................................... 5、标准块大小............................................. 6、非标准块的大小......................................... 7、数据块的内容:......................................... 8、块的空间利用参数:..................................... 9、数据块管理:........................................... 10、管理回滚段(Undo)的数据.............................. 20、Undo段的类型:........................................ 1.NON-SYSTEM类型:........................................ 2.SYSTEM类型:............................................ 3.自动UNDO段管理的其他参数: ............................. 21、管理表................................................ 1.创建表提示:............................................ 2.创建临时表.............................................. 资源来自网络,仅供学习! Oracle 从入门到精通 第 4 页,共 106 页 3.修改存储参数和块空间利用参数: ......................... 4.手动分配区:............................................ 5.非分区表的重组.......................................... 6.删除列:................................................ 7.重命名表中的一列:...................................... 8.标记列不再使用:........................................ 9.删除不使用的列:........................................ 10.继续列的删除操作:..................................... 11.得到表的信息:......................................... 22、管理索引(index) .................................... 1.索引的分类:............................................ 2.索引结构:.............................................. 3.存储参数:.............................................. 4.创建B-TREE索引:........................................ 5.索引PCTFREE的变化: .................................... 6.创建索引的提示:........................................ 7.创建位图索引:.......................................... 8.改变索引参数:........................................... 9.重建索引:.............................................. 10.在线重建索引:(建议不使用) .......................... 11.合并索引:............................................. 12.删除索引:............................................. 13.确定未使用的索引:..................................... 14.查看索引信息:......................................... 23、管理口令安全和资源.................................... 1.口令帐户锁定:.......................................... 2.自动锁定,可以手动解锁.................................. 3.口令的到期和过期:...................................... 4.口令历史:.............................................. 5.口令的校验:............................................ 6.用户提供的校验函数:.................................... 7.口令校验函数:.......................................... 8.创建profile口令设置: .................................. 9.修改 profile :口令设置................................. 10.删除 profile: 口令设置................................ 24、资源管理:............................................ 1.启动资源限制通过:...................................... 2.会话级参数:............................................ 3.调用级参数:............................................ 4.创建profile:资源配制................................... 5.查看:.................................................. 24、管理用户.............................................. 1. 用户:................................................. 2.数据库的方案:.......................................... 3.创建用户的步骤:........................................ 4.创建一个新的用户:数据库认证............................ 资源来自网络,仅供学习! Oracle 从入门到精通 第 5 页,共 106 页 5.改变用户的表空间配额: ................................. 6.删除用户:.............................................. 7.查看:.................................................. 25、管理权限.............................................. 1. 两种用户权限:......................................... 2.系统权限:.............................................. 3.授予系统权限:.......................................... 4.授予对象权限:.......................................... 5.移除系统权限:.......................................... 7.移除对象权限:.......................................... 8.查看:.................................................. 26、管理角色.............................................. 1.创建角色:.............................................. 2.赋予角色权限:.......................................... 3.将角色赋予用户;........................................ 4.设置用户的默认角色在需要的时候启用或禁用角色; ......... 5.移除角色;.............................................. 6.删除角色;.............................................. 7.预定义角色:............................................ 8.查看:.................................................. 27、使用全球化支持........................................ 28、基本的ORA网络服务器端配置............................. 三、PL/SQL................................................0 1、创建PL/SQL语句的过程:................................. 2、PL/SQL中的SQL语句...................................... 1. 查询语句:可以直接使用,语法和规则有改变。 ............ 2. 循环控制:............................................. 3. index by tables 中的方法: ............................ 4. SQL Cursor ............................................ 5.FOR循环的游标使用:..................................... 6.带参数的游标:.......................................... 7.异常处理................................................ 8.预定义异常:............................................ 9.非预定义异常:.......................................... 3、函数:................................................. 4、存储程序单元........................................... 5、管理PL/SQL程序块: .................................... 6、包(package) ......................................... 1.组成:.................................................. 2.构建没有包头的包:...................................... 3.SQL中使用包函数的限制................................... 4.与开发相关的系统包:.................................... 7、触发器................................................. 1.语句级:................................................ 2.行级触发器:............................................ 3.INSTEAD OF TRIGGER:替换类型触发器...................... 资源来自网络,仅供学习! Oracle 从入门到精通 第 6 页,共 106 页 4.DDL触发器:............................................. 5.系统事件触发器:........................................ 8、审计................................................... 9、数据同步:............................................. 四、backup and recover备份与恢复.......................... 1、备份与恢复概论:....................................... 2、定义一个备份、恢复策略: .............................. 3、数据库的同步:.......................................... 4、数据库的备份........................................... 1.物理备份与逻辑备份:.................................... 2.数据库的恢复............................................ 4、ARCHIVELOG模式下的不完全恢复: ........................

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:酷酷鲨 设计师:CSDN官方博客 返回首页
评论 2

打赏作者

不剪发的Tony老师

为 5 个 C 币而折腰。

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值