第一次自己写存储过程去进行设备录入——存做纪念
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,---录设备---存储过程create or replace procedure lu_shebei (r_res out varchar2) istype t_cursor is ref curso
千家信息网最后更新 2024年09月22日第一次自己写存储过程去进行设备录入——存做纪念
---录设备---存储过程
create or replace procedure lu_shebei (r_res out varchar2) istype 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 theninsert 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;
设备
详情
路由
存储
用户
过程
位置
卡片
号码
地址
日志
模块
类别
服务
第一次
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
方舟服务器管理员踢人
数据库触发器如何调测
上海畅想网络技术有限公司
访问服务器端文件
网络安全安全测评
广播室打印服务器一直显示未运行
网络安全采取应对措施
成都网络安全服务公司
龙源网与龙源数据库
网络安全防火墙筑起
出版行业网络安全
做原油什么软件开发
电力网络技术开发前沿
公网ip建设服务器有危险吗
崇明区海航软件开发代理商
网络安全技能大赛网上报名
怀旧服服务器内部配置
网络计算机软件开发
数据库外键的表达形式
服务器内存配置怎么看
网络安全 网评文章
太原市恒速网络技术有限公司
怎样查找数据重复的数据库
端游天刀大区服务器地址
数据库机被淘汰
猪八戒网软件开发靠谱吗
开发人员管理服务器
版权局 计算机软件开发能力
南京安卓软件开发教程
数据库如何设置代码数