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

This article will explain in detail what the usage of oracle merge into is, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. The use of MERGE INTO

MERGE INTO is a new feature that appeared after Oracle 9i. So what is this function?

To put it simply, it is: "if you have it, update it; if you don't, insert it."

From this sentence, it should be understood that when merge into manipulates an object'A', it needs to have another result set as the source data.

Merge into' compares the data in B with the data in An according to a certain condition'C'. If the data in A satisfies condition C, the update operation is performed, and if the condition 'condition' is not met, the insert operation is performed. (please note this correspondence)

2. Grammatical structure

MERGE [INTO] [schema.] table [alias]

USING {[schema.] table | views | query} [alias]

ON {condition}

WHEN MATCHED THEN UPDATE SET {clause}

WHEN NOT MATCHED THEN INSERT VALUES {clause}

We can be used for the processing of single data or for batch processing of data. For merge into, it was all a piece of cake for Zhang Fei to eat bean sprouts. And it is more efficient than performing update+insert operations alone.

Note, however, that the result set B in the using statement cannot be the same as the object An of merge into, otherwise, it will be because the result set An and B are identical.

When on () makes equivalence judgment, it can only carry out update operation, not insert operation. When on () makes non-equivalence judgment, it can only carry out insert operation, not update operation.

Maybe it's not very clear to say that. Let's demonstrate the actual operation below and we will understand it clearly.

3. Test MERGE INTO

-

-- create a test table-- TEST1--

SQL > CREATE TABLE TEST1 (

2 USERID NUMBER

3 ID NUMBER)

Table created

-

-- populating test data--

SQL > begin

2 for i in 200..230 loop

3 insert into test1 values (I, (iTun100) / 10)

4 end loop

5 end

6 /

PL/SQL procedure successfully completed

SQL > select * from test1

USERID ID

--

200 30

201 30.1

202 30.2

203 30.3

204 30.4

205 30.5

206 30.6

207 30.7

208 30.8

209 30.9

210 31

211 31.1

212 31.2

213 31.3

214 31.4

215 31.5

216 31.6

217 31.7

218 31.8

219 31.9

USERID ID

--

220 32

221 32.1

222 32.2

223 32.3

224 32.4

225 32.5

226 32.6

227 32.7

228 32.8

229 32.9

230 33

31 rows selected

-

-- create test tables-- TEST2, TEST3--

SQL > create table test2

2 as

3 select userid,trunc (id,-1) id2 from test1 where length (id) = 4

Table created

SQL > select * from test2

USERID ID2

--

201 30

202 30

203 30

204 30

205 30

206 30

207 30

208 30

209 30

211 30

212 30

213 30

214 30

215 30

216 30

217 30

218 30

219 30

221 30

222 30

USERID ID2

--

223 30

224 30

225 30

226 30

227 30

228 30

229 30

27 rows selected

SQL > create table test3

2 as

3 select userid,id id3 from test1 where length (id) 4

Table created

SQL > select * from test3

USERID ID3

--

200 30

210 31

220 32

230 33

-

-- Test MERGE INTO--

SQL > MERGE INTO test3 t

2 USING (SELECT userid,id2 FROM test2 WHERE id2=30) tw

3 ON (t.userid = tw.userid)

4 WHEN MATCHED THEN UPDATE SET t.id3=tw.id2

5 WHEN NOT MATCHED THEN INSERT VALUES (tw.userid,tw.id2)

Done

SQL > SELECT * FROM test3

USERID ID3

--

200 30

210 31

220 32

230 33

202 30

201 30

204 30

203 30

228 30

217 30

209 30

221 30

219 30

227 30

205 30

212 30

225 30

208 30

222 30

226 30

USERID ID3

--

215 30

218 30

214 30

216 30

211 30

224 30

213 30

223 30

206 30

207 30

229 30

31 rows selected

SQL >

When the test is complete, we see that all the data in the test2 table is added to the test3 table.

PS:

On (condition_clause) in the conditional clause here, you can use comparison operators such as =, >, <, and so on.

So much for sharing about the usage of oracle merge into. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.