oracle ²éÕÒ¡¢É¾³ýÖظ´¼Ç¼
×ܽáÁËÒ»ÏÂɾ³ýÖظ´¼Ç¼µÄ·½·¨£¬ÒÔ¼°Ã¿ÖÖ·½·¨µÄÓÅȱµã¡£
¼ÙÉè±íÃûΪTbl£¬±íÖÐÓÐÈýÁÐcol1£¬col2£¬col3£¬ÆäÖÐcol1£¬col2ÊÇÖ÷¼ü£¬²¢ÇÒ£¬col1£¬col2ÉϼÓÁËË÷Òý¡£
1¡¢Í¨¹ý´´½¨ÁÙʱ±í
¿ÉÒÔ°ÑÊý¾ÝÏȵ¼Èëµ½Ò»¸öÁÙʱ±íÖУ¬È»ºóɾ³ýÔ±íµÄÊý¾Ý£¬ÔÙ°ÑÊý¾Ýµ¼»ØÔ±í£¬SQLÓï¾äÈçÏ£º
creat table tbl_tmp (select distinct* from tbl);
truncate table tbl;//Çå¿Õ±í¼Ç¼
insert into tbl select * from tbl_tmp;//½«ÁÙʱ±íÖеÄÊý¾Ý²å»ØÀ´¡£
ÕâÖÖ·½·¨¿ÉÒÔʵÏÖÐèÇ󣬵«ÊǺÜÃ÷ÏÔ£¬¶ÔÓÚÒ»¸öǧÍò¼¶¼Ç¼µÄ±í£¬ÕâÖÖ·½·¨ºÜÂý£¬ÔÚÉú²úϵͳÖУ¬Õâ»á¸øϵͳ´øÀ´ºÜ´óµÄ¿ªÏú£¬²»¿ÉÐС£
2¡¢ÀûÓÃrowid
ÔÚoracleÖУ¬Ã¿Ò»Ìõ¼Ç¼¶¼ÓÐÒ»¸örowid£¬rowidÔÚÕû¸öÊý¾Ý¿âÖÐÊÇΨһµÄ£¬rowidÈ·¶¨ÁËÿÌõ¼Ç¼ÊÇoracleÖеÄÄÄÒ»¸öÊý¾ÝÎļþ¡¢¿é¡¢ÐÐÉÏ¡£ÔÚÖظ´µÄ¼Ç¼ÖУ¬¿ÉÄÜËùÓÐÁеÄÄÚÈݶ¼Ïàͬ£¬µ«rowid²»»áÏàͬ¡£SQLÓï¾äÈçÏ£º
delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)
Èç¹ûÒѾ֪µÀÿÌõ¼Ç¼ֻÓÐÒ»ÌõÖظ´µÄ£¬Õâ¸ösqlÓï¾äÊÊÓᣵ«ÊÇÈç¹ûÿÌõ¼Ç¼µÄÖظ´¼Ç¼ÓÐNÌõ£¬Õâ¸öNÊÇδ֪µÄ£¬¾ÍÒª¿¼ÂÇÊÊÓÃÏÂÃæÕâÖÖ·½·¨ÁË¡£
3¡¢ÀûÓÃmax»òminº¯Êý
ÕâÀïҲҪʹÓÃrowid£¬ÓëÉÏÃ治ͬµÄÊǽáºÏmax»òminº¯ÊýÀ´ÊµÏÖ¡£SQLÓï¾äÈçÏÂ
delete from tbl a where rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//ÕâÀïmaxʹÓÃminÒ²¿ÉÒÔ
»òÕßÓÃÏÂÃæµÄÓï¾ä
delete from tbl a where rowid < (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2
4¡¢ÀûÓÃgroup by£¬Ìá¸ßЧÂÊ
ƽʱ¹¤×÷ÖпÉÄÜ»áÓöµ½µ±ÊÔͼ¶Ô¿â±íÖеÄijһÁлò¼¸Áд´½¨Î¨Ò»Ë÷Òýʱ£¬ÏµÍ³Ìáʾ ORA-01452 £º²»ÄÜ´´½¨Î¨Ò»Ë÷Òý£¬·¢ÏÖÖظ´¼Ç¼¡£
ÏÂÃæ×ܽáһϼ¸ÖÖ²éÕÒºÍɾ³ýÖظ´¼Ç¼µÄ·½·¨£¨ÒÔ±íCZΪÀý£©£º
±íCZµÄ½á¹¹ÈçÏ£º
SQL> desc cz
Name Null? Type
----------------------------------------- -------- ------------------
C1 &n
Ïà¹ØÎĵµ£º
¶ÔÓÚ×°ºÃÁ˸ÃÈí¼þºó,ÀûÓÃsystemÊÇÄܵǽøÈ¥µÄ,»ú×ÓÖØÆôºó,³öÏֵĸÃÎÊÌ⣺
¿ÉÄÜÄú ÔËÐÐ--sqlplusw ÊÇÄܵÇÉÏÈ¥µÄ ¶ø»»³ÉPL/SQL Developer È´Á¬²»ÉÏ ·þÎñÆ÷,Èç¹ûÄúÈ·¶¨ÄãµÄ·þÎñ¿ªÆôÁË
ËÑË÷ÕÒµ½tnsnames.oraºÍlistener.oraÎļþ, °ÑÆäÖеÄHOST=ºóµÄÖ÷»úÃû»òip¸ÄΪµ±Ç°µ ......
ÕÛÌÚÁËÁ½¸öÍíÉϵÄlinuxºÍoralce£¬×ÜËã³öÀ´µã¶«Î÷ÁË¡£Oralce°²×°ÒªÑ¡ÓÃÊÊÓõİ汾£¬²»È»°²×°²»»á³É¹¦£¬×îºóÎҵľö¶¨ÊÇlinuxÑ¡ÓÃcentos 5.0£¬oracleÑ¡ÓÃ10¡£
Ò»¡¢ ×¼±¸¹¤×÷£º
1. ʹÓÃrootÓû§µÇ¼
ÎÒʹÓõÄÊÇÔÚ±¾»ú×°£¬ËùÒÔÖ±½Ó´ò¿ªÖÕ¶Ë¾Í ......
SQLServerºÍOracleµÄ³£Óú¯Êý¶Ô±È
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual ......
OracleÊý¾ÝÀàÐͼò½é
¡¡¡¡Ò»¡¢¸ÅÊö
¡¡¡¡ÔÚORACLE8Öж¨ÒåÁË£º±êÁ¿£¨SCALAR£©¡¢¸´ºÏ£¨COMPOSITE£©¡¢ÒýÓã¨REFERENCE£©ºÍLOBËÄÖÖÊý¾ÝÀàÐÍ£¬ÏÂÃæÏêϸ½éÉÜËüÃǵÄÌØÐÔ¡£
¡¡¡¡¶þ¡¢±êÁ¿£¨SCALAR£©
¡¡¡¡ºÏ·¨µÄ±êÁ¿ÀàÐÍÓëÊý¾Ý¿âµÄÁÐËùʹÓõÄÀàÐÍÏàͬ£¬´ËÍâËü»¹ÓÐһЩÀ©Õ¹¡£ËüÓÖ·ÖΪÆ߸ö×飺Êý×Ö¡¢×Ö·û¡¢ÐС¢ÈÕÆÚ¡¢Ðбêʶ¡¢²¼¶ûºÍ¿É ......
OracleÊý¾Ý¿âÖÐ,ÓÐЩÇé¿öÏÂ,¶ÔÊý¾Ý¼Ç¼ÐèÒª¼Ç¼ÈÕÖ¾,»ò±£´æ²Ù×÷ÀúÊ·µÈÇé¿ö.ÔÚ´ËÎÒÃÇ¿ÉÒÔ½èÖú“Êý¾Ý¿âTrigger”½øÐС£ÏÂÃæÒÔÒ»Àý½øÐÐ˵Ã÷£º
CREATE OR REPLACE TRIGGER aits_auth_group_auth_trga_diu
AFTER update OR DELETE OR INSERT on aits_authority_group_auth
for each row
......