Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

OracleµÄrownumÔ­ÀíºÍʹÓÃ

Ô­Îijö×Ô£ºhttp://tenn.javaeye.com/blog/99339
ÔÚOracle
ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum
¾Í¸ã¶¨ÁË¡£ 
select * from emp where rownum
 <= 5 
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum
ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
 
select * from emp where rownum
 > 5 
ÔòÊÇʧ°ÜµÄ¡£ÒªÖªµÀΪʲô»áʧ°Ü£¬ÔòÐèÒªÁ˽ârownum
±³ºóµÄ»úÖÆ£º 
1 Oracle
 executes your query.
2 Oracle
 fetches the first row and calls it row number 1.
3 Have we gotten past row number meets the criteria? If no, then Oracle
 discards the row, If yes, thenOracle
 return the row.
4 Oracle
 fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5 Go to step 3.
Á˽âÁËÔ­Àí£¬¾ÍÖªµÀrownum
>²»»á³É¹¦£¬ÒòΪÔÚµÚÈý²½µÄʱºò²éѯ³öµÄÐÐÒѾ­±»¶ªÆú£¬µÚËIJ½²é³öÀ´µÄrownum
ÈÔÈ»ÊÇ1,ÕâÑùÓÀÔ¶Ò²²»»á³É¹¦¡£
ͬÑùµÀÀí£¬rownum
Èç¹ûµ¥¶ÀÓÃ=,Ò²Ö»ÓÐÔÚrownum
=1ʱ²ÅÓÐÓá£
¶ÔÓÚ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;£¨¿ÉÒÔÓÃÔÚÏ


Ïà¹ØÎĵµ£º

Oracle·ÖÒ³²éѯÓï¾ä

·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐз­Ò³µÄԭʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN >= 21¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶à ......

Oracle MATERIALIZED VIEW ´´½¨ÎﻯÊÓͼ

ÎﻯÊÓͼ¸ÅÊö
OracleµÄÎﻯÊÓͼÌṩÁËÇ¿´óµÄ¹¦ÄÜ£¬¿ÉÒÔÓÃÔÚ²»Í¬µÄ»·¾³ÖС£ÔÚ²»Í¬µÄ»·¾³ÖУ¬ÎﻯÊÓͼµÄ×÷ÓÃÒ²²»Ïàͬ¡£Êý¾Ý²Ö¿âÖеÄÎﻯÊÓͼÖ÷ÒªÓÃÓÚÔ¤ÏȼÆËã²¢±£´æ±íÁ¬½Ó»ò¾Û¼¯µÈºÄʱ½Ï¶àµÄ²Ù×÷µÄ½á¹û£¬ÕâÑù£¬ÔÚÖ´Ðвéѯʱ£¬¾Í¿ÉÒÔ±ÜÃâ½øÐÐÕâЩºÄʱµÄ²Ù×÷£¬¶ø´Ó¿ìËٵĵõ½½á¹û¡£ÔÚÊý¾Ý²Ö¿âÖУ¬»¹¾­³£Ê¹ÓòéÑ¯ÖØÐ´£¨query r ......

oracle ½éÖʻָ´¼ò½é

Èç¹ûÊý¾ÝÎļþË𻵣¬»áÓöµ½1157 ºÍ 1110µÄ´íÎó¡£ÕâʱºòÐèÒª½éÖʻָ´Êý¾Ý¿â¡£
»Ö¸´Êý¾Ý¿âÐèÒªÓÐÏàÓ¦µÄ±¸·ÝºÍ±¸·ÝÒÔÀ´²úÉúµÄËùÓеĹ鵵ÈÕÖ¾ÎļþºÍÁª»úÈÕÖ¾Îļþ¡£
Èç¹ûÔÚmountµ½openµÄʱºò·¢ÏÖÆÕͨµÄÊý¾ÝÎļþË𻵣¬Êý¾Ý¿â»áÍ£ÁôÔÚmount״̬Ï£¬Õâʱºò¿ÉÒÔÖ´ÐÐalter database datafile x offline drop ºÍalter database open à ......

Oracle×óÓÒÈ«Á¬½Ó×ܽá

--½¨Á¢²âÊÔÊý¾Ý
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);
commit;
--×ó:
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a left ......

oracle 10G ÎﻯÊÓͼÐÂÌØÐÔ(²âÊÔЧ¹û²»ÀíÏë)

http://mrhaozi.itpub.net/post/41048/495175
ÎﻯÊÓͼ
ÀûÓÃÇ¿ÖÆ²éÑ¯ÖØÐ´ºÍеÄÇ¿´óµÄµ÷Õû¹ËÎʳÌÐò — ËüÃÇʹÄú²»ÔÙÐèҪƾ²Â²â½øÐй¤×÷ — µÄÒýÈ룬ÔÚ 10g ÖйÜÀíÎﻯÊÓͼ±äµÃ¸ü¼ÓÈÝÒ×
ÎﻯÊÓͼ (MV) — Ò²³ÆÎª¿ìÕÕ — Ò»¶Îʱ¼äÀ´ÒѾ­¹ã·ºÊ¹Óá£MV ÔÚÒ»¸ö¶ÎÖд洢²éѯ½á¹û£¬²¢ÇÒÄܹ»ÔÚÌá½»²éѯ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ