之前探讨过FORALL在数据批量处理中结合INSERT、DELETE和UPDATE的使用,Oracle 11g引入FORALL结合MERGE语句的使用方法。
另外值得一提的是,11g取消了FORALL使用UPDATE语句时,SET部分不能引用数组索引的限制。
有例程表原数据为
SQL> select t.* from demo_all_people_f t
2 /
EMPLOYEE_NUMBER FULL_NAME LAST_UPDATE_DATE
--------------- ---------- ----------------
7788 SCOTT 1982/12/9
7876 ADAMS 1983/1/12
执行例程
DECLARE
TYPE rcd_emp IS RECORD(
employee_number NUMBER,
full_name VARCHAR2(30),
last_update_date DATE);
TYPE tbl_emp IS TABLE OF rcd_emp INDEX BY BINARY_INTEGER;
t_emp tbl_emp;
CURSOR cur_emp IS
SELECT employee_number, full_name, trunc(SYSDATE) last_update_date
FROM demo_all_people_f
WHERE rownum = 1
UNION ALL
SELECT 5152 employee_number,
'YUSUF' full_name,
trunc(SYSDATE) last_update_date
FROM dual;
BEGIN
OPEN cur_emp;
FETCH cur_emp BULK COLLECT
INTO t_emp;
CLOSE cur_emp;
FORALL k IN t_emp.first .. t_emp.last
MERGE INTO demo_all_people_f t
USING (SELECT t_emp(k).employee_number employee_number,
t_emp(k).full_name full_name,
t_emp(k).last_update_date last_update_date
FROM dual) s
ON (s.employee_number = t.employee_number)
WHEN NOT MATCHED THEN
INSERT
(employee_number, full_name, last_update_date)
VALUES
(s.employee_number, s.full_name, s.last_update_date)
WHEN MATCHED THEN
UPDATE
SET t.last_update_date = s.last_update_date
WHERE t.employee_number = s.employee_number;
COMMIT;
END;
执行后表数据
SQL> select t.* from demo_all_people_f t
2 /
EMPLOYEE_NUMBER FULL_NAME LAST_UPDATE_DATE
--------------- ---------- ----------------
5152 YUSUF 2023/5/9
7788 SCOTT 2023/5/9
7876 ADAMS 1983/1/12