Oracle常用函数

1.  日期函数

(1) months_between(date1, date2)：算date1和date2之间的月的数量,可以是小数可以是负数

比如：months_between(‘01-sep-95’,’11-jan-94’) = 1.9774194

(2) add_months(date, n)：为date加上N个月，N只可以是整数

(3) next_date(date,’char’)：查找date的下一个星期N，

比如：next_date(‘01-sep-95’,’FRIDAY’)=08-SEP-95

(4) last_day(date): 查找date月的最后一天

(5) rount(date)：把日期四舍五入

比如：

rount(25-MAY-95’,’MONTH’)=01-JUN-95

rount(25-MAY-95’,’YEAR’)=01-JAN-95

(6) trunc(date)：把日期截断

比如：

trunc (25-MAY-95’,’MONTH’)=01-MAY-95

trunc (25-MAY-95’,’YEAR’)=01-JAN-95

(7) TO_DATE函数的小问题

当省略HH、MI和SS对应的输入参数时，Oracle使用0作为DEFAULT值。如果输入的日期数据忽略时间部分，Oracle会将时、分、秒部分都置为0，也就是说会取整到日。同样，忽略了DD参数，Oracle会采用1作为日的默认值，也就是说会取整到月。但是，不要被这种“惯性”所迷惑，如果忽略MM参数，Oracle并不会取整到年，取整到当前月。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

2006-05-01 19:25:34

SQL> SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL;

2006-05-01 19:25:00

SQL> SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL;

2006-05-01 19:00:00

SQL> SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL;

2006-05-01 00:00:00

SQL> SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL;

2006-05-01 00:00:00

SQL> SELECT TO_DATE('2006', 'YYYY') FROM DUAL;

2006-05-01 00:00:00

 

2.  数学函数

(1) round:四舍五入

比如：

round(2.566,2)=2.57

round(45,-1)=50

(2) trunc: 截断

比如：

trunc(2.566,2)=2.56

trunc(45,-1)=40

(3)    取整函数

1)   取整(大)

select ceil(-1.001) value from dual

2)   取整（小）

select floor(-1.001) value from dual

3)   取整（截取）

select trunc(-1.002) value from dual

4)   取整(舍入) 

select round(-1.001) value from dual

 

(4) ABS()：返回n的绝对值

(5) ACOS()：反余玄函数，返回-1到1之间的数

(6) ASIN()：反正玄函数，返回-1到1，n表示弧度

(7) ATAN()：反正切函数，返回n的反正切值，n表示弧度。

(8) CEIL()：返回大于或等于n的最小整数。

(9) COS()：返回n的余玄值，n为弧度

(10)      COSH()：返回n的双曲余玄值，n 为数字。

(11)      EXP()：返回e的n次幂，e=2.71828183.

(12)      FLOOR()：返回小于等于N的最大整数。

(13)      LN()：返回N的自然对数，N必须大于0

(14)      LOG(,)：返回以n1为底n2的对数

(15)      MOD()：返回n1除以n2的余数，

(16)      POWER(,)：返回n1的n2次方

(17)      ROUND(,)：返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。

(18)      SIN（)：返回n的正玄值,n为弧度。

(19)      SINH()：返回n的双曲正玄值,n为弧度。

(20)      SQRT()：返回n的平方根,n为弧度

(21)      TAN（) ：返回n的正切值,n为弧度

(22)      TANH()：返回n的双曲正切值,n为弧度

(23)      TRUNC(,)：返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。

 

3.  字符函数

(1) lower: 把字符转成小写

(2) upper: 把字符转成大写

(3) initcap: 把单词的第一个字母变成大写

(4) concat: 连接字符

concat(‘good’,’morning’) = goodmoring

(5) SUBSTR (column\expression, m[,n]) 用于对字符串进行截取操作，从第m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算

比如：

substr(‘string’,1,3) = str

substr(‘string’-3,3) = ing

(6) INSTR('String', 'r') = 3：返回子字符串所在字符串的索引，没有找到返回0

(7) length: 用于返回表达式中的字符数，注意返回的是NUMBER。

(8) NVL(expression1, expression2) ：NVL 函数用以把一个空值转换为一个实值,

如：NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0，如果两个字段类型不同必须进行转换。

(9) Min(): 返回最小值，如果是字符则A<a

(10) ASCII(c1)：c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR(i)

(11)  CHR(i)：i是一个数字，函数返回十进制表示的字符

(12)  LPAD(c1,i, c2)：c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符（即截取c1右边的字符）。c2的缺省值为单空格。

(13)  RPAD(c1,i, c2)：在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格。

(14)  LTRIM(c1, c2)：把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变（取出c1中的第一个字符，如果在c2中存在则去掉，然后再取出剩余的c1中的第一个字符作类似操作，如此循环，直到c1的第一个字符在c2中不存在）。比如：Select ltrim('abcdefg', 'aefgb') From dual 其结果为：cdefg

(15)  RTRIM(c1, c2)：与ltrim类似，只是把c1中最右边的字符去掉，使其第后一个字符不在c2中。

(16)  TRIM(c)：将c串中前后的空格都删除。

(17)  REPLACE(c1,c2,c3)：c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。

(18)  TRANSLATE(c1,c2,c3)：将c1中与c2相同的字符以c3代替。

 

4.  转换函数

(1) TO_CHAR：

TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0

TO_CHAR(total,’fm$999999’)

如果想转成$0.25,那就要写成fm$9999990.99

可以把日期转换成字符

TO_CHAR(log_time,’MM/YY’)

TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)

具体格式如下

HH24:MI:SS AM-----------15:24:32 pm

DD’’of’’MONTH-------12 of MAY

Ddspth------------------fourteenth

Ddsp--------------------fourteen

ddth--------------------4^th

YYYY--------------------1978

MM----------------------12

MONTH-------------------MAY