c+oracle+bulk,使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据

--Local variables here

l_date_f DATE;

l_date_t DATE;

TYPE t_event_idIS TABLE OF NUMBER INDEX BYBINARY_INTEGER;

l_event_id t_event_id;CURSOR c_data(p_date_f DATE, p_date_t DATE) IS

SELECTxe.event_id,

xe.event_date,

xe.application_id,

xe.process_status_codeFROMxla_events xeWHERE xe.application_id = 707

AND xe.process_status_code <> ‘P‘

AND xe.event_date >p_date_fAND xe.event_date <=p_date_t;

TYPE t_tabIS 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);

LOOPFETCH c_data BULKCOLLECTINTO l_event LIMIT 10000;--dbms_output.put_line(l_event.count);

EXIT WHEN l_event.count = 0;

FORALL jIN 1 .. l_event.count

INSERT INTOxla_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 jIN 1 .. l_event.count

UPDATExla_events xeSET xe.process_status_code = ‘P‘, xe.event_status_code = ‘P‘

WHERE xe.event_id =l_event(j).event_idAND xe.application_id =l_event(j).application_id;COMMIT;ENDLOOP;CLOSEc_data;ENDLOOP;END;

使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据

标签：cursor   integer   批量提交   参考   class   state   rom   archive   UI

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/huanghongbo/p/7426676.html