Shulou(Shulou.com)06/01 Report--

1. The purpose of MERGE INTO:

MERGE INTO is a new syntax of Oracle 9i, which is added at 10g to merge UPDATE and INSERT statements. Join queries are made according to one table or subquery with another table, and the join conditions are matched.

UPDATE, mismatch to INSERT, this syntax only needs a full table scan to complete all the work, the execution efficiency will be higher than the simple UPDATE+INSERT, the specific application can be used for synchronization between tables.

2. Syntax of MERGE INTO:

Grammatical structure:

MERGE [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

Syntax description:

MERGE INTO [table name] [alias]-- the target table that needs to be updated

USING (subquery / table name / view) [alias]-source table

ON ([connection condition] AND [...]...)-- join condition / update condition

WHEN MATHED THEN UPDATE SET [...]-if there is a match, update the table record. If it is only updated, the following INSERT section can be removed.

WHEN NOT MATHED THEN INSERT VALUES () [...]-if there is no match, insert the table record

3. MERGE INTO demonstration:

1 > create test tables and data:

-- Table YAG1 as the source table and table YAG2 as the target table for updates

Record comparison of the first two tables updated by CREATE TABLE YAG1 AS SELECT OBJECT_NAME,oOBJECT_ID FROM USER_OBJECTS WHERE ROWNUMMERGE INTO:

SQL > SELECT A. OBJECTT ORDER BY ORDER BY A. OBJECTCTNAME FROM YAG1 AreYAG2B WHERE A.OBJECT_ID=B.OBJECT_ID (+) IDMagne1

A.OBJECT_ID A.OBJECT_NAME B.OBJECT_NAME

46366 AAAAA T_CAT

46367 SUM_STRING SUM_STRING

46368 ARRAYLIST ARRAYLIST

46369 TYSKZ_SJDX TYSKZ_SJDX

46370 TYSKZ_SJXMGX TYSKZ_SJXMGX

46371 PARAOBJECT

46372 T_LINK

46373 STR_SPLIT

46374 SPLIT_TYPE

46375 SYS_PLSQL_95487_9_1

3 > execute the following MERGE INTO statement:

MERGE INTO YAG2 A

USING YAG1 B

ON (A.OBJECT_ID = B.OBJECT_ID)

WHEN MATCHED THEN

UPDATE SET A.OBJECT_NAME = B.OBJECT_NAME

WHEN NOT MATCHED THEN

INSERT VALUES (B.OBJECT_NAME, B.OBJECT_ID)

COMMIT

4 > comparison of the records of the two tables after MERGE INTO update:

SQL > SELECT A. OBJECTT ORDER BY ORDER BY A. OBJECTCTNAME FROM YAG1 AreYAG2B WHERE A.OBJECT_ID=B.OBJECT_ID (+) IDMagne1

A.OBJECT_ID A.OBJECT_NAME B.OBJECT_NAME

46366 AAAAA AAAAA

46367 SUM_STRING SUM_STRING

46368 ARRAYLIST ARRAYLIST

46369 TYSKZ_SJDX TYSKZ_SJDX

46370 TYSKZ_SJXMGX TYSKZ_SJXMGX

46371 PARAOBJECT PARAOBJECT

46372 T_LINK T_LINK

46373 STR_SPLIT STR_SPLIT

46374 SPLIT_TYPE SPLIT_TYPE

46375 SYS_PLSQL_95487_9_1 SYS_PLSQL_95487_9_1

.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.