选课存储过程业务逻辑
course 表和student 表,student_course表
student不能重复选课
student选课时间（timeRange字段）不能有冲突
上代码：
View Code
1 CREATE PROCEDURE [dbo].[selectCourse]( 2 @course_id int , 3 @student_id int , 4 @result int output 5 ) 6 AS 7 BEGIN 8 SET NOCOUNT ON; 9 IF(EXISTS(SELECT * FROM [student_course] WHERE [course_id] = @course_id AND [student_id] = @student_id)) 10 BEGIN 11 set @result = 0; 12 --RETURN 12;--已经选择了该课程 13 END 14 ELSE IF(EXISTS(SELECT * FROM [student_course] WHERE [student_id] = @student_id)) 15 BEGIN 16 --print 'ss' 17 declare @courseid int,@timerange int,@timerangetoinsert int; 18 SELECT @timerangetoinsert=timeRange FROM [course] where id = @course_id; 19 --print @timerangetoinsert; 20 DECLARE selectedcourse_cursor CURSOR FOR 21 select sc.course_id,c.timeRange from [student_course] as sc inner join course as c on c.id = sc.course_id where [student_id] = @student_id; 22 23 OPEN selectedcourse_cursor; 24 25 -- Perform the first fetch and store the values in variables. 26 -- Note: The variables are in the same order as the columns 27 -- in the SELECT statement. 28 29 FETCH NEXT FROM selectedcourse_cursor 30 INTO @courseid,@timerange; 31 32 -- Check @@FETCH_STATUS to see if there are any more rows to fetch. 33 WHILE @@FETCH_STATUS = 0 34 BEGIN 35 IF(@timerangetoinsert<>@timerange) 36 BEGIN 37 INSERT INTO [student_course](course_id,student_id)VALUES(@course_id,@student_id); 38 set @result = 1;--选课成功 39 BREAK;--选课成功 40 END 41 ELSE 42 BEGIN 43 set @result = 2;--时间冲突 44 BREAK; 45 END 46 47 -- This is executed as long as the previous fetch succeeds. 48 FETCH NEXT FROM selectedcourse_cursor 49 INTO @courseid,@timerange; 50 END 51 52 CLOSE selectedcourse_cursor; 53 DEALLOCATE selectedcourse_cursor; 54 END 55 ELSE 56 BEGIN 57 INSERT INTO [student_course](course_id,student_id)VALUES(@course_id,@student_id); 58 set @result = 1;--选课成功 59 END 60 RETURN @result; 61 END
