Oracle資料庫實用函數

實用函數

DECODE

文法如下：　　

DECODE(value, if1, then1, if2,then2,if3,then3, . . . else )  Value 代表某個表的任何類型的任意列或一個通過計算所得的任何結果。當每個value值被測試，如果value的值為if1，Decode 函數的結果是then1；如果value等於if2，Decode函數結果是then2；等等。事實上，可以給出多個if/then 配對。如果value結果不等於給出的任何配對時，Decode 結果就返回else 。　　需要注意的是，這裡的if、then及else 都可以是函數或計算運算式。

執行個體

decode(tr.PERIOD_NUM,1,'P01',2,'P02',3,'P03','Err')

 

INSTR

返回要尋找字元的位置

 

CASEWHEN

詳見http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

--簡單Case函數 

CASE sex 

WHEN '1' THEN '男' 

WHEN '2' THEN '女' 

ELSE '其他' END 

--Case搜尋函數 

CASE

WHEN sex = '1' THEN '男' 

WHEN sex = '2' THEN '女' 

ELSE '其他' END

 

decode和case when區別？

decode只能做等值的，case when可以做區間的，使用範圍來進行條件區分，decode能做到的，case when也能做到；效率方面，個人覺得兩者效率差不多；但decode的文法要簡單些，但它只能做等值的比較；case when end 能做條件的判斷。

http://blog.sina.com.cn/s/blog_6da521f90100tcm4.html

 

SUBSTR

substr() 函數返回字串的一部分。

文法：substr(string,start,length)

string - 指定的要截取的字串。

start - 必需，規定在字串的何處開始。

正數 - 在字串的指定位置開始

負數 - 在從字串結尾的指定位置開始

0 - 在字串中的第一個字元處開始

length - 可選，指定要截取的字串長度，預設時返回字元運算式的值結束前的全部字元。

例如：select substr('abcdefg',3,4) from dual;結果是cdef

select substr('abcdefg',-3,4) from dual;結果efg

 

OracleRegex函數：

regexp_like、regexp_substr、regexp_instr、regexp_replace

 

REPLACE

replace(x,y,z)返回值為將串X中的Y串用Z串替換後的結果字串。若省略Z參數，則將串X中為Y串的地方刪除

SELECT REPLACE('JACK and JUE','J','BL')  "Changes"  FROM DUAL;

Changes

--------------

BLACK and BLUE 

例子

有一個列匯入的資料應該時‘2011-10-11’的格式，結果匯入的資料為‘2011/10/11’格式的，5000多條記錄要一條條改基本不可能。 後來想到了replace這個函數，具體用法如下：

update 表1  t  set  t.列1=replace((select 列1 from 表1  a where a.主鍵列=t.主鍵列) ,'/' , '-' )  解決了我們問題。

replace 函數用法如下：

replace（'將要更改的字串','被替換掉的字串','替換字串'）

 

select"PARENTNAME", "MEMBERNAME", "ALIAS"

  from (select'Product' parentname,

               'ProductTotal' membername,

               '產品彙總'alias

          from dual)

 

CONNECT BY

Oracle “CONNECT BY”是層次查詢子句，一般用於樹狀或者層次結果集的查詢。其文法是：

[ START WITH condition ]

 

CONNECTBY [ NOCYCLE ] condition

1．  可用來產生序列

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM<= 10;

2.可用來實現樹狀查詢

CONNECT BY rownum <= length(p_str))對輸入的字串進行逐個遍曆

selectrownumfrom dualCONNECTBYrownum <=length('qwertyui')

 

詳見http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

 

RPAD

rpad函數從右邊對字串使用指定的字元進行填充 　　rpad(string,padded_length,[pad_string])　　

string 表示：被填充的字串 　　

padded_length 表示：字元的長度，是返回的字串的數量，如果這個數量比原字串的長度要短，rpad函數將會把字串截取成從左至右的n個字元; 　　

pad_string 是個選擇性參數，這個字串是要粘貼到string的右邊，如果這個參數未寫，lpad函數將會在string的右邊粘貼空格。 　　

例如：　　

rpad('tech', 7); 將返回'tech ' 　　

rpad('tech', 2); 將返回'te' 　　

rpad('tech', 8, '0'); 將返回'tech0000'　　

rpad('tech on the net', 15, 'z'); 將返回 'tech onthe net' 　　

rpad('tech on the net', 16, 'z'); 將返回 'tech onthe netz'

 

TRUNC

TRUNC函數返回以指定元素格式截去一部分的日期值。

--Oracle trunc()函數的用法
/**************日期********************/
1.select trunc(sysdate) from dual  --2011-3-18 今天的日期為2011-3-18
2.select trunc(sysdate, 'mm')   from  dual  --2011-3-1   返回當月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1      返回當年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18   返回當前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1  返回當年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00  目前時間為14:41  
8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函數沒有秒的精確
/***************數字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的數字。
Num_digits 用於指定取整精度的數字。Num_digits的預設值為 0。
TRUNC()函數截取時不進行四捨五入
*/
9.select trunc(123.458) from dual--123
10.select trunc(123.458,0) from dual--123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual--120
13.select trunc(123.458,-4) from dual--0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual--123
17.select trunc(123,-1) from dual--120

 

一下語句會經常用到，相當於不用建立表而可以插入幾條資料

Select ‘Jan’ membername,’1月’ name from dual union select ‘Feb’ membername,’2月’ name from dual;

 

ADD_MONTHS

ADD_MONTHS(d,n)--時間點d再加上n個月

 

有兩個簡單例子，以說明"exists"和"in"的效率問題

1) select * from T1 where exists(select 1from T2 where T1.a=T2.a) ;

　　T1資料量小而T2資料量非常大時，T1<<T2 時，1) 的查詢效率高。

2) select * from T1 where T1.a in (selectT2.a from T2) ;

T1資料量非常大而T2資料量小時，T1>>T2時，2) 的查詢效率高。

 

In適合內外表都很大的情況，exists適合外表結果集很小的情況

 

過使用EXISTS，Oracle會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配項，這就節省了時間。

Oracle在執行IN子查詢時，首先執行子查詢，並將獲得的結果清單存放在一個加了索引的暫存資料表中。

在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在暫存資料表中以後再執行主查詢。

這也就是使用EXISTS比使用IN通常查詢速度快的原因。

 

OVER函數的作用及用法

RANK ( ) OVER ( [query_partition_clause]order_by_clause )

DENSE_RANK ( ) OVER ([query_partition_clause] order_by_clause )

可實現按指定的欄位分組排序，對於相同分組欄位的結果集進行排序,

其中PARTITION BY 為分組欄位，ORDER BY 指定排序欄位。

 

over不能單獨使用，要和分析函數：rank(),dense_rank(),row_number()等一起使用。
其參數：over（partition by columnname1 order by columnname2）
含義：按columname1指定的欄位進行分組排序，或者說按欄位columnname1的值進行分組排序。
例如：employees表中，有兩個部門的記錄：department_id ＝10和20
select department_id，rank（） over（partition by department_idorder by salary) from employees就是指在部門10中進行薪水的排名，在部門20中進行薪水排名。如果是partition by org_id，則是在整個公司內進行排名。

 

TO_CHAR TO_DATE

to_char和to_date經驗分享

㈠比較下面兩種寫法：

to_char(start_time,'yyyy-mm-ddhh24:mi:ss')='2013-06-06 16:18:30'

和

start_time=to_date('2013-06-06 16:18:30','yyyy-mm-ddhh24:mi:ss')

任何時候都推薦使用後者！！

㈡數字轉為英文的簡單實現

sys@ORCL> selectto_char(to_date('1314','J'),'Jsp') as "I love 1314" from dual; 

I love 1314 

----------------------------------- 

One Thousand Three Hundred Fourteen 

TO_CHAR(aDate,'JSP')可以得到一個數位拼字,例如:
SELECT TO_CHAR(SYSDATE, 'JSP') FROM DUAL;
這條SQL的查詢結果是:
TWO MILLION FOUR HUNDRED FIFTY-THREE THOUSAND FIVE HUNDRED NINE
該數字為:20004530509
為什麼?
因為TO_CHAR(aDate,'JSP')是指日期aDate距離JULIAN日期的第一天即4712-01-01BC的天數,也就是從公元前4712年的1月1號到aDate這個日期的天數的英文拼字.Cool!
JULIAN日期的範圍是公元前4712-01-01到公元9999-01-01.期間的天數是我們能夠拼字的最大值,最小值是1.
那麼我們要想得到對一個數位拼字,只要知道這些天數之後的那個日期就行,得到這個日期很簡單:
TO_DATE(aDigital,'J') 其中aDigital就是你要表示的數字.
這樣我們得到一個數位拼字可以這樣(例如101):
SELECT TO_CHAR(TO_DATE(101, 'J'), 'JSP') FROM dual
得到的結果是: ONE HUNDRED ONE

㈢一個 to_date 的陷阱

我們可以用to_date函數來構造自己想要的任何時間

當省略了HH,MI,SS時，oracle預設置為0，即：取整到日

當省略了DD時，oracle預設置為1，即：取整到月

當省略了MM時，oracle會取整到年嗎？我們都會慣性的認為：會。然，非也！

Oracle不會取整到年，他會取整到當前月！

select to_date('1991','yyyy')from dual;

結果為1991-6-1