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
