ORACLE循序漸進講解（第三講）

標籤：oracle   資料庫   函數   

一、oracle常用系統函數 1、日期類函數（1）to_date(字串,格式)

SQL> select to_date('2014-10-11 12:13:14','yyyy-mm-dd hh:mi:ss') from dual;TO_DATE('2014-10-1112:13:14','------------------------------2014-10-11 12:13:14

（2）擷取當前月份的第一天。

SQL> select trunc(sysdate,'month') from dual;TRUNC(SYSDATE,'MONTH')----------------------2014-11-01

（3）擷取當前月份的最後一天

SQL> select trunc(last_day(sysdate)) from dual;TRUNC(LAST_DAY(SYSDATE))------------------------2014-11-30

（4）擷取當前年份的第一天

SQL> select trunc(sysdate,'year') from dual;TRUNC(SYSDATE,'YEAR')---------------------2014-01-01

（5）擷取當前年份的最後一天

SQL> select add_months(trunc(sysdate,'year'),12)-1 from dual;ADD_MONTHS(TRUNC(SYSDATE,'YEAR------------------------------2014-12-31

（6）取當前月份的天數

SQL> select cast(to_char(last_day(sysdate),'dd') as int) from dual;         CAST(TO_CHAR(LAST_DAY(SYSDATE)---------------------------------------                                     30

（7）取當前月份剩下的天數

SQL> select last_day(sysdate)-sysdate from dual;LAST_DAY(SYSDATE)-SYSDATE-------------------------                        8

（8）取當前日期的下一個星期日的日期（返回下個星期的日期,day為1-7或星期日-星期六,1表示星期日）

SQL> select next_day(sysdate,1) from dual;NEXT_DAY(SYSDATE,1)-------------------2014-11-23 17:39:48

（9）取兩個日期相差月份

<pre name="code" class="sql" style="font-size: 14px;">SQL> <span style="font-family: Arial, Helvetica, sans-serif;">select months_between(to_date('2014-11-01','yyyy-mm-dd'),to_date('2014-01-01','yyyy-mm-dd')) from dual;</span>

MONTHS_BETWEEN(TO_DATE(‘2014-1------------------------------ 10

2、字串類函數（1）to_char(字串,格式) 

SQL> select to_char(sysdate,'yyyy-mm') from dual;TO_CHAR(SYSDATE,'YYYY-MM')--------------------------2014-11

（2）upper和lower 大小寫轉化、length長度、串連符||

SQL> select upper('a') || lower('B') || length('aaa') from dual;UPPER('A')||LOWER('B')||LENGTH------------------------------Ab3

（3）LPAD和RPAD，填充函數，在左邊或右邊添加指定字元使長度達到指定的長度。

SQL> select lpad('aaa',5,'*'),rpad('aaa',5,'*') from dual;LPAD('AAA',5,'*') RPAD('AAA',5,'*')----------------- -----------------**aaa             aaa**

（4）LTRIM,RTRIM和TRIM,他們的作用是從字串的左邊，右邊，左右兩邊刪除不需要的字元，預設時刪除空格

SQL> select ltrim('abbb','a'),rtrim('1234','4'),trim(' ABC ') from dual;LTRIM('ABBB','A') RTRIM('1234','4') TRIM('ABC')----------------- ----------------- -----------bbb               123               ABC

（5）SUBSTR(string,start [,count]) 該函數告訴oracle提取出string一個子集，start開始位置，count提取長度，不指定count時，預設提取到該字串的尾部。

SQL> select substr('abcde',1,2) from dual;SUBSTR('ABCDE',1,2)-------------------ab

（6）INSTR 返回指定的字串所在的位置
  INSTR(string,set[,start [,occurrence ] ] ) 如果指定start，oracle則跳過前面所有字串到該位置開始搜尋，occurence，是強迫instr跳過前幾次與字串匹配，給出下一次匹配的位置，如果occurence指定3,那就是匹配第三次的位置了。

SQL> select instr('abcad','a',1),instr('abcad','a',2,1) from dual;INSTR('ABCAD','A',1) INSTR('ABCAD','A',2,1)-------------------- ----------------------                   1                      4

3、數學類函數
（1）round(n,[m]) 用於執行四捨五入，如果省去m則四捨五入到整數，如果m為正數則四捨五入到小數點m位後，如果m為負數則四捨五入到小數點m位前。

SQL> select round(12.12),round(12.12,1),round(12.12,-1) from dual;ROUND(12.12) ROUND(12.12,1) ROUND(12.12,-1)------------ -------------- ---------------          12           12.1              10

（2）trunc(n,[m]) 用於截取數字，如果省掉m就截取小數部分，如果m為正數則截取到小數點m位後，如果m為負數則截取到小數點前m位。

SQL> select trunc(12.12),trunc(12.12,1),trunc(12.12,-1) from dual;TRUNC(12.12) TRUNC(12.12,1) TRUNC(12.12,-1)------------ -------------- ---------------          12           12.1              10

（3）MOD(x,y) 返回x除以y的餘數、CEIL(x)返回大於或等於x的最小整數、FLOOR(x)返回小於或等於x的最大整數。

SQL> select mod(1,3),ceil(12.56),floor(12.56) from dual;  MOD(1,3) CEIL(12.56) FLOOR(12.56)---------- ----------- ------------         1          13           12

4、系統函數sys_context
（1）terminal 當前會話客戶所對應的終端標識符。（2）lanuage:語言。（3）db_name：當前資料庫名稱。（4）nls_date_format：當前會話客戶對應的日期格式。（5）session_user：當前會話客戶所對應的資料庫使用者名稱。（6）current_schema：當前會話客戶所對應的預設方案名。（7）host：返回資料庫所在的主機名稱。

SQL> select sys_context('userenv','db_name') dbName,sys_context('userenv','nls_date_format') date_fromat from dual;DBNAME                                                                           DATE_FROMAT-------------------------------------------------------------------------------- --------------------------------------------------------------------------------orcl                                                                             DD-MON-RR 

ORACLE循序漸進講解（第三講）