create or replace procedure p_get_haoduan(v_start_number in number, --传入起始号
v_end_number in number, --传入终止号
v_CITY_CODE in varchar2, --传入城市代号
emsg out varchar2) --接收返回结果
as
/*
操作流程:
根据起始号,终止号在 SK_DEVICE_SHIPMENTS_NUMBER 查找是否有记录
1、如果没有,不管了
2、如果存在,与在SK_DEVICE_SHIPMENTS关联 判断省份(CITY_CODE)是否不正确 且准入状态(SHIPMENTS_STATUS = 3)
2.1 如果不是,进行提示,不能删除
2.2 如果是 ,在数据库已有号段中删除起号-止号间的记录。
*/
v_qsh number; --接收起始号码
v_zzh number; --接收终止号码
v_citycode varchar2(330); --接收城市代号
v_status varchar2(330); --接收操作状态
v_uuid varchar2(32); --接收UUID
v_DQ_GUID varchar2(32);
cursor cur(v_start_number number, v_end_number number) is
select b.start_number,
b.end_number,
a.city_code,
a.shipments_status,
b.uuid
from SK_DEVICE_SHIPMENTS_NUMBER b, SK_DEVICE_SHIPMENTS a
where to_number(end_number) >= v_start_number
and to_number(start_number) <= v_end_number
and a.uuid = b.fk_uuid;
begin
emsg := ' ';
open cur(v_start_number, v_end_number);
loop
fetch cur
into v_qsh, v_zzh, v_citycode, v_status, v_uuid;
exit when cur%notfound;
if length(v_start_number)<>13 or length(v_end_number)<>13 or length(v_CITY_CODE)<>6 then
emsg :='输入参数有误';
goto endtras;
end if;
if (v_citycode != v_CITY_CODE and v_status = 3) then
--起始号小于等于数据库起始号,终止号大于等于数据库终止号
if v_start_number <= v_qsh and v_end_number >= v_zzh then
delete SK_DEVICE_SHIPMENTS_NUMBER r where r.uuid = v_uuid;
emsg := emsg || ' delete:' || v_qsh || ' -- ' || v_zzh;
--起始号小于等于数据库起始号,终止号码小于等于数据库终止号码
elsif v_start_number <= v_qsh and v_end_number <= v_zzh then
update SK_DEVICE_SHIPMENTS_NUMBER r
set r.start_number = to_char(v_end_number + 1),
r.device_number = to_number(r.end_number) -
(v_end_number + 1)+1
where r.uuid = v_uuid;
emsg := emsg || ' update:' || to_char(v_end_number + 1) || ' -- ' || v_zzh;
--起始号大于等于数据库起始号,终止号大于等于数据库终止号
elsif v_start_number >= v_qsh and v_end_number >= v_zzh then
update SK_DEVICE_SHIPMENTS_NUMBER r
set r.end_number = to_char(v_start_number - 1),
r.device_number = to_number(v_start_number-1 ) -
to_number(r.start_number)+1
where r.uuid = v_uuid;
emsg := emsg || ' update:' || v_qsh || ' -- ' || to_char(v_start_number - 1);
--起始号大于等于数据库起始号,终止号小于等于数据库终止号
elsif v_start_number >= v_qsh and v_end_number <= v_zzh then
v_DQ_GUID := SYS_GUID();
update SK_DEVICE_SHIPMENTS_NUMBER r
set r.end_number = to_char(v_start_number - 1),
r.device_number = to_number(v_start_number - 1) -
to_number(r.start_number)+1
where r.uuid = v_uuid;
emsg := emsg || ' update:' || v_qsh || ' -- ' || to_char(v_start_number - 1);
insert into SK_DEVICE_SHIPMENTS_NUMBER
(uuid,
fk_uuid,
model_number,
version_number,
device_number,
start_number,
end_number)
select v_DQ_GUID uuid,
fk_uuid,
r.model_number,
r.version_number,
to_number(v_zzh) - (v_end_number + 1)+1,
to_char(v_end_number + 1),
v_zzh
from SK_DEVICE_SHIPMENTS_NUMBER r
where r.uuid = v_uuid;
emsg := emsg || ' insert:' || to_char(v_end_number + 1) || ' -- ' || v_zzh;
end if;
else
emsg := emsg || ' 不能删除,请确认:' || v_qsh || ' -- ' || v_zzh;
--记录下来,提示用
end if;
end loop;
close cur;
<<endtras>>
null;
exception
when no_data_found then
emsg := '失败';
when others then
emsg := '失败';
end p_get_haoduan;
/
转载于:https://www.cnblogs.com/iyoume2008/p/10067317.html