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