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

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

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;