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