ORACLE ÖÐROWNUMÓ÷¨×ܽá
2009-11-15 13:06:35
½ñÌìÔÚITPUB·¢ÏÖһƪÎÄÕ£¬ÀÏÔçÒÔÇ°µÄÁË£¬Ð´µÄÂùÓÐÒâ˼£¬ÌØÒâת¹ýÀ´Êղء£
¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>, >=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËƺõÊÇĪÃûÆäÃîµÄ½á¹ûÀ´£¬ÆäʵÄúÖ»ÒªÀí½âºÃÁËÕâ¸ö rownum αÁеÄÒâÒå¾Í²»Ó¦¸Ã¸Ðµ½¾ªÆ棬ͬÑùÊÇαÁУ¬rownum Óë rowid ¿ÉÓÐЩ²»Ò»Ñù£¬ÏÂÃæÒÔÀý×Ó˵Ã÷
¡¡¡¡¼ÙÉèij¸ö±í t1(c1) ÓÐ 20 Ìõ¼Ç¼
¡¡¡¡Èç¹ûÓà select rownum,c1 from t1 where rownum < 10, Ö»ÒªÊÇÓÃСÓںţ¬²é³öÀ´µÄ½á¹ûºÜÈÝÒ×µØÓëÒ»°ãÀí½âÔÚ¸ÅÄîÉÏÄÜ´ï³ÉÒ»Ö£¬Ó¦¸Ã²»»áÓÐÈκÎÒÉÎʵġ£
¡¡¡¡¿ÉÈç¹ûÓà select rownum,c1 from t1 where rownum > 10 (Èç¹ûдÏÂÕâÑùµÄ²éѯÓï¾ä£¬ÕâʱºòÔÚÄúµÄÍ·ÄÔÖÐÓ¦¸ÃÊÇÏëµÃµ½±íÖкóÃæ10Ìõ¼Ç¼)£¬Äã¾Í»á·¢ÏÖ£¬ÏÔʾ³öÀ´µÄ½á¹ûÒªÈÃÄúʧÍûÁË£¬Ò²ÐíÄú»¹»á»³ÒÉÊDz»ËɾÁËһЩ¼Ç¼£¬È»ºó²é¿´¼Ç¼Êý£¬ÈÔÈ»ÊÇ 20 Ìõ°¡£¿ÄÇÎÊÌâÊdzöÔÚÄÄÄØ£¿
¡¡¡¡ÏȺúÃÀí½â rownum µÄÒâÒå°É¡£ÒòΪROWNUMÊǶԽá¹û¼¯¼ÓµÄÒ»¸öαÁУ¬¼´ÏȲ鵽½á¹û¼¯Ö®ºóÔÙ¼ÓÉÏÈ¥µÄÒ»¸öÁÐ (Ç¿µ÷£ºÏÈÒªÓнá¹û¼¯)¡£¼òµ¥µÄ˵ rownum ÊǶԷûºÏÌõ¼þ½á¹ûµÄÐòÁкš£Ëü×ÜÊÇ´Ó1¿ªÊ¼ÅÅÆðµÄ¡£ËùÒÔÄãÑ¡³öµÄ½á¹û²»¿ÉÄÜûÓÐ1£¬¶øÓÐÆäËû´óÓÚ1µÄÖµ¡£ËùÒÔÄúû°ì·¨ÆÚÍûµÃµ½ÏÂÃæµÄ½á¹û¼¯£º
¡¡¡¡11 aaaaaaaa
¡¡¡¡12 bbbbbbb
¡¡¡¡13 ccccccc
¡¡¡¡.................
¡¡¡¡rownum >10 ûÓмǼ£¬ÒòΪµÚÒ»Ìõ²»Âú×ãÈ¥µôµÄ»°£¬µÚ¶þÌõµÄROWNUMÓÖ³ÉÁË1£¬ËùÒÔÓÀԶûÓÐÂú×ãÌõ¼þµÄ¼Ç¼¡£»òÕß¿ÉÒÔÕâÑùÀí½â£º
¡¡¡¡ROWNUMÊÇÒ»¸öÐòÁУ¬ÊÇoracleÊý¾Ý¿â´ÓÊý¾ÝÎļþ»ò»º³åÇøÖжÁÈ¡Êý¾ÝµÄ˳Ðò¡£ËüÈ¡µÃµÚÒ»Ìõ¼Ç¼ÔòrownumֵΪ1£¬µÚ¶þÌõΪ2£¬ÒÀ´ÎÀàÍÆ¡£Èç¹ûÄãÓÃ>,>=,=,between...andÕâЩÌõ¼þ£¬ÒòΪ´Ó»º³åÇø»òÊý¾ÝÎļþÖеõ½µÄµÚÒ»Ìõ¼Ç¼µÄrownumΪ1£¬Ôò±»É¾³ý£¬½Ó×ÅÈ¡ÏÂÌõ£¬¿ÉÊÇËüµÄrownum»¹ÊÇ1£¬ÓÖ±»É¾³ý£¬ÒÀ´ÎÀàÍÆ£¬±ãûÓÐÁËÊý¾Ý¡£
¡¡¡¡ÓÐÁËÒÔÉÏ´Ó²»Í¬·½Ã潨Á¢ÆðÀ´µÄ¶Ô rownum µÄ¸ÅÄÄÇÎÒÃÇ¿ÉÒÔÀ´ÈÏʶʹÓà rownum µÄ¼¸ÖÖÏÖÏñ
¡¡¡¡1. select rownum,c1 from t1 where rownum != 10 ΪºÎÊÇ·µ»ØÇ°9ÌõÊý¾ÝÄØ£¿ËüÓë select rownum,c1 from tablename where rownum < 10 ·µ»ØµÄ½á¹û¼¯ÊÇÒ»ÑùµÄÄØ£¿
¡¡¡¡ÒòΪÊÇÔÚ²éѯµ½½á¹û¼¯ºó£¬ÏÔʾÍêµÚ 9 Ìõ¼Ç¼ºó£¬Ö®ºóµÄ¼Ç¼Ҳ¶¼ÊÇ != 10,»òÕß >=10,ËùÒÔÖ»ÏÔʾǰÃæ9Ìõ¼ÇÂ
Ïà¹ØÎĵµ£º
DML Error Logging in Oracle 10g
Ö÷ÒªÔÚÓÚʹÓÃDBMS_ERRLOG.create_error_log Õâ¸ö°üÀ´¸ú×Ùdml´íÎóÐÅÏ¢
SQL> CREATE TABLE source (
2 id NUMBER(10) NOT NULL,
3 code VARCHAR2(10),
4 description VARCHAR2(50),
5 CONSTRAINT source_pk PRIMARY KEY (id)
6 );
±íÒÑ´´½¨¡£
SQL> DECLARE
2 TYPE t_tab IS ......
select sysdate from dual; ´Óα±í²éϵͳʱ¼ä£¬ÒÔĬÈϸñʽÊä³ö¡£
sysdate+(5/24/60/60) ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ãë
sysdate+5/24/60 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5·ÖÖÓ
sysdate+5/24 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Сʱ
sysdate+5 ÔÚϵͳʱ¼ä»ù´¡ÉÏÑÓ³Ù5Ìì
ËùÒÔÈÕÆÚ¼ÆËãĬÈϵ¥Î»ÊÇÌì
round (sysdate,’day’) ²»ÊÇËijý ......
¸Õ²ÅÔÚÍøÉÏËÑÕâ¸öÎÊÌâµÄʱºòËѵ½ITPUBÉÏyangtingkun²©¿ÍÀïµÄһƪÎÄÕ£¬Ð´µÄͦºÃµÄ£¬×Ô¼º¸´ÖÆÁËÒ»ÏÂËûµÄʵÑé¹ý³Ì£¬¼ÓÁ˵㲽ÖèÑéÖ¤ÁË×Ô¼ºµÄÏë·¨£¬Ò²½â¾öÁËÒÉÎÊ¡£
Ç°Ãæд¹ýһƪÎÄÕ£¬½éÉܹýΨһԼÊøµÄÇé¿öÏ£¬Oracle´¦ÀíNULLµÄÌص㡣¼òµ¥µÄ˵£¬¾ÍÊÇÈç¹û¶à¸öÁй¹³ÉÁËΨһ£¬ÇÒÆäÖаüº¬Ò»¸öÒÔÉϵÄNULL£¬ÄÇôOracle»áÒªÇó²»ÎªNULL ......
oracleÖÐÁ¬½ÓÓë»á»°²»ÊÇÒ»¸ö¸ÅÄî!!!
ÔÚOracleÖУ¬Á¬½ÓÖ»ÊÇ¿Í»§½ø³ÌºÍÊý¾Ý¿âʵÀýÖ®¼äµÄÒ»ÌõÌØÊâÏß·£¬×î³£¼ûµÄ¾ÍÊÇÍøÂçÁ¬½Ó¡£ÕâÌõÁ¬½Ó¿ÉÄÜÁ¬½Óµ½Ò»¸öרÓ÷þÎñÆ÷½ø³Ì£¬Ò²¿ÉÄÜÁ¬½Óµ½µ÷¶ÈÆ÷¡£ÈçÇ°ËùÊö£¬Á¬½ÓÉÏ¿ÉÒÔÓÐ0¸ö»ò¶à¸ö»á»°£¬Õâ˵Ã÷¿ÉÒÔÓÐÁ¬½Ó¶øÎÞÏàÓ¦µÄ»á»°¡£ÁíÍ⣬һ¸ö»á»°¿ÉÒÔÓÐÁ¬½ÓÒ²¿ÉÒÔûÓÐÁ¬½Ó¡£Ê¹Óø߼¶Oracle N ......