sql中存储过程和游标简单示例

代码如下

复制代码

--创建存储过程 sp_tittles
IF EXISTS(SELECT * FROM sys.objects WHERE type='p' AND name='sp_titles')
    DROP PROC sp_titles
GO
CREATE PROC sp_titles
AS
     DECLARE @ProblemID int,@TitleID int,@item int
     --声明一个游标
     DECLARE cursor_title CURSOR FOR
         SELECT [pid],[id]
         FROM [tb_titles]
     --打开游标
     OPEN cursor_title
     --提取游标第一行
     FETCH NEXT FROM cursor_title INTO @ProblemID,@TitleID
     --循环提取游标内容
     WHILE @@FETCH_STATUS=0
     BEGIN
         IF EXISTS(
             SELECT *
             FROM [tb_ProblemIndex]
             WHERE [ProblemID]=@ProblemID)
             BEGIN
                 SELECT TOP 1 @item=[id] FROM [tb_titles] WHERE [pid]=@ProblemID ORDER BY createtime DESC
                 UPDATE [tb_ProblemIndex] SET [TitleID] = @item WHERE [ProblemID]=@ProblemID
             END
         ELSE
             INSERT INTO [tb_ProblemIndex]([ProblemID],[TitleID])
             VALUES(@ProblemID,@TitleID)
         FETCH NEXT FROM cursor_title INTO @ProblemID,@TitleID
     END
     --关闭游标
     CLOSE cursor_title
     --释放游标资源
     DEALLOCATE cursor_title
GO
--存储过程 sp_titles 结束

--执行存储过程 sp_titles
EXEC sp_titles

--创建存储过程 sp_answers
IF EXISTS(SELECT * FROM sys.objects WHERE type='p' AND name='sp_answers')
     DROP PROC sp_answers
GO
CREATE PROC sp_answers
AS
     DECLARE @ProblemID int,@AnswerID int
     --声明一个游标
     DECLARE cursor_answer CURSOR FOR
         SELECT [pid],[id]
         FROM [tb_answers]
     --打开游标
     OPEN cursor_answer
     --提取游标第一行内容
     FETCH NEXT FROM cursor_answer INTO @ProblemID,@AnswerID
     --循环提取游标内容
     WHILE @@FETCH_STATUS=0
     BEGIN
         UPDATE [tb_ProblemIndex] SET [AnswerID]=@AnswerID WHERE [ProblemID]=@ProblemID
         FETCH NEXT FROM cursor_answer INTO @ProblemID,@AnswerID
     END
     --关闭游标
     CLOSE cursor_answer
     --释放游标资源
     DEALLOCATE cursor_answer
GO
--存储过程 sp_answers 结束

--执行存储过程 sp_answers
EXEC sp_answers

补充：数据库,Mssql