第一次自己写存储过程去进行设备录入——存做纪念

---录设备---存储过程

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;