SQL精华收集

order by 的数值型灵活使用 19楼空间V)K2^S!hP\
select * from table_a where id=p_id order by decode(函数,'asc',1,'desc',-1)*jsny; 19楼空间B~UPc6z.gXi#Ti

4g]pu?#D~0控制试图的访问时间： 19楼空间5v;?.FF)D3?'[z
6.create view ...
G~3bOD2o+l;Rs0as
z](x;M7J _^0select ... from where exists(select x from dual where sysdate>=8:00am and sysdate<=5:00pm) 19楼空间a$G$e P3IrH

!D;MB+F"w0妙用decode实现排序
(p!K2B mKptAn3G0select * from tabname 19楼空间2f6F#F/@[!AX
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
e tX[s019楼空间'fbVGh
select * from tabname
+Kr,bZ.I&k2d+F0order by decode(mode,'FIFO',rq-sysdate, sysdate-rq)
'd$t+Qn@c4O0
%QV'j/R^X0
?u'Q qcN2m7t0找出某个时期内工作日数： 19楼空间*V6hGYm Nth7p
select count(*) 19楼空间)u%Lf)H ^
Q+XcR"X@
from ( select rownum-1 rnum 19楼空间^D:L!O ul(xo:\.VON
from all_objects
q6cT O;r!?K6]0where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002- 19楼空间\%](S+d
B
|/b%KT
02-01','yyyy-mm-dd')+1 )
gEY"k&[,[5S$O0where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not
|'E2\?&E3QGa7l3Bl0in ( '1', '7' ) 19楼空间 N3WHF0pts:~

*kHuG4L}(h#Uo0我觉得查询重复记录的语句就很经典
5L%\k:t7`6^(pp0select rowid,bdsszd from BADWDJ a where a.rowid != (select max(rowid) from BADWDJ b where a.bdsszd =b.bdsszd)
V3yp.?xm0由它引申的就有很多有用的语句，如昨天回答别人的排序的难题 19楼空间|cII$R4j
select id,bdsszd from BADWDJ a where a.id = (select max(id) from BADWDJ b where a.bdsszd =b.bdsszd) order by id 19楼空间 {1L&Z$y"[O Wy`
19楼空间^WCM8l[L
树型结构表的查询：
*l$ew2m{&j*|MgMl0select ID,PARENT_ID from parent_child
.Ze2[,Ix%_9g$e%bYb0connect by prior id = parent_id
?
pnm7q$h kbN0start with id = 1;
4\e,v;nC N%Xt}0F4lN019楼空间 m7w+hH A4[k j!gt
1.decode这个函数一定需要会，我觉得sql的灵活很多地方都是通过这个function来体现的，相当于if，很好用。 19楼空间
m2BU,PJ*Nt

xN
Ul)ju/R5Y.^02.group by，这个东东想想简单，其实好多统计功能是离不开这个操作的。oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。值得注意的是，当你对事物做过有效的人为归并之后执行group by 往往会更让人心旷神怡。 19楼空间H6bn'{2Q!CoG2w
3.很表竖置的经典写法，也要记住：sum(decode( )) group by ... 19楼空间,`3a'j Zp)X3E @u
注意：需要在一个subquery中确定一个横置判点。 19楼空间q"x5p8L8^
c
4.树形结构表的遍历写法：select ...from ....
|W"v2e
vBB0start with ... connect by prior (父子关系表达式)
h'TP
waq ~(ZTD0
z-SK,Kt0select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var);
-V@qo2aC"e019楼空间9muuoh(d,N
816以上的 一些分析函数如 rank() over() and row_number() over() 19楼空间/BSat;c
当然关于 group by rollup/cube使用的人恐怕特别少 19楼空间}4Ff b4i$mC e
19楼空间3w9ed5H$RH
如何实现最大匹配的操作? 19楼空间(L0cLB-n
例如:给定字符串 '1234', 而表中可能有记录项'1','12','123','1234','12345',要选出'1234'项 19楼空间%M8?-X;Y4q `
select * from ( 19楼空间+Kj;P'~h B
select col_FT from table_FT
_ {5ef
ZB$VO*W1mf?0where instr('12345',col_FT)=1
6@]1b6RVcO9l0order by length(col_FT) desc) 19楼空间q!M3C:V7]0D
where rownum =1
S ~!A't3]4V0
|P@B"\0给你一个意想不到的东西
.G"A!guo~NMHM0
)RKu1hJ;F|0J&Xu$N019楼空间'Vm0w$J3?7gc A o
SQL> select to_char(to_date(12,'yyyy'),'year') from dual;
!Vciu&f8b} vB%@0TO_CHAR(TO_DATE(12,'YYYY'),'YEAR')
0EvrK'a0------------------------------------------ 19楼空间!BN0\;rhlVF.a
twelve
C!Q"f/x+b019楼空间0O:a
|&QI
select to_char(sysdate,'day') from dual
A G'};GW7N-u0还有 d、iw、mm等等格式 19楼空间!i*zn_?SM.G n
?
19楼空间1]#l2fO)E0L
对于translate函数有一个功能

?"JTDi0比如：找出某个字符串中完全是数字 19楼空间0u mIL%WE^F9B
select * from xxx where translate(column1,'1234567890','') = column1; 19楼空间xZ.v _:pV
select trunc(sysdate) from dual;
b({3Jc9A aa
u0select trunc(sysdate,'mm') from dual; 19楼空间xv[aWO$^W
大家构造几个例子看看就能明白 19楼空间3R2d8i"Z,n#o1^ \
select a,b,sum(c) from xxx group by rollup(a,b); 19楼空间_h^g3o

6O#m7lBke1d|0select a,b,sum(c) from xxx group by cube(a,b); 19楼空间`4@(ntE6C(wV)I

8Z;Y|2h5V.Q*j4X:T0怎么查找字符串里面包含有%的记录：
2uP(A]%M#J%G
Kd0当然，常规方法就是利用 escape了 19楼空间!B'Wl6m|n
可如果不知道escape也行，比如 19楼空间6i jp'F&`*Wv]
select * from xxx where replace(a,'%','') = a;
)h:Lp!X F019楼空间&e Gq'n|)Q]
利用decode解决动态sql的不确定条件查询的问题: 19楼空间e0L3rsy|
假设前台传入的都是变量
Bk\XZ:W5N~0select * from xxx where decode(:var,null,'0',column1) = decode(:var,null,'0',:var); 19楼空间 k'u%k~f%@]'M
这样比 like :var||'%' 效率高
p4ic^-NV-y019楼空间%s:ru,^:O xL7{U k'@'`
另:对于 19楼空间q1y9PV1?
select ... 19楼空间.L^u P7Z5TD
from a,b 19楼空间 p*Yx9r*g3KT4[?
where a.a = b.a(+) and b.a is null; 19楼空间:c2?O#~r"j
我想对于不明白的朋友,我要交代一下用处才好: 19楼空间s3r(rK!a
m-Ym
19楼空间!h6q[/}%ZN_\3U
比如,你需要查找在a表中有而b表中没有的记录 19楼空间&y4ZnJ2v:JWK$i]
也许你会选择 not in:
unq&pN0select * from a aa where aa.a1 not in (select a1 from bb);
8JtEjp%L9V7~0这是效率最低的
E&i)X_$MU*_X7o0或者:
$Z+n'e3J4x3H0select a1 from aa 19楼空间L+a5Uw.P3sb
minus
g4{*aU"xe$R^0D"]7^iy0select a1 from bb; 19楼空间0frOK"z

p*_)d!f,k X1Q0所有这些写法,都不如下面下率高: 19楼空间~$[8` dP$d?
select a.* from aa a,bb b 19楼空间S4?&bqN-Rj/?m0E
where a.a1 = b.a1(+) and b.a1 is null;
+J)j0m[2n{Y;B3o!q0

x-NC2O(S@{Rf2`0给一个很普通的适用的最高效的外连接例子（不是什么新鲜玩意）：
p-e-FzUF0select ...
0bwAcKT B)`!l0from a,b
L2J]T p0where a.a = b.a(+) and b.a is null;
$F| L(o6mAh0
%@9A U[No9Y0我要按年龄段（小于20，20-30，---）统计人数，我可以用
pX
nTKp q4m0select 19楼空间:LQ9f ]l(N
sum(decode(sign(age - 20),-1,1,0)), 19楼空间}3eOI$Y*x^QR
sum(decode(sign(age - 20),-1,0,(decode(sign(age - 30,-1,1,0))))), 19楼空间fB0u3r3}
sum(decode(sign(age - 30),-1,0,(decode(sign(age - 40,-1,1,0))))), 19楼空间6DY:PJ(F
Y%M*j
sum(decode(sign(age - 40),-1,0,(decode(sign(age - 50,-1,1,0))))), 19楼空间.Quw/[.gy0[!W
sum(decode(sign(age - 50),-1,0,1))
&o}2W!FN-~|3\0from xxx;
(u.y5ug R1[jO0这样只做一遍表扫描 19楼空间GnA!M0b:BN+p j
这是分了20以下和50以上的 19楼空间(IO%Fy r/]V
类似的问题，自己扩展了
P1Vb0m%b'[+i0
#e4Qr+s:v&c-w%t0添加行号： 19楼空间3l,c"Yx5N
select (select count(*) from a1 where item <= a.item) AS ROW, * FROM a1 as a order by item 19楼空间5X9FoM%_

^1w.E4G5Uj?.wo0select * from table1 a
t0ttA;k0where id in (select top 3 from table1 where 物品=a.物品 order by price desc) 19楼空间R9`.[#ty

'sM1`v6l t1Y0每一种物品有很多价格，每一种物品选择排在前三的纪录
d ?4N2xf0
.D3b mQU01。job的使用： 19楼空间bkm)J&?$u JP
DBMS_JOB.SUBMIT(:jobno,//job号
xY|-Go[W b0'your_procedure;',//要执行的过程
jrq H p1n5GzV`0trunc(sysdate)+1/24,//下次执行时间
'f2Q8A"x9~0'trunc(sysdate)+1/24+1'//每次间隔时间 19楼空间"]*k.iN$bw'o
);
e2~l4vR0删除job:dbms_job.remove(jobno); 19楼空间c r0J0Q4QPQ*g
修改要执行的操作:job:dbms_job.what(jobno,what);
5t v1XfyR+Y&?QvU0修改下次执行时间：dbms_job.next_date(job,next_date);
%g,LM!Lw \1K8o0修改间隔时间：dbms_job.interval(job,interval);
2xDS.eO/{P"@0停止job:dbms.broken(job,broken,nextdate);
`;B E,u7J/YKB!DXIX0启动job:dbms_job.run(jobno);
~*{3IrC G0例子：
Hp_`ji$Cr z0VARIABLE jobno number; 19楼空间x&Jm-E(V9H&P&D%Q
begin
#j
{$Y)j
Po
~ R0DBMS_JOB.SUBMIT(:jobno, 19楼空间hH:TDlX^
'Procdemo;',//Procdemo为过程名称 19楼空间$t$VfW"vPaLGG%G/E
SYSDATE, 'SYSDATE + 1/720');
M VyM V0commit;
Rk!O.MN1f!h N0end;
;yotqgf_7@F0/ 19楼空间/GgkS
cn i?#o#uW
2。把一个表放在内存里 19楼空间Q@fP!I
alter table tablename cache. 19楼空间+a
{tZ"S9?8S
3。创建临时表
&WM]|-g]0CREATE GLOBAL TEMPORARY TABLE TABLENAME ( 19楼空间K'_zo2yr J1da}
COL1 VARCHAR2(10),
2ycPM.WY2nH`(y6m0COL2 NUMBER
rF'D3f4J!a gYf~a3FV0) ON COMMIT PRESERVE(DELETE) ROWS ;
k#rU3k}5M+P*sx%|0这种临时表不占用表空间，而且不同的SESSION之间互相看不到对方的数据
syUcW;r0在会话结束后表中的数据自动清空，如果选了DELETE ROWS，则在提交的时候即清 19楼空间 `x"K"v^z
19楼空间 _5r3tl{
空数据，PRESERVE则一直到会话结束
{hQ|p'MG*S04。加一个自动增加的id号 19楼空间5e!Etj8Um+EXg1m
第一种方法：
Q M
f:w.hjw.ma0第一步：创建SEQUENCE
F/zjGPhg0create sequence s_country_id increment by 1 start with 1 maxvalue
]X8I }7m:})i019楼空间%I,Z|S??
999999999;
6| Yu#Fgp tBX)~0g0第二步：创建一个基于该表的before insert 触发器，在触发器中使用该 19楼空间%z(MM;A8B|G
19楼空间%c3gr}H[:Nb
SEQUENCE
L3J qiX X0create or replace trigger bef_ins_t_country_define
"a6e wC,se&q0before insert on t_country_define
1m y x9Z)\Pu k'`uY0referencing old as old new as new for each row
4O'z Mj&n-X [0begin
9S,C'YhA5W0select s_country_id.nextval into :new.country_id from dual;
2L/?;Y7d Q \ I8Yq8k!u0end; 19楼空间e0r&]%v5~'r{x8m(x0pa
/ 19楼空间7r\Y4aC6h-RmNV
|
第二种方法：
7cz]|&j`Dl0CREATE OR REPLACE TRIGGER TR1 19楼空间m d@%G9tS
BEFORE INSERT ON temp_table 19楼空间u%M+Qf r@ |H
FOR EACH ROW
$v4P:RPM(^t0declare
/V#G0z
RhzNv{_0com_num NUMBER;
TZl/Nv_X(iDl0BEGIN
$N1q`` h0h8Md0SELECT MAX(ID) INTO COM_NUM FROM TEMP_TABLE;
O)Lr Um(V2S|mr@&H)o j0:NEW.ID:=COM_NUM+1;
Orb0~7\/JB0END TR1;
0^W?wa'r Y`0/
ad)H5s$Hi4C05。限制用户登录：创建一个概要文件

oc!U/{0OaO-F0create profile CLERK_PROFILE limit
9qT"b!L)q:L*xY0session_per_user 1 #用户可拥有的会话次数 19楼空间*N.F1i0eb4VT u5J
idle_time 10 #进程处于空闲状态的时间(10分钟) 19楼空间:GzMo)T8x
然后就可以将该概要文件授予一个用户
s4l*kK h2_ [a O&j S0alter user A profile CLERK_PROFILE;
5Z{ WS^.^Z06。使触发器为无效alter trigger yourtriggername disable
\ojB2`l019楼空间#J'Mg HQlk3| u
如果是对于某一个表的所有的触发器：
M3]`|5vH^'a0alter table yourtablename disable all triggers
:a6Yos6]n$aG8{019楼空间bD4}Px&d3k
19楼空间"R @CIZ
更改数据库时间显示格式：
:} niT)NE%vk0SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; 19楼空间!` z2Cbw_E
会话已更改。 19楼空间9K h'Vr;\ga;i
19楼空间b `;kO$f!d
1. 选取 TOP N 行记录 19楼空间1yu W{f"g*C0W$o
A. SELECT * FROM CAT WHERE ROWNUM<=N
Qr2C] |"R5tvc?0B. SELECT * FROM 19楼空间9@S%_;L;]R/Ni:B
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
0kj%p)}&J\y?0WHERE ROWNUM<=N
s}euU.{y,v n2D0
Mc9N2BG4dh7B}5ZBHl02. 选取N1-N2行记录 19楼空间:b_.R
Z G(`,U9A
A. SELECT TABLE_NAME,TABLE_TYPE FROM 19楼空间#Q|9?VZo:n
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ) 19楼空间%iq1}]2S6Gg$e'it5w
WHERE ROWSEQ BETWEEN N1+1 AND N2; 19楼空间,j\Y:UUXw"}F
或:
V#xE6OD:Z0SELECT * FROM CAT WHERE ROWNUM<=N2
0i}8`!Qy(j8x0MINUS 19楼空间 V}bq*u
SELECT * FROM CAT WHERE ROWNUM
^'Yo%mOWDj0B. SELECT TABLE_NAME,TABLE_TYPE FROM 19楼空间,tU"}2Lb`8MO
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE) 19楼空间8w.`q^9}q)y'ii o
WHERE ROWSEQ BETWEEN N1+1 AND N2;
9u&s
w dW
Q0
Cp+A Rmi0查主键名称： 19楼空间|lDM]^9s.y{
select * from user_constraints
9I x.B
\1W:u"p;e0where table_name = 'ART' 19楼空间5k z8W+I\Z*p
and constraint_type ='P'; 19楼空间P.S5Yz,}`E0xT

2cs)L"?V!~6gA0保存过程内容到文件
HH/Nqo EX0先修改init.ora 19楼空间4Pz%eq#tK;_N
例如： 19楼空间s8CGeQ
utl_file_dir=/usr //路径为 oracle所在的盘:/usr 19楼空间+j@'cy8{{u
此过程将用户TEMP的P1过程的代码保存到ORACLE安装盘下/USR/TEXT.TXT中 19楼空间9z}K-M\.an6T
create or replace procedure TEST

|Q4cTSt0is
8_ZOw b]+H?0file_handle utl_file.file_type; 19楼空间n [mJ7EaY
STOR_TEXT VARCHAR2(4000); 19楼空间 R Y \8B/W&_WF
N NUMBER;
\3X$q2[JI jCoZ0I NUMBER; 19楼空间-]ce6Cnt
begin 19楼空间|)d*| GB5k1k1JD
I:=1;
*PlYb'j4E4q`+a0SELECT MAX(LINE) INTO N FROM ALL_SOURCE WHERE WNER='TEMP' AND NAME='P1'; 19楼空间t.[9ih~
file_handle:=utl_file.fopen('/usr','test.txt','a');
KXz|-Z,\[)H0WHILE I<=N LOOP 19楼空间)jU9Jj0Bu g
SELECT TEXT INTO STOR_TEXT FROM ALL_SOURCE WHERE WNER='TEMP' AND NAME='P1' AND LINE= I; 19楼空间ilYTp|R#`%E
I:=I+1;
/M8CZ:[V3N"l0utl_file.put_line(file_handle,stor_text);
S"ib:L*p|vrF E0END LOOP;
^WIA2z Bf8@ y!a0utl_file.fclose(file_handle);
?2n6XrT0commit;
k[(A0h
p0E8n0end TEST;
vXJ/B]C0/ 19楼空间v$g"d|(c*u!e7O-Q%F9l

.EP6{A2j00、建立分区表 19楼空间 pQ3e.xj j7n
create table partition_test 19楼空间4B8o
fUNl)R4z![
( 19楼空间?&_W-?6Z N \8k
id number(9),

~}t~"y8g5eK0tmpStr varchar2(10) 19楼空间p;P#zG;XL0K ?
) 19楼空间jpOb5[ W+K3cE[
partition by range(id)
JO#yy2N4V0(
E/{v|-b |4d0partition id01 values less than (3000000) tablespace test_tabspc1, 19楼空间3T aA/y$v!oj
partition id02 values less than (6000000) tablespace test_tabspc2,
S:Ll1uw3h;H#v0partition id03 values less than (9000000) tablespace test_tabspc3,
m%?uh*|Q
g:i,]0partition id04 values less than (12000000) tablespace test_tabspc4, 19楼空间W`.[!j5q
@pu
partition id05 values less than (MAXVALUE) tablespace test_tabspc5 19楼空间
NI\9zb1C
) 19楼空间%A,h+ev?hlI|
/
-iSF$CPOZB0
vf? m'E+CtH _01、建立局部分区索引 19楼空间hV#^,^?7P[N
Create index your_index on caishui.partition_test(id)
KBv!Jh:LB0local 19楼空间1]~R}O/W&LaB]
(
1\^D6{C3@H ?)fC0partition id01 tablespace test_tabspc1,
P*U}4{`p0partition id02 tablespace test_tabspc2, 19楼空间Z1xO\A4@}Vm_U
partition id03 tablespace test_tabspc3,
(W/ep^j Up0partition id04 tablespace test_tabspc4, 19楼空间x{a@+h
partition id05 tablespace test_tabspc5
Bxh8A `$\(Pc0) 19楼空间;~'HK*fKv
/
4^gJ-Tk7VU019楼空间#w aw \8GL2w d8jV
2、重建某一个分区的索引 19楼空间q"f\H0xe p
alter index your_index rebuild partition id01 tablespace test_tabspc1
/B1ih&RO.TjZCv0/
b9B!SG es J019楼空间w6s^Te x!LF O
3、增加分区
]$Ns,uOc3I$S;[0alter table caishui.partition_test 19楼空间 Q$I6y2Q5jmi]
add partition id06 values less than (15000000) tablespace test_tabspc6
xdc)[jGYE0/ 19楼空间He BR&d2Lz
19楼空间-?s5V
[ QZ+v
4、有影响
cR:]`-X3N:S7U&r)h0
J7jL&E1?05、可以
O;k9rjsm
?5~i j7j_0ALTER TABLE PARTITION_TEST
5uJX;M*WI0MERGE PARTITIONS
4Q[4j/_MD\Wo6g0id01, id02 19楼空间9^xj!U4Wk;x~5b
INTO PARTITION 新分区名 19楼空间9`c3[z-w$T-E5K
/
TtJ BHI_0
7_'],EHmRF3^b06、外部数据文件 d:\test.txt
.L9N+h-e6oCn }01|猪八戒
M1I"M]7wyy
D02|孙悟空
$GmDB*y'J$E03|唐僧 19楼空间4V'cy!PDb
19楼空间rBw1a]\he|2ey
建一个控制文件 d:\test.ctl 19楼空间 d4RxAOs
load data
1B:\ }#o.a/W2@7z0infile 'd:\test.txt'
^C5v7h]0append
&WE t9b/~IaL0G-C0j)^0into table partition_test 19楼空间7|4MY!V3NM:y
FIELDS TERMINATED BY "|"
QS4OD
f1j ?+B0(id,tmpStr)
SL9L2o@
g-n x9]0
'i-]HV7o@m }0将数据文件的数据导入数据库 19楼空间2w#D?#v4U?q'B-Y
sqlldr userid=caishui/password control=d:\test.ctl 19楼空间3xz F2QcwE
19楼空间7h)x8V}Fm'w-F
如何正确利用Rownum来限制查询所返回的行数？ 19楼空间S)RZ-s+j+f

wu6gEF|!z.h)J7E;g^0软件环境： 19楼空间$Ah'ts&dH[
1、Windows NT4.0+ORACLE 8.0.4 19楼空间eXx jOmU
2、ORACLE安装路径为：C:\ORANT 19楼空间/x
\'X B4uob8E3g

*z2d%scR0q'[8TN0含义解释：
7F"ND8H W~3R01、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2， 19楼空间L7DG!q5b } s&|"Y
　 依此类推，这个伪字段可以用于限制查询返回的总行数。 19楼空间8gFR;iPK;^^
2、rownum不能以任何基表的名称作为前缀。 19楼空间#R6Zv&w8d,]
使用方法：
I6ev\9c!rK:k1h,v`h0现有一个商品销售表sale，表结构为：
;e2^ \A8DR0`%S?-M8N0month　　　 char(6)　　　　　 --月份
Dy8Y`%H5Wq^H0sell　　　　number(10,2)　　　--月销售金额
%koD2D8FF019楼空间w-?*|6]n%GPk+c/h
create table sale (month char(6),sell number);
r,U2U*ciR[#}V3k]0insert into sale values('200001',1000);
J m e Uj2@*B2[)H'Z0insert into sale values('200002',1100);
#f5Ks]#xB'CXI0insert into sale values('200003',1200); 19楼空间E9LE#U5D4YP(g*cL
insert into sale values('200004',1300);
g#rp"l+@'l pS$j0insert into sale values('200005',1400); 19楼空间5r0i9R H$hO
insert into sale values('200006',1500); 19楼空间~"{0J-LIPc
insert into sale values('200007',1600); 19楼空间 o;Zp#i-^
Il
insert into sale values('200101',1100);
ul-D#md!w0insert into sale values('200202',1200);
ob,G-t!s0insert into sale values('200301',1300); 19楼空间!o9j7O9\z(CHM$TI
insert into sale values('200008',1000);
yHG1\a0commit; 19楼空间J/?k,K][v"j3~o
19楼空间(E gh'd2R8Z
SQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标） 19楼空间 N'k,P)k$~D

R+S!Rjh$l0ROWNUM MONTH SELL 19楼空间-v.|(T [#^
--------- ------ --------- 19楼空间f8f9L5_ W;}Rz
1 200001 1000
@mt]B6V
H019楼空间/I/{df$X4z,qw!V
SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录） 19楼空间L5@"v/w"~(m1u.^

6G!D)q{ YtqP(Z0没有查到记录
+w^*F(M7r019楼空间(? Ir3s$@,Ke z%aZ [g1~
SQL> select rownum,month,sell from sale where rownum>5; 19楼空间'_B+WNzQ
（由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录） 19楼空间7h"{?|r3lU9~#ws

%CY\^T%p3YRI0
5m[.x'UAEK$S0没有查到记录 19楼空间 H'rtP'@\|Gv
19楼空间_/X Gt-zW&rI
只返回前3条纪录
&V9Pr,sOQ[ @@H0SQL> select rownum,month,sell from sale where rownum<4;
t-A!Pv0C![%Tj019楼空间/GUqmj"P.@B
ROWNUM MONTH SELL
2A,S}(g
P:bf0--------- ------ --------- 19楼空间 u qx1?0e"Dj-a,c
1 200001 1000
fN"l
c]M\Ro.|)J@/C02 200002 1100 19楼空间8h lilSd"C
3 200003 1200
.z$?s!l
w _ C0
ZeQ gW2g8`{0
_-e1B;j"tg0如何用rownum实现大于、小于逻辑？（返回rownum在4—10之间的数据）（minus操作，速度会受影响）
HV {9Rf]E+Qw0SQL> select rownum,month,sell from sale where rownum<10
@!DTl9wI\02 minus
2\q{_{ m~ cG03 select rownum,month,sell from sale where rownum<5; 19楼空间N$Q XiC)Q;N%tn
19楼空间u"@\DhE?&Ls
ROWNUM MONTH SELL
7h{FV1y0--------- ------ --------- 19楼空间%EB9|(qH
5 200005 1400
b5j n
ZC2Q Yl06 200006 1500
8WQP$@2V0{+D[07 200007 1600 19楼空间/I mY%Dy2JD
8 200101 1100 19楼空间4u^i0lQa+Rv+u
9 200202 1200 19楼空间6kl9uO`;QzP

3V
\~`W0想按日期排序，并且用rownum标出正确序号（有小到大） 19楼空间bJlxn
SQL> select rownum,month,sell from sale order by month;
)H3\W X'a^019楼空间pQt,~F)O!X B!xv
ROWNUM MONTH SELL
?"Q-[;m#X4N6t0--------- ------ ---------
p"X/~FX8wk-Y01 200001 1000 19楼空间yzK)C~q
2 200002 1100
wUS-VZf03 200003 1200
Wj@!K2V!J#N/^r04 200004 1300 19楼空间6s-N y9t^&d?
5 200005 1400
2H+eutu+IA;s.U;a06 200006 1500
5U*JJD.|07 200007 1600 19楼空间*IQCD%W9V8g
11 200008 1000 19楼空间.AX0U NC#dQ1_ J _
8 200101 1100
9` ^+P O-ycVqk09 200202 1200 19楼空间8E%i^2tBnj9_dG
10 200301 1300
7Qys p8}u?LtH
p0
:y([Ym*h7ff}3U0查询到11记录. 19楼空间7D%|BION8}
19楼空间
Le%Z}a6i%n
可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的
)da
k)~R,qo019楼空间Zf8B AY8|DOJ ?Qe
SQL> select rowid,rownum,month,sell from sale order by rowid;
-Km-C-]gM+{l1L^N019楼空间 h h4[z)dm"h
ROWID ROWNUM MONTH SELL
%a%u;A%Heb!@0------------------ --------- ------ --------- 19楼空间q+B(K0Y^(p.sp2jsV
000000E4.0000.0002 1 200001 1000 19楼空间4_:Q#vhT)Dz
000000E4.0001.0002 2 200002 1100 19楼空间`"Ab7].X$?W
000000E4.0002.0002 3 200003 1200
[N Oh }Oy0000000E4.0003.0002 4 200004 1300
_XPa,Rg#m K{0000000E4.0004.0002 5 200005 1400 19楼空间BUr[(c
000000E4.0005.0002 6 200006 1500
G!u*nF
S[6f0J7MG0000000E4.0006.0002 7 200007 1600 19楼空间MSLj$H1i&o6r
000000E4.0007.0002 8 200101 1100 19楼空间i/H{O*NP3\9i
000000E4.0008.0002 9 200202 1200
$Z+?+a3} v'`J4K5G0000000E4.0009.0002 10 200301 1300
2E!S4`Io4g0000000E4.000A.0002 11 200008 1000
0tGvp$O |(b"]c019楼空间yL
{uR(a } X
查询到11记录.
'H%ix-H yq&e:F0
Lm N#HY;y$j#G|0正确用法，使用子查询 19楼空间t uDVz lv*v.w V:yC
SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13; 19楼空间3Oy+lk/`

WgSD+Ej%|X v7~0ROWNUM MONTH SELL
7R
Oz9s"q^0--------- ------ ---------
/dQUU"@#Q+u&S Z-B-Z |01 200001 1000 19楼空间 r1^#E'[XX
2 200002 1100
+{U&g"eE.]A03 200003 1200
,Q&e1p}+\%j04 200004 1300
o5A8q,z|\ E,C05 200005 1400
3x!mw3R_G06 200006 1500 19楼空间}f0p+l!zg
X!Rju
7 200007 1600 19楼空间,i9H$Hx5Zp9}*wR
8 200008 1000 19楼空间
aI+{4aLHP
9 200101 1100
$M"xYR8?To\t-@ j+WC010 200202 1200 19楼空间}$U+V([&[o
11 200301 1300
J:hL"B ^\Lp019楼空间0F"s]_R`
按销售金额排序，并且用rownum标出正确序号（有小到大） 19楼空间6Ijb{So/DA7r(kTM
SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13; 19楼空间UX(S @I

{0P|z|k4oh0ROWNUM MONTH SELL 19楼空间,h$wU$O V l4Px1N.l
Q
--------- ------ ---------
]jd\$g W4W _9u"V D01 200001 1000
Jg"f4d&Fu(s{02 200008 1000
3N?3n/l)g(z03 200002 1100 19楼空间'b*{l6G IGv a
4 200101 1100 19楼空间V'Hk:ZFJ;f$e
5 200003 1200 19楼空间g J*o&J3cTPV
6 200202 1200
(Gd6J
Q3]ur\7jo7cYs07 200004 1300 19楼空间Rw#_p @O
8 200301 1300
:]G4x7M:bc"_^09 200005 1400
.|(y9N\J^3L%t010 200006 1500 19楼空间&jy~4Llta+\m ^%O
11 200007 1600 19楼空间&m-A"|+z:{.T M6[&p
19楼空间{]v [f
查询到11记录.
2w,b|djc019楼空间#J5@U T1jRN
利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。 19楼空间0{:v'ci#oJ6`$x"m,[

#C%DYK4uC SU0返回第5—9条纪录，按月份排序
t;X"X_5Z
\|Ui0SQL> select * from (select rownum row_id ,month,sell
/iW9T&zE!g02 from (select month,sell from sale group by month,sell))
4BA&S$^@4[4Ei03 where row_id between 5 and 9;
c8yx"GN y(SK7}0
?m8v9{{0ROW_ID MONTH SELL
VT*{!TN v0---------- ------ ---------- 19楼空间 c\-oAu8Q
5 200005 1400
YK6H)ypm-^? S06 200006 1500 19楼空间x%P#I@X0XT!S*b%P
7 200007 1600 19楼空间m_0B2F5V5`
B4X
8 200008 1000 19楼空间B/_(g4owmmS
9 200101 1100 19楼空间M:u7X-C^z*BJ(R
19楼空间 q[?*W
T1\R
(1)
8t(\mXbze d019楼空间!DC7wq5b
查所及杀锁 19楼空间}w@IzH7Z8[$p
select l.session_id sid,
zY}$I2WX8\0l.locked_mode lockmode, 19楼空间/U$P5a(P]H G
l.oracle_username db_user,
1Xh\8g^*S0o0l.os_user_name os_user, 19楼空间 c CSP(yG3BV
s.machine,
(n ezx%}jHkvk0s.schemaname,
_!F:UcM
nV?#h0o.object_name tablename, 19楼空间KQ T
r9~$fT${k#J
q.sql_text 19楼空间&_Zt0MK0{.C*} H.m
from v$locked_object l, v$session s, v$sql q, all_objects o 19楼空间sqwJ2CoLD
where l.session_id=s.sid and 19楼空间vf e5aW? ]6C6x5Q
s.type='USER' and
Refx8Xo Xm$j0s.sql_address=q.address and
ac bGJfN!L0l.object_id=o.object_id 19楼空间,G*otF?4g(c&Q4Y0[bw

;U@%FSI0f9|kQ7i0alter system kill session 'sid,SERIAL#'
k.Qh9J L5g u0
5t6oJ6GCq4DZ ]n019楼空间W6KN
I5W r:Vm/C~*^"q
1.having 子句的用法
U!b`2_R019楼空间]2q.R]6Qn#\
　　having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.
p+x*`c#VO
}0
4e@"MN%lFgn0　　2.外部联接"+"的用法
j/lS`r019楼空间8\ NPu$u`
　　外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢 19楼空间:]/ek`g
19楼空间;tfw
^,ZMM~
select a.empno from emp a where a.empno not in 19楼空间'X5OPW3Cx

!E7k-u[)n"uP5eE0z0(select empno from emp1 where job=’SALE’); 19楼空间#x'MO,]6u)T

2uYw7R(Gq%k7@8t0　　倘若利用外部联接,改写命令如下:
6y*y'F%Rl M;Tx;I-KM0
ir~T*L?sE$A6y$J0select a.empno from emp a ,emp1 b 19楼空间9I5wzU
e$]q

jp|3ES5o e"p2n0where a.empno=b.empno(+)
nJw
D;o ^+T,q0
_ ~?NSa*Q^G0and b.empno is null
ESd)Mg?CTb019楼空间x9Uf)a$_VM
and b.job=’SALE’; 19楼空间 r^LL{n@.yL
19楼空间0GC#HVca+Y;_
　　可以发现,运行速度明显提高. 19楼空间t8l9tT&m

c*zi#f1h6F%lmXp&o03.删除表内重复记录的方法 19楼空间7BR?*sL/RX.o


B R ?5U.\7S|G ^2h0　　可以利用这样的命令来删除表内重复记录:
eM _.dNu{7x}0
4R1k7]LR0delete from table_name a 19楼空间*s%V+A6oTn(X

2s8M8yO*H'h_0where rowid< (select max(rowid) from table_name 19楼空间+e&~aCuz1e&M
19楼空间p*b*a%W?0zY X
where column1=a.column1 and column2=a.column2 19楼空间t!j8F"I_,^ g

V@{Uyug B0and colum3=a.colum3 and ...);
}H w7k5H0P ^019楼空间C uq(Jm$bT.V
{
问：用ORACLE的like（匹配操作命令）操作时，要查的条件含有特殊符号（_或%），该怎样写？ 19楼空间'u,bH)e+\1l"t
如我要找出以tt_开头的表，若安以下写法只能取出以tt开头的表， 19楼空间V#]
]'Vv a!s,]
因为_在like中用意是任意单一字符。
K(m9G{}
g u0SELECT Tname FROM tab
F9x0OBB4H(XSNM#c0WHERE Tname like 'tt_％'
r9m[%l/S+^0
8R*e.aNCt!a!V019楼空间}H7w8^*V3ZJ#m
答：SELECT * FROM tab 19楼空间,gY5d} S
a:g0l[
WHERE tname LIKE 'TT/_%' ESCAPE '/'
\+GJCG?f ?019楼空间tmyL Z DZ*S
自增字段： 19楼空间(r6c4d3qJ-s
ORACLE一般的做法是同时使用序列和触发器来生成一个自增字段. 19楼空间)USW CYe
CREATE SEQUENCE SEQname
8pC M3mP2qAc0INCREMENT BY 1 19楼空间
V+sq&u8P#Z |+FV
START WITH 1
?ik4Ty0MAXVALUE 99999999 19楼空间|@8Bu ?)|c um
/ 19楼空间O[%T5x;`[dr
CREATE TRIGGER TRGname
*a6t qHzD{|o0BEFORE INSERT ON table_name 19楼空间-@q#I3sX0X$c[Ek
REFERENCING 19楼空间ss1F2NZz8c)kV,_
NEW AS :NEW 19楼空间-qWD9d?/f
FOR EACH ROW 19楼空间 XQG.G1w#S!m6S$rjg
Begin
9u3|Vm oxl S0SELECT SEQname.NEXTVAL

Fu8b"P-L0INTO :NEW.FIELDname 19楼空间+Xz C N+?+JTNb
FROM DUAL;
[;YSi{0End;
Sq6E7MN*C!S0/ 19楼空间R6P8uvp)Cvz
19楼空间O$P"YH\Gm r6M1d
动态sql:
9DQ*^p`ggH0在oracle8.1.5中： 19楼空间!`f#@\%L
用execute immediate来实现 19楼空间 g(B4Q J L.Gctq
C*b A
declare 19楼空间 CGTUX2C
tsql varchar2(200);
|b&M Sx0begin
5_/Qk_:D'f`0tsql:='insert into '||tname||'values ('aaa','bbb')'; 19楼空间4]nR^"t
EuD
execute immediate tsql; 19楼空间;|"X@(W&u&cVlPs;?p
end; 19楼空间(An_ Mc,Dtc L"a
/ 19楼空间.V3a9|tkJ
19楼空间+H6A4^ ^v*J/W$g3a9P+z

,UF3|9I&{0
n^b/HV;EU0说明：复制表(只复制结构,源表名：a 新表名：b)
Z YM)a A.N0SQL: select * into b from a where 1<>1
yTKB%I+G0说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
b4nh;v~ l0SQL: insert into b(a, b, c) select d,e,f from b; 19楼空间:?
c,x?*Ui
说明：显示文章、提交人和最后回复时间 19楼空间 @9CcY5~"T5T1ALX;P
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 19楼空间t^:P d]Qo
说明：外连接查询(表名1：a 表名2：b) 19楼空间:to(YQ5l
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

z RL){JZ\0说明：日程安排提前五分钟提醒 19楼空间1j5K+W%M
ks6R2ah
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 19楼空间u0O_i#]-yLC2X
说明：两张关联表，删除主表中已经在副表中没有的信息
Fi:x5y#D%@t&RM0SQL:
ej_:sYA?0delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 19楼空间'l)R?Ix'gZ$}
说明：--
3y9_F4o.raZ0SQL:
s.x5q_,j)~-T ~vj0SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
,L,x!K`"uL,^n'{0FROM TABLE1,
e
~P~lK8u
sL0(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
,k$q'O,v"i2^:}f"?0FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND 19楼空间:gcG.L#s@0[,v0H
FROM TABLE2 19楼空间O5TcK x][N#|
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
.BFa uo0(SELECT NUM, UPD_DATE, STOCK_ONHAND 19楼空间r3{9N&y.x6Z GfD
FROM TABLE2
mv/D;pJOa^/V0O
~0WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
|U.]-e ` w0TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
2Z-rV]Eb0WHERE X.NUM = Y.NUM （+） 19楼空间ot&K;Z;L
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B 19楼空间xpG%M0?Xj%L
WHERE A.NUM = B.NUM 19楼空间L/@P,q9B
说明：--
3Mj-`f"FWs0|
r0SQL: 19楼空间jh+W)SR$N9v!R$E
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
)s:u
QrwT5oJ)F0?0e0说明： 19楼空间HGuH'?:l:u?
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
HK&rO!zy4a^$E)U0SQL:
]V*cn`$KX0SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SE[e
K'F+I_,q
w|#|0SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, 19楼空间JNwnB6u^
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, 19楼空间3o{X8V.YY
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, 19楼空间 c\.V)v_*e"T){
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, 19楼空间ALu@H4E s
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
`@5ZD$BE9f0SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
'l.W!I{2Dk$Hm0SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, 19楼空间,O'KT0[f/x'[S*[
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, 19楼空间x4rO p8zv+K+[)ln
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
M l5A/~ M#V1d0SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
f1X1v;Z mJ0SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, 19楼空间 O]YwqlK0d:uC*Y {
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC 19楼空间 f:g$\\|Et*l
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
0WQv4R,Oc
A0FROM TELFEESTAND a, TELFEE b
:ghR7N D0WHERE a.tel = b.telfax) a 19楼空间,y2^$T[R(Q
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 19楼空间}i9?,t:X8_ ^7]|Qz
说明：四表联查问题：
]*o'W~}0SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 19楼空间%l2F/? K6y5B

mt\*jK2X[\0说明：得到表中最小的未使用的ID号
3G#U\
RpR,b iD0SQL: 19楼空间(BO o#Ch"e
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
X o)LfYF!Q O"wS0FROM Handle
QqP lG0WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)