---录设备---存储过程
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;
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
