代码如下 | 复制代码 |
--创建存储过程 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 |
时间: 2024-08-04 01:43:38