ORACLE ÖÐROWNUMÓ÷¨×ܽá
¶ÔÓÚ 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Ìõ¼Ç¼¡£Ò²¿ÉÒÔÕâÑùÀí½â£¬rownum Ϊ9ºóµÄ¼ÇÂ
Ïà¹ØÎĵµ£º
ʵÀý£º
1¡¢Í¨¹ýDBLINKµÄ·½Ê½£º±í¿Õ¼ä²»Ò»Ñù
impdp logfile=DUP_MCMDB.LOG network_link=to_crm_7 schemas=MCMDB REMAP_TABLESPACE=table_space11:table_space12,table_space21:table_space22
2¡¢Í¨¹ýDBLINKµÄ·½Ê½£º·½°¸£¨schemas£©²»Ò»Ñù
impdp logfile=DUP_MCMDB.LOG net ......
Ä¿Ç°£¬ÕýÔò±í´ïʽÒѾÔںܶàÈí¼þÖеõ½¹ã·ºµÄÓ¦Ó㬰üÀ¨*nix£¨Linux, UnixµÈ£©£¬HPµÈ²Ù×÷ϵͳ£¬PHP£¬C#£¬JavaµÈ¿ª·¢»·¾³¡£
Oracle 10gÕýÔò±í´ïʽÌá¸ßÁËSQLÁé»îÐÔ¡£ÓÐЧµÄ½â¾öÁËÊý¾ÝÓÐЧÐÔ£¬ Öظ´´ÊµÄ±æÈÏ, Î޹صĿհ׼ì²â£¬»òÕß·Ö½â¶à¸öÕýÔò×é³É
µÄ×Ö·û´®µÈÎÊÌâ¡£
Oracle 10gÖ§³ÖÕýÔò±í´ïʽµÄËĸöк¯Êý ......
Ò»¡¢Á˽ⱸ·ÝµÄÖØÒªÐÔ
¡¡¡¡¿ÉÒÔ˵£¬´Ó¼ÆËã»úϵͳ³öÊÀµÄÄÇÌìÆ𣬾ÍÓÐÁ˱¸·ÝÕâ¸ö¸ÅÄ¼ÆËã»úÒÔÆäÇ¿´óµÄËٶȴ¦ÀíÄÜÁ¦£¬È¡´úÁ˺ܶàÈËΪµÄ¹¤×÷£¬µ«ÊÇ£¬ÍùÍùºÜ¶àʱºò£¬ËüÓÖÊÇÄÇôÈõ²»½û·ç£¬Ö÷°åÉϵÄоƬ¡¢Ö÷°åµç·¡¢ÄÚ´æ¡¢µçÔ´µÈÈκÎÒ»Ïî²»ÄÜÕý³£¹¤×÷£¬¶¼»áµ¼Ö¼ÆËã»úϵͳ²»ÄÜÕý³£¹¤×÷¡£µ±È»£¬ÕâЩË𻵿ÉÒÔÐÞ¸´£¬²»»áµ¼ÖÂÓ¦Óú ......
µÚ1Ò³
1. Oracle µÄʹÓÃ
1.1. SQLPLUS µÄÃüÁî
³õʼ»¯±íµÄλÖãº
set
NLS_LANG=american_america.us7ascii (ÉèÖñàÂë²Å¿ÉÒÔʹÓÃÏÂÃæ½Å±¾)
cd
$ORACLE_HOME/rdbms cd demo summit2.sql
********* ......
ÓÃϵͳ¹ÜÀíÔ±£¬²é¿´µ±Ç°Êý¾Ý¿âÓм¸¸öÓû§Á¬½Ó£º
SQL> select username,sid,serial# from v$session;
Èç¹ûҪͣij¸öÁ¬½ÓÓÃ
SQL> alter system kill session 'sid,serial#';
......