oracle - merge usage (transfer)

2010-08-23  来源:本站原创  分类:Database  人气:172 

Oracle9i introduces the MERGE command, you can in a SQL statement on a table while the implementation of inserts and u pda TES operation. MERGE command from one or more data sources, select OK to UPD ATI ng or inserting one or more tables. MERGE in Oracle 10g, there are some improvements:

1, UPDATE or INSERT clause is optional

2, UPDATE and INSERT clause can add WHERE clause

3 ON conditions in the use of constant filter predicate to insert all rows into the target table does not need to connect the source and destination tables

4, UPDATE DELETE clause clause can be followed to remove some unwanted lines

First create a sample table:

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', 'DV
D');
    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, can be omitted in the UPDATE or INSERT clause

In Oracle 9i, MERGE statement is required you must also specify the INSERT and UPDATE clauses. In Oracle 10g, you can omit the UPDATE or INSERT clause one. Following the example of PRODUCT_ID field under the table NEWPRODUCTS whether the matching updates the table PRODUCTS Information:

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category;

    3 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    SQL>
    SQL> ROLLBACK;
    Rollback complete.
    SQL>

In the above example, MERGE statement is a product id to affect the 1502, 1601 and 1666 lines. Their names and types of products to be updated to the table newproducts values. The following examples omit the UPDATE clause to form the new PRODUCT_ID NEWPRODUCTS PRODUCTS inserted into the table in the table for the two to match the data on the PRODUCT_ID without any treatment. From this example, you can see PRODUCT_ID = 1700 rows are inserted into the table PRODUCTS.

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name,
    7 np.category);

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS

2, with the conditions of U pda clause TES and Inserts

You can add a WHERE clause to UPDATE or INSERT clause to go to skip the update or insert operation on some lines of treatment. The following example to update the table under the table NEWPRODUCTS PRODUCTS data, but must also have the same time the field matches CATEGORY :

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name
    7 WHERE p.category = np.category;

    2 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DV
D
    SQL>
    SQL> rollback;

In this case, the product ID for the 1502,1601 and 1666 match the ON condition, but does not match 1666's category. So MERGE command only updates the two lines of data. The following example shows Updates and Inserts in the WHERE clause use clause:

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 WHERE p.category = 'DVD'
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    12 WHERE np.category != 'BOOKS'
    SQL> /

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS

    SQL>

Note Because there are no insert INSERT WHERE clause does not match the ON condition of all rows to the table PRODUCTS.

3, unconditional Inserts

You can not link the source table and target table put the data source table into the target table. This is all you want to insert rows into the target table is very useful. Oracle 10g now supports the use of constants in the ON condition of filter predicates. To give an example of a constant filter predicate ON (1 = 0). The following example from the source table insert rows into the table PRODUCTS, does not check if these lines exist in the table PRODUCTS:

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (1=0)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name, np.category)
    7 WHERE np.category = 'BOOKS'
    SQL> /

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS
    6 rows selected.
    SQL>

4 additional DELETE clause

Oracle 10g's MERGE provides a clear line to perform data operations option. You can WHEN MATCHED THEN UPDATE clause is included in DELETE clause. DELETE WHERE clause must be a condition to remove the line match certain conditions. DELETE WHERE condition matches the ON condition, but does not match the line will not be removed from the table.

The following example shows the DELETE clause. We merge rows from table to table NEWPRODUCTS PRODUCTS, but delete the category for the ELECTRNCS line.

SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 DELETE WHERE (p.category = 'ELECTRNCS')
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    SQL> /

    4 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    1700 WAIT INTERFACE BOOKS
    SQL>

Product ID is 1502 rows from the table PRODUCTS been deleted because it also matches the ON condition and the DELETE WHERE condition. Product ID match for the 1501 line but does not match the DELETE WHERE condition ON condition, so it is not deleted. Product ID is 1700 The line does not match the ON condition, it is inserted into the table PRODUCTS. Product ID for the 1601 and 1666 match the ON condition of the line but does not match the DELETE WHERE condition, it has been updated to the table NEWPRODUCTS values.

相关文章
  • oracle - merge usage (transfer) 2010-08-23

    Oracle9i introduces the MERGE command, you can in a SQL statement on a table while the implementation of inserts and u pda TES operation. MERGE command from one or more data sources, select OK to UPD ATI ng or inserting one or more tables. MERGE in O

  • Oracle distinct usage (transfer) 2011-03-03

    distinct keyword to filter out unwanted duplicate records leaving only one, but often only use it to return the number of unique records, rather than use it to return without re-recording of all values. The reason is that only with two distinct queri

  • DatabaseMetaData usage (transfer) method to obtain the database table structure 2010-03-10

    DatabaseMetaData usage (transfer) 2008-08-07 at 12:25 1. Be an instance of this object Connection con; con = DriverManager.getConnection (url, userName, password); DatabaseMetaData dbmd = con.getMetaData (); 2. Methods getTables usage <br /> prototy

  • VisualSVN Server's configuration and usage (transfer) 2010-07-21

    VisualSVN Server's configuration and usage (transfer) 1. Why use VisualSVN Server, instead of Subversion? Answer: If the direct use of Subversion, then the Windows system, to let it start with the system, we must package SVN Server to windws service,

  • Oracle Merge statement Efficiency 2010-06-28

    You will come across some database operations in the update, Also considered the primary key will repeat the question, Simple solution is to first select, Then insert the returned value judgments are still update. Because the company requested that a

  • Oracle Merge statement in the efficiency of 2010-08-22

    You will come across some database operations in the update, Also considered the primary key will repeat the question, Simple solution is to first select, Then insert the returned value judgments are still update. Because the company requested that a

  • Oracle% rowtype usage as well as for loop and Log4plsql 2010-09-30

    Oracle% rowtype usage: Line indicates the type of data types, data storage is a line, a line can have multiple data in columns, similar to the table row of data, it can be a cursor in the line of data, such as: vs_row1 table% rowtype; vs_row2 cursor%

  • Oracle: sequence usage 2011-06-28

    Oracle: sequence usage in the Oracle database, sequence the same as serial numbers, each taking the time sequence will be automatically increased, usually acting on the sort required by the serial number of the place. 1, Create Sequence (Note: You ne

  • Oracle MERGE INTO的用法示例介绍 2015-02-05

    这篇文章主要介绍了Oracle MERGE INTO的用法,需要的朋友可以参考下 很多时候我们会出现如下情境,如果一条数据在表中已经存在,对其做update,如果不存在,将新的数据插入.如果不使用Oracle提供的merge语法的话,可能先要上数据库select查询一下看是否存在,然后决定怎么操作,这样的话需要写更多的代码,同时性能也不好,要来回数据库两次.使用merge的话则可以一条SQL语句完成. 1)主要功能 提供有条件地更新和插入数据到数据库表中 如果该行存在,执行一个UPDATE操作,

  • Oracle merge line on a small record (transfer) 2010-08-05

    Transfer from: http://www.ll19.com/log/2010/06/04/115.html ORACLE encountered in a working merge line, sort the combined results of the records. Originally a simple function on the line that the use of wmsys.wm_concat results from a number of small p

  • oracle merge in the transfer function 2010-12-21

    oracle's merge functions such as: table1 (col_1a, col_1b, col_1c) table2 (col_2a, col_2b, col_2c) I would like to update table1, when col_1a = col_2a will col_2b and col_2c assigned to col_1b and col_1c. merge into table a using table b on a.col = b.

  • [Transfer] oracle time usage 2011-05-09

    Transfer from: http://www.poluoluo.com/jzxy/201004/82129.html oracle representation in 24 hours, were posted to share with you! 24 hours Representation: to_date ('2004-06-30 23:59:59 ',' yyyy-mm-dd hh24: mi: ss') 12-hour representation: to_date ('200

  • Oracle query table space usage (transfer) 2010-03-27

    - Query the table space usage SELECT UPPER (F. TABLESPACE_NAME) "table space name", D. TOT_GROOTTE_MB "table space (M)", D. TOT_GROOTTE_MB - F. TOTAL_BYTES "has been the use of space (M)", TO_CHAR (ROUND ((D. TOT_GROOTTE_MB -

  • oracle of the date usage (transfer) 2010-08-01

    oracle of the date ORACLE date type field in the treatment of --ORACLE/JSP Technology involves the date, time of treatment (1) in the English version of ORACLE in default date format 'DD-MON-YY', such as '01-JAN-98 ' Speaking of the Chinese version o

  • the oracle merge into .. using .. on .. when .. when .. usage 2010-09-09

    Syntax: MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND [...]...) WHEN MATHED THEN [here you can execute some update sql

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • OpenDataSource.OPENQUERY.OPENROWSET usage (transfer) 2010-09-29

    OpenDataSource, OPENQUERY, OPENROWSET usage Database 2008-07-26 08:50:11 248 Comments 0 read font size: medium and small Subscriptions A, OpenDataSource ############################## 1. Operation SQL Server data, such as SQL Server / Oracle, etc. Fo

  • Oracle export data (transfer) 2010-12-14

    1.1 exp and examples of use exp / imp as a database backup recovery tool can also transfer data between different databases as a tool for the operating system where the two databases can be different exp database data can be exported as a binary file

  • Oracle common techniques (transfer) 2011-10-11

    1. Drop the tablespace DROP TABLESPACE TableSpaceName [INCLUDING CONTENTS [AND DATAFILES]] 2. Delete User DROP USER User_Name CASCADE 3. Notes on the table to delete a table in the delete all the data, the need to use TRUNCATE TABLE table; for use DR

  • Dwell Oracle CPU usage optimization 2010-03-29

    http://database.51cto.com/art/200911/163251.htm I am very fond of Oracle, is also at work on the Oracle optimization like CPU usage summary of lessons learned, the following detail on this issue to speak. CPU is an important resource server, the serv