Oracle 中的一些函数

一、Oracle 比较大小函数 sign(n)

函数说明：
取数字 n 的符号，大于 0 返回 1；小于 0 返回 -1；等于 0 返回 0。

示例：
1、select sign( 2 ),sign(- 2 ),sign( 0 ) from dual;

SIGN(2) SIGN(-2) SIGN(0)
　———- ———- ———-
　　 1 -1 0

2、a=100 b=200 则 sign(a-b) 返回 -1

二、nvl(EXPR1,EXPR2)

MySQL中的IFNULL(expr1,expr2)

函数说明：
从两个表达式返回一个非 null 值。如果 EXPR1 的计算结果为 null，则 NVL( ) 返回 EXPR2。如果 EXPR1 的计算结果不是 null，则返回 EXPR1。EXPR1 和 EXPR2 可以是任意一种数据类型。如果 EXPR1 与 EXPR1 的结果皆为 null，则 NVL( ) 返回 NULL.。

返回值类型：
字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null

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

-- 注意：两个参数得类型要匹配
select nvl(a.name,'空得') as name 
from student a join school b on a.ID=b.ID

三、nvl2（exp1,exp2,exp3）

MySQL中的IF(expr1,expr2,expr3)

Oracle 在 NVL() 的功能上扩展，提供了 NVL2 函数。该函数决意 exp1 是空值和非空值时返回哪个表达式：

• 假设 exp1 为空值，则返回 exp3；
• 假设 exp1 不为空，则返回 exp2。

四、coalesce()函数

coalesce(expression_1, expression_2, …,expression_n) 依次参考各参数表达式，遇到非 null 即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
1、coalesce 函数是用来获取第一个不为空的列的值
2、coalesce 函数里面所有表达式必须为同一类型或者能转换成同一类型
3、CREATE OR REPLACE VIEW v AS SELECT NULL AS c FROM dual;这样建立的视图，列 c 的数据类型是 char。

-- 注意：COALESCE(expression1,...n) 与此 CASE 函数等价：
CASE  expression
WHEN (expression1 IS NOT NULL) THEN expression1
.
.
.
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

coalesce 函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提供了它的简化版，这些简化版中只接受两个变量，其参数格式如下：
MySQL：

IFNULL(expression,value)   //expr1不为null返回expr1，否则返回expr2

Oracle：

NVL(expression,value)

这几个函数的功能和 COALESCE(expression,value) 是等价的。比如 SQL 语句用于返回人员的“意中人”，如果自己的 name 不为空则将 name 做为“意中人”，如果 name 为空则返回 NULL：

-- MySQL: 
select name,herName,isnull(name,herName) as Love from table
-- Oracle: 
select name,herName,NVL(name,herName) as Love from table

五、is null、is not null 和 “<>”、!=

首先对Oracle中的null做一些简单的说明：
1、oracle中null与0，空字符串，空格，包括null与null也是不等的。
2、oracle中对null做算术运算结果仍然为null。
3、处理方法有nvl函数，比较方法是is null或者is not null。
4、null不能被索引，比如select count (null) from dual的查询结果是0。
5、null排序比其他所有类型的大，一个可以为空的字段倒排序，前面的都是null数据。

"<>’'只能判断空字符串，is Null是对Null字符的判断，两种完全不同的数据。一个是空字符，是个字符串，只不过是没有值，另一个是空值(null)。null在数据库中是特有的一类数据。where子句中的is null和is not null将不会使用索引而是进行全表搜索。因此优化效率需要通过改变查询方式，分情况讨论等方法，去掉where子句中的is null和is not null。
例一：

select  a.字段n from tab_a a where a.字段2 is not null;
//a.字段2增加了索引的，但是查询速度非常慢，

做了如下优化：

select  a.字段n from tab_a a where nvl(a.字段2,'0') != '0';

速度提升很明显。
原因是什么呢？其实很简单，因为is null和is not null使字段的索引失效了。
例二：

select * from table where field = null;
select * from table where field is null;
如果 field 是null的话第一种条件查不出结果，第二种条件查的出结果。

原因是在oracle中，判断一个字段或者一个变量是否为 null 语法是 “is null”。返回的结果是boolean型。如果使用 “= null”，会返回null。如果在代码或语句中使用，可能会获取不到结果或者报错。另外，null并不表示不存在，而是未知，我们通常成为“UNKNOWN”。
如果case when条件写判断是否为null的话，会导致查询的结果有未知的错误。
结论：如果要判断是否为null，Oracle使用is null 或者is not null

六、TRUNC()：类似截取函数，按指定的格式截取输入的数据。

1.【trunc(for dates)】TRUNC()函数处理日期

-- 语法格式：
TRUNC（date[,fmt]）

其中：date 一个日期值；fmt 日期格式。该日期将按指定的日期格式截取；忽略它则由最近的日期截取。
示例：

select trunc(sysdate) from dual;--2017/2/13,返回当前时间
select trunc(sysdate,'yy') from dual;--2017/1/1,返回当年第一天
select trunc(sysdate,'mm') from dual;--2017/2/1,返回当月的第一天
select trunc(sysdate,'d') from dual;--2017/2/12,返回当前星期的第一天，即星期天
select trunc(sysdate,'dd') from dual;--2017/2/13,返回当前日期,今天是2017/2/13
select trunc(sysdate ,'HH24') from dual;--2017/2/13 15:00:00,返回本小时的开始时间
select trunc(sysdate ,'MI') from dual;--2017/2/13 15:13:00,返回本分钟的开始时间

2.【TRUNC(for number)】TRUNC()函数处理number型数字

-- 语法格式：
TRUNC（number[,decimals]）

其中：number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。
注意：截取时并不对数据进行四舍五入。
示例：

select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

七、instr()函数的格式（俗称：字符查找函数）

1️⃣格式

格式一：instr( string1, string2 )
/ instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )
/ instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

2️⃣实例

格式一
1、select instr(‘helloworld’,‘l’) from dual; --默认第一次出现“l”的位置

2、select instr(‘helloworld’,‘lo’) from dual; --在“lo”中，“l”开始出现的位置

3、select instr(‘helloworld’,‘wo’) from dual; --“w”开始出现的位置

格式二
1、select instr(‘helloworld’,‘l’,2,2) from dual;
–在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置

2、select instr(‘helloworld’,‘l’,3,2) from dual;
–在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置

3、select instr(‘helloworld’,‘l’,4,2) from dual;
–在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置

4、select instr(‘helloworld’,‘l’,-1,1) from dual;
–在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置

5、select instr(‘helloworld’,‘l’,-1,2) from dual;
–在"helloworld"的倒数第1(d)号位置开始，往回查找第二次出现的“l”的位置

6、select instr(‘helloworld’,‘l’,2,3) from dual;
–在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置

7、select instr(‘helloworld’,‘l’,-2,3) from dual;
–在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

注：模糊查询 like 和 instr() 函数有同样的查询效果：
select * from table where colName like ‘%helloworld%’;
select * from table where instr(colName ,‘helloworld’)>0; --这两条语句的效果是一样的
例：

八、DECODE 函数

DECODE函数是ORACLE PL/SQL的功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其它数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢？ 先构造一个例子，假设我们想给这些职员加工资，其标准是：工资在8000元以下的加20%；工资在8000元或以上的加15%，通常的做法是，先选出记录中的工资字段值 —>select salary into var-salary from employee，然后对变量var-salary用if-then-else或 case when then else end之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary*1.15) 
from employee

是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,…,else)，表示如果value等于if1时，DECODE函数的结果返回then1，……，如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能的。

1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
　　例如:
　　变量1=10，变量2=20
　　则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
　　
2、表、视图结构转化
现有一个商品销售表sale，表结构为:
　　month　　　 char(6)　　　　　 --月份
　　sell　　　　number(10,2)　　　--月销售金额

现有数据为:
　　200001　　1000
　　200002　　1100
　　200003　　1200
　　200004　　1300
　　200005　　1400
　　200006　　1500
　　200007　　1600
　　200101　　1100
　　200202　　1200
　　200301　　1300

想要转化为以下结构的数据：

year　　　 char(4)　　　　　 --年份
　　month1　　 number(10,2)　　　--1月销售金额
　　month2　　 number(10,2)　　　--2月销售金额
　　month3　　 number(10,2)　　　--3月销售金额
　　month4　　 number(10,2)　　　--4月销售金额
　　month5　　 number(10,2)　　　--5月销售金额
　　month6　　 number(10,2)　　　--6月销售金额
　　month7　 　number(10,2)　　　--7月销售金额
　　month8　 　number(10,2)　　　--8月销售金额
　　month9　　 number(10,2)　　　--9月销售金额
　　month10　 number(10,2)　　 --10月销售金额
　　month11　 number(10,2)　　 --11月销售金额
　　month12　　number(10,2)　　 --12月销售金额

结构转化的SQL语句为：

create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
　　substrb(month,1,4),
　　sum(decode(substrb(month,5,2),'01',sell,0)),
　　sum(decode(substrb(month,5,2),'02',sell,0)),
　　sum(decode(substrb(month,5,2),'03',sell,0)),
　　sum(decode(substrb(month,5,2),'04',sell,0)),

补充1：
有学生成绩表student,现在要用decode函数实现以下几个功能：成绩>85,显示优秀；>70显示良好；>60及格；否则是不及格。
假设student的编号为id，成绩为score，那么：
select id,
decode(sign(score-85),
1,‘优秀’,
0,‘优秀’,
-1, decode(sign(score-70),
1,‘良好’,
0,‘良好’,
-1, decode(sign(score-60),
1,‘及格’,
0,‘及格’,
-1,‘不及格’)))
from student；

补充2：
Decode函数的语法结构如下：

decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函数比较表达式和搜索字，如果匹配返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

九、TO_NUMBER

Oracle 中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与 TO_CHAR() 的作用正好相反。格式如下：

-- TO_NUMBER(字符串，格式）
to_number(varchar2 or char,'format model')

格式值	含义
0	强迫0显示
9	代表一个数字
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

select 
TO_NUMBER('$123,456.78','$999,999.99') a, --将字符串转成数值
to_number('123.45')  b, 
to_number('00001228') c, --将字符串转换为数字数据类型
trunc(to_number('123.123'),2) d,
to_number('0A', 'XX') e,--转换一个十六进制数的浮标
to_number(100000,'XXXXXXXX') f,--将十六进制数转换为十进制
to_number('0123') g,
trunc(to_number('0123.123'),2) h,
to_number('120.11','999.99') i,
to_number('0a','xx') j,
to_number(100000,'xxxxxx') k
from dual;

十、取整函数(ceil、floor、round)

1️⃣ceil：在英文中，是天花板的意思，有向上的意思，所以，此函数是向上取整，它返回的是大于或等于函数参数，并且与之最接近的整数。

select 
ceil(1.1) a,
ceil(1.9) b,
ceil(2.0) c,
ceil(-1.1) d,
ceil(-1.9) e,
ceil(-2.0) f 
from dual;

java代码：

Math.ceil(num))

2️⃣floor：在英文中，是地面，地板的意思，有下面的意思，所以，此函数是向下取整，它返回的是小于或等于函数参数，并且与之最接近的整数。

select 
floor(1.1) a,
floor(1.9) b,
floor(2.0) c,
floor(-1.1) d,
floor(-1.9) e,
floor(-2.0) f 
from dual;

java代码：

Math.floor(num))

3️⃣round：在英文中是有大约，环绕，在某某四周，附近的意思，所以，可以取其大约的意思，在函数中是四舍五入。

select 
round(1.1) a,
round(1.9) b,
round(2.0) c,
round(-1.1) d,
round(-1.9) e,
round(-2.0) f 
from dual;

java代码：

Math.round(num))

十一、replace 函数

oracle REPLACE 函数是用另外一个值来替代串中的某个值。比如可以用一个匹配数字来替代字母的每一次出现。REPLACE 的格式如下所示：

REPLACE(char, search_string [, replace_string])如果没有指定 replace_string 的值，那么当发现 search_string 的值时，就将其删除。输入可以为任何字符数据类型——CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB。

select 
replace('123456789','2345','a') a, --1a6789
replace('123456789','2345','') b, --16789
replace('123456789','2345') c  --16789
from dual;

银行卡号脱敏：

select
replace('123456789',SUBSTR('123456789',5,length('123456789')-8),'***') a
from dual;

十二、排序，null靠后

根据录入时间倒序排列，姓名，性别，年龄升序排列。相同条件下，年龄为null的靠后显示。

select id,lrrq,name,sex,age from tab
order by lrrq desc,name,sex,age nulls first;--优先显示null

select id,lrrq,name,sex,age from tab
order by lrrq desc,name,sex,age nulls last;--优先显示有值的数据

select id,lrrq,name,sex,age from tab
order by lrrq desc,name,sex,nvl(age,'1');

select id,lrrq,name,sex,age from tab
order by nvl(pch,'zzzzzzzzzzz');

十三、TO_DATE()

-- 字符串转换为日期
select to_date('2008-08-08 08:08:08','yyyy-mm-dd hh24:mi:ss')   a  from dual;

-- 日期转换为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; 

--分别获取时间的年 、月、日、时、分、秒
select 
to_char(sysdate,'yyyy') as nowYear, 
to_char(sysdate,'mm') as nowMonth,
to_char(sysdate,'dd') as nowDay,
to_char(sysdate,'hh24') as nowHour,
to_char(sysdate,'mi') as nowMinute,
to_char(sysdate,'ss') as nowSecond
from dual;

--求某天是星期几
select to_char(to_date('2008-08-08','yyyy-mm-dd'),'day') 
from dual;

--两个日期间的天数     
select floor(sysdate - to_date('20080808','yyyymmdd'))
from dual;

十四、Oracle 处理日期

1. 加法

select sysdate+N from dual;
sysdate+1 -- 加一天
sysdate+1/24 -- 加1小时
sysdate+1/(24*60) -- 加1分钟
sysdate+1/(24*60*60) -- 加1秒钟
-- 类推至毫秒0.001秒

--加1年
select sysdate,add_months(sysdate,12) from dual;
--加1月
select sysdate,add_months(sysdate,1) from dual;
--加1星期
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual;
--加1天
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;
--加1小时
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;
--加1分钟
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;
--加1秒
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;

2. 减法

--减1年
select sysdate,add_months(sysdate,-12) from dual;
--减1月
select sysdate,add_months(sysdate,-1) from dual;
--减1星期
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual;
--减1天
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual;
--减1小时
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual;
--减1分钟
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual;
--减1秒
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual;