create or replace procedure pro_insert_code(start_code in varchar2, end_code in varchar2) is v_dwmc varchar2(50) := '云南'; v_dwdm varchar2(50) := '35'; v_length number; v_start number; v_end number; begin select length(start_code) into v_length from dual; v_start := to_number(start_code); v_end := to_number(end_code); for v_start in to_number(start_code) .. v_end loop insert into a (dwdm, dwmc, fph) values (v_dwdm, v_dwmc, lpad(v_start, v_length, '0')); end loop; end; 在命令窗口调用: exec pro_insert_code('00000001','00020000'); 查询插入结果: select * from a order by fph; 查询结果:
改成这样效果一样吗? create or replace procedure pro_insert_code(start_code in varchar2, end_code in varchar2) is v_dwmc varchar2(50) := '云南'; v_dwdm varchar2(50) := '35'; v_length number; v_start number; v_end number; begin select length(start_code) into v_length from dual; for v_start in to_number(start_code) .. to_number(end_code) loop insert into a (dwdm, dwmc, fph) values (v_dwdm, v_dwmc, lpad(v_start, v_length, '0')); end loop; end;
end_code in varchar2) is
v_dwmc varchar2(50) := '云南';
v_dwdm varchar2(50) := '35';
v_length number;
v_start number;
v_end number;
begin
select length(start_code) into v_length from dual;
v_start := to_number(start_code);
v_end := to_number(end_code);
for v_start in to_number(start_code) .. v_end loop
insert into a
(dwdm, dwmc, fph)
values
(v_dwdm, v_dwmc, lpad(v_start, v_length, '0'));
end loop;
end;
在命令窗口调用:
exec pro_insert_code('00000001','00020000');
查询插入结果:
select * from a order by fph;
查询结果:
改成这样效果一样吗?
create or replace procedure pro_insert_code(start_code in varchar2,
end_code in varchar2) is
v_dwmc varchar2(50) := '云南';
v_dwdm varchar2(50) := '35';
v_length number;
v_start number;
v_end number;
begin
select length(start_code) into v_length from dual;
for v_start in to_number(start_code) .. to_number(end_code) loop
insert into a
(dwdm, dwmc, fph)
values
(v_dwdm, v_dwmc, lpad(v_start, v_length, '0'));
end loop;
end;