Oracle常用函数及示例

1、coalesce(exp1,exp2,exp3,......expn)函数

用法：

coalesce函数较为简单，依次参考各参数表达式，返回参数中第一个不为null的数值，如果表达式都是null，最终返回一个null

示例：

select a,coalesce(b,c,d,'Naruto') from teacher_info；

如果b=null，c=null，d='one_piece'，那么查询结果为a,one_piece，如果b=c=d=null，那么查询结果为a,Naruto。

2、decode(value,if1,then1,if2,then2......else)函数

用法：

decode函数会根据第一个参数“value”的值的不同，返回不同的值，如果满足“if1”的条件，然后会返回“then1”，如果满足“if2”的条件，返回“then2”，等等等等，如果都不满足前面的条件，返回最后的一个“else”参数。

示例：

这里我们以teacher表为例。

select name,decode(sex,'1','男老师','2','女老师','未填写性别')from teacher_info

上述SQL中，sex如果是1，则返回男老师，如果是2，返回女老师，其他则表示性别未填写。

select name,sex,decode(sign(age-20),'0','20岁','1','大于20岁','-1','小于20岁','年龄未知') from teacher_info

上述SQL中，如果age=20，sign(age-20)结果为0，返回'20岁'；如果age>20，sign(age-20)结果为1，返回'大于20岁'；如果age<20,sign(age-20)结果为-1，返回小于'20岁';

3、greatest(exp1,exp2,exp3...expn)和least(exp1,exp2,exp3...expn)

用法：

greatest(exp1,exp2,exp3...expn)	返回exp1、exp2、exp3......expn中的最大值，当其中参数有一个为null时，返回null
least(exp1,exp2,exp3...expn)	返回exp1、exp2、exp3......expn中的最最小值，当其中参数有一个为null时，返回null

示例：

select greatest(1,3,2,4,6,9,12,17) A from dual

查询结果为

select greatest('1','2','12','5','8') A from dual

返回值为,当比较对象为字符时，会优先按第一位比较，如果第一位相同，再比较第二位。

least函数的用法与greatest用法相同，区别在于greatest返回的是最大值，least返回的是最小值。

4、nvl(exp1,exp2),nvl2(exp1,exp2,exp3),nvllif(exp1,exp2)三个函数的用法和区别

 用法：

nvl(exp1,exp2)	该函数表示如果exp1=null，那么返回exp2的值，如果exp1不为null，就返回exp1的值。
nvl2(exp1,exp2,exp3)	该函数表示如果exp1不为null，返回exp2的值，如果exp1=null，返回exp3的值。
nvlliff(exp1,exp2)	该函数表示如果exp1和exp2的两个参数值相等则返回null，不等则返回exp1。

上述三个函数的参数类型必须相同，否则会报错。

示例：

select name,nvl(sex,'未知') from teacher_info;

如果sex为null，查询出结果就是name和'未知'，如果sex不是null，查询出结果为name和该教师的真是性别；这个查询能够使用户能够准确查出教师的性别，如果为null，就显示未知。

select name,nvl(hasCode,code,0) from teacher;

如果hasCode是null，查询出结果为name和0，如果hasCode不是null，查询出结果就是a和code；hasCode表示该教师是否有编号，如果是null，表示没有编号，如果不是null表示有编号，并查询出教师真实的name和code。

select a, nvllif(b,c) from teacher ;

如果b=c，查询出结果就是a和nul，如果b不等于c，查询出结果就是a和b；

5、replace('exp1','exp2','exp3')函数

用法：

replace函数就是将原字段中的内容修改替换成我们想要的新字段内容。

表达式说明

exp1	原字段
exp2	原字段中要替换的内容
exp3	要替换成的内容

示例：

select replace('asdasdsakdkdkd','a','熊道远') AS str from dual;

这里把字符‘a’替换为字符‘熊道远’，查询结果如下：

select replace('asdasdsakdkdkd','a',null) AS str from dual;

这里把字符'a'替换为null，相当于去掉字符'a'，查询结果如下：

6、sign(exp1)函数

用法：

sign(exp1)为比较大小函数，exp1必须为数字，exp1大于0，返回1；小于0，返回-1；等于0，返回0。

示例：

select sign(100),sign(-100),sign(0) from dual;

返回结果为1  -1  0。

--exp1=20,exp2=12
select sign(exp1-exp2),sign(exp2-exp1) from dual;

返回结果1  -1。

7.substr(exp1,exp2)函数

用法：

该函数表示截取exp1的exp2位，并将结果返回

exp1	要截取的字符串
exp2	要截取的位数，如果为负数，表示从最后一位开始截取

 

 

 

示例：

select substr(phone_number,-4) as 尾号 from employees

截取后四位作为手机尾号。