实验八 SQL 的服务器端编程（4 学时）

--(1)
CREATE PROCEDURE myproc1 @x CHAR(5)
AS
SELECT Clno,Student.Sno,Sname,Course.Cno,Cname
   FROM Student,Course,Cj
    WHERE Student.Sno=Cj.Sno 
      AND Course.Cno=Cj.Cno and Clno = @x 
        ORDER by Cj.Sno
GO
EXEC myproc1 @x= '00311'

--(2)
ALTER TABLE Cj ADD DJ char(10)
 
CREATE PROCEDURE myproc2 
@Cname CHAR(50),
@Grade CHAR(50)
AS
UPDATE Cj SET DJ='优秀' WHERE Grade>=90 AND Grade<=100;
UPDATE Cj SET DJ='良好' WHERE Grade>=80 AND Grade<90;
UPDATE Cj SET DJ='中等' WHERE Grade>=70 AND Grade<80;
UPDATE Cj SET DJ='及格' WHERE Grade>=60 AND Grade<70;
UPDATE Cj SET DJ='不及格' WHERE Grade>=0 AND Grade<60;
SELECT count(Student.Sno)
	FROM Student,Course,Cj
	 WHERE Student.Sno = Cj.Sno
	  AND Course.Cno=Cj.Cno 
	  AND Cname = @Cname 
	  AND DJ = @Grade
GO
EXEC myproc2 @Cname= '离散数学',@Grade = '良好'

--(3)
Create PROCEDURE LX
@Sno CHAR(10),
@Sname CHAR(10),
@Ssex CHAR(10),
@Sage CHAR(10),
@Clno CHAR(10)
AS
	Begin 
		begin transaction
		declare @pd int
		insert student values(@Sno,@Sname,@Ssex,@Sage,@Clno)
		select @pd=count(Sname) from student WHERE Sname = @Sname
        if  @pd<>1
			begin  
				PRINT '有错误，回滚'        
				rollback                                 
			end             
			else  
				PRINT '成功，提交'     
				commit
     End
	
GO
--验证（3）
EXEC LX @Sno='2000112',@Sname='李勇',@Ssex='男',@Sage='22',@Clno='00312'
EXEC LX @Sno='2000111',@Sname='小明',@Ssex='男',@Sage='22',@Clno='00311'
 
delete from student where Sno = '2000111'
delete from student where Sno = '2000112'
 
select * from student
 
DROP  PROCEDURE LX

--(4)
update Class 
set Number = 2
where Clno = '00311'
 
update Class 
set Number = 3
where Clno = '01311'
 
select * from Class

-- (5)
-- 创建触发器，添加张艺
CREATE TRIGGER Tri1 ON Student FOR INSERT
     AS
     UPDATE Class SET Number = Number + 1
     WHERE class.Clno =
          (SELECT Clno FROM inserted WHERE    
                  Class.Clno=inserted.Clno) 

insert student values('2000122','张艺','男','20','00311')

-- 创建触发器，删除张艺
CREATE TRIGGER Tri2 ON Student FOR DELETE
     AS
     UPDATE Class SET Number = Number - 1
     WHERE class.Clno =
          (SELECT Clno FROM deleted WHERE    
                  Class.Clno = deleted.Clno) 

delete from student where Sno = '2000122';


-- 创建触发器Tri3，将李勇同学从00311班转入01311班
CREATE TRIGGER Tri3 ON Student FOR UPDATE  
AS
   IF UPDATE(Clno)
   BEGIN
     UPDATE Class SET Number = Number - 1
     WHERE class.Clno =
          (SELECT Clno FROM deleted WHERE    
                  Class.Clno = deleted.Clno) 
     UPDATE Class SET Number = Number + 1
     WHERE class.Clno =
          (SELECT Clno FROM inserted WHERE    
                  Class.Clno=inserted.Clno) 
   END
 
update Student
set Clno = '01311'
where Sname = '李勇';

--(6)
CREATE TRIGGER cfq3
  ON Course
  FOR DELETE
AS
  DELETE cj
    FROM cj, deleted
    WHERE cj.Cno= deleted.Cno;
 
 
CREATE TRIGGER cfq4
  ON Course
  FOR UPDATE
AS
  IF UPDATE(Cno)
  BEGIN
    UPDATE cj
      SET cj.Cno = inserted.Cno
        FROM cj, deleted, inserted
        WHERE cj.Cno = deleted.Cno
   END