存储过程示例4

goOut:BEGIN
 #Routine body goes here...
 -- AUTHORS ：zm
 -- 时间： 2013年8月23日10:20:04
 -- 根据传入的城市、频道、节目， 计算该频道的节目在该城市的收视率
 -- a_oiRate 返回计算的收视率
 -- a_iSdate 传入的计算收视率的开始如期
 -- a_iEdate  传入的计算收视率的结束日期
 -- a_iChannleid 传入的计算收视率的频道
 -- a_iProgramid 传入的计算收视率的节目
 -- a_iCityId 传入的计算收视率的城市

 declare broadcastTime int default 0;
 declare viewTime BIGINT default 0;
 declare AllviewTime BIGINT default 0;
 
 declare sumBTime int default 0;
 declare SumVTime BIGINT default 0;
 declare SumAVTime BIGINT default 0;

 declare SumActive BIGINT default 0;

 DECLARE done INT DEFAULT 0; 

 DECLARE cur1 CURSOR FOR
 select epg_btime, epg_vtime, epg_allvtime
 from TOP_TVRate_Mid  where cityid = a_iCityId and channelid = a_iChannleid and programid = a_iProgramid and epg_date between a_iSdate and a_iEdate;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

 set a_oiRet = '';
 set a_oiRatings = '';
 set a_oiMartShr = '';
 
 open cur1;
 REPEAT
   FETCH cur1 into broadcastTime, viewTime, AllviewTime;
   if not done THEN
      if broadcastTime > 0  and viewTime > 0 and AllviewTime >= 0 then
       set sumBTime = sumBTime + broadcastTime;
       set SumVTime = SumVTime + viewTime;
       set SumAVTime = SumAVTime + AllviewTime;
      end if;
   end if;
 UNTIL done END REPEAT;
 close cur1;
 
 if not a_iCityId then
   select count(t1.mac) into SumActive from
   (SELECT DISTINCT a.mac from statistics.t_devChannelInfo a, haierdb.udev_basic b where a.start < a.end and a.mac=b.mac and DATE_FORMAT(a.start, '%Y-%m-%d')
   BETWEEN DATE_FORMAT(a_iSdate, '%Y-%m-%d') and DATE_FORMAT(a_iEdate, '%Y-%m-%d') union
   SELECT DISTINCT a.mac from statistics.t_devChannelInfo a, haierdb.udev_basic b where a.start < a.end and a.mac=b.mac and DATE_FORMAT(a.end, '%Y-%m-%d')
   BETWEEN DATE_FORMAT(a_iSdate, '%Y-%m-%d') and DATE_FORMAT(a_iEdate, '%Y-%m-%d')) as t1;
 ELSE
   select count(t1.mac) into SumActive from
   (SELECT DISTINCT a.mac from statistics.t_devChannelInfo a, haierdb.udev_basic b where a.start < a.end and a.mac=b.mac and b.citycode = a_iCityId AND
   DATE_FORMAT(a.start, '%Y-%m-%d') BETWEEN DATE_FORMAT(a_iSdate, '%Y-%m-%d') and DATE_FORMAT(a_iEdate, '%Y-%m-%d') union
   SELECT DISTINCT a.mac from statistics.t_devChannelInfo a, haierdb.udev_basic b where a.start < a.end and a.mac=b.mac and b.citycode = a_iCityId and
   DATE_FORMAT(a.end, '%Y-%m-%d') BETWEEN DATE_FORMAT(a_iSdate, '%Y-%m-%d') and DATE_FORMAT(a_iEdate, '%Y-%m-%d')) as t1;
 end if;

  IF NOT SumActive THEN
   SET a_oiRet = 1001;
   LEAVE goOut;
  end if;

  select SumVTime/(SumVTime*SumActive) into a_oiRatings;
  select SumVTime/SumAVTime into a_oiMartShr;
  
  -- select '111111111111', a_oiRatings, a_oiMartShr;
  set a_oiRet = 1000;
  LEAVE goOut;
END

 

参数:OUT a_oiRet int, OUT a_oiRatings varchar(12), OUT a_oiMartShr varchar(12),  IN a_iSdate  date,  IN a_iEdate  date,  IN a_iChannleid int,  IN a_iProgramid int,  IN a_iCityId int