ORACLE函式學習

1、字元
lower :小寫
upper :大寫
substr:取字元
Exp：SUBSTR('The Three Musketeers',1,3)
instr :返回從第三個字元開始，第二次出現i的位置
Exp：INSTR('Mississippi', 'i',3,2)
initcap：首字母大寫
length：檢視字元的長度
lengthb：檢視字元的長度按照BIT算，中文是2個BIT
ltrim：左邊消除指定的字元
lpad ：左邊補足相應的字元
rtrim ：右邊消除指定的字元到最後一個未出現的位置為止
Exp：RTRIM('Mississippi','ip')
rpad：右邊補足相應的字元，列子補足欄位table_name為38位，右邊用.補足
 RPAD(table_name,38,'.')
Replace：完全匹配的替換相應字元
Exp：REPLACE('uptown','up','down')
Translate：對對應字元進行完全轉換、
Exp：TRANSLATE('fumble','uf','aR')
Trim：去掉首尾（也可以只去掉左邊和右邊的，但是意義不大可以用ltrim和rtrim代替）的相應字元 ，列子中為去掉首尾的. ，預設為去掉首尾的空格
Exp：select length(trim(both '.'from '........woai......')),length('      woai       ') from dual;
2、數字
Abs：取絕對值
Exp：abs(-10)
ceil ：取整，向正無窮取
Exp:
SQL> select ceil(10.2),ceil(-10.2) from dual;
CEIL(10.2) CEIL(-10.2)
---------- -----------
        11         -10
Floor：取整，向負無窮取
Exp：
SQL> select floor(10.2),floor(-10.2) from dual;
FLOOR(10.2) FLOOR(-10.2)
----------- ------------
         10          -11
log 取冪
Exp:
SQL> select log(2,64) from dual;
 
 LOG(2,64)
----------
         6
mod 取餘
Exp:
SQL> select mod(35,8),mod(-49,8) from dual;
 
 MOD(35,8)     MOD(-49,8)
----------          ----------
         3         -1
power 冪集
Exp:
SQL> select power(8,3) from dual;
POWER(8,3)
----------
       512
round 四捨五入
Exp:
SQL> select round(15.555,2),round(15.554,2) from dual;
 
ROUND(15.555,2) ROUND(15.554,2)
--------------- ---------------
          15.56           15.55

trunc 截斷,不同於四捨五入
Exp：
SQL> select round(15.555,2),round(15.554,2),trunc(15.555,2),round(15.554,2) from dual;
 
ROUND(15.555,2) ROUND(15.554,2) TRUNC(15.555,2) ROUND(15.554,2)
--------------- --------------- --------------- ---------------
          15.56           15.55           15.55           15.55

3、日期
add_months：增加相應的月份
Exp：
SQL> select add_months(sysdate,1) from dual;
 
ADD_MONTHS(SYSDATE,1)
---------------------
2012/5/23 23:18:58
current_date：會話當前時間
Exp：
select sysdate,current_date,sessiontimezone from dual;
CURRENT_TIMESTAMP：會話當前時間帶上時區
Exp：
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
 
CURRENT_TIMESTAMP
-------------------------------------------------
23-APR-12 11.39.00.676507 PM +08:00
EXTRACT：抽取相應的時間部分
Exp：
 SELECT SYSDATE
         ,EXTRACT(YEAR FROM SYSDATE ) YEAR
         ,EXTRACT(MONTH FROM sysdate) MONTH
         ,EXTRACT(day FROM sysdate) DAY
   FROM dual;
last_day ：月的最後一天
Exp：
select last_day(add_months(sysdate,1)) from dual;

months_between：月份相減
Exp:
SQL> select months_between(sysdate,add_months(sysdate,1)) from dual;
 
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
                            -1
next_day:取指定日期的下一個星期幾的日期時間
Select NEXT_DAY(sysdate-40,'星期一') From dual ;

4、轉換型函式

Round：四捨五入日期時間，可以是天，也可以是小時，分
EXP：
select round(sysdate,'MI') from dual;
select round(sysdate,'HH') from dual;
select round(sysdate) from dual;

Trunc：截斷日期，可以是天，也可以是小時，分
Exp:
select trunc(sysdate,'MI') from dual;

cast:進行資料型別的轉化
Exp：
select cast(sysdate as  varchar2(100)) from dual;

NUMTODSINTERVAL：將間隔指定為時分秒間隔型別
Exp：
SELECT SYSDATE
      ,SYSDATE+NUMTODSINTERVAL(2,'HOUR') "2 hours later"
      ,SYSDATE+NUMTODSINTERVAL(30,'MINUTE') "30 minutes later"
      ,sysdate+1/12
      ,sysdate+1/48
FROM dual;

NUMTOYMINTERVAL：轉化間隔為年月型別

Exp：
SELECT SYSDATE
      ,SYSDATE+NUMTOYMINTERVAL(2,'YEAR') "2 years later"
      ,SYSDATE+NUMTOYMINTERVAL(6,'MONTH') "6 months later"
FROM dual;

To_char:轉化日期和數字型別為字元型別
Exp：
SELECT TO_CHAR(SYSDATE, 'dd" day of "Month, YYYY" Quarter="q" weekday="day') from dual;
SELECT    to_char(sysdate,'" time="hh24-mi-ss.sssss" week of month="w" week of year="www') from dual;
select      to_char(sysdate, '"day of a week="d" day of year="ddd') from dual;
SELECT SYSDATE
      ,TO_CHAR(SYSDATE,'Mmspth') Month
      ,TO_CHAR(SYSDATE,'DDth') Day
      ,TO_CHAR(SYSDATE,'Yyyysp') Year
      ,TO_CHAR(SYSDATE,'year') Year
,TO_CHAR(SYSDATE,'YEAR') Year
FROM dual;

SELECT TO_CHAR(123456,'9.99999EEEE')
      ,TO_CHAR(123456,'9.9EEEE')
FROM dual;

To_number: 轉化字元型別為數字型別
TO_DATE：字元型別轉換為日期型別
Exp：
SQL> select      to_date('day of a week=3 day of year=115', '"day of a week="d" day of year="ddd') from dual;
 
TO_DATE('DAYOFAWEEK=3DAYOFYEAR
------------------------------
2012/4/24

TO_DSINTERVAL:轉換為日期可以接受的INTERVAL DAY TO SECOND型別。

SELECT SYSDATE
      ,SYSDATE+TO_DSINTERVAL('007 12:00:00') "+7 1/2  days"
      ,SYSDATE+7.5 "+7 1/2  days"
FROM dual;

5、其他型別

Nvl：nvl(x1,x2) x1為空返回x2，不為空就返回x1
Nvl2：nvl2（x1，x2，x3）  當x1是空返回x3，不為空返回x2
Coalesce:返回第一個不為null的值
Exp：
select coalesce('','m') from dual;