Oracle常用函数
2018-05-07，本文 4061 字，阅读全文约需 12 分钟
1、CASE WHEN
CASE WHEN condition THEN return_expr
WHEN condition THEN return_expr
...
ELSE else_expr
END
when：
sex = ‘1’
salary > 500 AND salary <= 600
BETWEEN 0 AND 1
-
简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END -
Case搜索函数
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END -
SELECT CASE WHEN 用法
SELECT (CASE
WHEN FIELD_NAME BETWEEN 0 AND 1 THEN
'0 - 1'
WHEN FIELD_NAME BETWEEN 1 AND 2 THEN
'1 - 2'
ELSE
'2 - 100'
END) AS FIELD_NAME
FROM TABLE
- WHERE CASE WHEN 用法
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
T1.SOME_TYPE LIKE 'NOTHING%'
THEN 1
WHEN T2.COMPARE_TYPE != 'A' AND
T1.SOME_TYPE NOT LIKE 'NOTHING%'
THEN 1
ELSE 0
END) = 1
- GROUP BY CASE WHEN 用法
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
2、Sign
比较大小函数SIGN
sign(x)或者Sign(x)叫做符号函数，其功能是取某个数的符号（正或负）：
当x>0，sign(x)=1
当x=0，sign(x)=0
当x<0，sign(x)=-1
x可以是函数或计算表达式
3、Decode
将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值
decode (expression, search_1, result_1, search_2, result_2, …., search_n, result_n, default)
- 使用DECODE实现按字段内容分组
SELECT DECODE(JOB,'VP','VP_CEO','CEO','VP_CEO','OPERATION') JOB,
COUNT(*) JOB_CNT
FROM t1
GROUP BY DECODE(JOB,'VP','VP_CEO','CEO','VP_CEO','OPERATION');
- 使用DECODE实现按字段内容排序
- 按字段内容排序
SELECT ID,DEPT_NAME,REGION_ID
FROM t2
ORDER BY DECODE(DEPT_NAME,
'deptA',1,
'deptb',2,
3);
- 按字段内容指定动态列排序
需求：若DEPT_NAME为deptA，则按ID升序排列，否则按REGION_ID升序排序
SELECT ID,DEPT_NAME,REGION_ID
FROM t2
ORDER BY DECODE(DEPT_NAME,
'deptA',ID,
REGION_ID);
- 使用DECODE实现固定行转列
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
decode实现比较
SELECT DECODE(SIGN(TO_DATE('20201010', 'YYYY/MM/DD') - SYSDATE),
-1,
TO_DATE('20101010', 'YYYY/MM/DD'),
SYSDATE)
FROM DUAL;
4、lag() 和 lead()
over()表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b 表示以 a 字段进行分组，再以 b 字段进行排序，对数据进行查询。
5、instr
instr可以代替like来进行模糊查询，效率很高
instr(title,’手册’)>0 相当于 title like ‘%手册%’
instr(title,’手册’)=1 相当于 title like ‘手册%’
instr(title,’手册’)=0 相当于 title not like ‘%手册%’
特殊用法：
select id, name from users where instr(‘101914, 104703’, id) > 0;
它等价于
select id, name from users where id = 101914 or id = 104703;
6、lpad 和 rpad
lpad( string1, padded_length, [ pad_string ] )
string1：源字符串
padded_length:最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截断处理
pad_string:用于填充的字符，可以不填，默认为空字符
eg：
–返回值长度大于源字符串长度，将进行填充
select lpad(‘zhanglt’,10,’z’) from dual;
结果为：zzzzhanglt
–返回值长度小于源字符串长度，将进行截断
select lpad(‘zhanglt’,3,’z’) from dual;
结果为：zha
7、replace
REPLACE(‘abcd’,’a’,’’)
将字符串中的某个字符串替换为某个字符串
REPLACE(‘abcd’,’a’)
将字符串中的a都删除掉
8、GREATEST
GREATEST(‘A’,2,3,’Ab’)
比较数非空时取最大，有空则为空
9、NVL
NVL(expr1,expr2)
如果expr1为空则显示expr2的值，否则显示expr1的值
10、NVL2
NVL2(expr1,expr2, expr3)
如果expr1为空则显示expr2的值，否则显示expr3的值
11、NULLIF
NULLIF(exp1,expr2)
如果expr1与expr2相等则返回空（NULL），否则返回expr1值
12、Coalesce
Coalesce(expr1, expr2, expr3….. exprn)
如果所有expr都为null，返回null，否则返回第一个不为空的expr
coalesce(exp1,0)将exp1为空的数据转为0
13、substr
substr(‘helloworld’,-3)
返回expr1的后三位，倒数第三位开始截取。——rld
substr(‘helloworld’,-3,1)
倒数第三位开始截取一位。——r
substr(‘helloworld’,0,3)和substr(‘helloworld’,1,3)
从第一位开始截取3位。——hel
substr(‘helloworld’,3)
从第三位开始截取。——lloworld
14、translate
translate(expr,from_string,to_string)
将expr中对应的字符进行修改，from_string中的字符与to_string中的一一对应,没有对应的字符则对应空
Translate(‘aaaaaasssssss’,’as’,’2311’)——2222223333333
Translate(‘asbf’,’as’,’2’)——2bf
15、REGEXP_COUNT
字符串匹配次数
regexp_count (‘The pro-niece was born today, so exciting.’, ‘a’)——2
支持从指定位置检测，大小写敏感
16、regexp_replace
正则表达式替换
regexp_replace(‘abc10’,’[0-9]’,’’)——abc
regexp_replace(‘abc10’,’^[0-9]’,’’)——10
17、regexp_like
regexp_like(data,’a’)== like ‘%a%’
regexp_like(data,’^a’)== like ‘a%’
regexp_like(data,’a$’)== like ‘%a’
regexp_like(data,’^$a’)== like ‘a’
18、listagg
listagg(ename,’,’) within group(order by ename)多行转列，以逗号分隔
19、wm_concat
与listagg一样，实现的是多行转列
20、to_char
to_char，函数功能，就是将数值型或者日期型转化为字符型
高级
Select TO_CHAR(123.0233,’FM9999999.0099’) FROM DUAL
FM:除空格、9999999.0099：允许小数点左边最大正数为7位，小数点右边最少2位，最多4位，且对第5位进行四舍五入