Oracle 12個精簡常用函式

(1)trunc(for date)

這裡注意 to_date date是日期值 所以轉換後是不含有時間

    TRUNC(for date) TRUNC函式為指定元素而截去的日期值。

　　其具體的語法格式如下：　TRUNC（date[,fmt]）

　　其中：date一個日期值，fmt日期格式，該日期將由指定的元素格式所截去。忽略它則由最近的日期截去

下面是該函式的使用情況：（mi minute 分鐘的意思）

  select trunc(to_date('2012-03-23 23:59:59','yyyy-mm-dd hh24:mi:ss')) from dual 

系統定義表,只有一筆記錄.可以用來返回函式值. select user from dual;返回當前使用者. dual為了完善語義，因為沒有from oracle中是報錯的

-- return date : 2012-3-23

trunc(sysdate,'yyyy') --返回當年第一天.

trunc(sysdate,'mm') --返回當月第一天.

trunc(sysdate,'d') --返回當前星期的第一天(如2012-07-11所在星期的第一天是2012-07-08)

思考：？如何獲取當月的最後一天(提示：根據系統時間)？

 

(2)trunc(number)

　　TRUNC函式返回處理後的數值，其工作機制與ROUND函式極為類似，只是該函式不對指定小數前或後的部分做相應舍入選擇處理，而統統截去。

　　其具體的語法格式如下TRUNC（number[,decimals]）

　　其中：number待做擷取處理的數值，decimals指明需保留小數點後面的位數。可選項，忽略它則截去所有的小數部分

　　下面是該函式的使用情況：

　　TRUNC（89.985，2）=89.98

　　TRUNC（89.985）=89

　　TRUNC（89.985，-1）=80

　　注意：第二個引數可以為負數，表示為小數點左邊指定位數後面的部分截去，即均以0記。與取整類似，比如引數為1即取整到十分位，如果是-1，則是取整到十位，以此類推；如果所設定的引數為負數，且負數的位數大於整數的位元組數的話，則返回為0。如：TRUNC(89.985,-3)=0.

 

(3)to_char

把日期或數字轉換為字串;to_date是把字串轉換為資料庫中得日期型別

使用TO_CHAR函式處理數字：TO_CHAR(number, '格式')；TO_CHAR(salary,’$99,999.99’);

使用TO_CHAR函式處理日期：  TO_CHAR(date,’格式’);

 

(4)to_date

使用TO_DATE函式將字元轉換為日期：TO_DATE(char[, '格式'])

e.g   select to_date('2011-11-5 4:39:57','yyyy-mm-dd hh24:mi ss') as col from dual

 

(5)to_number

使用TO_NUMBER函式將字元轉換為數字：TO_NUMBER(char[, '格式'])

各種格式：

數字格式格式	日期格式
9代表一個數字 0強制顯示0 $放置一個$符 L放置一個浮動      本地貨幣符 .顯示小數點 ,顯示千位指示符	格式控制描述 YYYY、YYY、YY分別代表4位、3位、2位的數字年，YEAR年的拼寫 MM數字月，MONTH月的全拼，MON月的縮寫 DD數字日，DAY星期的全拼，DY星期的縮寫，AM表示上午或者下午 HH24、HH12 12小時制或24小時制，MI分鐘，SS秒鐘 SP數字的拼寫，TH數字的序數詞 “特殊字元”假如特殊字元 HH24:MI:SS AM 15:43:20 PM DD “OF” MONTH 12 OF OCTOBER DDSPTH fourteenth Date的格式 ’18-5月-84’

 

(6)instr

INSTR方法的格式為：INSTR(源字串,目標字串,起始位置,匹配序號)

查詢是否匹配目標字串

例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字串為'CORPORATE FLOOR',目標字串為'OR'，起始位置為3，取第2個匹配項的位置。

預設查詢順序為從左到右。當起始位置為負數的時候，從右邊開始查詢。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1)  "Instring" FROM DUAL的顯示結果是：14

 別名Instring

 

(7)substr

取得字串中指定起始位置和長度的字串substr( string, start_position, [ length ] )

如:

substr('This is a test', 6, 2)       return 'is'

substr('This is a test', 6)         return 'is a test'

substr('TechOnTheNet', -3, 3)   return 'Net'

substr('TechOnTheNet', -6, 3)    return 'The'select substr('Thisisatest', -4, 2) value from dual

舉個例子更容易區分這兩個函式：

 

(8)trim

Oracle中的trim函式是用來刪除給定字串或者給定數字中的頭部或者尾部的給定字元。

trim函式具有如下的形式trim([leading/trailing/both][匹配字串或數值][from][需要被處理的字串或數值])這裡如果

①   指明瞭leading表示從刪除頭部匹配的字串

②   如果指明瞭trailing表示從刪除尾部匹配的字串

③   如果指明瞭both，或者不指明任何位置，則兩端都將被刪除

④如果不指明任何匹配字串或數值則認為是空格，即刪除前面或者後面的空格。

trim函式返回的型別是varchar2下面是一些例子：

各種情況	例子	結果
指明leading表示從刪除頭部匹配的字串	select trim(leading '1' from '12321Tech11') from dual;	2321Tech11
指明trailing表示從刪除尾部匹配的字串	select trim(trailing '1' from '12321Tech11') from dual;	12321Tech
如果指明瞭both，或者不指明任何位置，則兩端都將被刪除	select trim(‘中’from‘中秋八月中‘) as詩from dual;    select trim(both '1' from '12321Tech 111') from dual;	秋八月 2321Tech
如果不指明任何匹配字串或數值則認為是空格，即刪除前面或者後面的空格	select trim('   tech   ')  as詩from dual; select trim(' '  from  '   tech   ')  as詩from dual; select trim(0 from 7500) from dual;	tech tech 75

 LTRIM, RTRIM 不但可以去掉空格，還可以去掉指定字元, 如LTRIM(',aaaa',',')

 ltrim  rtrim

 

(9)translate

 語法：TRANSLATE(char, from, to)  

 用法：返回將出現在from中的每個字元替換為to中的相應字元以後的字串。

 若from比to字串長，那麼在from中比to中多出的字元將會被刪除。

三個引數中有一個是空，返回值也將是空值。

 舉例：SQL> select translate('abcdefga','abc','wo')返回值from dual;

返回值------- wodefgw 

 分析：該語句要將'abcdefga'中的'abc'轉換為'wo',

由於'abc'中'a'對應'wo'中的'w',故將'abcdefga'中的'a'全部轉換成'w';

而'abc'中'b'對應'wo'中的'o',故將'abcdefga'中的'b'全部轉換成'o';

而'abc'中的'c'在'wo'中沒有與之對應的字元,故將'abcdefga'中的'c'全部刪除;

簡單說來，就是將from中的字元轉換為to中與之位置對應的字元，若to中找不到與之對應的字元，返回值中的該字元將會被刪除。

在實際的業務中，可以用來刪除一些異常資料，比如表a中的一個欄位t_no表示電話號碼，而電話號碼本身應該是一個由數字組成的字串，

為了刪除那些含有非數字的異常資料，就用到了translate函式：

SQL> delete from a，  where length(translate(trim(a.t_no), '0123456789' || a.t_no,                                             '0123456789')) <> length(trim(a.t_no));

 

(10)replace

 語法：REPLACE(char, search_string,replacement_string)

 用法：將char中的字串search_string全部轉換為字串replacement_string，沒有匹配的字串就都不變。       

 舉例：

SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') from dual;返回值from dual;

結果是fgsgswsgs

       SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt')返回值from dual;

結果是fgeeerrrtttsweeerrrttts

分析：第一個例子中由於'fgsgswsgs'中沒有與'fk'匹配的字串，故返回值仍然是'fgsgswsgs'；

第二個例子中將'fgsgswsgs'中的字串'sg'全部轉換為'eeerrrttt'。

總結：綜上所述，replace與translate都是替代函式，只不過replace針對的是字串，而translate針對的是單個字元。

 

(11)decode()

DECODE函式，它將輸入數值與函式中的引數列表相比較，根據輸入值返回一個對應值。函式的引數列表是由若干數值及其對應結果值組成的若干序偶形式。當然，如果未能與任何一個實參序偶匹配成功，則函式也有預設的返回值。

區別於SQL的其它函式，DECODE函式還能識別和操作空值。 

語法：DECODE(control_value,value1,result1[,value2,result2…][,default_result]); 

試圖處理的數值。DECODE函式將該數值與後面的一系列的偶序相比較，以決定返回值。

value1   是一組成序偶的數值。如果輸入數值與之匹配成功，則相應的結果將被返回。對應一個空的返回值，可以使用關鍵字NULL於之對應。

result1  是一組成序偶的結果值。

default_result  未能與任何一個值匹配時，函式返回的預設值。 

例如：

select decode(x,1,'x is 1',2,'x is 2','others') from dual

當x等於1時，則返回‘x is 1’。

當x等於2時，則返回‘x is 2’。

否則，返回others’。 

需要，比較2個值的時候，可以配合sign()函式一起使用。

SELECT DECODE( SIGN(5-6), 1 'Is Positive', -1, 'Is Nagative', 'Is Zero')from dual；

同樣，也可以用CASE實現：

SELECT CASE SIGN(5-6) 

   WHEN  1  THEN  'Is Positive'

   WHEN　-1 THEN  'Is Nagative'

   ELSE　'Is Zero' END

FROM DUAL 

此外，還可以在Order by中使用Decode。

例如：表subject，有subject_name列。要求按照：語、數、外的順序進行排序。這時，就可以非常輕鬆的使用Decode完成要求了。

select * from subject order by decode(subject_name, '語文',1, '數學',2, '外語',3) 

(12)nvl

nvl( ) 函式(類似於SQLSERVER的isnull)

語法:  1.     NVL(eExpression1, eExpression2)

引數:  1.     eExpression1, eExpression2

如果eExpression1的計算結果為null值，則NVL( )返回eExpression2。

如果eExpression1的計算結果不是null值，則返回eExpression1。

eExpression1和eExpression2可以是任意一種資料型別。

如果eExpression1與eExpression2的結果皆為null值，則NVL( )返回.NULL.。

1.    select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID 

注意：兩個引數得型別要匹配

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28982975/viewspace-1097637/，如需轉載，請註明出處，否則將追究法律責任。