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

How to use merge into sentence in Oracle, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Introduction to grammar

An example like the one above would be very simple to implement in SQL

If exists (select 1 from T where T.asides 1001')

Update T set T.b=2 Where T. axiom 1001'

Else insert into T (A, B) values ('1001)

In Oracle, Merge into is used to implement (the function introduced by Oracle 9i). The syntax is as follows:

MERGE INTO table_name alias1 USING (table | view | sub_query) alias2ON (join condition) WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1, col2 = col_val2 WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values)

Examples of usage

Let's just use the temporary table created in the previous chapter.

Let's check the data source tskuplu first.

You can see that there are two pieces of data in our commodity table.

Then let's take a look at the temporary table temp_cstable created in the previous chapter

There's nothing in it. Let's start writing sentences now.

Determine the incode in the temp_cstable table and the plucode in the tskuplu table. If it exists, update the Plulong character value in the tskuplu to update the yhtotal field value in the temp_cstable. If it does not exist, insert the data in the tskuplu into the temp_cstable. The default yhtotal value for the xstotal value used as the plulong is 0.

Then let's take a look at the results for the first time

You can see that there are two pieces of data in the temp_cstable table, and XStotal takes the plulong value in tskuplu as 1

Let's modify the statement again to make the merge into statement execute twice.

You can see that if the above does not exist for the first time, insert the data first, and if it exists for the second time, update the value of Yhtotal in the temporary table temp_cstable.

Complete code

Declare vi_count integer

Vs_sSql varchar2 (4000): =''

Begin

Vs_sSql:= 'select count (*) from user_tables where table_name = upper (' | |

Chr (39) | | 'temp_cstable' | | chr (39) | |')'

Execute immediate vs_sSql into vi_count

Dbms_output.put_line (vi_count)

-- determine whether the temporary table of temp_cstable exists. If there is a temporary table, it will be created if it does not exist.

If vi_count > 0 then

Vs_sSql: = 'delete from temp_cstable'

Execute immediate vs_sSql

Else

Vs_sSql: ='

Create global temporary table temp_cstable (

Incode varchar2 (20)

Barcode varchar2 (20)

Xstotal number

Yhtotal number) on commit Preserve rows'

Execute immediate vs_sSql

End if

/ * judge the incode in temp_cstable table and the plucode in tskuplu table

If it exists, update the Plulong character value in tskuplu to the yhtotal field value in temp_cstable.

If it does not exist, insert the data from tskuplu into temp_cstable, where xstotal is used as the value of plulong

The default value of yhtotal is 0

, /

Vs_sSql:= 'merge into temp_cstable T1

Using (select * from tskuplu) T2

On (t1.incode=t2.plucode)

When not matched then

Insert (incode,barcode,xstotal,yhtotal) values (t2.plucoderecitation t2.plunamerecoveryt2.plulongre0)

When matched then

Update set t1.yhtotal=t2.plulong'

Execute immediate vs_sSql

Execute immediate vs_sSql

End

This is the answer to the question about how to use the merge into sentence in Oracle. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.