Oracle分支控制语句(函数)总结

最新推荐文章于 2024-03-31 06:04:38 发布

Master_Shifu_

最新推荐文章于 2024-03-31 06:04:38 发布

阅读量3.3k

点赞数

分类专栏： Sql基础及优化文章标签： Oracle分支控制语句(函数)

本文链接：https://blog.csdn.net/Master_Shifu_/article/details/87903179

版权

Sql基础及优化专栏收录该内容

14 篇文章 1 订阅

订阅专栏

1 前言:

在显示业务中经常会有如果某一个值是 A的情况下执行A条件的查询sql,当这个值是B的时候执行B条件下的查询sql,这样的场景就不得不用到分支控制语句(函数),

第一部分 : 三种分支函数基础方法介绍
第二部分 : 使用分支函数进行行转列
第三部分 : 使用分支函数减少统计时查询表消耗

if-then-else
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
case when

注:WITH t1 AS是创建子查询,相当于创建视图

第一部分 : 三种分支函数基础方法介绍

2 if-then-else

IF-THEN语句的序列之后的ELSE语句的可选序列，ELSE语句块在IF条件为FALSE时执行。语法IF-THEN-ELSE语句的语法是 -

IF condition THEN 
   S1;  
ELSE  
   S2; 
END IF;

其中，S1和S2是不同的语句序列。在IF-THEN-ELSE语句中，当测试条件 condition 为TRUE时，执行语句S1并跳过S2; 当测试条件 condition 为FALSE时，则跨过S1并执行语句S2中的语句块。例如 -

IF color = red THEN 
  'You have chosen a red car'
ELSE 
  Please choose a color for your car'; 
END IF;

因为if-then-else是一个二维的分支控制语句,有其局限性,所以一般使用这个语句做分支控制还是比较少的

3 decode()

decode (条件, 值1, 返回值1, 值2, 返回值2,…值n,返回值n,缺省值)
eg : decode(code , ‘001’ , code, ‘002’ , NAME, salary)
解析 :
当code 等于’001’ 返回code的值,当code 等于’002’ 返回NAME的值,没有等于的值则返回salary

3.1含义解释：

当字段或字段的运算的值等于值1时，该函数返回返回值1，当字段或字段的运算的值等于值2时，该函数返回返回值2 … 如果所有值都没有匹配上则返回默认的缺省值.
当然值1，值2，值3也可以是表达式，这个函数使得分支控制语句简单了许多

执行SQL : 
WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME,
        500 AS salary FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME,
        1000 AS salary FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME,
        300 AS salary FROM dual
)
SELECT 
decode(code , '001' , code,
              '002' , NAME, 
              salary) AS obj
from t1

在这里插入图片描述

3.2 解析 :

当code 等于’001’ 返回t1表中code的值,当code 等于’002’ 返回t1表中NAME的值,没有等于的值则返回t1表中salary

4 case when

4.1 Case具有两种格式。简单Case函数和Case搜索函数。**

    --简单Case函数
    CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他' END
     
   --Case搜索函数 
    CASE WHEN sex = '1' THEN '男' 
    WHEN sex = '2' THEN '女' 
    ELSE '其他' END

4.2 以下解析搜索函数

搜索函数 执行SQL:
  WITH t1 AS 
(
 SELECT 
        '1' as objId,
        '001' AS code,
        'aa' AS NAME,
        500 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '002' AS code,
        'bb' AS NAME,
        1000 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '003' AS code,
        'cc' AS NAME,
        300 AS salary FROM dual
)
SELECT 
(case when code = '001'  then objId 
     when code = '002'  then NAME
     else (salary || '') end
)
as obj
FROM t1

解析 :
当code = ‘001’ 取t1表中 objId的值,当 code = ‘002’ 取t1表中NAME的值,如果都没有匹配到值则取,注意else后面的 || ‘’ ,case后的其他类型都是字符串类型,salary是number类型,如果不拼上字符串,该语句会报错,说明case when相对于decode()有严格的类型控制

第二部分 :使用分支函数进行行转列

5 为什么要使用行转列

存储的数据是一对多数据,比如在某宝上买了一个商品,评价的时候有商品评价评分,满意度评价评分,物流评分和一个语言评价四个维度,那在某个表中肯定是一个相同的商品id存储了对应的四份数据,

而对于统计或者数据导出的时候我只是一条订单只关心他的商品评价多少分,满意度多少分,物流多少分,评语,那么同一个商品id的四条行数据,我如何只取一行中的一个或者几个字段组成新的列呢?

这种情况就需要用到行转列

5.1 使用case when进行行转列

执行sql:
WITH t1 AS 
(
 SELECT 
        '1' as objId,
        '001' AS code,
        'aa' AS NAME,
        500 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '002' AS code,
        'bb' AS NAME,
        1000 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '003' AS code,
        'cc' AS NAME,
        300 AS salary FROM dual
)
SELECT 
max(case when code = '001'  then objId  ELSE '' end) as objId,
max(case when code = '001'  then code  ELSE '' end) as code,
max(case when code = '002'  then NAME  ELSE '' end) as NAME,
max(case when code = '003'  then salary  ELSE 0 end) as salary
FROM t1

在这里插入图片描述

5.2 解析

这里怎么看出是行转列呢?注意每一行前的max()函数,把上面SQL的每一行的max函数去掉在执行会得到如下结果,可以看出每一行都有一个目标值,除了目标值之外的其他都是null值,而使用max()可以自然的过滤出每次我们要的非空目标值,从而将三行数据合并为一行数据,当然,有兴趣的同学可以使用decode()实现一下
在这里插入图片描述

第三部分 :使用分支函数减少统计时查询表消耗

6 使用分支函数减少统计时查询表消耗

6.1 要求 : 现有如下数据,需要统计出其中最大的 code值和所有的数据的salary总和

在这里插入图片描述

数据 SQL:
WITH t1 AS 
(
 SELECT 
        '1' as objId,
        '001' AS code,
        'aa' AS NAME,
        500 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '002' AS code,
        'bb' AS NAME,
        1000 AS salary FROM dual 
UNION ALL
 SELECT '1' as objId,
        '003' AS code,
        'cc' AS NAME,
        300 AS salary FROM dual
)
select * from t1

6.2正常的查询逻辑SQl

正常的查询sql就是查两次t1表,然后使用 Union All进行数据的组合或者在外层包一层 Select * From 查询语句进行数据组合,如下面的sql就是包一层select 查询语句进行数据组合.但是这样有一个问题,就是有一个统计函数(如MAX(),SUM()等需要全表扫描的函数)就需要全表扫描一次,那假如一个表数据达到10万条,有5个统计函数,那么就需要全表查询50万次,那么这这么一条语句的查询消耗就太高了

正常的查询逻辑 执行SQL:
    WITH t1 AS 
    (
     SELECT 
            '1' as objId,
            '001' AS code,
            'aa' AS NAME,
            500 AS salary FROM dual 
    UNION ALL
     SELECT '1' as objId,
            '002' AS code,
            'bb' AS NAME,
            1000 AS salary FROM dual 
    UNION ALL
     SELECT '1' as objId,
            '003' AS code,
            'cc' AS NAME,
            300 AS salary FROM dual
    )
    (
    select  
      (select sum(salary) from t1)  as salary,
      (select max(code) from t1) as code 
    from 
    dual
    )

在这里插入图片描述

6.2使用case when的查询逻辑SQl

执行SQL: 
    WITH t1 AS 
    (
     SELECT 
            '1' as objId,
            '001' AS code,
            'aa' AS NAME,
            500 AS salary FROM dual 
    UNION ALL
     SELECT '1' as objId,
            '002' AS code,
            'bb' AS NAME,
            1000 AS salary FROM dual 
    UNION ALL
     SELECT '1' as objId,
            '003' AS code,
            'cc' AS NAME,
            300 AS salary FROM dual
    )
    SELECT 
    objId,
    sum(case when 1=1  then salary  ELSE 0 end) as salary,
    MAX(case when 1=1  then code  ELSE '' end) as code
    FROM t1
    group BY 
    objId

在这里插入图片描述

6.3 结论

可以看出,使用case when在一次查表数据之后就把每一次查询的一条数据分别匹配每一个统计函数,所以不管之后有多少统计函数,查询的数量永远也只是全表扫描一次,当然,有兴趣的同学可以使用decode()实现一下

7 扩展使用 WITH XX AS 定义子查询部分减少统计时查询表消耗

7.1 WITH AS的含义

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

7.2 这个with XX as 是不是感觉很眼熟

没错,就是之前使用的在没有任何建表语句,但是有表数据的 WITH t1 AS ,

只要记住需要在后面的查询中需要频繁用到的需要查询的数据可以统一用 WITH t1 AS 查出,然后后面的查询使用 from t1的数据,那么所有的查询数据就可以共享,而不要频繁进行查表操作
这里给一个使用 WITH t1 AS 组合 decode()实现的减少查询消耗的例子,
具体的其他的 WITH XX AS使用方法请看这 SQL With As 用法

WITH t1 AS  组合 decode()
执行SQL : 
WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME,
        500 AS salary FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME,
        1000 AS salary FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME,
        300 AS salary FROM dual
)

SELECT 
decode('' , '' , (SELECT sum(salary) FROM t1)) AS salary,
decode('' , '' , (SELECT max(code) FROM t1)) AS code,
(SELECT '2'  FROM dual )AS status
from dual

在这里插入图片描述

7.2 结论

因为with XX as 的使用,所以我多次在decode()中多次使用 from t1是没关系的,因为需要统计的数据已经全部查到了t1表中,而这样也同样实现了 统计出其中最大的 code值和所有的数据的salary总和 的要求

参考文章:
PL/SQL IF-THEN-ELSE语句
 Oracle 中 decode 函数用法
 CASE WHEN 及 SELECT CASE WHEN的用法

Master_Shifu_

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
Oracle分支控制语句(函数)总结

1 前言:在显示业务中经常会有如果某一个值是 A的情况下执行A条件的查询sql,当这个值是B的时候执行B条件下的查询sql,这样的场景就不得不用到分支控制语句(函数),第一部分 : 三种分支函数基础方法介绍第二部分 : 使用分支函数进行行转列第三部分 : 使用分支函数减少统计时查询表消耗if-then-elsedecode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺...
复制链接

扫一扫