BATCHINSERTSK_DEVICE_RECORD1-最后一次修订
CREATE OR REPLACE PROCEDURE BATCHINSERTSK_DEVICE_RECORD1
(
xmlstr IN clob,
v_commits out number,
p_rows Out Sys_Refcursor
)
AS
iscz number;
zqcomit number;
pchsc varchar2(50);
BEGIN
savepoint sp01; --设置回退点
v_commits:=0; --v_commits初始值 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);
commit;
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>
-- <d><u><i>1</i><t>case1</t><l>1</l><b>1</b><n>1</n><m>纳税人</m><h>回收人</h><r>2015/09/28</r><y>1</y><p>a</p><s>1</s><ot>1</ot><ht>1</ht><e>1</e></u><u><i>2</i><t>case2</t><l>2</l><b>2</b><n>2</n><m>纳税人02</m><h>回收人02</h><r>2015/09/28</r><y>2</y><p>b</p><s>2</s><ot>2</ot><ht>2</ht><e>2</e></u><u><i>3</i><t>case3</t><l>3</l><b>3</b><n>3</n><m>纳税人03</m><h>回收人03</h><r>2015/09/28</r><y>3</y><p>c</p><s>3</s><ot>3</ot><ht>3</ht><e>3</e></u><u><i>4</i><t>case4</t><l>4</l><b>4</b><n>4</n><m>纳税人04</m><h>回收人04</h><r>2015/09/28</r><y>4</y><p>d</p><s>4</s><ot>4</ot><ht>4</ht><e>4</e></u><u><i>5</i><t>case5</t><l>5</l><b>5</b><n>5</n><m>纳税人05</m><h>回收人05</h><r>2015/09/28</r><y>5</y><p>5</p><s>5</s><ot>5</ot><ht>5</ht><e>5</e></u><u><i>6</i><t>case6</t><l>6</l><b>6</b><n>6</n><m>纳税人06</m><h>回收人06</h><r>2015/09/28</r><y>6</y><p>e</p><s>6</s><ot>6</ot><ht>6</ht><e>6</e></u><u><i>7</i><t>case7</t><l>7</l><b>7</b><n>7</n><m>纳税人07</m><h>回收人07</h><r>2015/09/28</r><y>7</y><p>f</p><s>7</s><ot>7</ot><ht>7</ht><e>7</e></u><u><i>8</i><t>case8</t><l>8</l><b>8</b><n>8</n><m>纳税人08</m><h>回收人08</h><r>2015/09/28</r><y>8</y><p>g</p><s>8</s><ot>8</ot><ht>8</ht><e>8</e></u></d>
end;
