【ORACLE】收集一些较为少见但很有用的SQL函数及写法.part1

前言

sql作为传统关系型数据一种常见语言，广泛使用在各种程序项目中。随着数据库厂商不断地更新迭代版本，sql的功能越来越丰富和强大，已经不局限于关系型查询，甚至对递归、数组、对象等都有支持。但是实际开发中，大多数开发人员对sql研究并不深，不清楚sql能支持到何种程度。虽然近几年网络上已经开始有一些技术分析文了，但总感觉受众还是不够广，所以我就开一个专题系列，分享一些sql的写法，主要针对oracle数据库，每篇根据复杂度大概介绍不超过10种，下面开始第一篇，先来点简单的

---

1.cast

作用：强制或者指定查询字段的输出类型，常用于“CREATE TABLE AS SELECT …”中
例：

create table  tmp_table as 
select CAST(132 as number(8, 4)) number_col,
       CAST(132 as VARCHAR2(10)) VARCHAR2_col,
       cast(null as date) date_null,
       cast(null as number) number_null,
       cast(null as VARCHAR2(10)) varchar2_null,
       '1' char_null
  from dual;

desc tmp_table;

名称	是否为空?	类型
NUMBER_COL		NUMBER(8,4)
VARCHAR2_COL		VARCHAR2(10)
DATE_NULL		DATE
NUMBER_NULL		NUMBER
VARCHAR2_NULL		VARCHAR2(10)
CHAR_NULL		CHAR(1)

注意观察char_null这个字段和varchar2_null字段的区别，varchar2是可变长字符串，在存储空间的节省上，要优于char，建议尽量避免使用char类型。

而且，如果使用下面这2种写法，是会报错的，不允许长度为0的列

create table  tmp_table as 
select 
       '' char_null
  from dual;
create table  tmp_table as 
select 
       null char_null
  from dual;

---

2.with …as

作用：将子查询提取出来作为一个虚拟表，简化sql，增强sql的可读性，有时也能在一定程度上提升sql的查询效率（这个其实还有更复杂的用法，以后再说）
例：

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual)
select t1.expr2 + t2.expr3 res from t t1, t t2

此例子中，对同一个子查询sql引用了两次，常规写法要重复写两个一模一样的，但使用with子句可以精简为只写一个子查询sql，而且with子句后还支持并列或者嵌套多个sql，比如

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual)，
     t1 as
 (select * from t where t.expr2=2)
select * from t ,t1

---

3.NVL2

作用：该函数有3个参数，判断第一个参数是否为空，如果不为空则显示第二个参数，如果为空则显示第三个参数
例：

with t as
 (select null expr1, 2 expr2, 3 expr3 from dual)
select NVL2(expr1, expr2, expr3) res from t;

RES

3

注意区分该函数和nvl的区别

---

4.COALESCE

作用：该函数是nvl函数的升级版，可传入很多个参数，按参数输入顺序，输出第一个不为空的参数的值
例：

with t as
 (select null expr1, null expr2, 3 expr3 from dual)
select COALESCE(expr1, expr2, expr3) res from t;

RES

3

---

5.TRANSLATE

作用：对第一个参数中的字符串进行替换，替换规则为，把第二个参数中的第N个字符替换成第三个参数中的第N个字符。可以用于字符串加密
例：

select  TRANSLATE('A爱B，但是B不爱A','A爱','C恨') res   from dual;

RES

C恨B，但是B不恨C

---

6.RPAD

作用：在第一个参数右边以第三个参数填充，使输出结果长度达到第二个参数的值，类似的还有LPAD，即在左边填充。常用于输出一些格式化文本的处理
例：

select RPAD('我银行卡上的数字为100',50,'0') res  from dual;

RES

我银行卡上的数字为10000000000000000000000000000000

---

7.SIGN

作用：判断输入参数的正负号，正数返回1，负数返回-1，0返回0。在做数据统计时，如果求和的列有正有负，要分别求出正数的和和负数的和，或者说正数和负数的数据要分开处理，那么在group by 后面加上sign(求和列)则是个不错的方式。
例：

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)

SELECT NAME ,SUM(VALUE) S FROM T GROUP BY NAME,SIGN(VALUE)

NAME	S
B	5
A	-3
A	5

我们可以猜想A和B两个人，后面的统计是拉到的客户数，正数表示成功人数，负数表示失败人数，虽然最后成功的人数一样，但B属于稳扎稳打，没有失败，但A则失败了3个，这个可能会造成一些负面影响。当然也可以用其他角度来观察，这就靠人自行想象了。

---

8.ROLLUP/CUBE

作用：接在group by 后面，查询结果能多出几行不同的聚合维度的数据。其中ROLLUP是按后面接的参数顺序逐级聚合，CUBE则是列出后面参数所有组合情况的聚合。
例：

WITH T AS 
(SELECT 'A' NAME,-2 VALUE,3 WEIGHT FROM DUAL UNION ALL
SELECT 'B',3 , 3 FROM DUAL UNION ALL
SELECT 'A',-1,2 FROM DUAL UNION ALL
SELECT 'A',5,2 FROM DUAL UNION ALL
SELECT 'B',2,4 FROM DUAL 
)

SELECT NAME ,WEIGHT,SUM(VALUE) S FROM T GROUP BY ROLLUP (NAME,WEIGHT)

NAME	WEIGHT	S
A	2	4
A	3	-2
A		2
B	3	3
B	4	2
B		5
		7

这个例子中，输出结果的第三行，表示 NAME为A的汇总行，不管WEIGHT；第6行为B的汇总行；第7行为所有的汇总。在做一些报表数据时，不需要再另外针对不同的汇总维度再多写几段sql了，而且性能开销更低。结合NVL函数，可以让数据结果更直观，比如

WITH T AS 
(SELECT 'A' NAME,-2 VALUE,3 WEIGHT FROM DUAL UNION ALL
SELECT 'B',3 , 3 FROM DUAL UNION ALL
SELECT 'A',-1,2 FROM DUAL UNION ALL
SELECT 'A',5,2 FROM DUAL UNION ALL
SELECT 'B',2,4 FROM DUAL 
)

SELECT nvl(NAME, '全员') NAME,
       nvl(to_char(WEIGHT), '合计') WEIGHT,
       SUM(VALUE) S
  FROM T
 GROUP BY ROLLUP(NAME, WEIGHT)

NAME	WEIGHT	S
A	2	4
A	3	-2
A	合计	2
B	3	3
B	4	2
B	合计	5
全员	合计	7

---

9.LISTAGG(…,’,’) within GROUP (order by …) /(WM_CONCAT)

作用：将某列数据的值放到一个值里，用指定的分隔符串起来，且支持排序（分隔符可自定义，上面是指定逗号），在某种意义上可以理解为是行转列的一种方式。
例：

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)
SELECT LISTAGG(NAME,',')  within GROUP (order by NAME) res FROM t 

RES

A,A,A,B,B

可以看出这里的结果并没有去重，这里就要提到另一个函数了WM_CONCAT,WM_CONCAT支持WM_CONCAT(DISTINCT
字段) 的写法，
但是，
一般在oralce 11g版本及之前，可以用wm_concat这个函数，使用起来更简单，但是从12C版本开始，ORACLE移除了这个函数，因为WM_CONCAT这个函数一开始并不是开放给用户使用的，所以在正式的代码里尽量使用LISTAGG而非WM_CONCAT,以免程序移植或者数据库版本升级导致程序出现异常。当然如果一定要用wm_concat，也可以自行在12C以上的版本把这个wm_concat函数给加上去，这里就不给代码了，请自行在互联网检索。
不过，ORACLE既让我们改用LISTAGG又不给去重，这合理么？
不合理。
所以，ORACLE 在19C版本把这个功能加上去了，而这个功能，在众多流行的数据库中，在这个时候只有DB2和ORACLE这2款数据库支持LISTAGG里加distinct(未来可能会有更多的数据库支持)。
而且，从oracle 18c版本开始，LISTAGG可以不需要 “within GROUP ”了，效果如下

with t as 
(select 'A' NAME,-2 VALUE FROM DUAL UNION ALL
select 'B',3 from dual union all
select 'A',-1 from dual union all
select 'A',5 from dual union all
select 'B',2 from dual 
)
SELECT LISTAGG(NAME)  res,LISTAGG(NAME,',') res2  FROM t 

RES	RES2
ABAAB	A,B,A,A,B

另外一个要注意的点是，WM_CONCAT输出结果是4000长度以内的CLOB类型，LISTAGG是4000长度以内的varchar2类型，所以 别拼太长!