代码实例
declare
v_info_type_code varchar(12);
v_info_number varchar(8);
n_info_type_code NUMBER(12);
n_info_number NUMBER(8);
ser_num number;
deal_num number;
v_num number;
begin
ser_num := 1;
--查询需要更新数据的条数
select count(*) into v_num from POINTS_SPACE_INVOICE_HEAD t where t.info_print_flag is not null;
--获取到需要更新的数据,并进行遍历
for cur in (select t.id ,t.info_type_code,t.info_number from POINTS_SPACE_INVOICE_HEAD t where t.info_print_flag is not null)
loop
v_info_type_code := cur.info_type_code;
v_info_number := cur.info_number;
if ser_num < v_num then
--将发票代码与发票号码做递增处理
n_info_type_code := to_number(v_info_type_code)+ser_num;
n_info_number := to_number(v_info_number)+ser_num;
--长度进行限制,不足左边补0
v_info_type_code := lpad(n_info_type_code,12,'0');
v_info_number := lpad(n_info_number,8,'0');
--执行更新处理
update points_space_invoice_head h set h.info_type_code = v_info_type_code,h.info_number = v_info_number where h.id = cur.id;
deal_num := sql%rowcount;
commit;
--更新成功,变量加1
if deal_num = 1 then
ser_num := ser_num + 1;
end if;
end if;
end loop;
end;
