sql 游标示例

最新推荐文章于 2025-07-14 12:06:11 发布

转载最新推荐文章于 2025-07-14 12:06:11 发布 · 55 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/qiantuwuliang/archive/2009/03/27/1423428.html

本文介绍了一个使用SQL存储过程的示例，展示了如何通过存储过程动态地从数据库中获取并展示特定职业相关信息的方法。该存储过程接收职业名称作为输入参数，并创建一个临时表来存储查询结果。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SelectSide](@career nchar(2))
AS
CREATE TABLE #tmp
(
KeyWord nvarchar(50),
Website1 nvarchar(50),
URL1 varchar(100),
Introduce1 nvarchar(max),
Website2 nvarchar(50),
URL2 varchar(100),
Introduce2 nvarchar(max),
Website3 nvarchar(50),
URL3 varchar(100),
Introduce3 nvarchar(max),
Website4 nvarchar(50),
URL4 varchar(100),
Introduce4 nvarchar(max),
Website5 nvarchar(50),
URL5 varchar(100),
Introduce5 nvarchar(max),
Website6 nvarchar(50),
URL6 varchar(100),
Introduce6 nvarchar(max),
Website7 nvarchar(50),
URL7 varchar(100),
Introduce7 nvarchar(max),
Website8 nvarchar(50),
URL8 varchar(100),
Introduce8 nvarchar(max),
Website9 nvarchar(50),
URL9 varchar(100),
Introduce9 nvarchar(max)
)
DECLARE @keyword nvarchar(max),
   @keyTmp nvarchar(50),
      @url varchar(100),
     @url1 varchar(100),
     @website nvarchar(50),
    @website1 nvarchar(50),
   @introduce nvarchar(max),
@introduce1 nvarchar(max),
     @i int,
   @iBegin int,
     @iEnd int,
   @switch bit

SELECT     @keyword = KeyWord
FROM         TCareer
WHERE     (Career = @career)

SET @switch = 1

SET @iBegin = 1
SET @iEnd = CHARINDEX(',', @keyword, 1)

WHILE @iEnd > 0
BEGIN
SET @keyTmp = SUBSTRING(@keyword, @iBegin, @iEnd - @iBegin)

DECLARE cur1 CURSOR FOR
SELECT     TOP (9) URL, Website, Introduce
FROM         TAddress
WHERE     (Career = @career) AND (KeyWord = @keyTmp)
ORDER BY AddTime DESC

OPEN cur1
FETCH NEXT FROM cur1 INTO @url,@website,@introduce

   SET @i = 1

   WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @introduce1 = REPLACE(@introduce,'''','''''')
   SET @url1 = REPLACE(@url,'''','''''')
   SET @website1 = REPLACE(@website,'''','''''')
      IF @i = 1
         INSERT INTO #tmp (KeyWord,Website1,URL1,Introduce1)
         VALUES (@keyTmp,@website,@url,@introduce)
      ELSE
         EXECUTE ('UPDATE #tmp SET URL'+ @i +'='''+ @url1 +''',Website'+ @i +' = N'''+ @website1 +''',Introduce'+ @i +' = N'''+ @introduce1 +'''
         WHERE (KeyWord = N'''+ @keyTmp +''')')

      SET @i = @i + 1
      FETCH NEXT FROM cur1 INTO @url,@website,@introduce
   END

   CLOSE cur1
   DEALLOCATE cur1

   SET @iBegin = @iEnd + 1
   SET @iEnd = CHARINDEX(',',@keyword,@iBegin)

   IF @iEnd = 0 AND @switch = 1
   BEGIN
      SET @iEnd = LEN(@keyword) + 1
      SET @switch = 0
   END
END

SELECT * FROM #tmp

RETURN
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

转载于:https://www.cnblogs.com/qiantuwuliang/archive/2009/03/27/1423428.html