oracleÖÐrownumµÄÓ÷¨
rownum ÕæµÄÊǺܺõĶ«Î÷£¬ÏÖÔÚ´ó¶àÊýÓÃÓÚ´æ´¢¹ý³Ì·ÖÒ³. µ«ÔõôÓÃÄØ£¿Õâ¾ÍÀ´Ñо¿Ò»Ï¡£ ºÙºÙ
--- ÒÔÏÂΪתÔØÖ®´¦£¬½ö¹©Ñ§Ï°
¶ÔÓÚrownumÀ´ËµËüÊÇoracleϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖƲéѯ·µ»ØµÄ×ÜÐÐÊý£¬¶øÇÒrownum²»ÄÜÒÔÈκαíµÄÃû³Æ×÷Ϊǰ׺¡£
¾ÙÀý˵Ã÷£º
ÀýÈç±í£ºstudent(ѧÉú)±í£¬±í½á¹¹Îª£º
ID¡¡¡¡¡¡ char(6)¡¡¡¡¡¡¡¡¡¡ --ѧºÅ
name¡¡¡¡¡¡¡¡VARCHAR2(10)¡¡¡¡¡¡--ÐÕÃû
create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘ÕÅÒ»’);
insert into sale values('200002',‘Íõ¶þ’);
insert into sale values('200003',‘ÀîÈý’);
insert into sale values('200004',‘ÕÔËÄ’);
commit;
(1) rownum ¶ÔÓÚµÈÓÚijֵµÄ²éѯÌõ¼þ
Èç
¹ûÏ£ÍûÕÒµ½Ñ§Éú±íÖеÚÒ»ÌõѧÉúµÄÐÅÏ¢£¬¿ÉÒÔʹÓÃrownum=1×÷ΪÌõ¼þ¡£µ«ÊÇÏëÕÒµ½Ñ§Éú±íÖеڶþÌõѧÉúµÄÐÅÏ¢£¬Ê¹ÓÃrownum=2½á¹û²é²»µ½Êý¾Ý¡£Òò
Ϊrownum¶¼ÊÇ´Ó1¿ªÊ¼£¬µ«ÊÇ1ÒÔÉϵÄ×ÔÈ»ÊýÔÚrownum×öµÈÓÚÅжÏÊÇʱÈÏΪ¶¼ÊÇfalseÌõ¼þ£¬ËùÒÔÎÞ·¨²éµ½rownum =
n£¨n>1µÄ×ÔÈ»Êý£©¡£
SQL> select rownum,id,name from student where rownum=1;£¨¿ÉÒÔÓÃÔÚÏÞÖÆ·µ»Ø¼Ç¼ÌõÊýµÄµØ·½£¬±£Ö¤²»³ö´í£¬È磺ÒþʽÓα꣩
SQL> select rownum,id,name from student where rownum=1;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 ÕÅÒ»
SQL> select rownum,id,name from student where rownum =2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
£¨2£©rownum¶ÔÓÚ´óÓÚijֵµÄ²éѯÌõ¼þ
Èç¹ûÏëÕÒµ½´ÓµÚ¶þÐмǼÒÔºóµÄ¼Ç¼£¬µ±Ê¹ÓÃrownum>2ÊDz鲻³ö¼Ç¼µÄ£¬ÔÒòÊÇÓÉÓÚrownumÊÇÒ»¸ö×ÜÊÇ´Ó1¿ªÊ¼µÄαÁУ¬Oracle ÈÏΪrownum> n(n>1µÄ×ÔÈ»Êý)ÕâÖÖÌõ¼þÒÀ¾É²»³ÉÁ¢£¬ËùÒԲ鲻µ½¼Ç¼
SQL> select rownum,id,name from student where rownum >2;
ROWNUM ID NAME
---------- ------ ------------------------------------
Ïà¹ØÎĵµ£º
oracleµÄÌåϵºÜÅÓ´ó£¬ÒªÑ§Ï°Ëü£¬Ê×ÏÈÒªÁ˽âoracleµÄ¿ò¼Ü¡£ÔÚÕâÀ¼òÒªµÄ½²Ò»ÏÂoracleµÄ¼Ü¹¹£¬ÈóõѧÕ߶ÔoracleÓÐÒ»¸öÕûÌåµÄÈÏʶ¡£
¡¡¡¡1¡¢ÎïÀí½á¹¹£¨ÓÉ¿ØÖÆÎļþ¡¢Êý¾ÝÎļþ¡¢ÖØ×öÈÕÖ¾Îļþ¡¢²ÎÊýÎļþ¡¢¹éµµÎļþ¡¢ÃÜÂëÎļþ×é³É£©
¡¡¡¡¿ØÖÆÎļþ£º°üº¬Î¬»¤ºÍÑéÖ¤Êý¾Ý¿âÍêÕûÐԵıØÒªÐÅÏ¢¡¢ÀýÈ磬¿ØÖÆÎļþÓÃÓÚʶ±ðÊý¾ÝÎļ ......
declare
begin
--SQLÓï¾ä
--Ö±½ÓдµÄSQLÓï¾ä(DML/TCL)
--¼ä½Óдexecute immediate <DDL/DCLÃüÁî×Ö·û´®>
--select Óï¾ä
<1>±ØÐë´øÓÐinto×Ó¾ä
&n ......
Ò»¡£Oracle·þÎñÆ÷µÄ³£ÓÃÃüÁîÐÐÏêϸ½²½â
1.Êý¾Ý¿â¹ÜÀíÏà¹Ø
¡¡¡¡bbed.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ý¿éä¯ÀÀºÍ±à¼¹¤¾ß
¡¡¡¡dbv.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾ÝÎļþÂß¼¼ì²â¹¤¾ß
¡¡¡¡exp.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ýµ¼³ö¹¤¾ß
¡¡¡¡imp.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ýµ¼È빤¾ß
¡¡¡¡mig.exe¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Êý¾Ý¿âÒÆÖ²¹¤¾ß
¡¡¡ ......
Àý£º
create user his identified by his default tablespace users temporary tablespace temp;
grant connect,resource,dba to his;
create tablespace his
logging
datafile 'd:\oracle\product\10.2.0\oradata\zjxsh\his.ora' size 100M extent
management local segment space management auto;
exp his/ny@ny ......