CREATE OR REPLACE PROCEDURE BATCHINSERTSK_DEVICE_RECORD1( xmlstr IN clob, v_commits out number, p_rows Out Sys_Refcursor)ASiscz number;zqcomit number;pchsc varchar2(50);BEGIN savepoint sp01; --设置回退点v_commits:=0; --v_counts初始值 0--生成批次号，用于记录请求的唯一标识select to_char(systimestamp,'yyyymmddhh24missff3') into pchsc from dual;-- 循环解析后的xml数据，逐条验证是否重复 for xsk in (select * from ( SELECT EXTRACTVALUE(VALUE(t),'/u/i') i --recover_uuid ,EXTRACTVALUE(VALUE(t),'/u/t') t --register_unit_uuid ,EXTRACTVALUE(VALUE(t),'/u/l') l --device_type ,EXTRACTVALUE(VALUE(t),'/u/b') b --device_id ,EXTRACTVALUE(VALUE(t),'/u/n') n --nsrsbh ,EXTRACTVALUE(VALUE(t),'/u/m') m --nsrmc ,EXTRACTVALUE(VALUE(t),'/u/h') h --recover_people ,to_date(EXTRACTVALUE(VALUE(t),'/u/r'),'yyyy/MM/dd') r --recover_date ,EXTRACTVALUE(VALUE(t),'/u/y') y --recover_reason ,EXTRACTVALUE(VALUE(t),'/u/p') p --recover_remark ,EXTRACTVALUE(VALUE(t),'/u/s') s --status ,EXTRACTVALUE(VALUE(t),'/u/ot') ot --hold_unit ,EXTRACTVALUE(VALUE(t),'/u/ht') ht --history_hold_unit ,EXTRACTVALUE(VALUE(t),'/u/e') e -- register_people FROM TABLE(XMLSEQUENCE(EXTRACT( XMLTYPE(xmlstr), '/d/u'))) t) xat ) loop --从正式表中遍历插入的数据，取出和 解析xml后的结果集的重复数据 。取出符合条件的数据条数 赋值变量 iscz select count(1) into iscz from sk_device_recover_tmp sk where sk.device_type=xsk.l and sk.device_id=xsk.b; if iscz=0 then --如果数据为0，没有重复数据。那么插入正式表 insert into sk_device_recover_tmp r ( r.recover_uuid, r.register_unit_uuid, r.device_type, r.device_id, r.nsrsbh, r.nsrmc, r.recover_people, r.recover_date, r.recover_reason, r.recover_remark, r.status, r.hold_unit, r.history_hold_unit, r.register_people ) values (xsk.i,xsk.t,xsk.l,xsk.b,xsk.n,xsk.m,xsk.h,xsk.r,xsk.y,xsk.p,xsk.s,xsk.ot,xsk.ht,xsk.e) ; v_commits:=v_commits+1; --v_commits依次加1 zqcomit:=zqcomit+1; --zqcomit依次加1 dbms_output.put_line(zqcomit); if mod(zqcomit,100)=0 then --分批提交 commit; end if; else --把重复的数据先放到表中临时存储，使用批次号标记 insert into admin_work_area_tmp values(xsk.l,xsk.b,pchsc); end if; end loop; --返回xml中和数据库中重复的数据，使用批次号查询 open p_rows for select * from admin_work_area_tmp where pch=pchsc;--删除当前批次号的数据delete admin_work_area_tmp where pch=pchsc;commit;v_commits:=v_commits+1;--异常处理exception when others then v_commits:=0; dbms_output.put_line(sqlcode||sqlerrm); rollback to savepoint sp01; --xml字符串 示例 <d><u><i>123456654321555</i><t>caee3254e0e84384a24067ea510cfa74</t><l>1</l><b>2534556654321</b><n>123456</n><m>纳税人</m><h>回收人</h><r>2015/09/28</r><y>1</y><p>wqerqwr</p><s>1</s><ot>57106214a6f048989aa38a5fd80c7ba3</ot><ht>57106214a6f048989aa38a5fd80c7ba3</ht><e>1130000001</e></u></d>end;/创建 中转表 正式表create table SK_DEVICE_RECOVER_tmp( recover_uuid VARCHAR2(32) not null, register_unit_uuid VARCHAR2(32), device_type CHAR(1), device_id VARCHAR2(20), nsrsbh VARCHAR2(40), nsrmc VARCHAR2(200), recover_people VARCHAR2(200), recover_date DATE, recover_reason CHAR(1), recover_remark VARCHAR2(200), status CHAR(1), hold_unit VARCHAR2(32), history_hold_unit VARCHAR2(300), register_date DATE default sysdate, is_error CHAR(1) default 0, stock_label CHAR(1) default 'N', pre_status CHAR(1), register_people VARCHAR2(100))create table ADMIN_WORK_AREA_tmp( l NUMBER, b VARCHAR2(50), pch VARCHAR2(50))tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 1 minextents 1 maxextents unlimited );
转载于:https://www.cnblogs.com/iyoume2008/p/4844614.html
相关资源：JAVA上百实例源码以及开源项目