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 ......
×÷Õߣº ÈÕÆÚ£º2005-12-8 1:43:32 À´Ô´£ºInternet µã»÷£º´Î ÆÀÂÛ
¡¡¡¡±¾ÎÄÖ»ÌÖÂÛOracleÖÐ×î³£¼ûµÄË÷Òý£¬¼´ÊÇB-treeË÷Òý¡£±¾ÎÄÖÐÉæ¼°µÄÊý¾Ý¿â°æ±¾ÊÇOracle8i¡£
¡¡¡¡Ò». ²é¿´ÏµÍ³±íÖеÄÓû§Ë÷Òý
¡¡¡¡ÔÚOracleÖУ¬SYSTEM±íÊÇ°²×°Êý¾Ý¿âʱ×Ô¶¯½¨Á¢µÄ£¬Ëü°üº¬Êý¾Ý¿âµÄÈ«²¿Êý¾ ......
ÈçºÎ¶¨ÒåÓαêÀàÐÍ
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
ÉùÃ÷Óαê±äÁ¿
cursor_name ref_type_name;
´Ó¼¼Êõµ×²ã¿´£¬Á½ÕßÊÇÏàͬµÄ¡£ÆÕͨplsql cursorÔÚ¶¨ÒåʱÊÇ“¾²Ì¬”µÄ¡£¶øRef cursors¿ÉÒÔ¶¯Ì¬´ò¿ª¡£
ÀýÈçÏÂÃæÀý×Ó£º
Declare
type rc is ref cursor;
cursor c is select * from dual ......
OracleµÄÍâ¼üÓÃÀ´ÏÞÖÆ×Ó±íÖвο¼µÄ×ֶεÄÖµ£¬±ØÐëÔÚÖ÷±íÖдæÔÚ¡£¶øÇÒÔÚÖ÷±íµÄ¼Ç¼·¢Éú±ä»¯µ¼ÖÂÍâ¼ü²Î¿¼Î¨Ò»Ô¼ÊøÖµ·¢ÉúÁ˱仯ʱ£¬¶¨ÒåÁËһϵÁеĶ¯×÷¡£
ÔÚSQL92±ê×¼Öж¨ÒåÁ˼¸ÖÖÍâ¼ü¸Ä±äºó£¬ÈçºÎ´¦Àí×Ó±í¼Ç¼µÄ¶¯×÷£¬ÆäÖаüÀ¨£º
ÏÞÖÆRestrict£ºÕâÖÖ·½Ê½²»ÔÊÐí¶Ô±»²Î¿¼µÄ¼Ç¼µÄ¼üÖµÖ´Ðиüлòɾ³ýµÄ²Ù×÷£»
ÖÃΪ¿ÕSet to nu ......
¡¾ÒýÓá¿http://tangshd.blog.sohu.com/96035768.html
Oracle¶àÐмǼºÏ²¢/Á¬½Ó/¾ÛºÏ×Ö·û´®µÄ¼¸ÖÖ·½·¨
ÔõôºÏ²¢¶àÐмǼµÄ×Ö·û´®£¬Ò»Ö±ÊÇoracleÐÂÊÖϲ»¶ÎʵÄSQLÎÊÌâÖ®Ò»£¬¹ØÓÚÕâ¸öÎÊÌâµÄÌû×ÓÎÒ¿´¹ý²»ÏÂ30¸öÁË£¬ÏÖÔھͶÔÕâ¸öÎÊÌ⣬½øÐÐÒ»¸ö×ܽᡣ
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©Ä ......