大批量数据插入思路
1 建立临时表 table_temp:
ORACLE临时表有两种类型:会话级的临时表和事务级的临时表(依据自身需求建表)
关键字:
会话级别:
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ( ID NUMBER , NAME VARCHAR2(32) ) ON COMMIT PRESERVE ROWS;
事务级别:
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ( ID NUMBER , NAME VARCHAR2(32) ) ON COMMIT DELETE ROWS;
2 数据写入临时表:insert into 临时表 table_temp
3 merge目标表 table_obj
MERGE INTO table_obj T1 USING
(select 。table_temp) T2 WHEN MATCHED
THEN UPDATE
SET T1.a = T2.a
WHEN NOT MATCHED THEN
INSERT(...)
VALUES (...)