一、to_char函數(shù)
?
Parameter????Explanation?
YEAR???????????Year spelled out?
YYYY???????????4 digits of year
YYY??????????? 3 digits of year
YY?????????????2 digits of year
Y????????????? 1 digit of year
IYYY?????????? 4digits year based on the ISO standard?
IYY??????????? 3 digits of ISO year
IY?????????????2 digits of ISO year
I????????????? 1 digit of ISO year?
Q????????????? Quarter of year (1 .. 4)
MM?????????????Month (01 ..12)
MON??????????? Abbreviated name of month
MONTH????????? Name of month,
padded with blanks to length of 9 characters.?
RM?????????????Roman numeral month (I .. XII)
WW?????????????Week of year (1-53) where 7 days 1 week (與星期幾無(wú)關(guān))
W????????????? Week of month (1-5) where 7 days 1 week (與星期幾無(wú)關(guān))
IW???????????? Week of year (1-52 or 1-53) based on the ISO standard.?
???????????????(周一到周日為一周，若1日為周五-周日，則為上年最后一周)
D????????????? Day of week (周日1 .. 周六7)
DY???????????? Abbreviated name of day.?
DAY????????????Name of day
DD???????????? Day of month (1-31)
DDTH?????????? Day of month (1-31)
DDD??????????? Day of year (1-366)
J????????????? Julian day；the number of days since January 1, 4712 BC.?
HH???????????? Hour of day (1-12).?
HH12?????????? Hour of day (1-12).?
HH24?????????? Hour of day (0-23).?
MI?????????????Minute (0-59).?
SS?????????????Second (0-59).?
SSSSS????????? Seconds past midnight (0-86399).?
FF?????????????Fractional seconds.?
XXXXX????????? 轉(zhuǎn)換為8進(jìn)制
?
to_char(1210.73, '9999.9')???????would return '1210.7'?
to_char(1210.73, '9,999.99')?????would return '1,210.73'?
to_char(1210.73, '$9,999.00')????would return '$1,210.73'?
to_char(21, '000099')????????????would return '000021'?
to_char(21, '999999')????????????would return '??? 21'?
to_char(21, 'FM999999')??????????would return '21'
to_char(sysdate, 'FMYYY')????????would return '8'?
??????? --FM表示去掉0或空格??
?
to_char(125, 'XXXXX')??????????? would return '7D'?
to_number('7D','XXXXX')????????? would return '125'?
?
?
另注：trunc與to_char的比較
?
trunc原意為截取數(shù)據(jù)小數(shù)部分，例如：
?
trunc(23.48429387)??? 返回23
trunc(23.48429387,3)? 返回23.484
trunc(-1.443432)??????返回-1
?
但trunc(date) 具有與to_char(date) 相似的功能，但有區(qū)別：
?
trunc(sysdate,'cc')?? 取當(dāng)世紀(jì)的第一天???? to_char(sysdate,'cc')?? 取當(dāng)世紀(jì)數(shù)值
trunc(sysdate,'yyyy')?取當(dāng)年的第一天??? ?? to_char(sysdate,'yyyy')?取當(dāng)年數(shù)值
trunc(sysdate,'iyyy') 取上年的最后一天???? to_char(sysdate,'iyyy') 取當(dāng)年數(shù)值
trunc(sysdate,'q')??? 取當(dāng)季第一天???????? to_char(sysdate,'iyyy') 取當(dāng)季數(shù)值
trunc(sysdate,'mm')???取當(dāng)月第一天???????? to_char(sysdate,'mm')?? 取當(dāng)月數(shù)值
trunc(sysdate,'ww')???取當(dāng)周第一天(周二)???to_char(sysdate,'ww')?? 取當(dāng)周數(shù)值(第幾周)
trunc(sysdate,'iw')???取當(dāng)周第一天(周一)???to_char(sysdate,'iw')?? 取當(dāng)周數(shù)值(第幾周)
?
總結(jié)：trunc對(duì)日期的截取由后面參數(shù)決定位置后將之后所有數(shù)值為默認(rèn)初始值！
?
?
?
二、order by函數(shù)
?
--
自動(dòng)將結(jié)果列表按字段順序?qū)?yīng)排序
order
by
1
,
2
,
3
?
--
可對(duì)字段
decode
后再排序，下例為將
2222
、
1111
排在前兩位，其他按順序排列
select
a,b,
c
from
t1
order
by
decode(a,
'2222'
,
1
,
'1111'
,
2
,a)
?
--
如遇到空值時(shí)，
order by
默認(rèn)將空值排在最下面，如要排在最上面，則：
order
by
nulls
first
?
?
?
三、取整類(lèi)函數(shù)整理
?
ceil
：
取整
(
大
)
???
select
ceil (-
1.001
)
value
from
dual??? /-
1
floor
：取整（小）
???
select
floor(-
1.001
)
value
from
dual??? /-
2
trunc
：取整（截取）
???
select
trunc(-
1.001
)
value
from
dual??? /-
1
round
：取整
(
舍入
)
select
round(-
1.001
)
value
from
dual??? /-
1
?
應(yīng)用舉例：(根據(jù)時(shí)間算年齡)
trunc(months_between(
sysdate
,birthday)/
12
) Age
?
?
四、LPAD與RPAD的用法：
?
比較：select LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') from dual;
??????????? |WhaT|???????? ????? WhaT is tHis| -------------WhaT is tHis
???? select RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') from dual;
??????????? |WhaT| ?WhaT is tHis???????????? |?WhaT is tHis-------------
?
作用：作為調(diào)整格式的輸出，例：
?
with
x
as
(
select
'aa'
chr
from
dual
union
all
select
'bb'
chr
from
dual)
select
level
,chr,lpad(
' '
,(
level
-
1
)*
5
,
'-'
)||chr other
from
x
connect
by
level
<=
3
?
說(shuō)明：若LPAD對(duì)空字符串操作無(wú)效，因此至少必須有' '空格符!
?
?
LPAD的實(shí)際應(yīng)用：
?
select
distinct
lpad(selltype,
2
,
'0'
)
from
lccont;
?
由于系統(tǒng)中其他的selltype字段均為01、02等2位，但出現(xiàn)7，另有null值
所以使用 lpad(selltype,2,'0') 可以即保存null值又將7更新為07
?
?
?
五、rank() order by()和row_number() order by()的區(qū)別：
?
with
t
as
(
select
1
a
from
dual
union
all
select
2
a
from
dual
union
all
select
1
a
from
dual
)
select
a,rank() over(
order
by
a) rank,row_number() over(
order
by
a) num
from
t;
?
?
?
六、translate和replace的區(qū)別：
?
select translate('What is this','ait','-*%') from dual;---Wh-% *s %h*s
selectreplace('What is this','ait','-*%') from dual;-----What is this
selectreplace('What is this','hat','-*%') from dual;-----W-*% is this
?
translate的實(shí)際應(yīng)用：
select translate('12XXX5869XXXX','0123456789'||'XXX5869XXXX','0123456789')from dual;
<取字符串中的所有數(shù)字>
?
?
?
七、sysdate與current_date的差別：
?
select?sysdate,current_date?from?dual;
某些情況下current_date會(huì)比sysdate快一秒。
我們認(rèn)為current_date是將current_timestamp中毫秒四舍五入后的返回
雖然沒(méi)有找到文檔支持，但是想來(lái)應(yīng)該八九不離十。
?
?
?
八、一些有用的時(shí)間函數(shù)：
?
select NEXT_DAY(sysdate,5) from dual;--下一個(gè)星期四(不算今天)
select NEXT_DAY(sysdate,'星期三') from dual;--下一個(gè)星期一(大小寫(xiě)都可)
select LAST_DAY(sysdate) from dual;--當(dāng)月最后一天
?
?
?
九、一些有用的數(shù)字/字符函數(shù)：
?
select GREATEST(a,b) Greatest from t2;----------求最大值
select LEAST(a,b) Greatest from t2;-------------求最大值
select NULLIF('a','b'),NULLIF('a','a') from dual;-------a=b則返回null；a<>b則返回a
select nvl(null,'a'),nvl('1','a') from dual;------------為null時(shí)返回a，不會(huì)null返回原值
select nvl2(null,'a','b'),nvl2('1','a','b') from dual;--為null時(shí)返回b，不為null返回a
selectCOALESCE(null,5,6,null,9) from dual;-----返回第一個(gè)非空值
select POWER(2.2,2.2) from dual;? ----a的b次方
?
?
?
十、一些有用的字符串操作函數(shù)：
?
select CHR(95) from dual;-------------ASCII碼對(duì)應(yīng)字符
select ASCII('_') from dual;----------字符對(duì)應(yīng)ASCII碼
select concat('aa','bb') from dual;------------等同于||
select INITCAP('whaT is this') from dual;------首字母大寫(xiě)，其余小寫(xiě)
select TO_MULTI_BYTE('ABC abc 中華') from dual;----------半角變?nèi)?/span>
select TO_SINGLE_BYTE('ＡＢＣ ａｂｃ中華') from dual;------全角變半角
select VSIZE('abc中華') from dual;-----返回字節(jié)數(shù)
select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----從第3位開(kāi)始查找第2個(gè)'OR'
?
?
?
十一、WMSYS.WM_CONCAT函數(shù)應(yīng)用：
?
此函數(shù)作用在于將某字段所有值列出到一個(gè)單元格中
?
select
replace
(WMSYS.WM_CONCAT(num),
','
,
' '
)
from
t1;
?
行列轉(zhuǎn)換中最簡(jiǎn)單的一種方法。
?
?
?
十二、單元格內(nèi)文本換行的方法：
?
Tab鍵 chr(9)
換行符chr(10)
回車(chē)符chr(13)
空格符chr(32)
select
'a'
||chr(
9
)||
'b'
from
dual;
select
'a'
||chr(
13
)||
'b'
from
dual;
注：須在SQLPlus中查看結(jié)果，PL/SQL Developer中無(wú)法顯示換行
?
?
-The End-