Oracle常见函数集锦

1、NVL函数
oracle中很有用的一个函数，以前用过今天用的时候没想起来，结果可想而知——浪费时间呗，好了，闲话少说，步入正题。

标准解释
NVL(expr1,expr2)
如果expr1是NULL,则返回expr2,否则返回expr1.返回值与expr1类型相同,除非expr1是字符串类,在这种情况下将返回VARCHAR2类型.这个函数用于确保查询记录集中不包含NULL值.

类似的还有
NVL(expr1,expr2,expr3)
如果expr1是NULL,则返回expr2,否则返回expr3.返回值与expr2类型相同,除非expr2是字符类型,在这种情况下返回VARCHAR2类型。

今天我用的例子举个实例吧。

--从两个表中更新数据

--描述如下

--a：a1，a2，a3，……

--b：b1，b2，b3，……

--a.a1,a.a2与b.b1,b,b2对应的字段属性一样，存在对应关系，现在a.a2缺值要来自b.b2，

--由于表a的a2的约束条件是not null，第一次写的时候也没留意，导致一直更新不了，

--第一次代码：

--update   a   set  a.a2=(select b.b2 from b where a.a1=b.b1);

--死活更新不了，后来琢磨半天，原来a2有约束不能为null，使用nvl更新后的代码如下：

--update   a   set  a.a2=nvl((select b.b2 from b where a.a1=b.b1)，a.a2);

--ok顺利更新！

2、TRUNC()函数：
   --Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确
9.select trunc(sysdate, 'q') from dual --返回当前季度的第一天
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual  --123.458
15.select trunc(123) from dual  --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

http://database.51cto.com/art/201004/197703.htm

3、ROUND()函数：
Round( ) 函数

传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果

语法
ROUND( number, decimal_places )

number : 需四舍五入处理的数值
decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )

返回值类型

数字

例子
select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
select round(123.456, 3) from dual; 回传 123.456
select round(-123.456, 2) from dual; 回传 -123.46

4、DECODE()函数：
语法如下：
　　DECODE(control_value,value1,result1[,value2,result2…][,default_result]);
　　control _value
　　试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。
　　value1
　　是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
　　result1
　　是一组成序偶的结果值。
　　default_result 未能与任何一个值匹配时，函数返回的默认值。
　　示例如下：
　　select decode( x , 1 , ‘x is 1 ’, 2 , ‘x is 2 ’, ‘others’) from dual
　　当x等于1时，则返回‘x is 1’。
　　当x等于2时，则返回‘x is 2’。
　　否则，返回others’。
　　在需要比较2个值的时候，我们可以配合SIGN()函数一起使用。
　　SELECT DECODE( SIGN(5 -6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero')

另外，大家还可以在Order by中使用Decode。

　　例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。

　　select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

使用方法：

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”，达到了取较小值的目的。

5、LAST_DAY()函数：
  LAST_DAY函数返回指定日期对应月份的最后一天。
例：last_day(to_date('1999.11.29','yyyy.mm.dd'))       返回   1999年11月31日
例：last_day(to_date('2000.02.01','yyyy.mm.dd'))       返回   2000年2月29日

6、ADD_MONTHS()函数：
使用oracle add_months函数，就可以以得到某一时间之前或之后n个月的时间，如果您之前遇到过类似方面到的问题，不妨一看。
如果需要取上一个月的数据，并且每天都要进行此操作，每次都需要改时间，的确非常的麻烦，所以想到了oracle add_months函数这个函数
oracle add_months函数：
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间

如 select add_months(sysdate,-6) from dual;
该查询的结果是当前时间半年前的时间

select add_months(sysdate,6) from dual;
该查询的结果是当前时间半年后的时间