存储过程和游标示例

花了一个下午的时间终于把存储过程和游标搞明白，只传代码，不说话，对于想了解存储过程和游标写法的朋友可以参考一下，有何问题请留言。

 1 --创建存储过程 sp_tittles
 2 IF EXISTS(SELECT * FROM sys.objects WHERE type='p' AND name='sp_titles')
 3    DROP PROC sp_titles 
 4 GO
 5 CREATE PROC sp_titles
 6 AS
 7     DECLARE @ProblemID int,@TitleID int,@item int
 8     --声明一个游标
 9     DECLARE cursor_title CURSOR FOR
10         SELECT [pid],[id] 
11         FROM [tb_titles]
12     --打开游标
13     OPEN cursor_title
14     --提取游标第一行
15     FETCH NEXT FROM cursor_title INTO @ProblemID,@TitleID
16     --循环提取游标内容
17     WHILE @@FETCH_STATUS=0
18     BEGIN
19         IF EXISTS(
20             SELECT * 
21             FROM [tb_ProblemIndex] 
22             WHERE [ProblemID]=@ProblemID)
23             BEGIN
24                 SELECT TOP 1 @item=[id] FROM [tb_titles] WHERE [pid]=@ProblemID ORDER BY createtime DESC
25                 UPDATE [tb_ProblemIndex] SET [TitleID] = @item WHERE [ProblemID]=@ProblemID
26             END
27         ELSE
28             INSERT INTO [tb_ProblemIndex]([ProblemID],[TitleID]) 
29             VALUES(@ProblemID,@TitleID)
30         FETCH NEXT FROM cursor_title INTO @ProblemID,@TitleID
31     END
32     --关闭游标
33     CLOSE cursor_title
34     --释放游标资源
35     DEALLOCATE cursor_title
36 GO
37 --存储过程 sp_titles 结束
38 
39 --执行存储过程 sp_titles
40 EXEC sp_titles
41 
42 --创建存储过程 sp_answers
43 IF EXISTS(SELECT * FROM sys.objects WHERE type='p' AND name='sp_answers')
44     DROP PROC sp_answers
45 GO
46 CREATE PROC sp_answers
47 AS
48     DECLARE @ProblemID int,@AnswerID int
49     --声明一个游标
50     DECLARE cursor_answer CURSOR FOR
51         SELECT [pid],[id]
52         FROM [tb_answers]
53     --打开游标
54     OPEN cursor_answer
55     --提取游标第一行内容
56     FETCH NEXT FROM cursor_answer INTO @ProblemID,@AnswerID
57     --循环提取游标内容
58     WHILE @@FETCH_STATUS=0
59     BEGIN
60         UPDATE [tb_ProblemIndex] SET [AnswerID]=@AnswerID WHERE [ProblemID]=@ProblemID
61         FETCH NEXT FROM cursor_answer INTO @ProblemID,@AnswerID
62     END
63     --关闭游标
64     CLOSE cursor_answer
65     --释放游标资源
66     DEALLOCATE cursor_answer
67 GO
68 --存储过程 sp_answers 结束
69 
70 --执行存储过程 sp_answers
71 EXEC sp_answers

转载于:https://www.cnblogs.com/sunzhiyue/archive/2011/12/12/2285455.html