当前位置：Gxlcms >
数据库问题 >
使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据
使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据
时间：2021-07-01 10:21:17
帮助过：33人阅读
-- Local variables here
l_date_f DATE;
l_date_t DATE;
TYPE t_event_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_event_id t_event_id;
CURSOR c_data(p_date_f DATE, p_date_t DATE)
IS
SELECT xe.event_id,
xe.event_date,
xe.application_id,
xe.process_status_code
FROM xla_events xe
WHERE xe.application_id
= 707
AND xe.process_status_code
<> ‘P‘
AND xe.event_date
> p_date_f
AND xe.event_date
<= p_date_t;
TYPE t_tab IS TABLE OF c_data
%ROWTYPE;
l_event t_tab;
BEGIN
-- Test statements here
l_date_f :
= to_date(
‘2008/01/01‘,
‘yyyy/dd‘);
l_date_t := to_date(
‘2017/05/31‘,
‘yyyy/dd‘);
FOR i
IN 1 .. (l_date_t
- l_date_f) LOOP
--dbms_output.put_line(‘l_date_f:‘||to_char(l_date_f+i-1,‘yyyy/dd‘));
--dbms_output.put_line(‘l_date_t:‘||to_char(l_date_f+i,‘yyyy/dd‘));
OPEN c_data(l_date_f
+ i
- 1, l_date_f
+ i);
LOOP
FETCH c_data
BULK COLLECT
INTO l_event LIMIT
10000;
--dbms_output.put_line(l_event.count);
EXIT WHEN l_event.
count = 0;
FORALL j IN 1 .. l_event.
count
INSERT INTO xla_events_bak
(application_id,
event_id,
event_date,
event_status_code,
process_status_code)
VALUES
(l_event(j).application_id,
l_event(j).event_id,
l_event(j).event_date,
l_event(j).event_status_code,
l_event(j).process_status_code);
FORALL j IN 1 .. l_event.
count
UPDATE xla_events xe
SET xe.process_status_code
= ‘P‘, xe.event_status_code
= ‘P‘
WHERE xe.event_id
= l_event(j).event_id
AND xe.application_id
= l_event(j).application_id;
COMMIT;
END LOOP;
CLOSE c_data;
END LOOP;
END;
使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据
标签：cursor integer 批量提交 参考 class state rom archive UI
