oracle merge into Ó÷¨Ïê½â
oracle merge into Ó÷¨Ïê½â
2009-07-31 10:14
Oracle9iÒýÈëÁËMERGEÃüÁî,ÄãÄܹ»ÔÚÒ»¸öSQLÓï¾äÖжÔÒ»¸ö±íͬʱִÐÐinsertsºÍupdates²Ù×÷. MERGEÃüÁî´ÓÒ»¸ö»ò¶à¸öÊý¾ÝÔ´ÖÐÑ¡ÔñÐÐÀ´updating»òinsertingµ½Ò»¸ö»ò¶à¸ö±í.
Oracle 10gÖÐMERGEÓÐÈçÏÂһЩ¸Ä½ø£º
1¡¢UPDATE»òINSERT×Ó¾äÊÇ¿ÉÑ¡µÄ
2¡¢UPDATEºÍINSERT×Ó¾ä¿ÉÒÔ¼ÓWHERE×Ó¾ä
3¡¢ONÌõ¼þÖÐʹÓó£Á¿¹ýÂËν´ÊÀ´insertËùÓеÄÐе½Ä¿±ê±íÖÐ,²»ÐèÒªÁ¬½ÓÔ´±íºÍÄ¿±ê±í
4¡¢UPDATE×Ó¾äºóÃæ¿ÉÒÔ¸úDELETE×Ó¾äÀ´È¥³ýһЩ²»ÐèÒªµÄÐÐ
Ê×ÏÈ´´½¨Ê¾Àý±í£º
create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
1¡¢¿ÉÊ¡ÂÔµÄUPDATE»òINSERT×Ó¾ä
ÔÚOracle 9i, MERGEÓï¾äÒªÇóÄã±ØÐëͬʱָ¶¨INSERTºÍUPDATE×Ó¾ä.¶øÔÚOracle 10g, Äã¿ÉÒÔÊ¡ÂÔUPDATE»òINSERT×Ó¾äÖеÄÒ»¸ö. ÏÂÃæµÄÀý×Ó¸ù¾Ý±íNEWPRODUCTSµÄPRODUCT_ID×Ö¶ÎÊÇ·ñÆ¥ÅäÀ´updates±íPRODUCTSµÄÐÅÏ¢:
SQL> MERGE INTO products p
2 USING newproducts np
3 ON (p.product_id =
Ïà¹ØÎĵµ£º
ORACLEÊý¾Ý¿âͨ¹ýsqlnet.oraÎļþÖеIJÎÊýsqlnet.authentication_services,²ÎÊýÎļþÖеÄremote_login_passwordfileºÍ¿ÚÁîÎļþpwdsid.oraÈýÕßÐͬ×÷ÓÃʵÏÖÉí·ÝÈÏÖ¤.
Sqlnet.authentication_services=(NTS)|(NONE)
NTS:²Ù×÷ϵͳÈÏÖ¤·½Ê½,²»Ê¹ÓÿÚÁîÎļþ;
NONE:¿ÚÁîÎļþÈÏÖ¤·½Ê½
Remote_login_passwordfile=(NONE)|(EXCLU ......
¶¨Ò壺escape¹Ø¼ü×Ö¾³£ÓÃÓÚʹijЩÌØÊâ×Ö·û£¬ÈçͨÅä·û£º'%','_'תÒåΪËüÃÇÔ
À´µÄ×Ö·ûµÄÒâÒ壬±»¶¨ÒåµÄתÒå×Ö·ûͨ³£Ê¹ÓÃ'\',µ«ÊÇÒ²¿ÉÒÔʹÓÃÆäËûµÄ·ûºÅ¡£
ʵÀý£º
SQL> select * from t11 where name like '%_%';
NAME
----------
aa_a
aaa
SQL> select * from t11 where name like '%\_%' escape '\';
NAME
......
±¾ÉíÕâ¸ö²½ÖèºÜ¶à¸ßÊÖ¶¼ÒѾÌù¹ýÁË£¬Ö»ÊÇÎÒÔÚʹÓÃÖз¢ÏÖ´óÌåÉÏ´ó¼ÒдµÄ¶¼ÓÐЩ¸´ÔÓ£¬ÓÚÊÇ£¬ÎÒ×ܽáÁ˸ö³¬¼¶¼ò»¯°æµÄ£¬·½±ã´ó¼ÒʹÓãº
1.°²×°LOGMNR°ü£¬ÐèÒª±¾²½Öèûʲô¿É¶à˵µÄ£¬Ö»ÊÇÐèҪעÒâÔÚÁ¬½ÓÊý¾Ý¿âµÄʱºòĬÈÏ×îºÃʹÓñ¾µØÑéÖ¤·½Ê½
C:\>sqlplus /nolog
SQL> conn / as sysdba
SQL> @D:\oracle\product\10 ......
Ò»
¡¢執ÐÐ ORACLE_HOME/rdbms/admin/dbmslock.sql À´´´½¨ dbms_lock;
-ÔÚDBAÉí·ÖÏÂgrant execute on dbms_lock to USERNAME;
-執ÐÐ測試´ú碼
begin
dbms_output.put_line(to_char(sysdate,'yyyymmddhh24miss'));
dbms_lock.sleep(60);
dbms_output.put_line(to_char(sysdate,'yyyymmdd ......
select * from user_recyclebin where original_name like 'FINANCE_%' order by droptime desc;
FLASHBACK TABLE FINANCE_CASE_FEE_ITEM TO BEFORE DROP
¼´ËùÓÐdropµÄ±í¶¼ÔÚ user_recyclebin Õâ¸öoracle»ØÊÕÕ¾ÀïÃæµÄ£¬ÔÙͨ¹ýflashbackÃüÁԼ´¿É¡£
¿´ÁËÍøÉÏ»¹¿ÉÒÔͨ¹ýµ÷Õûoracleʱ¼ä £¬»Øµ½É¾³ýµÄÄ ......