oracle常用sql

字段为空判断

NVL

NVL(expr1, expr2)

#说明：
#expr1：需要判断是否为空的字段
#expr2：如果expr1为空，则显示expr2

NVL2

NVL2(expr1, expr2, expr3)

#说明：
#expr1：需要判断是否为空的字段
#expr2：如果expr1不为空，则显示expr2
#expr3：如果expr1为空，则显示expr3

条件判断

case when

CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;

#例：
select
(
	case
		then t.column1 = 'aaa' then 'text1'
		when t.column1 = 'bbb' then 'text2'
		else 'text3'
	end
) as "name"
from table2 t

DECODE

DECODE(expr, [search, result]*, default)

decode (
expression,
condition_01, result_01,
condition_02, result_02,
…,
condition_n, result_n,
result_default)

说明：
若表达式expression 值与condition_01 值匹配，则返回result_01，若不匹配，则继续判断；
若表达式expression 值与condition_02 值匹配，则返回result_02 ，若不匹配，则继续判断；
以此类推，若表达式expression 值condition_n 值匹配，则返回result_n ，若不匹配，则继续判断；
若表达式expression 值与以上所有的condition 都不匹配，则返回默认值result_default ，若省略result_default 参数，则返回null

字符串拼接

CONCAT

CONCAT(ch1, ch2)

#例：
CONCAT('Hello ', 'WSord') # Hello Word

||

ch1 || ch2

日期按格式强转

TO_CHAR(t.REQ_TIME, 'YYYY-MM-DD')
to_char(t.fd_create_time, 'yyyy/MM/dd hh24:mi:ss')

聚合

LISTAGG

LISTAGG( [ALL] measure_expr [,'delimiter' [,listagg_overflow_clause] ] ) [ WITH GROUP (order_by_clause) [ OVER query_partion_clause] ]

#说明：
#[]内表示是可选的
#ALL：ALL关键字是可选的
#measure_expr：是被操作的列，可以是任何基于列的表达式，列中空值将被忽略
#delimiter：可选的，分隔符，拼接每个列值。默认值为NULL
#listagg_overflow_clause:可选的，这个子句控制当返回值超过返回数据类型的最大长度时函数的行为
#order_by_clause：排序子句，决定被连接值的顺序
#query_partion_clause：略，可搜索oracle分析函数【over(partition by...)】来了解

例：A表code拼接的字符串关联B表，转换成B表name拼接字符串

select
(
   SELECT
   LISTAGG ( s.name, ',' ) WITHIN GROUP ( ORDER BY s.name)
   FROM
   table2 s
   WHERE
   instr( t.code, s.code) > 0
) as "name"
from table1 t

取第一条数据

oracle数据取第一条数据和其他数据库不一样，要十分注意

select * from (select p.*,ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY p.CREATE_TIME DESC) AS rn from table1 p) where rn=1

SELECT * FROM table1 WHERE ROWNUM = 1 ORDER BY CREATE_TIME DESC

SELECT * FROM table1 ORDER BY CREATE_TIME DESC FETCH FIRST ROW ONLY

小数点有效位数

ROUND(n, int)

#说明：
#n：number类型
#int：有效位数

#例：
#n=3.141
ROUND(n, 2) //3.14

生成UUID

SELECT RAWTOHEX(SYS_GUID()) FROM DUAL

ps：个人不是很建议直接写sql生成uuid，oracle生成的id虽然是唯一的，但字母重复度极高，肉眼十分不好区分（如下图），如果能够在后端插入数据的时候写是最好了