Efficiently process Oracle large amounts of data with bulk binding batch-bound mode

The way to use bulk binding (bulk binding). When looping through SQL statements that execute a bound variable, a large number of context switches occur in PL/SQL and in the engines (switches). With bulk binding, data can be batched from the Plsql engine to the SQL engine, reducing the context switching process and improving efficiency.

in the case of Oracle usage, you often encounter situations where you need to insert large amounts of data, which can be used to insert data using the bulk binding.

Reference: Oracle Progressive submission, batch submission, and limit acceleration methods

ORACLE Bulk Binding Batch Binding

Oracle PL/SQL optimization and tuning-Bulk instructions

Reference code:

DECLARE  --Local variables hereL_date_f date;  l_date_t date; TYPE t_event_id is TABLE  of  Number INDEX  byBinary_integer;  L_EVENT_ID t_event_id; CURSORC_data (p_date_f date, p_date_t date) is    SELECTxe.event_id, Xe.event_date, xe.application_id, Xe.process_status_code fromXla_events XEWHERExe.application_id= 707        andXe.process_status_code<> 'P'        andXe.event_date>P_date_f andXe.event_date<=p_date_t; TYPE T_tab is TABLE  ofC_data%ROWTYPE; L_event T_tab;BEGIN  --Test Statements hereL_date_f:=To_date ('2008/01/01','YYYY/DD'); l_date_t:=To_date ('2017/05/31','YYYY/DD');  forIinch 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 '));      OPENC_data (L_date_f+I- 1, L_date_f+i); LOOPFETCHC_dataBULKCOLLECT intoL_event LIMIT10000; --Dbms_output.put_line (l_event.count);      EXIT  whenL_event.Count = 0; FORALL Jinch 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_even          T (j). Event_status_code, L_event (j). Process_status_code); FORALL Jinch 1.. L_event.Count        UPDATEXla_events XESETXe.process_status_code= 'P', Xe.event_status_code= 'P'         WHERExe.event_id=L_event (j). event_id andxe.application_id=L_event (j). application_id; COMMIT; ENDLOOP; CLOSEC_data; ENDLOOP;END;

Efficiently process Oracle large amounts of data with bulk binding batch-bound mode