第一次自己写存储过程去进行设备录入——存做纪念
短信预约 -IT技能 免费直播动态提醒
---录设备---存储过程
create or replace procedure lu_shebei (r_res out varchar2) is
type t_cursor is ref cursor;
v_mycur t_cursor;
v_yonghu_id yong_hu.id%type;
v_id yonghu_luyou_xiangqing.id%type;
v_luyou_id yonghu_luyou.id%type;
v_shebei_id peixian_jia.id%type;
v_shebei_bianma peixian_jia.bian_hao%type;
v_mokuai_leibie yonghu_luyou_xiangqing.mokuai_leibie%type;
v_mokuai_id peixian_mokuai.id%type;
v_mokuai_bianma peixian_mokuai.bian_hao%type;
v_jusuo_id yong_hu.ju_suo%type;
v_xu_hao yonghu_luyou_xiangqing.xu_hao%type;
v_duankou_leibie peixian_duanzi.shebei_dalei%type;
v_lie varchar2(255);
v_kuai varchar2(255);
v_duankou_id peixian_duanzi.id%type;
v_duankou_hao peixian_duanzi.duanzi_hao%type;
v_sql varchar2(255);
v_count number(10);
v_count2 number(10);
v_duiduan_shebei_dalei shebei_duiduan_xinxi.duiduan_shebei_dalei%type;
v_lu_shebei_i lu_shebei_i%rowtype;
begin
v_sql := 'select * from lu_shebei_i';
open v_mycur for v_sql;
fetch v_mycur into v_lu_shebei_i;
while v_mycur%found loop
select count(*) into v_count from yong_hu y where y.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
if v_count != 0 then
---删用户路由详情
delete from yonghu_luyou_xiangqing x
where x.luyou_id in
(select id
from yonghu_luyou l
where l.yonghu_id in
(select id
from yong_hu y
where y.fuwu_haoma =
'' || v_lu_shebei_i.fuwu_haoma || ''))
and x.mokuai_leibie =''||v_lu_shebei_i.MOKUAI_LEIBIE||'';
commit;
--v_yonghu_id
select y.id into v_yonghu_id from yong_hu y where y.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
--v_jusuo_id
select y.ju_suo into v_jusuo_id from yong_hu y where y.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
--v_luyou_id
select l.id into v_luyou_id from yonghu_luyou l where l.yonghu_id=''||v_yonghu_id||'';
--v_xu_hao
select '0'||to_char(count(xu_hao)+1)||'.1' into v_xu_hao from yonghu_luyou_xiangqing x where x.luyou_id =''||v_luyou_id||'';
--v_shebei_id
select p.id into v_shebei_id from peixian_jia p where p.bian_hao=''||v_lu_shebei_i.SHEBEI_BIANMA||'';
--v_shebei_bianma
select p.bian_hao into v_shebei_bianma from peixian_jia p where p.bian_hao=''||v_lu_shebei_i.SHEBEI_BIANMA||'';
--v_mokuai_id
select m.id into v_mokuai_id from peixian_mokuai m where m.peixian_jia_id=''||v_shebei_id||'' and m.lie=''||v_lu_shebei_i.LIE||'' and m.kuai = ''||v_lu_shebei_i.KUAI||'' and m.shebei_dalei=''||v_lu_shebei_i.MOKUAI_LEIBIE||'';
--v_mokuai_bianma
select m.bian_hao into v_mokuai_bianma from peixian_mokuai m where m.peixian_jia_id=''||v_shebei_id||'' and m.lie=''||v_lu_shebei_i.LIE||'' and m.kuai = ''||v_lu_shebei_i.KUAI||'' and m.shebei_dalei=''||v_lu_shebei_i.MOKUAI_LEIBIE||'';
--v_duankou_leibie
select z.shebei_dalei into v_duankou_leibie from peixian_duanzi z where z.mokuai_id=''||v_mokuai_id||'' and z.duanzi_hao=''||v_lu_shebei_i.DUANZI||'';
--v_duankou_id
select z.id into v_duankou_id from peixian_duanzi z where z.mokuai_id=''||v_mokuai_id||'' and z.duanzi_hao=''||v_lu_shebei_i.DUANZI||'';
select count(*) into v_count2 from yonghu_luyou_xiangqing x where x.mokuai_id =''||v_mokuai_id||'' and x.duankou_hao=''||v_lu_shebei_i.DUANZI||'';
if v_count2 != 0 then
----判断位置是否被占用
insert into not_insert (
FUWU_HAOMA,
SHEBEI_BIANMA,
LIE,
KUAI,
DUANZI,
zhuangtai)
select l.zhuanxian_hao,x.shebei_bianma,x.lie,x.kuai,x.duankou_hao,'已占用'
from yonghu_luyou_xiangqing x, yonghu_luyou l
where x.luyou_id = l.id
and x.mokuai_id = '' || v_mokuai_id || ''
and x.duankou_hao = '' || v_lu_shebei_i.DUANZI || '';
commit;
else
---插入路由详情
insert into yonghu_luyou_xiangqing s
(ID,
LUYOU_ID,
XU_HAO,
WEIZHI_LEIXING,
WEIZHI_ID,
JUSUO_ID,
JIFANG_ID,
WANG_LUO,
SHEBEI_LEIBIE,
SHEBEI_ID,
SHEBEI_BIANMA,
JI_LIE,
JI_JIA,
JI_KUANG,
MOKUAI_LEIBIE,
MOKUAI_ID,
MOKUAI_BIANMA,
LIE,
KUAI,
DUANKOU_LEIBIE,
DUANKOU_ID,
DUANKOU_HAO,
LUOJI_ZIYUAN,
LIANJIE_LEIXING,
LUYOU_MIAOSHU,
GONGDAN_HAO,
CAOZUO_BUMEN,
BANBEN_HAO,
CAOZUO_YUAN,
CAOZUO_SHIJIAN,
CHUANGJIANREN,
CHUANGJIAN_SHIJIAN)
values
(seq_diaodu_luyou_xiangqing.nextval,
v_luyou_id,
v_xu_hao,
'201',
'',
v_jusuo_id,
'',
'',
v_lu_shebei_i.SHEBEI_LEIBIE,
v_shebei_id,
v_shebei_bianma,
'',
'',
'',
v_lu_shebei_i.MOKUAI_LEIBIE,
v_mokuai_id,
v_mokuai_bianma,
v_lu_shebei_i.LIE,
v_lu_shebei_i.KUAI,
v_duankou_leibie,
v_duankou_id,
v_lu_shebei_i.DUANZI,
'',
'0',
'' || v_shebei_id || '.' || v_duankou_leibie || '-3121566.44;-1;0;1',
'',
'',
'1',
'zmr',
sysdate,
'6471011',
sysdate);
commit;
end if;
else
insert into insert_log
values (
v_lu_shebei_i.fuwu_haoma,
v_lu_shebei_i.shebei_bianma,
v_lu_shebei_i.lie,
v_lu_shebei_i.kuai,
v_lu_shebei_i.duanzi);
commit;
r_res:= v_lu_shebei_i.fuwu_haoma||'用户不存在';
end if;
---查对端模块类别
select s.duiduan_mokuai_leibie into v_mokuai_leibie
from shebei_duiduan_xinxi s
where s.shebei_id =''||v_shebei_id||''
and s.mokuai_leibie =''||v_lu_shebei_i.MOKUAI_LEIBIE||''
and s.lie = ''||v_lu_shebei_i.lie||''
and s.kuai =''||v_lu_shebei_i.kuai||''
and s.qishi_duanzi =''||v_lu_shebei_i.duanzi||'';
---删对端路由详情
delete from yonghu_luyou_xiangqing x
where x.luyou_id in
(select id
from yonghu_luyou l
where l.yonghu_id in
(select id
from yong_hu y
where y.fuwu_haoma =
'' || v_lu_shebei_i.fuwu_haoma || ''))
and x.mokuai_leibie =''||v_mokuai_leibie||'';
commit;
----录对端
select s.duiduan_shebei_dalei into v_duiduan_shebei_dalei
from shebei_duiduan_xinxi s
where s.shebei_id =''||v_shebei_id||''
and s.mokuai_leibie =''||v_lu_shebei_i.MOKUAI_LEIBIE||''
and s.lie = ''||v_lu_shebei_i.lie||''
and s.kuai =''||v_lu_shebei_i.kuai||''
and s.qishi_duanzi =''||v_lu_shebei_i.duanzi||'';
if v_duiduan_shebei_dalei != 20 then
insert into yonghu_luyou_xiangqing s (
ID ,
LUYOU_ID ,
XU_HAO ,
WEIZHI_LEIXING ,
WEIZHI_ID ,
JUSUO_ID ,
JIFANG_ID ,
WANG_LUO ,
SHEBEI_LEIBIE ,
SHEBEI_ID ,
SHEBEI_BIANMA ,
JI_LIE ,
JI_JIA ,
JI_KUANG ,
MOKUAI_LEIBIE ,
MOKUAI_ID ,
MOKUAI_BIANMA ,
LIE ,
KUAI ,
DUANKOU_LEIBIE ,
DUANKOU_ID ,
DUANKOU_HAO ,
LUOJI_ZIYUAN ,
LIANJIE_LEIXING ,
LUYOU_MIAOSHU ,
GONGDAN_HAO ,
CAOZUO_BUMEN ,
BANBEN_HAO ,
CAOZUO_YUAN ,
CAOZUO_SHIJIAN ,
CHUANGJIANREN ,
CHUANGJIAN_SHIJIAN
)
select
seq_diaodu_luyou_xiangqing.nextval,
v_luyou_id,
v_xu_hao,
'201',
'',
s.DUIDUAN_JUSUO,
'',
'',
s.duiduan_shebei_dalei,
s.duiduan_shebei_id,
j.bian_hao,----
'',
'',
'',
s.duiduan_mokuai_leibie,
s.DUIDUAN_MOKUAI_ID,
p.bian_hao,----
s.duiduan_lie,
s.duiduan_kuai,
s.DUIDUAN_DUANZI_LEIBIE,
s.DUIDUAN_DUANZI_ID,
s.duiduan_qishi_duanzi,
'',
'0',
''||v_shebei_id||'.'||v_duankou_leibie||'-3121566.44;-1;0;1',
'',
'',
'1',
'zmr',
sysdate,
'6471011',
sysdate
from shebei_duiduan_xinxi s
left join peixian_mokuai p on s.duiduan_mokuai_id =p.id
left join peixian_jia j on s.duiduan_shebei_id=j.id
where s.shebei_id =''||v_shebei_id||''
and s.mokuai_leibie =''||v_lu_shebei_i.MOKUAI_LEIBIE||''
and s.lie = ''||v_lu_shebei_i.lie||''
and s.kuai =''||v_lu_shebei_i.kuai||''
and s.qishi_duanzi =''||v_lu_shebei_i.duanzi||'';
commit;
else
insert into yonghu_luyou_xiangqing s (
ID ,
LUYOU_ID ,
XU_HAO ,
WEIZHI_LEIXING ,
WEIZHI_ID ,
JUSUO_ID ,
JIFANG_ID ,
WANG_LUO ,
SHEBEI_LEIBIE ,
SHEBEI_ID ,
SHEBEI_BIANMA ,
JI_LIE ,
JI_JIA ,
JI_KUANG ,
MOKUAI_LEIBIE ,
MOKUAI_ID ,
MOKUAI_BIANMA ,
LIE ,
KUAI ,
DUANKOU_LEIBIE ,
DUANKOU_ID ,
DUANKOU_HAO ,
LUOJI_ZIYUAN ,
LIANJIE_LEIXING ,
LUYOU_MIAOSHU ,
GONGDAN_HAO ,
CAOZUO_BUMEN ,
BANBEN_HAO ,
CAOZUO_YUAN ,
CAOZUO_SHIJIAN ,
CHUANGJIANREN ,
CHUANGJIAN_SHIJIAN
)
select
seq_diaodu_luyou_xiangqing.nextval,
v_luyou_id,
v_xu_hao,
'201',
'',
s.DUIDUAN_JUSUO,
'',
'',
s.duiduan_shebei_dalei,
s.duiduan_shebei_id,
j.bian_hao,----
'',
'',
'',
s.duiduan_mokuai_leibie,
s.DUIDUAN_MOKUAI_ID,
p.bian_hao,----
s.duiduan_lie,
s.duiduan_kuai,
s.DUIDUAN_DUANZI_LEIBIE,
s.DUIDUAN_DUANZI_ID,
s.duiduan_qishi_duanzi,
'',
'0',
''||v_shebei_id||'.'||v_duankou_leibie||'-3121566.44;-1;0;1',
'',
'',
'1',
'zmr',
sysdate,
'6471011',
sysdate
from shebei_duiduan_xinxi s
left join fenxian_he_duanzi p on s.DUIDUAN_DUANZI_ID =p.id
left join fenxian_he j on s.duiduan_shebei_id=j.id
where s.shebei_id =''||v_shebei_id||''
and s.mokuai_leibie =''||v_lu_shebei_i.MOKUAI_LEIBIE||''
and s.lie = ''||v_lu_shebei_i.lie||''
and s.kuai =''||v_lu_shebei_i.kuai||''
and s.qishi_duanzi =''||v_lu_shebei_i.duanzi||'';
commit;
end if;
----删除中间表
delete from lu_shebei_i where fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
fetch v_mycur into v_lu_shebei_i;
end loop;
close v_mycur;
commit;
end lu_shebei;----未录入设备的 log 日志
select * from insert_log for update; --无卡片
select * from not_insert for update; --新录入地址已占用的服务号码--录设备中间表
select * from lu_shebei_i for update;---执行存储
declare
v_res varchar2(254);
begin
lu_shebei(v_res);
end;免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/15.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
