中信房屋：SQL Server在存储过程中使用游标

 
/*=======================================================
创建日期：2011-4-19
对象名称：sp_blog_contentData_GetRecommendList
功能描述：获取目前推荐博文(生活圈、主题等)
参数说明：@logType 对应程序里的Blog.App.Common.LogType枚举
调用示例：EXECUTE sp_blog_contentData_getRecommendList 2
=======================================================*/
CREATE PROCEDURE sp_blog_contentData_getRecommendList2
(
 @logType INT
)
AS
 DECLARE @v_logId1 VARCHAR(50),@v_logId2 VARCHAR(50),@v_logId3 VARCHAR(50);
BEGIN
 SELECT TOP 1 @v_logId1=LogID FROM Blog_ContentData WHERE logType=@logType AND RecommendIndex=1 ORDER BY RecommendData DESC
 SELECT TOP 1 @v_logId2=LogID FROM Blog_ContentData WHERE logType=@logType AND RecommendIndex=2 ORDER BY RecommendData DESC
 SELECT TOP 1 @v_logId3=LogID FROM Blog_ContentData WHERE logType=@logType AND RecommendIndex=3 ORDER BY RecommendData DESC

 SELECT a.LogID, a.logType, a.logTitle, a.AddData,a.RecommendIndex, b.clickCount,c.franchise_name FROM Blog_ContentData a LEFT JOIN
 (
  SELECT ArticleId, SUM(HotHit) clickCount FROM
  (
   SELECT * FROM Blog_Stat_Article_Hot
  ) t1
  GROUP BY ArticleId
 ) b
 ON a.LogID=b.ArticleId
 LEFT JOIN franchise c ON a.FranchiseID=c.franchise_id
 WHERE a.logType=@logType AND a.LogID IN (@v_logId1,@v_logId2,@v_logId3)
 ORDER BY RecommendIndex ASC;
END;
GO

 

 

 

/*=======================================================
创建日期：2011-4-19
对象名称：sp_blog_contentData_setDefaultRecommend
功能描述：设置默认推荐，即系统预设
参数说明：@logType 对应程序里的Blog.App.Common.LogType枚举
调用示例：EXECUTE sp_blog_contentData_setDefaultRecommend 2
=======================================================*/
ALTER PROCEDURE sp_blog_contentData_setDefaultRecommend
(
 @logType INT
)
AS
 --创建游标
 DECLARE c1 CURSOR FOR
  SELECT TOP 3 a.LogID FROM Blog_ContentData a LEFT JOIN
  (
   SELECT ArticleId, SUM(HotHit) clickCount FROM
   (
    SELECT * FROM Blog_Stat_Article_Hot
   ) t1
   GROUP BY ArticleId
  ) b
  ON a.LogID=b.ArticleId
  WHERE a.logType=@logType
  ORDER BY clickCount DESC;
 DECLARE @v_logId VARCHAR(50);
 DECLARE @V_NUMBER INT;
BEGIN
 --打开游标
 Open c1;
 
 SET @V_NUMBER = 1;
 --读取游标
 FETCH c1 INTO @v_logId;

    WHILE @@FETCH_STATUS=0
 BEGIN
  --PRINT @v_logId;
  --PRINT @V_NUMBER;
  UPDATE Blog_ContentData SET RecommendIndex=@V_NUMBER, RecommendData=GETDATE() WHERE LogID=@v_logId;

  FETCH c1 INTO @v_logId;
  SET @V_NUMBER = @V_NUMBER + 1;
 END;

 --关闭游标
 close c1;
 
 --删除游标
 DEALLOCATE c1;
END;
GO