CREATE OR REPLACE PROCEDURE protest(vacdt in varchar) AS
cursor cur_inst is
select LOG_NO, AC_DT from table where AC_DT = vacdt;
record cur_inst%rowtype;
i_count int;
i_allcount int;
BEGIN
dbms_output.put_line('开始更新xxxx数据日期:' || vacdt || '-数据');
i_count := 0;
i_allcount := 0;
open cur_inst;
loop
fetch cur_inst
into record;
if cur_inst%notfound then
--dbms_output.put_line('剩余条数:' ||i_count);
commit;
exit;
end if;
update table t
set t.ac_dt_month = substr(t.ac_dt, 0, 6)
where t.LOG_NO = record.LOG_NO
and t.AC_DT = record.AC_DT;
i_count := i_count + 1;
i_allcount := i_allcount + 1;
if i_count >= 1000 then
dbms_output.put_line('提交更新xxxx数据日期:' || vacdt || '-数据');
i_count := 0;
commit;
end if;
end loop;
close cur_inst;
dbms_output.put_line('结束更新xxxxx数据日期:' || vacdt || '-数据,数量:' || i_allcount);
END;
调用
--执行存储过程方法1::call
call protest('1111111');
--执行存储过程方法2:
begin
protest('111111');
end;
关于存储过程的基础知识的学习,可以参考下面
https://blog.csdn.net/weixin_41968788/article/details/83659164