CSDN博客

img huotu

数据库游标结合临时表的使用

发表于2008/9/28 11:35:00  339人阅读

一、创建临时表定义相关的变量:
        CREATE TABLE #tempTable      --创建临时表
        (
            MainTradeCode int,
            SubTradeCode int,
             SubTradeName varchar(50)
        )
         DECLARE @keyword varchar(200)
         DECLARE @SearchCount int
二、创建游标:
         DECLARE keywordCur CURSOR local FOR
         SELECT TOP 8 count(*) AS SearchCount,keyword
         FROM KeywordBySearch
         WHERE InfoType='sell'
         GROUP BY KeywordBySearch.keyword
        ORDER BY SearchCount DESC
三、打开游标获得数据并往临时表中插入数据:
         OPEN keywordCur
         FETCH NEXT FROM keywordCur INTO @SearchCount,@keyword
 
  WHILE @@FETCH_STATUS=0
     BEGIN
      INSERT INTO #tempTable
      SELECT TOP 1 sd.TradeCode AS MainTradeCode,SubTrade AS SubTradeCode,
      (SELECT SubTrades.TradeName FROM SubTrades WHERE SubTrades.TradeCode=sd.SubTrade) AS SubTradeName
      FROM SupplyDemand sd
      WHERE Keywords LIKE '%'+ @keyword +'%'
      ORDER BY sd.SdId desc
      FETCH NEXT FROM keywordCur INTO @SearchCount,@keyword
     END
四、删除游标、返回临时表数据和删除临时表:
        CLOSE keywordCur
        DEALLOCATE keywordCur
        SELECT * FROM #tempTable
        DROP TABLE #tempTable

由于游标比较耗性能,在大量数据的场合不太适合使用游标.
可用同样的方法操作游标和用户表

0 0

相关博文

我的热门文章

img
取 消
img