Oracle常用功能集錦

數值函數：

abs(m) m的絕對值

mod(m,n) m被n除後的餘數

power(m,n) m的n次方

round(m[,n]) m四捨五入至小數點後n位的值（n預設為0）

trunc(m[,n]) m截斷n位小數位的值（n預設為0）

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

字元函數：

initcap(st) 返回st將每個單詞的首字母大寫，所有其他字母小寫

lower(st) 返回st將每個單詞的字母全部小寫

upper(st) 返回st將每個單詞的字母全部大寫

concat(st1,st2) 返回st為st2接st1的末尾（可用操作符"||"）

lpad(st1,n[,st2]) 返回靠右對齊的st,st為在st1的左邊用st2填充直至長度為n,st2的預設為空白格

rpad(st1,n[,st2]) 返回靠左對齊的st,st為在st1的右邊用st2填充直至長度為n,st2的預設為空白格

ltrim(st[,set]) 返回st,st為從左邊刪除set中字元直到第一個不是set中的字元。預設時，指的是空格

rtrim(st[,set]) 返回st,st為從右邊刪除set中字元直到第一個不是set中的字元。預設時，指的是空格

replace(st,search_st[,replace_st]) 將每次在st中出現的search_st用replace_st替換，返回一個st。預設時，刪除search_st

substr(st,m[,n]) n=返回st串的子串，從m位置開始，取n個字元長。預設時，一直返回到st末端

length(st) 數值，返回st中的字元數

instr(st1,st2[,m[,n]]) 數值，返回st1從第m字元開始，st2第n次出現的位置，m及n的預設值為1

例：

1.

select initcap('THOMAS'),initcap('thomas') from test;

initca initca

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

Thomas Thomas

2.

select concat('abc','def') "first" from test;

first

-----

abcdef

3.

select 'abc'||' '||'def' "first" from test;

first

-----

abc def

4.

select lpad(name,10),rpad(name,5,'*') from test;

lpad(name,10) rpad(name,5,'*')

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

mmx mmx**

abcdef abcde

5.

去掉地址欄位末端的點及單詞st和rd

select rtrim(address,'. st rd') from test

6.

select name,replace(name,'a','*') from test;

name replace(name,'a','*')

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

great gre*t

7.

select substr('archibald bearisol',6,9) a,substr('archibald bearisol',11) b from test;

a b

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

bald bear bearisol

8.

select name,instr(name,' ') a,instr(name,' ',1,2) b from test;

name a b

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

li lei 3 0

l i l 2 4

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

轉換函式：

nvl(m,n) 如果m值為null,返回n,否則返回m

to_char(m[,fmt]) m從一個數值轉換為指定格式的字串fmt預設時，fmt值的寬度正好能容納所有的有效數字

to_number(st[,fmt]) st從字元型資料轉換成按指定格式的數值，預設時數值格式串的大小正好為整個數

附：

to_char()函數的格式：

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

符號 說明

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

9 每個9代表結果中的一位元字

0 代表要顯示的先導0

$ 貨幣符號列印在數的左邊

L 任意的當地貨幣符號

. 列印十進位的小數點

, 列印代表千分位的逗號

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

例：

1.

select to_number('123.45')+to_number('234.56') form test;

to_number('123.45')+to_number('234.56')

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

358.01

2.

select to_char(987654321) from test;

to_char(987654321)

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

987654321

3.

select to_char(123,'$9,999,999') a,to_char(54321,'$9,999,999') b,to_char(9874321,'$9,999,999') c from test;

a b c

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

$123 $54,321 $9,874,321

4.

select to_char(1234.1234,'999,999.999') a,to_char(0.4567,'999,999.999') b,to_char(1.1,'999,999.999') from test;

a b c

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

1,234.123 .457 1.100

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

分組函數：

avg([distinct/all] n) 列n的平均值

count([all] *) 返回查詢範圍內的行數包括重複值和空值

count([distinct/all] n) 非空值的行數

max([distinct/all] n) 該列或運算式的最大值

min([distinct/all] n) 該列或運算式的最小值

stdev([distinct/all] n) 該列或運算式的標準差，忽略空值

sum([distinct/all] n) 該列或運算式的總和

variance([distinct/all] n) 該列或運算式的方差，忽略空值

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

日期函數：

>

add_months(d,n) 日期d加n個月

last_day(d) 包含d的月份的最後一天的日期

month_between(d,e) 日期d與e之間的月份數，e先於d

new_time(d,a,b) a時區的日期和時間d在b時區的日期和時間

next_day(d,day) 比日期d晚，由day指定的周幾的日期

sysdate 當前的系統日期和時間

greatest(d1,d2,...dn) 給出的日期列表中最後的日期

least(d1,k2,...dn) 給出的日期列表中最早的日期

to_char(d [,fmt]) 日期d按fmt指定的格式轉變成字串

to_date(st [,fmt]) 字串st按fmt指定的格式轉成日期值，若fmt忽略，st要用預設格式

round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期

trunc(d [,fmt]) 日期d按fmt指定格式截斷到最近的日期

附：

日期格式：

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

格式代碼　 說明 舉例或可取值的範圍

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

DD 該月某一天 1－3

DY 　　　三個大寫字母表示的周幾 SUN，...SAT

DAY 　　　完整的周幾，大寫英文 SUNDAY，...SATURDAY

MM 月份 1－12

MON　　　　　 三個大寫字母表示的月份 JAN，...DEC

MONTH 完整 JANUARY,...DECEMBER

RM 月份的羅馬數字 I,...XII

YY或YYYY 兩位，四位元字年

HH:MI:SS　　　時：分：秒

HH12或HH24　　以12小時或24小時顯示

MI　　　　　　分

SS　　　　　　秒

AM或PM　　　　上下午指示符

SP　　　　　　尾碼SP要求拼字出任何數值欄位

TH　　　　　　尾碼TH表示添加的數字是序數　4th,1st

FM 首碼對月或日或年值，禁止填充

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

例：

1.

下一個周五的日期

select next_day(sysdate,6) from test;

2.

兩個月前的今天的日期

select add_months(sysdate,-2) from test;