oracle常用函数小结(一)

1. decode函数

decode(表达式,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)，--类似于case when

（1）两个值求较小的值

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

（2）统计求和

比如我要查询某班男生和女生的数量分别是多少?

selectsum(decode(t.gender，'男'，1，0）)，sum(decode(t.gender，'女'，1，0）) from table t where t.class_name='c1';

（3）order by对字符列进行特定的排序

select * from table_subject t

wheret.subject_name in('语文', '数学', '外语')

order by decode(t.subject_name, '语文', 1, '数学', 2, , '外语',3);--类似于order by id

2. instr函数

instr( string1, string2, start_position,nth_appearance ) 
在一个字符串中搜索指定的字符,返回指定的字符第几次出现的的位置;
string1  被搜索的字符串
string2  希望搜索的字符串
start_position    （可选）搜索起始位置,默认为1
nth_appearance    （可选）第几次出现,默认为1

select instr('oracle traning','z') as index from dual;--返回0，找不到
select instr('oracle traning','ra') as indexfrom dual;--返回2
select instr('oracle traning','ra',1,1) as indexfrom dual;--返回2，注：后两个参数可选，默认值都是1
select instr('oracle traning','ra',-1,1)as indexfrom dual;--返回9，注从右往左搜索第一次出现
select instr('oracle traning','ra',1,2)as indexfrom dual;--返回9，注从左往右搜索第二次出现

3. NVL, NVL2, Coalesce函数

(1)NVL( exp, replace_with)
<1>如果 exp 为NULL，则NVL函数返回replace_with的值，否则返回原来的值
<2>表达式 exp 的值可以是数字型、字符型和日期型
<3>string1和replace_with必须为同一数据类型，除非显式的使用TO_CHAR函数
如
NVL(TO_CHAR(numeric_column), 'some string')
NVL(t.name,t.en_name)
NVL(t.age,0)

(2)NVL2( exp, exp1,exp2)
如果exp为null，返回值为exp2的值,如果exp不为null，返回值为exp1的值

(3)Coalesce(exp1, exp2, exp3….. expn)
<1>NVL函数的升级,n>=2,否则报错参数不够
<2>此函数的功能为返回第一个不为空的表达式，如果都为空则返回空值
<3>所有表达式必须是相同类型，或者可以隐性转换为相同的类型

4.trim,ltrim,rtrim函数，

5.listagg函数--行转列