存储过程—本功能实现号段提取号头，例如：638000~到640999 提取638 639 640作为此号段的号头——

create or replace procedure F_GET_CODE_END(P_ERRCODE OUT NUMBER,
P_ERRMSG out varchar2) as
– return varchar2 is
– Result varchar2(32);
– v_office_code varchar2(32);
cursor cur is
select * from bps_special_test;
v_num number(8);
a1 number(8);
a2 number(8);
i number(8);
flag number(8);
a3 number(8);
a4 number(8);
code_number number(8);
v_start_number bps_special_test.start_number%type;
v_end_number bps_special_test.end_number%type;
v_number_type bps_special_test.number_type%type;
v_area_code bps_special_test.area_code%type;
v_bureau_code bps_special_test.bureau_code%type;
v_operator_id bps_special_test.operator_id%type;
v_valid_date bps_special_test.valid_date%type;
v_expire_date bps_special_test.expire_date%type;
v_notes bps_special_test.notes%type;
v_ord bps_special_test.ord%type;
begin
v_num := 1;
–打开游标
open cur;
–给第一行喂数据
fetch cur
into v_start_number,
v_end_number,
v_number_type,
v_area_code,
v_bureau_code,
v_operator_id,
v_valid_date,
v_expire_date,
v_notes,
v_ord;
–测试是否有数据，并执行循环
while cur%found loop
------本功能实现号段提取号头，例如：638000~到640999 提取638 639 640作为此号段的号头
for v_num in 1 … 7 loop
a1 := SUBSTR(v_start_number, 0, v_num + 1);
a2 := SUBSTR(v_end_number, 0, v_num + 1);
a3 := SUBSTR(v_start_number, v_num + 2, 1);
a4 := SUBSTR(v_end_number, v_num + 2, 1);
if a4 - a3 = 9 then
-----提取号头，当开始号码是0，结束号码是9时，取前面位的号段，列出号段之前的所有数，并将此号段的所有信息插入表中
flag := 1;
for i in a1 … a2 loop
code_number := i;
insert into BPS_SPECIAL_NEW
(ord,
code_number,
start_number,
end_number,
number_type,
area_code,
bureau_code,
opearator_id,
valid_date,
expire_date,
notes,
flag)
values
(v_ord,
code_number,
v_start_number,
v_end_number,
v_number_type,
v_area_code,
v_bureau_code,
v_operator_id,
v_valid_date,
v_expire_date,
v_notes,
flag);
commit;
flag := flag + 1; --标记增加的第几个数
end loop;
goto lq; —进入下一个游标循环
end if;
end loop;
–给下一行喂数据
<>
fetch cur
into v_start_number,
v_end_number,
v_number_type,
v_area_code,
v_bureau_code,
v_operator_id,
v_valid_date,
v_expire_date,
v_notes,
v_ord;
end loop;
close cur;
commit;
– return(Result);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_ERRCODE := SQLCODE;
P_ERRMSG := SUBSTR(SQLERRM, 1, 255);
–P_WRITE_ERRLOG(‘P_dzpt_proce_wj’, P_ERRCODE, P_ERRMSG);
COMMIT;
end F_GET_CODE_END;