\u6700\u8fd1\u6709\u4e00\u4e2a\u4e1a\u52a1\u4f7f\u7528merge into\u62a5\u4e3b\u952e\u51b2\u7a81\u7684\u9519\u8bef\u3002\u5404\u5730\u5e02\u5c06\u6570\u636e\u6c47\u603b\u5230\u7701\uff0c\u7701\u7684\u6570\u636e\u662f\u6309\u7167\u5c40\u7f16\u7801\u5206\u533a\uff0c\u7531\u4e8e\u4e0d\u540c\u7684\u5730\u5e02\uff0c\u6709\u4e3b\u952e\u76f8\u540c\u7684\u6570\u636e\uff0c\u5e94\u8be5\u662f\u5783\u573e\u6570\u636e\u3002<\/p>
--\u521d\u59cb\u5316\u6570\u636e<\/p>
drop table T_LIST purge;
drop table T_LIST1 purge;
?CREATE TABLE T_LIST
?(
? ? ID? NUMBER(7) NOT NULL PRIMARY KEY,
? ? CITY VARCHAR2(10),
? ? sort number
?)
?PARTITION BY LIST (CITY)
?(
? ? ? PARTITION P_BEIJING? VALUES ('BEIJING') ,
? ? ? PARTITION P_SHANGHAI VALUES ('SHANGHAI'),
? ? ? PARTITION P_GUANGZHOU VALUES ('GUANGZHOU')
?);
?insert into T_LIST values(1,'BEIJING',11);
?insert into T_LIST values(2,'SHANGHAI',22);
?insert into T_LIST values(3,'GUANGZHOU',33);
?commit;<\/p>
?CREATE TABLE T_LIST1
?(
? ? ID? NUMBER(7) PRIMARY KEY,
? ? CITY VARCHAR2(10),
? ? sort number
?)
?PARTITION BY LIST (CITY)
?(
? ? ? PARTITION P_BEIJING? VALUES ('BEIJING') ,
? ? ? PARTITION P_SHANGHAI VALUES ('SHANGHAI'),
? ? ? PARTITION P_GUANGZHOU VALUES ('GUANGZHOU')
?);
?insert into T_LIST1 values(1,'BEIJING',111);
?insert into T_LIST1 values(3,'SHANGHAI',222);
?insert into T_LIST1 values(2,'GUANGZHOU',333);
?commit;
--\u5efa\u7acb\u9519\u8bef\u65e5\u5fd7\u8868
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T_LIST', 'T_ERROR_LOG');<\/p>
?declare
? ? Type city is table of varchar2(10);
? ? v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');
? ? V_SQL? VARCHAR2(4000) :=
?'merge into T_LIST a using(select * from T_LIST1 where CITY = :1) b
?on (a.id =? b.id and a.city =? b.city and a.city = :2)
?when matched then
? update set a.sort=b.sort
?when not matched then
? insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT? UNLIMITED';
?begin
? ? ? for i in v_city.first .. v_city.last loop? ?
? ? ? execute immediate V_SQL using v_city(i),v_city(i);
? ? ? end loop;
? ? ? commit;
?end;<\/p>
?SQL> select ORA_ERR_MESG$,id,city from T_ERROR_LOG;
?ORA_ERR_MESG$? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ID? ? ? ? CITY
?-------------------------------------------------- ---------- ----------
?ORA-00001: \u8fdd\u53cd\u552f\u4e00\u7ea6\u675f\u6761\u4ef6 (TEST.SYS_C0011594)? ? 3? ? ? ? ? SHANGHAI
?
ORA-00001: \u8fdd\u53cd\u552f\u4e00\u7ea6\u675f\u6761\u4ef6 (TEST.SYS_C0011594)? ? 2? ? ? ? ? GUANGZHOU<\/p>
\u5f53\u7136\uff0c\u5728\u6570\u636e\u91cf\u5927\u7684\u60c5\u51b5\u4e0b\u8981\u4f7f\u7528\u5e76\u884c\uff0c\u6709\u53ef\u80fd\u4f1a\u6709\u95ee\u9898\uff0c\u56e0\u4e3a\u5e76\u884c\u9ed8\u8ba4\u662f\u76f4\u63a5\u8def\u5f84\u8bfb\u3002<\/p>
alter session enable parallel dml; \u89e3\u51b3\u65b9\u6848\u662f\uff1a\u52a0\u4e00\u4e2anoappend\u7684hint\uff0c\u5e76\u884c\u4e5f\u53ef\u4ee5\u6539\u4e3amerge \/*+parallel(a)? parallel(b) noappend*\/ into.
?declare
? ? Type city is table of varchar2(10);
? ? v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');
? ? V_SQL? VARCHAR2(4000) :=
?'merge \/*+parallel(2) *\/ into T_LIST a using(select * from T_LIST1 where CITY = :1) b
?on (a.id =? b.id and a.city =? b.city and a.city = :2)
?when matched then
? update set a.sort=b.sort
?when not matched then
? insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT UNLIMITED';
?begin
? ? ? for i in v_city.first .. v_city.last loop
? ? ? execute immediate V_SQL using v_city(i),v_city(i);
? ? ? commit;
? ? ? end loop;
?end;
ORA-12801: \u5e76\u884c\u67e5\u8be2\u670d\u52a1\u5668 P000 \u4e2d\u53d1\u51fa\u9519\u8bef\u4fe1\u53f7<\/span>
ORA-00001: \u8fdd\u53cd\u552f\u4e00\u7ea6\u675f\u6761\u4ef6 (TEST.SYS_C0011594)<\/span>
ORA-06512: \u5728 line 14<\/span><\/p>
?declare
? ? Type city is table of varchar2(10);
? ? v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');
? ? V_SQL? VARCHAR2(4000) :=
?'merge \/*+parallel(2) noappend<\/span>*\/ into T_LIST a using(select * from T_LIST1 where CITY = :1) b
?on (a.id =? b.id and a.city =? b.city and a.city = :2)
?when matched then
? update set a.sort=b.sort
?when not matched then
? insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT UNLIMITED';
?begin
? ? ? for i in v_city.first .. v_city.last loop
? ? ? execute immediate V_SQL using v_city(i),v_city(i);
? ? ? commit;
? ? ? end loop;
?end;<\/p>