一、比较大小函数 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值，则返回空值。