-------------------------------oracle基礎(chǔ)------------------------------------------
1.oracle中聲明變量時(shí)，變量類型是在變量名的后面。如：v_count number(7,1)
1.oracle中聲明變量時(shí)，變量類型是在變量名的后面。如：v_count number(7,1)
2.oracle中賦值用:=,等于用=.不同于java中等于用：“==”，賦值用"=".
v_msg varchar2(32) := 'testing'
3.字符常量用單引號(hào)，而不是雙引號(hào)。若要輸出帶字符可以如：'in='||v_count.
而不能被java中，用"in="+v_count.
4.在存儲(chǔ)過程或函數(shù)中，聲明變量名的規(guī)則是：
第一個(gè)字符必須是字母，不能是數(shù)字如：200_count；
變量不分大小寫；
不能含有"-","/","$"等符號(hào)，但可以用"_".
5.可以用"<>"或"!="來表示不等于。
單行注釋，用--，多行注釋，用/**/
單行注釋，用--，多行注釋，用/**/
6.條件語句if,case:
if,case以及以前的循環(huán)標(biāo)簽，結(jié)果時(shí)，都須用end[關(guān)鍵字]來表示結(jié)果。
6.1
if 條件 then ...
else if 條件 then ...
else ...
end if
如：
if salse>5000 then bonus :=500;
else if salse>3000 then bonus :=300;(即當(dāng)銷售額大于3000小于等于5000時(shí))
else bonus :=100
end if
6.2
case格式用二種，一種是有選擇器，另一種則沒有。如下：
case grade
when 'A' then status :='good';
when 'B' then status :='normal'
else status :='bad'
end case
或
case
when grade='A' then status :='good';
when grade='B' then status :='normal'
else status :='bad'
end case
case格式用二種，一種是有選擇器，另一種則沒有。如下：
case grade
when 'A' then status :='good';
when 'B' then status :='normal'
else status :='bad'
end case
或
case
when grade='A' then status :='good';
when grade='B' then status :='normal'
else status :='bad'
end case
7.循環(huán)語句，while<條件> loop...end loop / for...loop ...end loop.
實(shí)例如下：
create or replace procedure a(max in number,value out number)
as
index number :=0;
begin
while index<=max
loop
value :=value*value;
end loop;
end
實(shí)例如下：
create or replace procedure a(max in number,value out number)
as
index number :=0;
begin
while index<=max
loop
value :=value*value;
end loop;
end
若用for,可以是：
for <循環(huán)計(jì)數(shù)變量> in [reverse] <上限> .. <下限>
for m in 1 .. 10
loop
.........
end loop
將執(zhí)行從1到10的循環(huán)；
若用
for m in reverse 1 ..10
loop ...end loop
則實(shí)現(xiàn)從10到1的逆向循環(huán)。
for <循環(huán)計(jì)數(shù)變量> in [reverse] <上限> .. <下限>
for m in 1 .. 10
loop
.........
end loop
將執(zhí)行從1到10的循環(huán)；
若用
for m in reverse 1 ..10
loop ...end loop
則實(shí)現(xiàn)從10到1的逆向循環(huán)。
oracle左/右/全連接
inner join --內(nèi)連接和where相同;相當(dāng)于join;
left join --左向外連接，返回左邊表所有符合條件的
right join --右向外連接，返回右邊表所有符合條件的
full join --完整外部連接，左向外連接和右向外連接的合集
left join --左向外連接，返回左邊表所有符合條件的
right join --右向外連接，返回右邊表所有符合條件的
full join --完整外部連接，左向外連接和右向外連接的合集
--建立測試數(shù)據(jù)
create table a(id number);
create table b(id number);
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into b values(1);
insert into b values(2);
insert into b values(4);
create table a(id number);
create table b(id number);
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into b values(1);
insert into b values(2);
insert into b values(4);
--左:
--主流數(shù)據(jù)庫通用的方法
select * from a left join b on a.id=b.id;
--Oracle特有的方法 --最好不要用這樣，不便于sql語句的移植。
select * from a, b where a.id=b.id(+);
ID ID
---------- ----------
1 1
2 2
3
--右：
--主流數(shù)據(jù)庫通用的方法
select * from a right join b on a.id=b.id;
--Oracle特有的方法
select * from a, b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
4
--內(nèi)
--主流數(shù)據(jù)庫通用的方法
select * from a join b on a.id=b.id;
--where關(guān)聯(lián)
select * from a, b where a.id=b.id;
ID ID
---------- ----------
1 1
2 2
--全外
--主流數(shù)據(jù)庫通用的方法
select * from a full join b on a.id=b.id;
--Oracle特有的方法
select *
from a, b
where a.id = b.id(+)
union
select *
from a, b
where a.id(+) = b.id;
ID ID
---------- ----------
1 1
2 2
3
4
--主流數(shù)據(jù)庫通用的方法
select * from a left join b on a.id=b.id;
--Oracle特有的方法 --最好不要用這樣，不便于sql語句的移植。
select * from a, b where a.id=b.id(+);
ID ID
---------- ----------
1 1
2 2
3
--右：
--主流數(shù)據(jù)庫通用的方法
select * from a right join b on a.id=b.id;
--Oracle特有的方法
select * from a, b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
4
--內(nèi)
--主流數(shù)據(jù)庫通用的方法
select * from a join b on a.id=b.id;
--where關(guān)聯(lián)
select * from a, b where a.id=b.id;
ID ID
---------- ----------
1 1
2 2
--全外
--主流數(shù)據(jù)庫通用的方法
select * from a full join b on a.id=b.id;
--Oracle特有的方法
select *
from a, b
where a.id = b.id(+)
union
select *
from a, b
where a.id(+) = b.id;
ID ID
---------- ----------
1 1
2 2
3
4
對(duì)于多表查詢，如下：
select * from a
left join b on a.id=b.id
left join c on c.id=b.id;
left join b on a.id=b.id
left join c on c.id=b.id;
ID ID ID
---------- ---------- ----------
1 1 1
2 2
3
---------- ---------- ----------
1 1 1
2 2
3
自己理解：
相當(dāng)于先進(jìn)行第一個(gè)left join，得到結(jié)果集A(3條記錄)；結(jié)果集A再與第二個(gè)left join進(jìn)行查詢；
select * from a
left join b on a.id=b.id
right join c on c.id=b.id;
left join b on a.id=b.id
right join c on c.id=b.id;
相當(dāng)于先進(jìn)行第一個(gè)left join，得到結(jié)果集A（3條記錄）；結(jié)果集A再與right join進(jìn)行查詢,即以c表為主，因?yàn)镃表只有一個(gè)記錄，所以最終才顯示一條記錄；
-----------------------------------------------------------------------------------------
1.
NVL(ac.ovrided_ac_nam, en.stmt_nam) accountDisplayName,
NVL(EXPR1,EXPR2)
若EXPR1是NULL,則返回EXPR2,否則返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1; --注意不能是雙引號(hào)。
2.
DUAL叫虛表，又叫啞表。這表示其中的內(nèi)容并不是在數(shù)據(jù)庫實(shí)際存在的，而是隨表達(dá)式的不同而不是同的。
select * from dual，返回什么？
D
-
X
這表示其中沒有任何內(nèi)容，也不要對(duì)DUAL這個(gè)虛表進(jìn)行增刪改，這樣做沒有實(shí)際意義，更不能DROP。強(qiáng)調(diào)，它是一個(gè)系統(tǒng)表。
使用DUAL比較常見的情況是查詢當(dāng)前的系統(tǒng)時(shí)間，如：
SQL> select to_char(sysdate,'yyyy.mm.dd') as x from dual;
當(dāng)我們計(jì)算表達(dá)式時(shí)，可用
SELECT sqrt(81) FROM dual;
SELECT (5+6)*7 FROM dual;
3.
to_char,to_date
日期到字符操作
select to_char(sysdate,'yyyy.mm.dd') as x from dual;
字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
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'
4.
select a,
CASE WHEN t.b = 'yes' THEN
t.c ELSE 0 END yescolumn,
CASE WHEN t.b = 'no' THEN
t.c ELSE 0 END nocolumn,
在select語句中，也可應(yīng)用case.在select的字段中，會(huì)同時(shí)出現(xiàn)yes,no兩個(gè)字段。
5.
不等于：WHERE ROWID <> (SELECT MAX(ROWID)
6.
字段合并：
select ac_id || unit_cde from cc_ac t --make two column into one
相當(dāng)于：
select concat(ac_id , unit_cde) from cc_ac t --make two column into one
NVL(ac.ovrided_ac_nam, en.stmt_nam) accountDisplayName,
NVL(EXPR1,EXPR2)
若EXPR1是NULL,則返回EXPR2,否則返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1; --注意不能是雙引號(hào)。
2.
DUAL叫虛表，又叫啞表。這表示其中的內(nèi)容并不是在數(shù)據(jù)庫實(shí)際存在的，而是隨表達(dá)式的不同而不是同的。
select * from dual，返回什么？
D
-
X
這表示其中沒有任何內(nèi)容，也不要對(duì)DUAL這個(gè)虛表進(jìn)行增刪改，這樣做沒有實(shí)際意義，更不能DROP。強(qiáng)調(diào)，它是一個(gè)系統(tǒng)表。
使用DUAL比較常見的情況是查詢當(dāng)前的系統(tǒng)時(shí)間，如：
SQL> select to_char(sysdate,'yyyy.mm.dd') as x from dual;
當(dāng)我們計(jì)算表達(dá)式時(shí)，可用
SELECT sqrt(81) FROM dual;
SELECT (5+6)*7 FROM dual;
3.
to_char,to_date
日期到字符操作
select to_char(sysdate,'yyyy.mm.dd') as x from dual;
字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
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'
4.
select a,
CASE WHEN t.b = 'yes' THEN
t.c ELSE 0 END yescolumn,
CASE WHEN t.b = 'no' THEN
t.c ELSE 0 END nocolumn,
在select語句中，也可應(yīng)用case.在select的字段中，會(huì)同時(shí)出現(xiàn)yes,no兩個(gè)字段。
5.
不等于：WHERE ROWID <> (SELECT MAX(ROWID)
6.
字段合并：
select ac_id || unit_cde from cc_ac t --make two column into one
相當(dāng)于：
select concat(ac_id , unit_cde) from cc_ac t --make two column into one
----------------------------------sqlplus命令(系統(tǒng)命令)------------------------------
sql語句主要是對(duì)數(shù)據(jù)庫的定義與操作，而對(duì)像環(huán)境設(shè)置，數(shù)據(jù)庫會(huì)話等SQL語句是不能完成的。
Oracle公司提供的附加語句（或稱命令），可以滿足程序人員和管理員的一些特殊操作要求。比如，在顯示超過上百行記錄信息時(shí)，可以采用每屏“暫停”來實(shí)現(xiàn)。要達(dá)到這樣的目的，就要在SQL>下發(fā) set pause on 命令。由于SQL*PLUS命令較多，下面僅給出最常用的幾個(gè)命令的說明:
1.
logon可以是：
{username[/password][@connect_identifier]|/} [AS {SYSOPER|SYSDBA}]
sql語句主要是對(duì)數(shù)據(jù)庫的定義與操作，而對(duì)像環(huán)境設(shè)置，數(shù)據(jù)庫會(huì)話等SQL語句是不能完成的。
Oracle公司提供的附加語句（或稱命令），可以滿足程序人員和管理員的一些特殊操作要求。比如，在顯示超過上百行記錄信息時(shí)，可以采用每屏“暫停”來實(shí)現(xiàn)。要達(dá)到這樣的目的，就要在SQL>下發(fā) set pause on 命令。由于SQL*PLUS命令較多，下面僅給出最常用的幾個(gè)命令的說明:
1.
logon可以是：
{username[/password][@connect_identifier]|/} [AS {SYSOPER|SYSDBA}]
切換用戶：
connect user/password
退出：disconnect
connect user/password
退出：disconnect
2.
EXIT和QUIT
可以用 exit 或quit 來終止SQL*PLUS的操作（會(huì)話）。
3.
DESCRIBE(顯示表、視圖結(jié)構(gòu))
DESCRIBE可以用(只要用DESC即可)來顯示表、視圖的列的定義，也可以顯示同義詞、函數(shù)或存儲(chǔ)過程的說明。語法如下：DESC[RIBE] {[schema.]object[@connect_identifier]}
EXIT和QUIT
可以用 exit 或quit 來終止SQL*PLUS的操作（會(huì)話）。
3.
DESCRIBE(顯示表、視圖結(jié)構(gòu))
DESCRIBE可以用(只要用DESC即可)來顯示表、視圖的列的定義，也可以顯示同義詞、函數(shù)或存儲(chǔ)過程的說明。語法如下：DESC[RIBE] {[schema.]object[@connect_identifier]}
Schema:用戶名,如果省去，則為對(duì)象的所有者。
object
可以是 表（table）, 視圖（view）,類型（ type）, 存儲(chǔ)過程（procedure）,函數(shù)（ function）, 包（package）或同義詞（ synonym）
4.
LIST(列出)命令
可以用 LIST 命令來列出當(dāng)前SQL緩沖區(qū)中的一行或多行命令語句。
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
n 列出第n行
n m 列出n到m行
n * 列出第n行到當(dāng)前行
n LAST 列出第n行到最末行
* 列出所有行
* n 列出當(dāng)前行到第n行
* LAST列出當(dāng)前行到最末行
LAST 列出最末行
例：
SQL> LIST
1 SELECT ENAME, DEPTNO, JOB
2 FROM EMP
3 WHERE JOB = ’CLERK’
4* ORDER BY DEPTNO
5.
Save保存當(dāng)前緩沖區(qū)命令到文件
可以用SAVE命令將當(dāng)前的命令行保存到操作系統(tǒng)的文件中。
例：
SQL>select table_name from dict where table_name like ‘%ROLE%’;
SQL>save c:\get_role
6.
GET將命令文件讀到緩沖區(qū)
可以用GET 命令將操作系統(tǒng)的目錄下的命令文件讀到緩沖區(qū)（但不執(zhí)行）。語法如下：
GET filename [.ext] [LIS[T]|NOL[IST]]
其中：
filename: 希望加載到SQL緩沖區(qū)的文件名
ext: 文件的擴(kuò)展名，缺省為 SQL.
7.
再運(yùn)行當(dāng)前緩沖區(qū)的命令
在SQL>方式下，如果希望在運(yùn)行當(dāng)前的命令，可用Run(或R)或用 / 來實(shí)現(xiàn)，如：
1* select table_name from dict where table_name like '%ROLE%'
SQL> /
8.
設(shè)置顯示行字符數(shù)：set linesize 180(默認(rèn)80個(gè)字符)
設(shè)置頁面顯示行數(shù)：set pagesize 66
9.
事務(wù)自動(dòng)提交：
show auto,會(huì)顯示：autocommit on/off
set auto on/off.
設(shè)置暫停：
set pause on
---------------------------------------數(shù)據(jù)類型---------------------------------
Char 定長字符，≤255個(gè)字符
Varchar2 變長字符，≤2000個(gè)字符
Number(m,n) 數(shù)字類型，含整數(shù)、浮點(diǎn)、雙精度等
Date 固定長度（7字節(jié)）的日期型
Blob 大二進(jìn)制對(duì)象，≤4GB
Clob 大字符串對(duì)象，≤4GB
object
可以是 表（table）, 視圖（view）,類型（ type）, 存儲(chǔ)過程（procedure）,函數(shù)（ function）, 包（package）或同義詞（ synonym）
4.
LIST(列出)命令
可以用 LIST 命令來列出當(dāng)前SQL緩沖區(qū)中的一行或多行命令語句。
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
n 列出第n行
n m 列出n到m行
n * 列出第n行到當(dāng)前行
n LAST 列出第n行到最末行
* 列出所有行
* n 列出當(dāng)前行到第n行
* LAST列出當(dāng)前行到最末行
LAST 列出最末行
例：
SQL> LIST
1 SELECT ENAME, DEPTNO, JOB
2 FROM EMP
3 WHERE JOB = ’CLERK’
4* ORDER BY DEPTNO
5.
Save保存當(dāng)前緩沖區(qū)命令到文件
可以用SAVE命令將當(dāng)前的命令行保存到操作系統(tǒng)的文件中。
例：
SQL>select table_name from dict where table_name like ‘%ROLE%’;
SQL>save c:\get_role
6.
GET將命令文件讀到緩沖區(qū)
可以用GET 命令將操作系統(tǒng)的目錄下的命令文件讀到緩沖區(qū)（但不執(zhí)行）。語法如下：
GET filename [.ext] [LIS[T]|NOL[IST]]
其中：
filename: 希望加載到SQL緩沖區(qū)的文件名
ext: 文件的擴(kuò)展名，缺省為 SQL.
7.
再運(yùn)行當(dāng)前緩沖區(qū)的命令
在SQL>方式下，如果希望在運(yùn)行當(dāng)前的命令，可用Run(或R)或用 / 來實(shí)現(xiàn)，如：
1* select table_name from dict where table_name like '%ROLE%'
SQL> /
8.
設(shè)置顯示行字符數(shù)：set linesize 180(默認(rèn)80個(gè)字符)
設(shè)置頁面顯示行數(shù)：set pagesize 66
9.
事務(wù)自動(dòng)提交：
show auto,會(huì)顯示：autocommit on/off
set auto on/off.
設(shè)置暫停：
set pause on
---------------------------------------數(shù)據(jù)類型---------------------------------
Char 定長字符，≤255個(gè)字符
Varchar2 變長字符，≤2000個(gè)字符
Number(m,n) 數(shù)字類型，含整數(shù)、浮點(diǎn)、雙精度等
Date 固定長度（7字節(jié)）的日期型
Blob 大二進(jìn)制對(duì)象，≤4GB
Clob 大字符串對(duì)象，≤4GB
對(duì)于數(shù)字類型，也可以用：
Decimal
Double PREcision
Float
Integer
Int
Numeric
但，oracle在保存時(shí)，會(huì)將上面的類型自動(dòng)轉(zhuǎn)換為number.
Decimal
Double PREcision
Float
Integer
Int
Numeric
但，oracle在保存時(shí)，會(huì)將上面的類型自動(dòng)轉(zhuǎn)換為number.
------------------group by 與 分類統(tǒng)計(jì)-----------------
1.報(bào)表合計(jì)專用的Rollup函數(shù)
1.報(bào)表合計(jì)專用的Rollup函數(shù)
銷售報(bào)表
廣州 1月 2000元
廣州 2月 2500元
廣州 4500元
深圳 1月 1000元
深圳 2月 2000元
深圳 3000元
所有地區(qū) 7500元
廣州 1月 2000元
廣州 2月 2500元
廣州 4500元
深圳 1月 1000元
深圳 2月 2000元
深圳 3000元
所有地區(qū) 7500元
以往的查詢SQL:
Select area,month,sum(money) from SaleOrder group by area,month
然后廣州，深圳的合計(jì)和所有地區(qū)合計(jì)都需要在程序里自行累計(jì)
Select area,month,sum(money) from SaleOrder group by area,month
然后廣州，深圳的合計(jì)和所有地區(qū)合計(jì)都需要在程序里自行累計(jì)
1.其實(shí)可以使用如下SQL:
Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)
就能產(chǎn)生和報(bào)表一模一樣的紀(jì)錄
2.如果year不想累加，可以寫成
Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)
另外Oracle 9i還支持如下語法:
Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)
3.Grouping讓合計(jì)列更好讀
RollUp在顯示廣州合計(jì)時(shí)，月份列為NULL，但更好的做法應(yīng)該是顯示為"所有月份"
Grouping就是用來判斷當(dāng)前Column是否是一個(gè)合計(jì)列，1為yes，然后用Decode把它轉(zhuǎn)為"所有月份"
Select Decode(Grouping(area),1,'所有地區(qū)',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);
Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)
另外Oracle 9i還支持如下語法:
Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)
3.Grouping讓合計(jì)列更好讀
RollUp在顯示廣州合計(jì)時(shí)，月份列為NULL，但更好的做法應(yīng)該是顯示為"所有月份"
Grouping就是用來判斷當(dāng)前Column是否是一個(gè)合計(jì)列，1為yes，然后用Decode把它轉(zhuǎn)為"所有月份"
Select Decode(Grouping(area),1,'所有地區(qū)',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);
說明：
decode格式：
decode(字段或字段的運(yùn)算，值1，值2，值3）
這個(gè)函數(shù)運(yùn)行的結(jié)果是，當(dāng)字段或字段的運(yùn)算的值等于值1時(shí)，該函數(shù)返回值2，否則返回值3。
當(dāng)然值1，值2，值3也可以是表達(dá)式，這個(gè)函數(shù)使得某些sql語句簡單了許多。
decode格式：
decode(字段或字段的運(yùn)算，值1，值2，值3）
這個(gè)函數(shù)運(yùn)行的結(jié)果是，當(dāng)字段或字段的運(yùn)算的值等于值1時(shí)，該函數(shù)返回值2，否則返回值3。
當(dāng)然值1，值2，值3也可以是表達(dá)式，這個(gè)函數(shù)使得某些sql語句簡單了許多。
Decode(Grouping(area),1,'所有地區(qū)',area)，判斷Grouping(area)是否為1，若是，則采用“所有地區(qū)”，否則采用area.
比如我要查詢某班男生和女生的數(shù)量分別是多少?
通常我們這么寫:
select count(*) from 表 where 性別 ＝ 男；
select count(*) from 表 where 性別 ＝ 女；
要想顯示到一起還要 union一下，太麻煩了
通常我們這么寫:
select count(*) from 表 where 性別 ＝ 男；
select count(*) from 表 where 性別 ＝ 女；
要想顯示到一起還要 union一下，太麻煩了
用decode呢，只需要一句話
select decode(性別，男，1，0），decode(性別，女，1，0） from 表
select decode(性別，男，1，0），decode(性別，女，1，0） from 表
--------------------------------多級(jí)層次查詢---------------------------------
Oracle中Start with...Connect By理解及用法
Oracle中Start with...Connect By理解及用法
其基本語法是：
select ... from tablename start with cond1
connect by cond2
where cond3;
select ... from tablename start with cond1
connect by cond2
where cond3;
簡單說來是將一個(gè)樹狀結(jié)構(gòu)存儲(chǔ)在一張表里，比如一個(gè)表中存在兩個(gè)字段: id,parentid,那么通過表示每一條記錄的parent是誰，就可以形成一個(gè)樹狀結(jié)構(gòu)。用上述語法的查詢可以取得這棵樹的所有記錄。
其中COND1是根結(jié)點(diǎn)的限定語句，當(dāng)然可以放寬限定條件，以取得多個(gè)根結(jié)點(diǎn)，實(shí)際就是多棵樹。
COND2是連接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。
COND3是過濾條件，用于對(duì)返回的所有記錄進(jìn)行過濾。
COND2是連接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。
COND3是過濾條件，用于對(duì)返回的所有記錄進(jìn)行過濾。
例子:
創(chuàng)建示例表：
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
插入測試數(shù)據(jù)：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
從Root往樹末梢遞歸(取所有點(diǎn))
select * from TBL_TEST
start with id=1
connect by prior id = pid
select * from TBL_TEST
start with id=1
connect by prior id = pid
從末梢往樹ROOT遞歸(只取根結(jié)點(diǎn),根結(jié)點(diǎn)的根結(jié)點(diǎn)......)
select * from TBL_TEST
start with id=5
connect by prior pid = id
------------------------------------------------
3.更多報(bào)表/分析決策功能
select * from TBL_TEST
start with id=5
connect by prior pid = id
------------------------------------------------
3.更多報(bào)表/分析決策功能
3.1 分析功能的基本結(jié)構(gòu)
分析功能() over( partion子句,order by子句,窗口子句)
概念上很難講清楚,還是用例子說話比較好.
3.2 Row_Number 和 Rank, DENSE_Rank
用于選出Top 3 sales這樣的報(bào)表
當(dāng)兩個(gè)業(yè)務(wù)員可能有相同業(yè)績時(shí),就要使用Rank和Dense_Rank
比如
金額 RowNum Rank Dense_Rank
張三 4000元 1 1 1
李四 3000元 2 2 2
錢五 2000元 3 3 3
孫六 2000元 4 3 3
丁七 1000元 5 5 4
這時(shí),應(yīng)該把并列第三的錢五和孫六都選進(jìn)去,所以用Ranking功能比RowNumber保險(xiǎn).至于Desnse還是Ranking就看具體情況了。
SELECT salesperson_id, SUM(tot_sales) sp_sales, RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank FROM orders GROUP BY salesperson_id
3.3 NTILE 把紀(jì)錄平分成甲乙丙丁四等
比如我想取得前25%的紀(jì)錄,或者把25%的紀(jì)錄當(dāng)作同一個(gè)level平等對(duì)待,把另25%當(dāng)作另一個(gè)Level平等對(duì)待
SELECT cust_nbr, SUM(tot_sales) cust_sales, NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile FROM orders GROUP BY cust_nbr ORDER BY 3,2 DESC;
NTITLE(4)把紀(jì)錄以 SUM(tot_sales)排序分成4份.
(1)
between,add_months用法
如：
intCalc.Calc_Date between TO_DATE( '20100330', 'DDMMYYYY') and ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1
如：
intCalc.Calc_Date between TO_DATE( '20100330', 'DDMMYYYY') and ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1
分析：
between .. and ..：
between .. and ..：
相當(dāng)于大于A，小于B。 在其中不能加括號(hào)，如：between（.. and ..）是錯(cuò)的。
add_months("時(shí)間",正負(fù)數(shù))：
正數(shù)，表示之前的幾個(gè)月，負(fù)數(shù)，表示之前幾個(gè)月。
add_months("時(shí)間",正負(fù)數(shù)) +/- A：表示之后之前的幾天。
ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1：表示之后的一個(gè)月的前一天。
ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1：表示之后的一個(gè)月的前一天。
(2)
可以點(diǎn)擊pl/sql的美化功能。F8運(yùn)行功能。
(3)
TRUNC(i.expr_date, 'YYYY-MM'):
TRUNC（date[,fmt]）
其中：
date 一個(gè)日期值
fmt 日期格式，該日期將由指定的元素格式所截去。忽略它則由最近的日期截去
TRUNC（date[,fmt]）
其中：
date 一個(gè)日期值
fmt 日期格式，該日期將由指定的元素格式所截去。忽略它則由最近的日期截去
2.TRUNC(for number)
TRUNC函數(shù)返回處理后的數(shù)值，其工作機(jī)制與ROUND函數(shù)極為類似，只是該函數(shù)不對(duì)指定小數(shù)前或后的部分做相
TRUNC函數(shù)返回處理后的數(shù)值，其工作機(jī)制與ROUND函數(shù)極為類似，只是該函數(shù)不對(duì)指定小數(shù)前或后的部分做相
應(yīng)舍入選擇處理
，而統(tǒng)統(tǒng)截去。
其具體的語法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取處理的數(shù)值
decimals 指明需保留小數(shù)點(diǎn)后面的位數(shù)。可選項(xiàng)，忽略它則截去所有的小數(shù)部分
下面是該函數(shù)的使用情況：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二個(gè)參數(shù)可以為負(fù)數(shù)，表示為小數(shù)點(diǎn)左邊指定位數(shù)后面的部分截去，即均以0記。
其具體的語法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取處理的數(shù)值
decimals 指明需保留小數(shù)點(diǎn)后面的位數(shù)。可選項(xiàng)，忽略它則截去所有的小數(shù)部分
下面是該函數(shù)的使用情況：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二個(gè)參數(shù)可以為負(fù)數(shù)，表示為小數(shù)點(diǎn)左邊指定位數(shù)后面的部分截去，即均以0記。
(3)
oracle獲取子字符串函數(shù)：
substr(‘123456789’,1，2) -從1到2
substr(‘123456789’2) -從2到完
substr(‘123456789’,1，2) -從1到2
substr(‘123456789’2) -從2到完
(4)
oracle:
在oracle中，若group aCurrency,bCurrency,cCurrency，則表示當(dāng)：
aCurrency=HKD,bCurrency=HKD,cCurrency=HKD；
aCurrency=HKD,bCurrency=HKD,cCurrency=USD；
aCurrency=USD,bCurrency=HKD,cCurrency=HKD；
.....
每種排列組合時(shí)，進(jìn)行分組.統(tǒng)計(jì)。
在oracle中，若group aCurrency,bCurrency,cCurrency，則表示當(dāng)：
aCurrency=HKD,bCurrency=HKD,cCurrency=HKD；
aCurrency=HKD,bCurrency=HKD,cCurrency=USD；
aCurrency=USD,bCurrency=HKD,cCurrency=HKD；
.....
每種排列組合時(shí)，進(jìn)行分組.統(tǒng)計(jì)。