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