Introduction to the usage examples of Oracle MERGE into _oracle

A lot of times we have the situation where a piece of data already exists in the table, update it, and if it doesn't, insert the new data. If you don't use the merge syntax provided by Oracle, you might want to go to a database select query to see if it exists, and then decide what to do. That way, you need to write more code, and the performance is not good, to the database two times. Use the merge Word to complete a SQL statement.

1) Main function
Provides conditional updating and inserting of data into a database table
If the row exists, an update operation is performed, and if it is a new row, an insert operation is performed
-Avoid a separate update
-Improve performance and ease of use
-Useful in data warehousing applications

2 The syntax of the merge statement is as follows:

Copy Code code as follows:

MERGE [hint] into [schema.] table [T_alias] USING [schema.]
{Table | view | subquery} [T_alias] On (condition)
When matched THEN Merge_update_clause
When not matched THEN merge_insert_clause;

3) Example

Copy Code code as follows:

MERGE into TABLE TARGET
USING (SELECT ' 111111111 ' store_no,
' 2014-01 ' Target_ym,
' 1 ' target_d01,
' 2 ' target_d02,
' 2 ' target_d03,
' 2 ' target_d04,
' 2 ' target_d05,
' 2 ' target_d06,
' 2 ' target_d07,
' 2 ' target_d08,
' 2 ' target_d09,
' 2 ' target_d10,
' 2 ' target_d11,
' 2 ' target_d12,
' 2 ' target_d13,
' 2 ' Target_d14,
' 2 ' target_d15,
' 2 ' target_d16,
' 2 ' target_d17,
' 2 ' target_d18,
' 2 ' target_d19,
' 2 ' target_d20,
' 2 ' target_d21,
' 2 ' target_d22,
' 2 ' target_d23,
' 2 ' target_d24,
' 2 ' target_d25,
' 2 ' target_d26,
' 2 ' target_d27,
' 2 ' target_d28,
' 2 ' target_d29,
' 2 ' target_d30,
' 2 ' target_d31,
1 user_id
From DUAL) TEMP
On (TARGET. Store_no = TEMP. Store_no and TARGET. Target_ym = TEMP. TARGET_YM)
When matched THEN
UPDATE
SET TARGET. TARGET_D01 = TEMP. TARGET_D01,
TARGET. TARGET_D02 = TEMP. TARGET_D02,
TARGET. TARGET_D03 = TEMP. TARGET_D03,
TARGET. TARGET_D04 = TEMP. TARGET_D04,
TARGET. TARGET_D05 = TEMP. TARGET_D05,
TARGET. Target_d06 = TEMP. TARGET_D06,
TARGET. Target_d07 = TEMP. TARGET_D07,
TARGET. Target_d08 = TEMP. TARGET_D08,
TARGET. target_d09 = TEMP. TARGET_D09,
TARGET. TARGET_D10 = TEMP. TARGET_D10,
TARGET. Target_d11 = TEMP. Target_d11,
TARGET. Target_d12 = TEMP. TARGET_D12,
TARGET. TARGET_D13 = TEMP. TARGET_D13,
TARGET. TARGET_D14 = TEMP. TARGET_D14,
TARGET. Target_d15 = TEMP. TARGET_D15,
TARGET. Target_d16 = TEMP. Target_d16,
TARGET. Target_d17 = TEMP. Target_d17,
TARGET. Target_d18 = TEMP. Target_d18,
TARGET. TARGET_D19 = TEMP. TARGET_D19,
TARGET. TARGET_D20 = TEMP. TARGET_D20,
TARGET. Target_d21 = TEMP. Target_d21,
TARGET. Target_d22 = TEMP. Target_d22,
TARGET. Target_d23 = TEMP. TARGET_D23,
TARGET. TARGET_D24 = TEMP. TARGET_D24,
TARGET. TARGET_D25 = TEMP. TARGET_D25,
TARGET. Target_d26 = TEMP. TARGET_D26,
TARGET. Target_d27 = TEMP. TARGET_D27,
TARGET. TARGET_D28 = TEMP. TARGET_D28,
TARGET. target_d29 = TEMP. Target_d29,
TARGET. Target_d30 = TEMP. Target_d30,
TARGET. Target_d31 = TEMP. Target_d31,
TARGET. Opt_counter = TARGET. Opt_counter + 1,
TARGET. update_by = TEMP. USER_ID,
TARGET. Update_date = Sysdate
When not matched THEN
INSERT
VALUES
(SEQ. Nextval,
TEMP. Store_no,
TEMP. Target_ym,
TEMP. TARGET_D01,
TEMP. TARGET_D02,
TEMP. TARGET_D03,
TEMP. TARGET_D04,
TEMP. TARGET_D05,
TEMP. TARGET_D06,
TEMP. TARGET_D07,
TEMP. TARGET_D08,
TEMP. TARGET_D09,
TEMP. TARGET_D10,
TEMP. Target_d11,
TEMP. TARGET_D12,
TEMP. TARGET_D13,
TEMP. TARGET_D14,
TEMP. TARGET_D15,
TEMP. Target_d16,
TEMP. Target_d17,
TEMP. Target_d18,
TEMP. TARGET_D19,
TEMP. TARGET_D20,
TEMP. Target_d21,
TEMP. Target_d22,
TEMP. TARGET_D23,
TEMP. TARGET_D24,
TEMP. TARGET_D25,
TEMP. TARGET_D26,
TEMP. TARGET_D27,
TEMP. TARGET_D28,
TEMP. Target_d29,
TEMP. Target_d30,
TEMP. Target_d31,
Null
DEFAULT,
DEFAULT,
Null
TEMP. USER_ID,
DEFAULT,
Null
NULL);

Small help:

See the database table so many fields is not annoying, copy, write all trouble is also easy to error, you can do the following:

1, first query out all the fields of the table

Copy Code code as follows:

SELECT column_id,
COLUMN_NAME,
Data_type,
Data_length,
Data_precision,
Data_scale,
NULLABLE,
Data_default
From User_tab_columns
WHERE table_name = ' TABLE '
ORDER BY column_id

2, copy the table column name to Excel, use the function concatenate

Extrapolate, most of the time can be used, organized from the network