Oracle 常用函数

1 常用字符串函数

1.1 concat() 字符串连接函数

1.1.1 语法

CONCAT(string1, string2)

参数：string1：第一个要连接的字符串。 string2：第二个要连接的字符串。

返回值：返回 string1 连接 string2 后的一个字符串值

1.1.2 实例

 -- 使用
 SELECT concat('Hello','World') res from dual;
 -- res HelloWorld
 SELECT concat(1,'2') res from dual;
 -- res 12
 SELECT concat('Hello''','World') res from dual;
 -- res Hello'World  在字符串中添加单引号，使用2个额外的单引号实现

1.1.3 其他

1. 在字符串中添加单引号，使用 2 个额外的单引号实现
2. concat 函数只能有两个参数。如果要连接多个值，可以嵌套使用
3. Oracle 可以使用 || 用做连接字符串，而 + 符号用来进行运算

 SELECT ('1' || '2' || '3') res from dual;
 -- res 123
 SELECT ('1' + '2' + '3') res from dual;
 -- res 6

1.2 substr() 字符截取函数

1.2.1 语法

SUBSTR(String string, int a, int b);

参数：string：需截取的字符串 a：截取的开始位置，（0,1 都表示为第一个元素，如果为负数，则从倒数第 a 个开始向后截取） b：要截取的字符串长度（非必填，不填截取到最后一个元素）

返回值：截取后的字符串

1.2.2 实例

 select substr('HelloWorld',1,3) res from dual; 
 -- res Hel
 select substr('HelloWorld',2) res from dual; 
 -- res elloWorld
 select substr('HelloWorld',-2) res from dual; 
 -- res ld
 select substr('HelloWorld',-10) res from dual; 
 -- res HelloWorld
 select substr('HelloWorld',-11) res from dual; 
 -- res 空
 select substr('HelloWorld',11) res from dual; 
 -- res 空

1.3 instr() 字符查找函数

1.3.1 语法

INSTR( string1, string2 [, start_position [, nth_appearance ] ] )

参数：string1：源字符串 string2：要查找的目标字符串 start_position：非必填，开始的位置，默认为 1 nth_appearance：非必填，要查找的是第几次出现，默认为 1

返回值：在 string1 中从 start_position 个字符开始查 string2 第 nth_appearance 次出现的位置

1.3.2 实例

 select instr('HelloWorld','o') res from dual; 
 -- res 5
 select instr('HelloWorld','o',2) res from dual; 
 -- res 5
 select instr('HelloWorld','o',6) res from dual; 
 -- res 7 由这两个例子可看出，从开始位置向后查，返回第一个遇到的 'o' 原本的索引
 select instr('HelloWorld','o',6,2) res from dual; 
 -- res 0 不存在则返回0

1.4 REPLACE() 替换函数

1.4.1 语法

REPLACE( string, search_str [,replace_str] )

参数：string：源字符串 search_str：需要被替换的字符串 replace_str：非必填，替换的字符串

返回值：替换好的函数

1.4.2 实例

 select replace('HelloWorld','o','l') res from dual; 
 -- res HelllWlrld
 select replace('HelloWorld','o') res from dual; 
 -- res HellWrld

2 常用转换类函数

2.1 to_char() 字符格式化函数

2.1.1 语法

TO_CHAR(exp1, format)

参数：exp1：要进行格式化的内容 format：格式化的模板

返回值：格式化后的字符

2.1.2 实例

1. 日期时间格式化

 select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') res from dual;
 -- res 2022-02-02 10:10:49  常用的日期时间格式化模板，hh 是12小时制
 select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') res from dual;
 -- res 2022/02/02 10:12:11  hh24 是24小时制
 select to_char(sysdate,'yyyy"年"mm"月"dd"日"') res from dual;
 -- res 2022年02月02日  也可以支持中文
 下面列几个常用模板，还有许多，有需要可以去网上查

模板	描述
HH	一天的小时数 (01-12)
HH12	一天的小时数 (01-12)
HH24	一天的小时数 (00-23)
MI	分钟 (00-59)
SS	秒 (00-59)
MM	月份 (01-12)
DDD	一年里的日子(001-366)
DD	一个月里的日子(01-31)
D	一周里的日子(1-7；SUN=1)
W	一个月里的周数
WW	一年里的周数
CC	世纪（2 位）
Q	季度

1. 常用数字格式化

 select to_char('1.1','999.999') res from dual;
 -- res 1.100 '9'代表任意一位数字，'999.999'代表小数点最大3位，小数点后保留3位，且会省略最大位的0，如：
 select to_char('011.1','999.999') res from dual;
 -- res 11.100 
 select to_char('10.1115','999.999') res from dual;
 -- res 10.112 小数超过3位四舍五入
 select to_char('1110.1','999.999') res from dual;
 -- res ########  整数超过3位
 select to_char('0.1','999.999') res from dual;
 -- re .100 为了解决这个问题，修改为'990.999'
 select to_char('0.1','990.999') res from dual;
 -- res 0.100 0：存在数字显示数字，不存在数字显示0
 select to_char('1.1','990.999') res from dual;
 -- res 1.100
 -- 注意，上述res 整数位前面会有空格,有几个空格取决于小数点前有几位数字，去除空格使用 FM 前缀
 select to_char('1.1','990.999') res from dual;
 -- res 1.1 FM：删除因为'9'转换的空格或0

1. 金额格式化

 -- 常用金额格式化处理：
 select to_char('10000','FM999,999,999,999,990.00') res from dual;
 -- res 10,000.00
 select to_char('10000','FM$999,999,999,999,990.00') res from dual;
 -- res $10,000.00 显示美元
 select to_char('10000','FML999,999,999,999,990.00') res from dual;
 -- res ￥10,000.00 显示本地货币符号

2.1.3 其他

to_date() 、to_number() 分别可以把字符串按照格式进行格式化转换为时间类型结果和数值类型结果，用法与 to_char() 基本符合，只需要注意转换前的字符串要符合 date 或 number 类型。

2.2 nvl() 空值转换函数

2.2.1 语法

NVL(Expression1, Expression2)

参数：Expression1：要计算的表达式 Expression2：如果 Expression1 为空，返回的表达式

返回值：如果 Expression1 不为空，返回的 Expression1，如果 Expression1 为空，返回的 Expression2。

2.2.2 实例

select nvl('','exp2') res from dual;
-- res exp2
select nvl('exp1','exp2') res from dual;
-- res exp1

2.2.3 其他

nvl2(exp1,exp2,exp3) 是 nvl() 函数的扩展，如果 exp1 为空，就返回 exp3，不为空就返回 exp2。

3 条件判断函数

3.1 case 表达式

3.2 decode()

4 聚合函数

4.1 count()

4.2 AVG()

4.3 SUM()

4.4 MIN()

4.5 MAX()

SELECT max(d.c_dict_item_code) keep (dense_rank first order by d.c_level desc) from tsys_dict_tree d where d.c_dict_entry_code='T0001' and d.c_dict_name='房地产开发经营'

取 房地产开发经营 的按 level 排序的第一条

5 集合运算

5.1 INTERSECT()

5.2 UNION ALL()

5.3 UNION()

5.4 MINUS()

 select * from table(查询集)
 
 exists
 
 in
 
 cursor
 
 for c in cs loop...end loop;
 
 if then...else...end if;
 
 case
 
 oracle分页、存储过程、触发器、declare
 
 z