Oracle基础知识（十七） - 转换函数

这里简单记录几个小问题，好多都不知道呢：

1. to_char

1.1 0(零).在对应位置返回对应的字符,如果没有则以'0'填充.

to_char(0.34,'9,9,9,0.$99')='$0.34';to_char(1234,'9999.00')='1234.00';

注意事项:这是一个强制的符号,对应位没有,则以'o'填充,这是9很大不同地方

1.2 9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符.

to_char(123,'999.99')=123.00; TO_CHAR(123,'99999.9')=123.0;

注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回'#'.

譬如 to_char(12345,'9999')='#####' 

2. to_date

参考网址：http://database.51cto.com/art/201010/231193.htm

在使用Oracle的to_date函数来做日期转换时，很多Java程序员也许会直接的采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，

但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。

原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。

要以24小时的形式显示出来要用HH24

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

3. decode

参考网址：http://knowyouknowme.iteye.com/blog/574974

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(返回值)
END IF

如果没有定义缺省值，会返回空

例子：比较大小(结合sign函数)

select decode(sign(98-100) , 0,'相等', 1 ,'大于','小于') from dual;

例子：确定数字范围(结合trunc函数)

写一个decode函数，要求如下:

如果 yrs_of_service < 1 返回 0.04

如果 yrs_of_service >= 1 and < 5 返回0.04

如果 yrs_of_service > 5 返回 0.06

select emp_name,decode(trunc((yrs_of_service+3)/4),0,0.04,1,0.04,0.06) as perc_value from employees;

3.5 sign()

sign(number)

number 要测试标志的数字.

If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.

4. months_between

参考网址：http://book.51cto.com/art/200812/103807.htm

MONTHS_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那么MONTHS_BETWEEN()就返回一个负数。

注意：

在调用MONTHS_BETWEEN函数时，日期的次序非常重要：如果想让结果为正数，稍晚的时间必须出现在前面。

下面这个例子显示了2008年5月25日和2008年1月15日之间相差的月数。注意由于第一个参数是稍后的日期(2005年5月25日)，因此所返回的结果是正数：

select months_between(to_date('20131201','yyyymmdd') , to_date('20131109','yyyymmdd')) from dual;

select 
       months_between(to_date('20131109','yyyymmdd') , to_date('20131201','yyyymmdd')) 
from dual;

5. 小技巧汇总