oracle 存储过程

此存储过程实现功能：

监控服务统计表，并自动累加总成功、失败和请求次数，以及计算全局健康度。  

 

create or replace procedure "FXKH_SERVICEMONITOR" (path in varchar2,newstatus in number, newsuc_count in number, newerr_count in number, newmsg_count in number) is
 sid number; --服务管理表中不存在的服务id
 sid2 number; --服务id
 allsuc_count number; --所有成功次数
 allmsg_count number; --所有请求次数
 all_health number; --全局健康度
 all_rate number;--全局正确率

cursor notin_cur is --声明服务状态表中不存在于服务管理表的服务的游标
  select serviceid from P_WS_SERVICE_STATUS where serviceid not in (select serviceid from P_WS_SERVICEMANAGEMENT);

cursor sid_cur is --声明服务管理表中匹配到服务名的游标
  select o.serviceid from P_WS_SERVICEMANAGEMENT o where (o.publishpath like path) and rownum<=1;

begin -- main
--循环开始
  for n in notin_cur loop
    sid := n.serviceid;
    --将不存在于服务管理表的服务的status置为健康度最低级:0
    update P_WS_SERVICE_STATUS set status=0 where serviceid = sid;
  end loop;
--循环结束


--循环开始
 for s in sid_cur loop
    sid2 := s.serviceid;
--通过服务管理表中匹配到服务名的服务id在服务状态表中查询所有成功次数和所有请求次数
  select allsuccesscount,allmessagecount into allsuc_count,allmsg_count
    from P_WS_SERVICE_STATUS where serviceid = sid2;

  if allsuc_count=allmsg_count then
    all_rate := 100;
  end if;
  if allsuc_count!=allmsg_count then
    all_rate := (allsuc_count+newsuc_count)/(allmsg_count+newmsg_count)*100;
  end if;
--获取相应全局健康度开始
  if all_rate>=0 and all_rate<=30 then
    all_health := 0;
  end if;
    if all_rate>30 and all_rate<=60 then
    all_health := 1;
  end if;
  if all_rate>60 and all_rate<=70 then
    all_health := 2;
  end if;
  if all_rate>70 and all_rate<=80 then
    all_health := 3;
  end if;
  if all_rate>80 and all_rate<=90 then
    all_health := 4;
  end if;
  if all_rate>90 and all_rate<=100 then
    all_health := 5;
  end if;
--获取相应全局健康度结束
--通过服务id更新服务状态表中的所有成功次数，所有失败次数和所有请求次数
    update P_WS_SERVICE_STATUS set status=newstatus,updatetime=sysdate,allsuccesscount=allsuccesscount+newsuc_count,allerrorcount=allerrorcount+newerr_count,
      allmessagecount=allmessagecount+newmsg_count,globalstatus=all_health where serviceid = sid2;
  end loop;
--循环结束
end;