浅谈Oracle之SQL中的函数与表达式

发表于 | 分类于
| 本文字数： 2.1k

针对 Oracle，本文会谈一谈，在 sql 中常会使用到的函数和表达式，适当的使用它们，可以提高开发效率和质量。

本文示例均使用 oracle 自带的示例表。

涉及一览 说明
CASE... WHEN... 类似于 IF/ELSE 。
WITH... AS 相当于创建临时表，适用于需要重复处理一类数据
NVL() / NVL2() NULL 处理，此时类似 IF/ELSE 。
DECODE() 值处理，此时类似 IF/ELSE 。
TRUNC() 时间截取函数。
TO_CHAR() / TO_DATE() 日期类型和字符串类型转换。
ROW_NUMBER() OVER 组内排序，并给出排序的编号。
LISTAGG(...) WITHIN GROUP(...) 列值拼接，一般配合分组使用。
START WITH...CONNECT BY PRIOR... 对树结构递归查询。

常用函数和表达式

CASE... WHEN...

类似于 IF/ELSE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 方式一
select 
     empno as 工号,
     (CASE ename
        WHEN 'SMITH' THEN '史密斯'
        WHEN 'ALLEN' THEN '艾伦'
        ELSE ename
      END) as 姓名
from emp;

-- 方式二
select 
     empno as 工号,
     (CASE 
        WHEN ename='SMITH' THEN '史密斯'
        WHEN ename='ALLEN' THEN '艾伦'
        ELSE ename
      END) as 姓名
from emp;

WITH... AS

相当于创建临时表，当需要重复处理一类数据时，可以起到优化作用。

1
2
3
4
5
6
7
8
with
    a as (select * from emp where hiredate > date'1981-01-01' and job = 'MANAGER'),
    b as (select * from emp where hiredate < date'1981-06-01' and job = 'SALESMAN')
select * from a where ename like '%A%' and ename not like '%B%'
union all
select * from a where ename like '%B%' and ename not like '%A%'
union all
select * from b

DECODE()

值处理，可以达到 IF/ELSE 的作用。

DECODE(value, if1, then1, if2,then2, . . . else ) 

1
2
3
4
select 
     empno as 工号,
     DECODE(ename, 'SMITH', '史密斯', 'ALLEN', '艾伦', ename) as 姓名
from emp;

NVL()

NULL 处理，可以达到 IF/ELSE 的作用。

示例 说明
NVL(v1, v2) 若v1为null时，取v2, 否则取本身v1
NVL2(v1, v2, v3) 若v1为null时，取v3, 否则取v2

TRUNC()

类似截取函数，按照指定的格式截取输入的数据。

1
2
-- date 时间格式，fmt 日期格式。
语法格式：trunc(date [, 'fmt'])

SYSDATE ：系统时间。

示例 2018/3/19 14:31:13 结果 说明
select trunc(sysdate, 'dd') from dual 2018/03/20 返回当前时间：天
select trunc(sysdate, 'hh24') from dual 2018/3/20 14:00:00 返回当前时间：小时
select trunc(sysdate) from dual 2018/03/20 返回当前时间
select trunc(sysdate, 'yy') from dual 2018/01/01 返回当年第一天
select trunc(sysdate, 'mm') from dual 2018/03/01 返回当月的第一天
select trunc(sysdate, 'd') from dual 2018/03/19 返回当前信息的第一天
select trunc(sysdate, 'mi') from dual 2018/3/19 14:31:00 返回当前时间：分钟

TO_CHAR() / TO_DATE()

这里仅说明 日期类型 和 字符串类型 的相互转换。

示例 2018/3/19 14:31:13 结果
select to_char( sysdate, 'YYYY-MM-DD') from dual 2018/3/19
select to_date('2018/3/19', 'YYYY-MM-DD') from dual 2018/3/19 
1
2
-- sql 调试时，可以直接使用以下方式。
select date'2018-03-19' from dual;

ROW_NUMBER() OVER

1
2
-- 将表按照a字段进行分组，之后按照b字段进行组内排序，并给出排序的编号。
ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)

根据工作进行分组，并按薪资进行降序排名。

1
2
3
select empno,ename,job,sal,
   ROW_NUMBER() OVER(PARTITION BY job order by sal desc) as job_rank
from emp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
     EMPNO ENAME      JOB              SAL   JOB_RANK
---------- ---------- --------- ---------- ----------
      7902 FORD       ANALYST         3000          1
      7788 SCOTT      ANALYST         3000          2
      7934 MILLER     CLERK           1300          1
      7876 ADAMS      CLERK           1100          2
      7900 JAMES      CLERK            950          3
      7369 SMITH      CLERK            800          4
      7566 JONES      MANAGER         2975          1
      7698 BLAKE      MANAGER         2850          2
      7782 CLARK      MANAGER         2450          3
      7839 KING       PRESIDENT       5000          1
      7499 ALLEN      SALESMAN        1600          1
      7844 TURNER     SALESMAN        1500          2
      7654 MARTIN     SALESMAN        1250          3
      7521 WARD       SALESMAN        1250          4

LISTAGG(...) WITHIN GROUP(...)

列值拼接，根据 sal 排序。

1
2
3
select 
    listagg(ename, ',') within group(order by sal) as name
from emp;
1
2
3
NAME
--------------------------------------------------------------------------------
SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING

一般配合分组使用。

列举各部门的员工，并按工资排序。

1
2
3
4
5
select 
    deptno, 
    listagg(ename, ',') within group(order by sal) as name
from emp
group by deptno;
1
2
3
4
5
DEPTNO       NAME
-------    ---------------------------------------
10         MILLER,CLARK,KING
20         SMITH,ADAMS,JONES,FORD,SCOTT
30         JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE

START WITH...CONNECT BY PRIOR...

对树结构的递归查询。

1
2
3
4
5
6
SELECT * FROM T
-- 开始条件（父节点为空）
START WITH p_id is null
-- 连接条件：向prior跟随的节点类型方向遍历（向子节点方向递归遍历）
CONNECT BY PRIOR id = p_id
-- 等同 CONNECT BY p_id = PRIOR id

相关常见问题

NULL问题

字段处理

当字段为 null 时，希望特殊处理可以使用函数： NVL() , DECODE() , CASE...WHEN... 。

排序处理

当字段为 null 时，默认的排序可能不是我们希望的，此时可以特殊处理。

oracle 中默认排序时，当字段值为 null ，desc 时，null 会在最前面，asc 会在最后面。

  • 字段处理，排除干扰

    NVL() , DECODE() , CASE...WHEN... 。

  • nulls last / nulls first

    1
    select * from emp order by sal desc nulls last;

日期问题

时间重叠

不重叠的条件:

t.endTime < '参数开始时间' : 当前开始时间 大于 历史结束时间。

t.startTime > '参数结束时间' : 当前结束时间 小于 历史开始时间。

1
2
-- 获得时间重叠的数据(验证时间是否重叠)
select ... from  table t where ... and  not ( t.startTime > '参数结束时间' or t.endTime < '参数开始时间' )

sequence

Oracle 中没有自增主键，此时可以使用 sequence 代替。

1
2
3
DROP SEQUENCE TAX_SEQUENCE;
CREATE SEQUENCE TAX_SEQUENCE INCREMENT BY 1 START WITH 100000  MAXVALUE 100000000 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

使用方式

1
2
3
4
-- 直接使用，可以直接在 SQL 中使用。
TAX_SEQUENCE.NEXTVAL
-- 间接使用，例如提供给 Java 程序。
select TAX_SEQUENCE.NEXTVAL from dual

判断字段值是否为数字

将数字字符转变为空格，再去空格，为空的则表示原字段值为纯数字字符组成。

1
2
-- 字段值为数字
select * from table where trim(translate(column,'0123456789',' ')) is NULL;