oracle存储过程

create or replace procedure sp_aspt_mh_ptjsqk /*平台建设情况 --20131217日志数暂时没有接到通知*/ as v_yys number; v_yycss number; v_css number; v_jrl number(10, 8); v_bz char(1); begin select count(distinct(app_name)) into v_yys from pm.t_operation_log_statistics t where t.region_id = '030000'; -- 提交公安部 update t_stat_show_ptjsqk t set t.stat_yysm = v_yys where t.model_id = '030000'; commit; for l in (select t.app_num, t.region_id from pm.t_aspt_monitor_status t where t.im_status = 'Y') loop --更新t_stat_show_ptjsqk在建的应用总数 update t_stat_show_ptjsqk t set t.stat_yysm = l.app_num where t.model_id = l.region_id; commit; if l.region_id not like '__0000' and l.region_id like '____00' then --更新t_stat_show_mapdata在建的应用数 update t_stat_show_mapdata t set t.access_state = '1', t.access_num = l.app_num where t.model_id = l.region_id; commit; end if; end loop; --结束完成后，进入下一个 for l in (select distinct (substrb(t.model_id, 1, 2)) v_qlw from t_stat_show_ptjsqk t where t.model_id <> '030000') loop select count(1) into v_yycss from t_stat_show_ptjsqk t where t.stat_yysm > 0 and t.model_id like l.v_qlw || '%'; select count(1) into v_css from t_stat_show_ptjsqk t where t.model_id like l.v_qlw || '%'; v_jrl := v_yycss / v_css; if v_jrl >= 0.8 then v_bz := '2'; elsif v_jrl < 0.8 and v_jrl >= 0.5 then v_bz := '3'; else v_bz := '1'; end if; update t_stat_show_ptjsqk t set t.is_build = v_bz where t.model_id = l.v_qlw||'0000'; commit; end loop; end;