>字符處理
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://mfm088.itpub.net/post/22599/250400
在Oracle/PLSQL中，lpad函數將左邊的字符串填充一些特定的字符，其語法格式如下：
lpad( string1, padded_length, [ pad_string ] )
其中string1是需要粘貼字符的字符串
padded_length是返回的字符串的數量，如果這個數量比原字符串的長度要短，lpad函數將會把字符串截取成padded_length;
pad_string是個可選參數，這個字符串是要粘貼到string1的左邊，如果這個參數未寫，lpad函數將會在string1的左邊粘貼空格。
例如：
| lpad('tech', 7); |
將返回' tech' |
| lpad('tech', 2); |
將返回'te' |
| lpad('tech', 8, '0'); |
將返回'0000tech' |
| lpad('tech on the net', 15, 'z'); |
將返回 'tech on the net' |
| lpad('tech on the net', 16, 'z'); |
將返回 'ztech on the net' |
-----------------------
select a.*, Level from (select * from dlsys.tcUnit order by DisplayOrder) a
start with a.SeniorUnitID is null
connect by a.SeniorUnitID = prior a.UnitID