-----录设备-存储过程
createorreplaceprocedurelu_shebei(r_resoutvarchar2)is
typet_cursorisrefcursor;
v_mycurt_cursor;
v_yonghu_idyong_hu.id%type;
v_idyonghu_luyou_xiangqing.id%type;
v_luyou_idyonghu_luyou.id%type;
v_shebei_idpeixian_jia.id%type;
v_shebei_bianmapeixian_jia.bian_hao%type;
v_mokuai_idpeixian_mokuai.id%type;
v_mokuai_bianmapeixian_mokuai.bian_hao%type;
v_jusuo_idyong_hu.ju_suo%type;
v_xu_haoyonghu_luyou_xiangqing.xu_hao%type;
v_duankou_leibiepeixian_duanzi.shebei_dalei%type;
v_lievarchar2(255);
v_kuaivarchar2(255);
v_duankou_idpeixian_duanzi.id%type;
v_duankou_haopeixian_duanzi.duanzi_hao%type;
v_sqlvarchar2(255);
v_countnumber(10);
v_lu_shebei_ilu_shebei_i%rowtype;
begin
v_sql:='select*fromlu_shebei_i';
openv_mycurforv_sql;
fetchv_mycurintov_lu_shebei_i;
whilev_mycur%foundloop
selectcount(*)intov_countfromyong_huywherey.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
ifv_count!=0then
selecty.idintov_yonghu_idfromyong_huywherey.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
selecty.ju_suointov_jusuo_idfromyong_huywherey.fuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
selectl.idintov_luyou_idfromyonghu_luyoulwherel.yonghu_id=''||v_yonghu_id||'';
select'0'||to_char(count(xu_hao)+1)||'.1'intov_xu_haofromyonghu_luyou_xiangqingxwherex.luyou_id=''||v_luyou_id||'';
selectp.idintov_shebei_idfrompeixian_jiapwherep.bian_hao=''||v_lu_shebei_i.SHEBEI_BIANMA||'';
selectp.bian_haointov_shebei_bianmafrompeixian_jiapwherep.bian_hao=''||v_lu_shebei_i.SHEBEI_BIANMA||'';
selectm.idintov_mokuai_idfrompeixian_mokuaimwherem.peixian_jia_id=''||v_shebei_id||''andm.lie=''||v_lu_shebei_i.LIE||''andm.kuai=''||v_lu_shebei_i.KUAI||''andm.shebei_dalei=5;
selectm.bian_haointov_mokuai_bianmafrompeixian_mokuaimwherem.peixian_jia_id=''||v_shebei_id||''andm.lie=''||v_lu_shebei_i.LIE||''andm.kuai=''||v_lu_shebei_i.KUAI||''andm.shebei_dalei=5;
selectz.shebei_daleiintov_duankou_leibiefrompeixian_duanzizwherez.mokuai_id=''||v_mokuai_id||''andz.duanzi_hao=''||v_lu_shebei_i.DUANZI||'';
selectz.idintov_duankou_idfrompeixian_duanzizwherez.mokuai_id=''||v_mokuai_id||''andz.duanzi_hao=''||v_lu_shebei_i.DUANZI||'';

insertintoyonghu_luyou_xiangqing
values(
seq_diaodu_luyou_xiangqing.nextval,v_luyou_id,v_xu_hao,'201','',v_jusuo_id,'0',v_shebei_id,v_shebei_bianma,'5',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,''||v_shebei_id||'.'||v_duankou_leibie||'-3121566.44;-1;0;1','1','zmr',sysdate,'6471011',sysdate
);
commit;
else
insertintoinsert_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||'用户不存在';
endif;

deletefromlu_shebei_iwherefuwu_haoma=''||v_lu_shebei_i.fuwu_haoma||'';
fetchv_mycurintov_lu_shebei_i;
endloop;
closev_mycur;
commit;

endlu_shebei;

----未录入设备的log日志
select*frominsert_log;

--录设备中间表
select*fromlu_shebei_iforupdate;

---执行存储
declare
v_resvarchar2(254);
begin
lu_shebei(v_res);
end;