首页手记 Oracle中的一些函数

Oracle中的一些函数

标签：

Oracle

一、比较大小函数 sign

函数语法：
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)

函数语法：
NVL(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）

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,默认截去小数点后面的部分;

点击查看更多内容