游标的使用demo

create or replace package body barcode is

procedure syncCommData is
--变量定义

--Sql语句
v_insert_sql varchar2(2000);
v_merge_sql varchar2(2000);
v_delete_sql varchar2(2000);
--是否有足够长的变更记录
hasLongRecord boolean := true;
--每次插入临时表的数据
v_tempCount number := 2000;
--异常代码
v_error_code number;
--异常信息
v_error_msg varchar2(255);

begin
---====================================
--首先查看该存储过程有没有别的Job在调用，没有就可以运行，否则直接退出。
if isJobRunning('syncCommData', true) = false then
changeJobState('syncCommData', 'running');
commit;
else
return;
end if;
-------================================
--循环向临时表插入数据
while hasLongRecord loop
begin
--向临时表插入2000条数据,并且要保证id小的排在前面
insert into temp_barcode_change
select *
from (select * from t_barcode_change_comm order by id)
where rownum <= v_tempCount;

--如果变更表没数据，就退出
if (SQL%ROWCOUNT < v_tempCount) then
hasLongRecord := false;
end if;
--循环驱动表，批量执行sql语句
for d in (select * from t_barcode_drivetable) loop
--=====插入=============
/* v_insert_sql := 'insert into ' || d.targettable ||
' (select * from ' || d.srcview ||
' where id in (select changeid from view_temp_barcode_change where srctable=''' ||
d.srctable || ''' and changetype=''insert''))';
*/
--=====修改=============
v_merge_sql := 'merge into ' || d.targettable ||
' t using (select * from ' || d.srcview ||
' where id in (select changeid from view_temp_barcode_change where srctable=''' ||
d.srctable ||
''' and (changetype = ''insert'' or changetype=''update''))) v on (t.id=v.id) when matched then ' ||
d.updatestring || ' when not matched then ' ||
d.insertstring;

--=====删除=============
v_delete_sql := 'delete ' || d.targettable ||
' t where id in (select changeid from view_temp_barcode_change where srctable=''' ||
d.srctable || ''' and changetype=''delete'') ';
--dbms_output.put_line(v_insert_sql);
--execute immediate v_insert_sql;
dbms_output.put_line(v_merge_sql);
execute immediate v_merge_sql;
dbms_output.put_line(v_delete_sql);
execute immediate v_delete_sql;
end loop;
--==--======删除变更表的记录====
--记录日志Log
insert into t_barcode_change_log
(id, changeid, srctable, changetype, createtime)
select seq_change_log.nextval,
changeid,
srctable,
changetype,
sysdate
from temp_barcode_change;
--删除变更表记录
delete from t_barcode_change_comm
where id in (select id from temp_barcode_change);
--提交，一定要写在此，否则临时表的数据没啦
commit;
exception
when others then

hasLongRecord := false;
--给变量赋值
v_error_code := sqlcode;
v_error_msg := sqlerrm;

rollback;
--插入日志t_barcode_exception_log
insert into t_barcode_exception_log
(id, errorsrc, errorcode, errormsg, createtime)
values
(seq_exception_log.nextval,
'bacode.syncCommData',
v_error_code,
v_error_msg,
sysdate);
commit;
end;
end loop;
--======设置当前存储过程状态为idle========
changeJobState('syncCommData', 'idle');
--========================================
end syncCommData;