PROCEDURE P_GET_BAR_FORALL AS
TYPE TYPE_bar_record_id IS TABLE OF lxy_tt_bar.bar_record_id%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_waybill_no IS TABLE OF lxy_tt_bar.waybill_no%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_zone_code IS TABLE OF lxy_tt_bar.zone_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_op_code IS TABLE OF lxy_tt_bar.op_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_op_attach_info IS TABLE OF lxy_tt_bar.op_attach_info%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_courier_code IS TABLE OF lxy_tt_bar.courier_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_opr_code IS TABLE OF lxy_tt_bar.bar_opr_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_upload_tm IS TABLE OF lxy_tt_bar.bar_upload_tm%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_obj_type_code IS TABLE OF lxy_tt_bar.obj_type_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_contnr_code IS TABLE OF lxy_tt_bar.contnr_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_pay_flg IS TABLE OF lxy_tt_bar.pay_flg%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_stay_why_code IS TABLE OF lxy_tt_bar.stay_why_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_subbill_piece_qty IS TABLE OF lxy_tt_bar.subbill_piece_qty%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_upload_type_code IS TABLE OF lxy_tt_bar.bar_upload_type_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_weight_qty IS TABLE OF lxy_tt_bar.weight_qty%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_fee_amt IS TABLE OF lxy_tt_bar.fee_amt%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_accountant_code IS TABLE OF lxy_tt_bar.accountant_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_other_info IS TABLE OF lxy_tt_bar.other_info%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_op_name IS TABLE OF lxy_tt_bar.op_name%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_zone_type_code IS TABLE OF lxy_tt_bar.zone_type_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_encrypt_string IS TABLE OF lxy_tt_bar.encrypt_string%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_sn IS TABLE OF lxy_tt_bar.bar_sn%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_schedule_code IS TABLE OF lxy_tt_bar.schedule_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_sign_type_code IS TABLE OF lxy_tt_bar.sign_type_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_src_contnr_code IS TABLE OF lxy_tt_bar.src_contnr_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_phone_zone IS TABLE OF lxy_tt_bar.phone_zone%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_phone IS TABLE OF lxy_tt_bar.phone%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_stop_over_flg IS TABLE OF lxy_tt_bar.stop_over_flg%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_batch_code IS TABLE OF lxy_tt_bar.batch_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_dest_zone_code IS TABLE OF lxy_tt_bar.dest_zone_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_autoloading IS TABLE OF lxy_tt_bar.autoloading%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_scan_tm IS TABLE OF lxy_tt_bar.bar_scan_tm%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_scan_dt IS TABLE OF lxy_tt_bar.bar_scan_dt%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_1 IS TABLE OF lxy_tt_bar.extend_attach_1%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_2 IS TABLE OF lxy_tt_bar.extend_attach_2%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_bar_upload_opr_code IS TABLE OF lxy_tt_bar.bar_upload_opr_code%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_3 IS TABLE OF lxy_tt_bar.extend_attach_3%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_4 IS TABLE OF lxy_tt_bar.extend_attach_4%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_5 IS TABLE OF lxy_tt_bar.extend_attach_5%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_6 IS TABLE OF lxy_tt_bar.extend_attach_6%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_extend_attach_7 IS TABLE OF lxy_tt_bar.extend_attach_7%TYPE INDEX BY BINARY_INTEGER;
TAB_bar_record_id TYPE_bar_record_id ;
TAB_waybill_no TYPE_waybill_no ;
TAB_zone_code TYPE_zone_code ;
TAB_op_code TYPE_op_code ;
TAB_op_attach_info TYPE_op_attach_info ;
TAB_courier_code TYPE_courier_code ;
TAB_bar_opr_code TYPE_bar_opr_code ;
TAB_bar_upload_tm TYPE_bar_upload_tm ;
TAB_obj_type_code TYPE_obj_type_code ;
TAB_contnr_code TYPE_contnr_code ;
TAB_pay_flg TYPE_pay_flg ;
TAB_stay_why_code TYPE_stay_why_code ;
TAB_subbill_piece_qty TYPE_subbill_piece_qty ;
TAB_bar_upload_type_code TYPE_bar_upload_type_code ;
TAB_weight_qty TYPE_weight_qty ;
TAB_fee_amt TYPE_fee_amt ;
TAB_accountant_code TYPE_accountant_code ;
TAB_other_info TYPE_other_info ;
TAB_op_name TYPE_op_name ;
TAB_zone_type_code TYPE_zone_type_code ;
TAB_encrypt_string TYPE_encrypt_string ;
TAB_bar_sn TYPE_bar_sn ;
TAB_schedule_code TYPE_schedule_code ;
TAB_sign_type_code TYPE_sign_type_code ;
TAB_src_contnr_code TYPE_src_contnr_code ;
TAB_phone_zone TYPE_phone_zone ;
TAB_phone TYPE_phone ;
TAB_stop_over_flg TYPE_stop_over_flg ;
TAB_batch_code TYPE_batch_code ;
TAB_dest_zone_code TYPE_dest_zone_code ;
TAB_autoloading TYPE_autoloading ;
TAB_bar_scan_tm TYPE_bar_scan_tm ;
TAB_bar_scan_dt TYPE_bar_scan_dt ;
TAB_extend_attach_1 TYPE_extend_attach_1 ;
TAB_extend_attach_2 TYPE_extend_attach_2 ;
TAB_bar_upload_opr_code TYPE_bar_upload_opr_code ;
TAB_extend_attach_3 TYPE_extend_attach_3 ;
TAB_extend_attach_4 TYPE_extend_attach_4 ;
TAB_extend_attach_5 TYPE_extend_attach_5 ;
TAB_extend_attach_6 TYPE_extend_attach_6 ;
TAB_extend_attach_7 TYPE_extend_attach_7 ;
SQLTEXT VARCHAR2(30000);
CURSOR MY_CURSOR IS
SELECT
*
FROM TEMP--TT_BAR_RECORD_30@linkdw18 T1
/*where t1.bar_scan_tm >= sysdate - 1 --起始id
and t1.bar_scan_tm < sysdate --结束id
and t1.waybill_no not like '00%'*/;
begin
rpt_system.log('p_get_bar_forall', 'ok', sysdate, '', 'staring');
/*begin
EXECUTE IMMEDIATE 'drop table temp';
exception
when others then
null;
end;*/
SQLTEXT := 'CREATE TABLE TEMP NOLOGGING PARALLEL 10 AS select /*+index(t1 ipk_tt_bar_record) */ * from TT_BAR_RECORD_30@linkdw18 t1 where t1.BAR_RECORD_ID >= 8906504301 and t1.BAR_RECORD_ID < 8927405506 ';
EXECUTE IMMEDIATE SQLTEXT;
EXECUTE IMMEDIATE 'ALTER TABLE TEMP NOPARALLEL';
OPEN MY_CURSOR;
LOOP
FETCH MY_CURSOR BULK COLLECT INTO
TAB_bar_record_id ,
TAB_waybill_no ,
TAB_zone_code ,
TAB_op_code ,
TAB_op_attach_info ,
TAB_courier_code ,
TAB_bar_opr_code ,
TAB_bar_upload_tm ,
TAB_obj_type_code ,
TAB_contnr_code ,
TAB_pay_flg ,
TAB_stay_why_code ,
TAB_subbill_piece_qty ,
TAB_bar_upload_type_code ,
TAB_weight_qty ,
TAB_fee_amt ,
TAB_accountant_code ,
TAB_other_info ,
TAB_op_name ,
TAB_zone_type_code ,
TAB_encrypt_string ,
TAB_bar_sn ,
TAB_schedule_code ,
TAB_sign_type_code ,
TAB_src_contnr_code ,
TAB_phone_zone ,
TAB_phone ,
TAB_stop_over_flg ,
TAB_batch_code ,
TAB_dest_zone_code ,
TAB_autoloading ,
TAB_bar_scan_tm ,
TAB_bar_scan_dt ,
TAB_extend_attach_1 ,
TAB_extend_attach_2 ,
TAB_bar_upload_opr_code ,
TAB_extend_attach_3 ,
TAB_extend_attach_4 ,
TAB_extend_attach_5 ,
TAB_extend_attach_6 ,
TAB_extend_attach_7
LIMIT 5000; --批量读取
FORALL I IN 1 .. TAB_bar_record_id.COUNT
INSERT INTO lxy_tt_bar
VALUES
(TAB_bar_record_id(I) ,
TAB_waybill_no(I) ,
TAB_zone_code(I) ,
TAB_op_code(I) ,
TAB_op_attach_info(I) ,
TAB_courier_code(I) ,
TAB_bar_opr_code(I) ,
TAB_bar_upload_tm(I) ,
TAB_obj_type_code(I) ,
TAB_contnr_code(I) ,
TAB_pay_flg(I) ,
TAB_stay_why_code(I) ,
TAB_subbill_piece_qty(I) ,
TAB_bar_upload_type_code(I) ,
TAB_weight_qty(I) ,
TAB_fee_amt(I) ,
TAB_accountant_code(I) ,
TAB_other_info(I) ,
TAB_op_name(I) ,
TAB_zone_type_code(I) ,
TAB_encrypt_string(I) ,
TAB_bar_sn(I) ,
TAB_schedule_code(I) ,
TAB_sign_type_code(I) ,
TAB_src_contnr_code(I) ,
TAB_phone_zone(I) ,
TAB_phone(I) ,
TAB_stop_over_flg(I) ,
TAB_batch_code(I) ,
TAB_dest_zone_code(I) ,
TAB_autoloading(I) ,
TAB_bar_scan_tm(I) ,
TAB_bar_scan_dt(I) ,
TAB_extend_attach_1(I) ,
TAB_extend_attach_2(I) ,
TAB_bar_upload_opr_code(I) ,
TAB_extend_attach_3(I) ,
TAB_extend_attach_4(I) ,
TAB_extend_attach_5(I) ,
TAB_extend_attach_6(I) ,
TAB_extend_attach_7(I)
);
COMMIT;
EXIT WHEN MY_CURSOR%NOTFOUND;
END LOOP;
--放源
BEGIN
TAB_bar_record_id.delete ;
TAB_waybill_no.delete ;
TAB_zone_code.delete ;
TAB_op_code.delete ;
TAB_op_attach_info.delete ;
TAB_courier_code.delete ;
TAB_bar_opr_code.delete ;
TAB_bar_upload_tm.delete ;
TAB_obj_type_code.delete ;
TAB_contnr_code.delete ;
TAB_pay_flg.delete ;
TAB_stay_why_code.delete ;
TAB_subbill_piece_qty.delete ;
TAB_bar_upload_type_code.delete ;
TAB_weight_qty.delete ;
TAB_fee_amt.delete ;
TAB_accountant_code.delete ;
TAB_other_info.delete ;
TAB_op_name.delete ;
TAB_zone_type_code.delete ;
TAB_encrypt_string.delete ;
TAB_bar_sn.delete ;
TAB_schedule_code.delete ;
TAB_sign_type_code.delete ;
TAB_src_contnr_code.delete ;
TAB_phone_zone.delete ;
TAB_phone.delete ;
TAB_stop_over_flg.delete ;
TAB_batch_code.delete ;
TAB_dest_zone_code.delete ;
TAB_autoloading.delete ;
TAB_bar_scan_tm.delete ;
TAB_bar_scan_dt.delete ;
TAB_extend_attach_1.delete ;
TAB_extend_attach_2.delete ;
TAB_bar_upload_opr_code.delete ;
TAB_extend_attach_3.delete ;
TAB_extend_attach_4.delete ;
TAB_extend_attach_5.delete ;
TAB_extend_attach_6.delete ;
TAB_extend_attach_7.delete ;
END;
EXECUTE IMMEDIATE 'CREATE table temp AS SELECT * FROM lxy_tt_bar WHERE 1=2';
rpt_system.log('p_get_bar_forall', 'ok', sysdate, '', 'end');
end p_get_bar_forall;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-674778/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12122734/viewspace-674778/
扫一扫
2966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
