存储过程示例5

goOUT:BEGIN
 #Routine body goes here...
 -- AUTHORS ：zm
 -- 时间： 2013年8月23日16:32:56
 -- 根据传入的城市、类型计算在该城市的节目、或者频道收视率,
 -- a_oiRate 返回计算的收视率
 -- a_iSdate 传入的计算收视率的开始如期
 -- a_iEdate  传入的计算收视率的结束日期
 -- a_iType 传入类型， 1、节目, 2、频道
 
 declare Cid BIGINT default 0;
 declare Pid BIGINT default 0;
 
 declare strQury varchar(1000) default '';

 DECLARE done INT DEFAULT 0;
 
 DECLARE cur1 CURSOR FOR
 select DISTINCT epg_channleid, epg_programid from TOP_TVRate_Mid where cityid = a_iCityId and epg_date BETWEEN a_iSdate and a_iEdate;
 
 DECLARE cur2 CURSOR FOR
 select DISTINCT epg_channleid from TOP_TVRate_Mid where cityid = a_iCityId and epg_date BETWEEN a_iSdate and a_iEdate;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
 if strTable is null then
   -- 设置表名称
   if a_iType = 1 and a_iCityId = 0 THEN
     set strTable = concat('Top_Program_Ratings_', a_iSdate, '_', a_iEdate);
   ELSEIF a_iType = 1 and a_iCityId != 0 THEN
     set strTable = concat('Top_Program_Ratings_', a_iSdate, '_', a_iEdate, '_', a_iCityId);
   elseif a_iType = 2 and a_iCityId = 0 THEN
     set strTable = concat('Top_Channle_Ratings_', a_iSdate, '_', a_iEdate);
   elseif a_iType = 2 and a_iCityId != 0 then
     set strTable = concat('Top_Channle_Ratings_', a_iSdate, '_', a_iEdate, '_', a_iCityId);
   end if;
  
  -- 创建表

  if a_iType = 1 THEN
   set strQury = CONCAT('create table ', strTable, '(cityid int(10) not null, start date not null, end date not null,
   channelid int(10) not null, programid int(10) not null,Ratings varchar(12) not null, MartShr varchar(12) not null,
   PRIMARY key(cityid, start, end, channelid, programid));');
  elseif a_iType = 2 THEN
   set strQury = CONCAT('create table ', strTable, '(cityid int(10) not null, start date not null, end date not null,
   channelid int(10) not null, Ratings varchar(12) not null, MartShr varchar(12) not null, PRIMARY key(cityid, start, end, channelid));');
  end if;

   PREPARE STMT FROM @strQury;
   EXECUTE STMT;
   DEALLOCATE PREPARE STMT;

 end if;

 set done = 0;

 if a_iType = 1 THEN
  open cur1;
  REPEAT
   FETCH cur1 into Cid, Pid;
    if not done THEN
      set @o_iRet = '';
      set @o_iRate = '';
      set @o_iMartShr = '';
       call getProgrameRatings(@o_iRet, @o_iRate, @o_iMartShr, a_iSdate, a_iEdate, Cid, Pid, a_iCityId);
      if @o_iRet = 1000 then
       insert into strTable(cityid, start, end, channelid, programid, Ratings, MartShr)
       values(a_iCityId, a_iSdate, a_iEdate, Cid, Pid, @o_iRate, @o_iMartShr);
      --  select @o_iRet;
        end if;
    end if;
  UNTIL done END REPEAT;
  close cur1;
 elseif a_iType = 2 THEN
  open cur2;
  REPEAT
   FETCH cur2 into Pid;
    if not done THEN
      set @o_iRet = '';
      set @o_iRate = '';
      set @o_iMartShr = '';
       call getChannelRatings(@o_iRet, @o_iRate, @o_iMartShr, a_iSdate, a_iEdate, Cid, a_iCityId);
      if @o_iRet = 1000 then
       insert into strTable(cityid, start, end, channelid, Ratings, MartShr)
       values(a_iCityId, a_iSdate, a_iEdate, Cid, @o_iRate, @o_iMartShr);
      --  select @o_iRet;
        end if;
    end if;
  UNTIL done END REPEAT;
  close cur2;
 end if;
  
 LEAVE goOut;

END

 

 

参数:OUT a_oiRet int, IN strTable varchar(200), IN a_iSdate  date,  IN a_iEdate  date, IN a_iCityId int, IN a_iType int