from http://dba.tw/index.php?topic=244.0

1>字符處理

upper(str)—將字元串str全部轉換成大寫
lower(str)—將字元串str全部轉換成小寫
initcap(str)—將字元串中每個單字的首字母大寫
concat(str1,str2)—將字元串str1與str2連接起來(也可以透過’||’號直接相連)
substr(str,a,b)—取字元串str中的指定字符，從位置a開始取長度為b的字元串，假如a為正則從左邊開始，否則從右邊開始
instr(str,’z’)—取得str字元串中從左邊開始每一次出現z字符的下標位置(下標從1開始)
lpad(str,12,’*’)—左填充，即將字元串str長度填充到12，假如其不足12位則在左邊以*號填充
rpad(str,12,’*’)—右填充，同上
length(str)—計算字元串str的長度

2>數字函數

round(45.926,2)—將前一數保留指定的小數位，並四舍五入(45.93)，假如指定位是負數則意為在小數點左邊保留指定位,如round(45.923,-1)=50,rount(45.923,0)=46,round(45.93,-2)=0,round(55.93,-2)=100
trunc(45.926,2)—同上，得不四舍五入(45.92)
mod(1600,300)—求余(100)

3>日期型函數

oracle中預設的格式是︰DD-MON-RR。
oracle中有個到當前系統時間–sysdate，如︰
select sysdate from dual

可對日期進行自述運算︰
select (sysdate-mybirthday)/7 from person

months_between(’01-sep-95′,’11-jan-94′)—取得二個日期之間的間隔月數(19.6774194)
add_months(’11-jan-94′,6)—給指定日期加上指定的月份後得到一個新的日期(11-jul-94)
next_day(’01-sep-85′,’friday’)—取得當前日期中下個周五的日期(01-jul-95)
last_day(’01-feb-95′)—取得當前日期中月份的最後一天(28-feb-95)

round進行四舍五入，trunc則否,以下是我的操作結果︰
sysdate為︰
SYSDATE
———-
28-7月 -06

select
round(sysdate,’month’) RM,
round(sysdate,’year’) RY,
trunc(sysdate,’month’) TM,
trunc(sysdate,’year’) TY
from dual;

RM RY TM TY
———- ———- ———- ———-
01-8月 -06 01-1月 -07 01-7月 -06 01-1月 -06

4>轉換函數

隱式轉換:系統自動轉換,如︰
varchar2 or char to number
varchar2 or char to date
number to varchar2
date to varchar2

顯式轉換︰人為以函數加以轉換
日期，字符，數據三者之間可以相互轉換:日期<-->字符<-->數據
日期格式:YYYY

日期–>字符
select to_char(sysdate,’yyyy-mm-dd’) ch from dual

CH
———-
2006-07-28

數字–>字符
to_char(number,’format_model’),oramat_model有如下︰
9—用對應數字表示
0—強製用０表示
$—加一$符號
L—前加本地貨幣單位表示
.—十進製點
,—千進製點

select to_char(0917,’l9999.99′) local from dual
LOCAL
——————
RMB917.00

字符–>日期
tselect to_date(‘19830917′,’yyyy-mm-dd’) bir from dual
BIR
———-
17-9月 -83

字符–>數字
select to_number(‘19821217′,’999999999’) mybr from dual

MYBR
———-
19821217

注：所有函数均可以嵌套使用

5>通用函數

nvl(expr1,expr2)—expr1為空則顯示expr2,否則顯示expr1
nvl2(expr1,expr2,expr3)—expr1為空則顯示expr2,否則顯示expr3
nullif(expr1,expr2)—二個相等則顯示空符,否則顯示expr1
coalesce(expr1,expr2,…,exprn)—從expr1開始依次找到不為空的expr，找到就顯示，直到最後，否則顯示
exprn case表達式，如下圖:

6>多表查詢
對普通的多表查詢，也就是不加where條件的時候實際上查詢結果是各表的笛凱爾集
外連接︰oracle實現外連接時在=號的二邊加+號就OK，當+在左邊時稱為右連接，反之為左連接,它常常用來當要求未受限制對象的表數據也要求顯示時，如下︰

select * from student

ID NAME ADDRESS
——————– ————–
1 zhangshan zhejiang
3 lishi hangzhou
7 lily guangzhou

select * from person

ID NAME ADDRESS
– ——————– ———–
1 zhangshan zhejiang
3 lishi hangzhou
7 lily guangzhou

select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id(+) = s.id

ID SEX ID NAME ADDRESS
— —- ———- ——————– ————
1 boy 1 zhangshan zhejiang
3 girl 3 lishi hangzhou
7 lily guangzhou

select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id = s.id(+)

ID SEX ID NAME ADDRESS
— —- ———- ——————– ————–
1 boy 1 zhangshan zhejiang
2 girl
3 girl 3 lishi hangzhou
4 boy
5 girl

self-join，就是同一張表連接，用自連接的時候要注意排除重複的記錄（自身，循環重複等），比如說找出student表中所有住在同一個地方的人。

select * from student

ID NAME ADDRESS
— ——————– ———
1 zhangshan zhejiang
3 lishi hangzhou
7 lily guangzhou
2 name2 guangzhou
4 name4 guangzhou
5 name5 hangzhou
6 name6 shanghai
8 name8 shanghai

沒有排除重複記錄時的結果︰

select t1.name,t2.name
from student t1, student t2
where t1.address = t2.address

NAME NAME
——————– ————–
lily lily
name2 lily
name4 lily
lily name2
name2 name2
name4 name2
lily name4
name2 name4
name4 name4
lishi lishi
name5 lishi

NAME NAME
——————– ————–
lishi name5
name5 name5
name6 name6
name8 name6
name6 name8
name8 name8
zhangshan zhangshan

已選擇18行

排除重複與循環記錄之後︰

select t1.name,t2.name,t1.address
from student t1, student t2
where t1.address = t2.address
and t1.id > t2.id

NAME NAME ADDRESS
——————– ——————– ——————-
lily name2 guangzhou
name4 name2 guangzhou
lily name4 guangzhou
name5 lishi hangzhou
name8 name6 shanghai

cross join,無條件連接，實際上跟不帶where時一樣得到的是笛凱爾集

natural join（也可以直接記作join），根據表中同名欄位來連接記錄,若表中可能出現多個同名欄位，則用using(col_name)來指定所欄位。colname不能指定表的別名﹗限制條件用on來指定
select id,p.sex,id,s.name from person p join student s using(id);

ID SEX ID NAME
—- —- ———- ——————–
1 boy 1 zhangshan
2 girl 2 name2
3 girl 3 lishi
4 boy 4 name4
5 girl 5 name5

在sql標準語法中,左(右)連接用left(right) out join,限制條件用on就可以了。

full out join完全外連接,顧名思義就是左外連接與右外連接都有
select id,p.sex,id,s.name from person p full join student s using(id);

ID SEX ID NAME
—- —- ———- ——————
1 boy 1 zhangshan
3 girl 3 lishi
2 girl 2 name2
4 boy 4 name4
5 girl 5 name5
8 8 name8
6 6 name6
7 7 lily

—<< 參考自 http://www.blogjava.net/Jkallen/archive/2006/07/28/60508.html >>–