Oracle 函数使用记录

常用方法函数

instr(String1，String2)

instr(String1，String2) ：String1包含String2，返回一个数字值。 字符串中的第一个位置是1。如果在字符串中找不到String2，那么INSTR函数将返回0。

例子：

1 select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果：4    即：在“lo”中，“l”开始出现的位置
3 select instr('helloworld','wo') from dual; --返回结果：6    即“w”开始出现的位置

instr(String1，String2) >0的使用和模糊查询（%%）效果等同。

例子：

查询名字为“郑加亮”的所在部门（两种方法）

select A.* from sys_org a
where  instr('.'||(select A.path from sys_org a
		join sys_user_org b on A.ORGID=B.ORGID
		join sys_user  c on B.USERID=C.USERID
		where C.FULLNAME='郑加亮'),'.'||a.orgid||'.')>0
order by a.orgtype


select A.* from sys_org a
where ( select A.path from sys_org a
		join sys_user_org b on A.ORGID=B.ORGID
		join sys_user  c on B.USERID=C.USERID
		where C.FULLNAME='郑加亮'
      ) like '%'||a.orgid ||'%'
order by a.orgtype

上述两个方法：第一个速度快很多。使用Oracle系统内函数要快很多

Parttion by 与over（）

Parttion by 关键字是Oracle中分析性函数，可以进行分组排序，和**row_number()、rank()、dense_rank()**函数一起使用

NO	CLASS	SCORE
1001	1	99
1002	2	98
1001	3	98
1004	1	98
1005	1	97
1006	1	99
1007	2	98
1008	3	97

• –row_number() 顺序排序 （永远是1、2、3、…）
  select row_number() over (partition by class order by score desc) 排名 ,NO,score,class from A_TEST

排名	NO	CLASS	SCORE
1	1001	99	1
2	1006	99	1
3	1004	98	1
4	1005	97	1
1	1002	98	2
2	1007	98	2
1	1008	97	3
2	1001	97	3

• –rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别（1、1、3、3、5、…）
  select rank() over (partition by class order by score desc) 排名 ,NO,score,class from A_TEST

排名	NO	CLASS	SCORE
1	1001	99	1
1	1006	99	1
3	1004	98	1
4	1005	97	1
1	1002	98	2
1	1007	98	2
1	1008	97	3
1	1001	97	3

• –dense_rank() 连续排序，如果有两个第一级别时，接下来是第二级（1、1、2、2、3、3、3、…）

  select dense_rank() over (partition by class order by score desc) 排名 ,NO,score,class from A_TEST

排名	NO	CLASS	SCORE
1	1001	99	1
1	1006	99	1
2	1004	98	1
3	1005	97	1
1	1002	98	2
1	1007	98	2
1	1008	97	3
1	1001	97	3

parttion by 是分析函数。group by 是分组函数

partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果

NVL(e1, e2)

如果 e1 的计算结果为 null 值，则 NVL( ) 返回 e2。如果 e1 的计算结果不是 null 值，则返回 e1。e1 和 2 可以是任意一种数据类型。如果 e1 与 e2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

巧用NVL（）：

--在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID

Decode函数

第一种形式

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

类比

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

第二种形式

decode(字段或字段的运算，值1，值2，值3）

含义：

if 字段或字段的运算==值1
    return 值2;
 else 
    return 值3;

LISTAGG 函数

作用：将多列记录聚合为一条记录

语法：

---按t4分组，t3排序，拼接t1字段，中间使用t2连接---
LISTAGG(t1,t2) WITHIN GROUP( ORDER BY t3)
group by t4

例子

LISTAGG(t1.name,'、') within group (order by rownum)
group by t1.classid

日期相关

to_char ：日期类型 -> 字符串 输出

格式：to_char(date,转换的格式)

转换的格式：

表示对象	符号	含义
year年	y	表示年的最后一位
	yy	表示年的后两位
	yyy	表示年的后三位
	yyyy	表示年的完全展示
month月	mm	用2位数字表示月
	mon	用简写形式 比如11月或者nov
	month	用全称形式比如11月或者november
day日	dd	表示当月第几天
	ddd	表示当年第几天
	dy	表示当周第几天的简写 比如星期五或者fri
	day	表示当周第几天 全写比如星期五或者Friday
hour小时	hh	2位数表示小时 12进制
	hh24	2位数表示小时 24小时
minute 分	mi	2位数表示分钟
second秒	ss	2位数表示秒 60进制

例：

select to_char(t1.date01,'yyyy-mm-dd') from t1

to_date：字符串 -> 日期类型 输出

格式：to_char（’要转换的字符串‘,’转换的格式‘）

要转换的字符串必须满足转换的格式要求。

例：

select to_date('2021-8-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual --显示：2021-8-25 13:25:59

但是

select to_date('2021-8-25,13:25:59','yyyy-mm-dd,hh:mi:ss') from dual--报错，因为hh是12小时制，13大于12。

关键字

union和union all

• union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;
• union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;