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

Problems encountered in developing with merge.

There is a requirement that you need to take data from the base table and put it into the target table. If you already have the data from the base table in the target table, modify the data in the target table.

If there is no data in the underlying table, the data from the underlying table is inserted into the target table. So merge is used to complete such an operation.

First of all, establish a basic environment.

Prompt PL/SQL Developer import file

Prompt Created on September 19, 2017 by SAMSUNG

Set feedback off

Set define off

Prompt Creating CL_BAS...

Create table CL_BAS

(

Line_id VARCHAR2 (20) not null

Index_item_code VARCHAR2 (4) not null

Worst_mansge_limit_nval NUMBER not null

Best_manage_limit_nval NUMBER not null

Reg_dttm DATE not null

Register_id VARCHAR2 (20) not null

Update_dttm DATE not null

Updater_id VARCHAR2 (20) not null

)

Comment on table CL_BAS

Is'?'

Comment on column CL_BAS.line_id

Is'?'

Comment on column CL_BAS.index_item_code

Is'?'

Comment on column CL_BAS.worst_mansge_limit_nval

Is'?'

Comment on column CL_BAS.best_manage_limit_nval

Is'?'

Comment on column CL_BAS.reg_dttm

Is'?

Comment on column CL_BAS.register_id

Is'?'

Comment on column CL_BAS.update_dttm

Is'?

Comment on column CL_BAS.updater_id

Is'?'

Alter table CL_BAS

Add constraint CL_BAS_PK primary key (LINE_ID, INDEX_ITEM_CODE)

Prompt Creating LINE_BAS...

Create table LINE_BAS

(

Line_id VARCHAR2 (20) not null

Line_type_code VARCHAR2 (2) not null

Line_name VARCHAR2 (100) not null

Line_desc VARCHAR2 (500)

Ltdly_unit_start_days NUMBER not null

Ltdly_unit_days NUMBER not null

Ltdly_unit_end_days NUMBER not null

Reg_dttm DATE not null

Register_id VARCHAR2 (20) not null

Update_dttm DATE not null

Updater_id VARCHAR2 (20) not null

)

Comment on table LINE_BAS

Is'?

Comment on column LINE_BAS.line_id

Is'?'

Comment on column LINE_BAS.line_type_code

Is'?'

Comment on column LINE_BAS.line_name

Is'?

Comment on column LINE_BAS.line_desc

Is'?

Comment on column LINE_BAS.ltdly_unit_start_days

Is'?'

Comment on column LINE_BAS.ltdly_unit_days

Is'?'

Comment on column LINE_BAS.ltdly_unit_end_days

Is'?'

Comment on column LINE_BAS.reg_dttm

Is'?

Comment on column LINE_BAS.register_id

Is'?'

Comment on column LINE_BAS.update_dttm

Is'?

Comment on column LINE_BAS.updater_id

Is'?'

Alter table LINE_BAS

Add constraint LINE_BAS_PK primary key (LINE_ID)

Prompt Disabling triggers for CL_BAS...

Alter table CL_BAS disable all triggers

Prompt Disabling triggers for LINE_BAS...

Alter table LINE_BAS disable all triggers

Prompt Deleting LINE_BAS...

Delete from LINE_BAS

Commit

Prompt Deleting CL_BAS...

Delete from CL_BAS

Commit

Prompt Loading CL_BAS...

Insert into CL_BAS (line_id, index_item_code, worst_mansge_limit_nval, best_manage_limit_nval, reg_dttm, register_id, update_dttm, updater_id)

Values ('DFDF',' 01, 1, 1, to_date ('19-09-2017 12-50 dd-mm-yyyy hh34:mi:ss'),'1-9, to_date ('19-09-2017 12-50 50-57, dd-mm-yyyy hh34:mi:ss'),'1')

Commit

Prompt 1 records loaded

Prompt Loading LINE_BAS...

Insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)

Values ('DFDF',' 02, 'dfdf',' dfdf', 11, 1, 1, to_date ('18-09-2017 14-33-24, 'dd-mm-yyyy hh34:mi:ss'),' admin', to_date ('18-09-2017 14-33-33, dd-mm-yyyy hh34:mi:ss'), 'admin')

Insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)

Values ('FDFD',' 01, 'dfdf',' dfdf', 1, 1, 1, to_date ('18-09-2017 14-33-24-dd-mm-yyyy hh34:mi:ss'), 'admin', to_date (' 18-09-2017 14-33-33-dd-mm-yyyy hh34:mi:ss'), 'admin')

Insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)

Values ('FDFDF',' 02, 'fdf',' dfdf', 1, 1, 1, to_date ('18-09-2017 14-33 fdf', 42, 'dd-mm-yyyy hh34:mi:ss'),' admin', to_date ('18-09-2017 14-33 fdf', 33-33), 'admin')

Insert into LINE_BAS (line_id, line_type_code, line_name, line_desc, ltdly_unit_start_days, ltdly_unit_days, ltdly_unit_end_days, reg_dttm, register_id, update_dttm, updater_id)

Values ('FFFF',' 04, 'ff',' ff', 1, 1, 1, to_date ('18-09-2017 1626 26 to_date, 'dd-mm-yyyy hh34:mi:ss'),' lu0.zhang', to_date ('18-09-2017 16 26 ff', 23, 'dd-mm-yyyy hh34:mi:ss'),' lu0.zhang')

Commit

Prompt 4 records loaded

Prompt Enabling triggers for CL_BAS...

Alter table CL_BAS enable all triggers

Prompt Enabling triggers for LINE_BAS...

Alter table LINE_BAS enable all triggers

Set feedback on

Set define on

Prompt Done.

Execution of merge Times error violates primary key constraint

MERGE INTO CL_BAS T1

USING LINE_BAS T2 ON (T1.LINE_ID = T2.LINE_ID

AND T2.LINE_ID = 'DFDF')

WHEN MATCHED THEN

UPDATE SET T1.WORST_MANSGE_LIMIT_NVAL = 1

WHEN NOT MATCHED THEN

INSERT

(LINE_ID

INDEX_ITEM_CODE

WORST_MANSGE_LIMIT_NVAL

BEST_MANAGE_LIMIT_NVAL

REG_DTTM

REGISTER_ID

UPDATE_DTTM

UPDATER_ID)

VALUES

('101'

'102'

one,

one,

SYSDATE

eleven,

SYSDATE

11)

It shows that MERGE adds data in batches, especially when inserting constant data, it does not insert only one piece of data, but adds all the data selected by USING to the target table.

Insert data, so if the inserted data is constant, it will inevitably lead to repeated insertion of data.

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.