ORACLE日期函数01(3)

正可负，当然也有可能为0 例如：

SELECT months_between(SYSDATE, sysdate),

months_between(SYSDATE, add_months(sysdate, -1)), months_between(SYSDATE, add_months(sysdate, 1)) FROM DUAL;

7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。 例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;

8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。 例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;

(五).转换函数(Conversion Functions)

转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

1、TO_CHAR() 本函数又可以分三小类，分别是

? 转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型； 例如：SELECT TO_CHAR('AABBCC') FROM DUAL;

? 转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；

例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

? 转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；

例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。 例如：

SELECT TO_DATE(2454336, 'J') FROM DUAL;

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

为什么公元制的话，c的值必须不大于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句： SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;

3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。

例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;

(六).其它辅助函数(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。

exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。

毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：select decode('a2','a1','true1','a2','true2','default') from dual;

2、GREATEST(n1,n2,...n) 返回序列中的最大值

例如：SELECT GREATEST(15,5,75,8) \

3、LEAST(n1,n2....n) 返回序列中的最小值

例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

4、NULLIF(c1,c2)

Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END

例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。 例如：SELECT NVL(null, '12') FROM DUAL;

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3

例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;

7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。 例如：

create table tmp3( rootcol varchar2(10), nodecol varchar2(10) );

insert into tmp3 values ('','a001'); insert into tmp3 values ('','b001');

insert into tmp3 values ('a001','a002'); insert into tmp3 values ('a002','a004');

insert into tmp3 values ('a001','a003'); insert into tmp3 values ('a003','a005'); insert into tmp3 values ('a005','a008'); insert into tmp3 values ('b001','b003'); insert into tmp3 values ('b003','b005');

select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/') from tmp3

start with rootcol = 'a001'

connect by prior nodecol =rootcol; 8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。

Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数：

? CURRENT_SCHEMA:当前模式名； ? CURRENT_USER:当前用户；

? IP_ADDRESS:当前客户端IP地址； ? OS_USER:当前客户端操作系统用户；

等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents

例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。

单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。

(一).数值型函数(Number Functions)

数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。

1、ABS(n) 返回数字的绝对值

例如：SELECT ABS(-1000000.01) FROM DUAL;

2、COS(n) 返回n的余弦值

例如：SELECT COS(-2) FROM DUAL;

3、ACOS(n) 反余弦函数，n between -1 and 1，返回值between 0 and pi。 例如：SELECT ACOS(0.9) FROM DUAL;

4、BITAND(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下： 假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。

例如：SELECT BITAND(3,9) FROM DUAL;

5、CEIL(n) 返回大于或等于n的最小的整数值 例如：SELECT ceil(18.2) FROM DUAL; 考你一下，猜猜ceil(-18.2)的值会是什么呢

6、FLOOR(n) 返回小于等于n的最大整数值 例如：SELECT FLOOR(2.2) FROM DUAL; 再猜猜floor(-2.2)的值会是什么呢

7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制

例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;

8、SIN(n) 返回n的正玄值,n为弧度。 例如：SELECT SIN(10) FROM DUAL;

9、SINH(n) 返回n的双曲正玄值,n为弧度。 例如：SELECT SINH(10) FROM DUAL;

10、ASIN(n) 反正玄函数，n between -1 and 1，返回值between pi/2 and -pi/2。 例如：SELECT ASIN(0.8) FROM DUAL;

11、TAN(n) 返回n的正切值,n为弧度 例如：SELECT TAN(0.8) FROM DUAL;

12、TANH(n) 返回n的双曲正切值,n为弧度 例如：SELECT TANH(0.8) FROM DUAL;

13、ATAN(n) 反正切函数，n表示弧度，返回值between pi/2 and -pi/2。 例如：SELECT ATAN(-444444.9999999) FROM DUAL;

14、EXP(n) 返回e的n次幂，e = 2.71828183 ... 例如：SELECT EXP(3) FROM DUAL;

15、LN(n) 返回n的自然对数，n>0 例如：SELECT LN(0.9) FROM DUAL;

16、LOG(n1,n2) 返回以n1为底n2的对数，n1 >0 and not 1 ，n2>0 例如：SELECT LOG(1.1,2.2) FROM DUAL;

17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n必须为整数

例如：SELECT POWER(2.2,2.2) FROM DUAL;