[ת] ½âÎöoracleµÄROWNUM ×÷Õß: chen_liang
¶ÔÓÚ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
---------- ------ ---------------------------------------------------
ÄÇÈçºÎ²ÅÄÜÕÒµ½µÚ¶þÐÐÒÔºóµÄ¼Ç¼ѽ¡£¿ÉÒÔʹÓÃÒÔϵÄ×Ó²éѯ·½·¨À´½â¾ö¡£×¢Òâ×Ó²éѯÖеÄrownum±ØÐëÒªÓбðÃû£¬·ñÔò»¹ÊDz»»á²é³ö¼Ç¼À´£¬
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
1.Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì
Oracle Öеĸ÷¸ö½ø³ÌÒªÍê³Éij¸öÌØ¶¨µÄÈÎÎñ»òÒ»×éÈÎÎñ£¬Ã¿¸ö½ø³Ì¶¼»á·ÖÅäÄÚ²¿Äڴ棨PGA Äڴ棩À´
Íê³ÉËüµÄÈÎÎñ¡£Oracle ʵÀýÖ÷ÒªÓÐ3 Àà½ø³Ì£º
·þÎñÆ÷½ø³Ì£¨server process£©£ºÕâЩ½ø³Ì¸ù¾Ý¿Í»§µÄÇëÇóÀ´Íê³É¹¤×÷¡£ÎÒÃÇÒѾ¶ÔרÓ÷þ
ÎñÆ÷ºÍ¹²Ïí·þÎñÆ÷ÓÐÁËÒ»¶¨µÄÁ˽⡣ËüÃǾÍÊÇ·þÎñÆ÷½ø³Ì¡£
º ......
oracle ĬÈϸôÀëµÈ¼¶ÊÇ£º¶ÁÒÑÌá½»¡£
²éÑ¯Ëø¶¨£¬·ÀÖ¹ÁíÍâÓû§¸üУº
select * from books for update;
µ±Ç°Óû§¸üÐÂÖ®ºó£¬ÁíÍâÓû§¿ÉÒÔ¸ü¸Ä¡£
01¡¢±íÁ¬½Ó
¼Ù¶¨from×Ó¾äÖдÓ×óµ½ÓÒÁ½¸ö±í·Ö±ðΪA£¬B±í¡£
ÄÚÁ¬½Ó£ºÑ¡È¡A¡¢B±íµÄÍêȫƥÅäµÄ¼¯ºÏ£¬Á½±í½»¼¯£º
select empno,ename,emp.deptno A,dept.deptno B,dname from emp ......
Oracle Ö÷ÒªÅäÖÃÎļþ½éÉÜ£º
profileÎļþ£¬oratab Îļþ£¬Êý¾Ý¿âʵÀý³õʼ»¯Îļþ initSID.ora£¬¼àÌýÅäÖÃÎļþ£¬ sqlnet.ora Îļþ£¬tnsnames.ora Îļþ
1.2 Oracle Ö÷ÒªÅäÖÃÎļþ½éÉÜ
1.2.1 /etc/profile Îļþ
ϵͳ¼¶µÄ»·¾³±äÁ¿Ò»°ãÔÚ/etc/p ......