07信管专业072班数据库系统概论课程设计论文
FOR UPDATE AS BEGIN
INSERT INTO ADJUSTRECORD(CNo,RoomNo,TNo, WeekNo,Weekday,SectionNo,NewWeekNo, NewWeekday,NewSectionNo,NewRoomNo) SELECT
DELETED.CNo,DELETED.RoomNo,DELETED.TNo,DELETED.WeekNo,DELETED.Weekday, DELETED.SectionNo,INSERTED.WeekNo,INSERTED.Weekday,INSERTED.SectionNo, INSERTED.RoomNo
FROM DELETED JOIN INSERTED ON INSERTED.CNo=DELETED.CNo; END
附3-3 教师调课事务处理
可以看到调课记录中自动生成了调课的记录，包含调课前后课程教室的信息。
5、 修改学生的出勤次数
/* 修改学生的出勤次数 */
CREATE PROCEDURE CHANGEATTENDENCE(@SNo CHAR(8),@CNo CHAR(4)) AS
IF NOT EXISTS(SELECT *FROM ATTENDENCE WHERE SNo=@SNo AND CNo=@CNo) INSERT INTO ATTENDENCE VALUES(@SNo,@CNo,1); ELSE
UPDATE ATTENDENCE
SET AttendSum=AttendSum+1
WHERE SNo=@SNo AND CNo=@CNo
EXEC CHANGEATTENDENCE '20080004','0001';
6、 处理查询事务的部分存储过程的建立与验证。
/*查询某班级上课应到人数*/
CREATE PROCEDURE SearchNumofPerson(@Pname CHAR(20)) AS
SELECT Cname,SUM(StuNum) AS 应到人数 FROM COURSE
WHERE Pname=@Pname GROUP BY Cname
/* 创建查询空教室的存储过程*/
30
07信管专业072班数据库系统概论课程设计论文
CREATE PROCEDURE SearchEmptyClassroom(@WeekNO char(10), @Weekday char(10),@SectionNo char(10)) AS
SELECT RoomNo,Roomname,Buildname,Campusname,RemainCapacity FROM CLASSROOM
WHERE IsUsable='可用' AND RoomNo NOT IN (
SELECT RoomNo
FROM CURRICULUM
WHERE WeekNo=@WeekNO AND Weekday=@Weekday AND SectionNo=@SectionNo )
附3-4 查询空教室结果
/*查询班级课程表*/
CREATE PROCEDURE SearchCurrivulum(@pname CHAR(20),@classNo CHAR(4)) AS BEGIN
SELECT WeekNo,Weekday,SectionNo,Pname,ClassNo,
KECHENGBIAO.Tname,Roomname,KECHENGBIAO.Cname FROM KECHENGBIAO,COURSE
WHERE KECHENGBIAO.Cname=Course.Cname
AND Pname=@pname AND COURSE.ClassNo=@classNo END
附3-5 班级课程表查询事务处理
31