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