SQL存储过程和游标示例

女爷i 2023-10-06 12:32 9阅读 0赞

--创建存储过程 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

参考搬运地址：[https://www.cnblogs.com/sunzhiyue/archive/2011/12/12/2285455.html][https_www.cnblogs.com_sunzhiyue_archive_2011_12_12_2285455.html]

[https_www.cnblogs.com_sunzhiyue_archive_2011_12_12_2285455.html]: https://www.cnblogs.com/sunzhiyue/archive/2011/12/12/2285455.html