oracle·ÖÒ³
racle·ÖÒ³²éѯÓï¾ä
ĬÈÏ·ÖÀà 2009-12-23 18:17 ÔĶÁ43 ÆÀÂÛ0 ×ֺţº ´ó´ó ÖÐÖРСС Oracle·ÖÒ³²éѯÓï¾ä
±¾ÎÄתÔØ×Ô£ºyangtingkun.itpub.net/post/468/100278
OracleµÄ·ÖÒ³²éѯÓï¾ä»ù±¾ÉÏ¿ÉÒÔ°´ÕÕ±¾Îĸø³öµÄ¸ñʽÀ´½øÐÐÌ×Óá£
·ÖÒ³²éѯ¸ñʽ£º
sql ´úÂë
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¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶àÊýÇé¿öÓµÓнϸߵÄЧÂÊ¡£·ÖÒ³µÄÄ¿µÄ¾ÍÊÇ¿ØÖÆÊä³ö½á¹û¼¯´óС£¬½«½á¹û¾¡¿ìµÄ·µ»Ø¡£ÔÚÉÏÃæµÄ·ÖÒ³²éѯÓï¾äÖУ¬ÕâÖÖ¿¼ÂÇÖ÷ÒªÌåÏÖÔÚWHERE ROWNUM <= 40Õâ¾äÉÏ¡£
Ñ¡ÔñµÚ21 µ½40Ìõ¼Ç¼´æÔÚÁ½ÖÖ·½·¨£¬Ò»ÖÖÊÇÉÏÃæÀý×ÓÖÐչʾµÄÔÚ²éѯµÄµÚ¶þ²ãͨ¹ýROWNUM <= 40À´¿ØÖÆ×î´óÖµ£¬ÔÚ²éѯµÄ×îÍâ²ã¿ØÖÆ×îСֵ¡£¶øÁíÒ»ÖÖ·½Ê½ÊÇÈ¥µô²éѯµÚ¶þ²ãµÄWHERE ROWNUM <= 40Óï¾ä£¬ÔÚ²éѯµÄ×îÍâ²ã¿ØÖÆ·ÖÒ³µÄ×îСֵºÍ×î´óÖµ¡£ÕâÊÇ£¬²éѯÓï¾äÈçÏ£º
sql ´úÂë
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
¶Ô±ÈÕâÁ½ÖÖд·¨£¬¾ø´ó¶àÊýµÄÇé¿öÏ£¬µÚÒ»¸ö²éѯµÄЧÂʱȵڶþ¸ö¸ßµÃ¶à¡£
ÕâÊÇÓÉÓÚCBO ÓÅ»¯Ä£Ê½Ï£¬Oracle¿ÉÒÔ½«Íâ²ãµÄ²éѯÌõ¼þÍƵ½ÄÚ²ã²éѯÖУ¬ÒÔÌá¸ßÄÚ²ã²éѯµÄÖ´ÐÐЧÂÊ¡£¶ÔÓÚµÚÒ»¸ö²éѯÓï¾ä£¬µÚ¶þ²ãµÄ²éѯÌõ¼þWHERE ROWNUM <= 40¾Í¿ÉÒÔ±»OracleÍÆÈëµ½ÄÚ²ã²éѯÖУ¬ÕâÑùOracle²éѯµÄ½á¹ûÒ»µ©³¬¹ýÁËROWNUMÏÞÖÆÌõ¼þ£¬¾ÍÖÕÖ¹²éѯ½«½á¹û·µ»ØÁË¡£
¶øµÚ¶þ¸ö²éѯÓï¾ä£¬ÓÉÓÚ²éѯÌõ¼þBETWEEN 21 AND 40ÊÇ´æÔÚÓÚ²éѯµÄµÚÈý²ã£¬¶øOracleÎÞ·¨½«µÚÈý²ãµÄ²éѯÌõ¼þÍƵ½×îÄڲ㣨¼´Ê¹ÍƵ½×îÄÚ²ãҲûÓÐÒâÒ壬ÒòΪ×îÄÚ²ã²éѯ²»ÖªµÀRN´ú±íʲô£©¡£Òò´Ë£¬¶Ô ÓÚµÚ¶þ¸ö²éѯÓï¾ä£¬Oracle×îÄڲ㷵»Ø¸øÖмä²ãµÄÊÇËùÓÐÂú×ãÌõ¼þµÄÊý¾Ý£¬¶øÖмä²ã·µ»Ø¸ø×îÍâ²ãµÄÒ²ÊÇËùÓÐÊý¾Ý¡£Êý¾ÝµÄ¹ýÂËÔÚ×îÍâ²ãÍê³É£¬ÏÔÈ»Õâ¸öЧÂÊ Òª±ÈµÚÒ»¸ö²éѯµÍµÃ¶à¡£
ÉÏÃæ·ÖÎöµÄ²éѯ²»½ö½öÊÇÕë¶Ôµ¥±íµÄ¼òµ¥²éѯ£¬¶ÔÓÚ×îÄÚ²ã²éѯÊÇ
Ïà¹ØÎĵµ£º
¼ÌÐøÌÖÂÛÈ«ÎÄË÷ÒýµÄDATASTOREÊôÐÔ£¬½éÉÜMULTI_COLUMN_DATASTORE¡£
Èç¹û±»Ë÷ÒýµÄÎÄÕÂÊDZ£´æÔÚÊý¾Ý¿âÖУ¬µ«ÊÇÄÚÈÝ·Ö²¼ÔÚ¶à¸öÁÐÖУ¬ÄÇô¿ÉÒÔͨ¹ý½¨Á¢Ò»¸öMULTI_COLUMN_DATASTOREÀ´Ë÷ÒýÍêÕûµÄÎÄÕ£º
SQL> CREATE TABLE T (ID NUMBER, DOC1 VARCHAR2(4000), DOC2 VARCHAR2(4000), DOC3 VARCHAR2(4000));
±íÒÑ´´½ ......
OracleÈ«ÎÄË÷ÒýµÄSTORAGEÊôÐÔÊÇΪÁ˸øÈ«ÎÄË÷ÒýÉú³ÉµÄ¸¨Öú±íÉèÖô洢²ÎÊýµÄ¡£
OracleµÄÈ«ÎÄË÷Òý»áÉú³ÉÒ»ÕÅ»ò¶àÕŸ¨Öú±í£¬ÓÉÓÚÕâЩ±íÊÇOracle×Ô¶¯Éú³ÉµÄ£¬Óû§Ã»Óа취ֱ½ÓÉèÖÃÕâЩ±íºÍË÷ÒýµÄÎïÀí²ÎÊý£¬Òò´ËOracleÌṩÁËSTORAGEÊôÐÔ£¬×¨ÃÅÉèÖÃÕâЩ¸¨Öú±íºÍË÷ÒýµÄÎïÀí²ÎÊý¡£
SQL> SELECT * from TAB;
TNAME ......
OracleµÄÈ«ÎÄË÷Òý³ýÁËÖ§³ÖÎÄ×ÖÆ¥Åä²éѯ£¬»¹Ö§³Ö¶ÔÎÄÕºÒåµÄ²éѯ¡£ÕâÊÇͨ¹ýABOUT²Ù×÷ʵÏֵġ£
OracleĬÈÏÇé¿öÏÂÖ§³ÖÓ¢Îĺͷ¨ÎĵÄÎÄÕºÒåÖ§³Ö¡£ÆäËûÓïÑÔ¿ÉÒÔÌí¼Óͨ¹ýÓû§×Ô¶¨ÒåµÄÓïÑÔ¿âÀ´ÊµÏÖÏàÓ¦µÄ¹¦ÄÜ¡£
ÔÚ
Õâ֮ǰÐèÒªÏÈ°²×°Companion CD µÄOracle Database 10g Products Installation
Type,ÆäÖаüÀ¨ÁËOracle Text Su ......
Ŀ¼
1. °²×°×¼±¸
1.1. ÉèÖÃÓû§
1.2. ´´½¨Ä¿Â¼ºÍÉèÖÃȨÏÞ
1.3. ¸ü¸ÄÅäÖÃ
1.4. °²×°
1.5. Æô¶¯/Í£Ö¹Oracle
1. °²×°×¼±¸
°²×°¿ªÊ¼Ç°£¬È·ÈÏÄãµÄϵͳ·ûºÏOracleµÄ×îС°²×°ÒªÇó:
* 500 MB ÄÚ´æ
......
CREATE SEQUENCE s_report_id INCREMENT BY 1 MAXVALUE 999999 START WITH 1;
CREATE SEQUENCE checkup_no_seq NOCYCLE MAXVALUE 999999 START WITH 2;
CRE ......