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

针对 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/ELSE1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19select

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

8with

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

4select

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语法格式：trunc(date [, 'fmt'])SYSDATE ：系统时间。示例 2018/3/19 14:31:13结果说明select trunc(sysdate, 'dd') from dual2018/03/20返回当前时间：天

select trunc(sysdate, 'hh24') from dual2018/3/20 14:00:00返回当前时间：小时

select trunc(sysdate) from dual2018/03/20返回当前时间

select trunc(sysdate, 'yy') from dual2018/01/01返回当年第一天

select trunc(sysdate, 'mm') from dual2018/03/01返回当月的第一天

select trunc(sysdate, 'd') from dual2018/03/19返回当前信息的第一天

select trunc(sysdate, 'mi') from dual2018/3/19 14:31:00返回当前时间：分钟

TO_CHAR() / TO_DATE()

这里仅说明 日期类型 和 字符串类型 的相互转换。示例 2018/3/19 14:31:13结果select to_char( sysdate, 'YYYY-MM-DD') from dual2018/3/19

select to_date('2018/3/19', 'YYYY-MM-DD') from dual2018/3/191

2select date'2018-03-19' from dual;

ROW_NUMBER() OVER1

2-- 将表按照a字段进行分组，之后按照b字段进行组内排序，并给出排序的编号。

ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)

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

2

3select empno,ename,job,sal,

ROW_NUMBER() OVER(PARTITION BY job order by sal desc) as job_rank

from emp1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16EMPNO 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

3select

listagg(ename, ',') within group(order by sal) as name

from emp;1

2

3NAME

--------------------------------------------------------------------------------

SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING

一般配合分组使用。列举各部门的员工，并按工资排序。1

2

3

4

5select

deptno,

listagg(ename, ',') within group(order by sal) as name

from emp

group by deptno;1

2

3

4

5DEPTNO 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

6SELECT * 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 first1select * from emp order by sal desc nulls last;

日期问题

时间重叠1

2-- 获得时间重叠的数据(验证时间是否重叠)

select ... from table t where ... and not ( t.betginTime > '参数结束时间' or t.endTime < '参数开始时间' )

sequence

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

2

3DROP 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