第一次自己写存储过程去进行设备录入――存做纪念
发布时间:2020-12-12 14:33:49 所属栏目:百科 来源:网络整理
导读:-----录设备-存储过程createorreplaceprocedurelu_shebei(r_resoutvarchar2)istypet_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_
-----录设备-存储过程 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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |