实验六 游标、存储过程和触发器

实验六 游标、存储过程与触发器
一、实验目的
掌握使用T-SQL实现游标、存储过程和触发器的创建，使用方法。
二、实验内容
在实验一、实验二创建的表中用T-SQL语句完成以下内容：
1.使用游标实现将及格的选课信息输出。
use Student;
DECLARE ST CURSOR FOR
SELECT *
FROM C
WHERE Cno IN(
SELECT Cno
FROM SC
WHERE Grade between 60 and 100
)
DECLARE @Cno nchar(10)
DECLARE @Cname nchar(20)
DECLARE @Credit float
DECLARE @Property nchar(14)
DECLARE @Tname varchar(10)
open ST
fetch ST INTO @Cno,@Cname,@Credit,@Property,@Tname;
WHILE @@FETCH_STATUS = 0
begin
PRINT @Cno+' '+@Cname+' '+cast(@Credit as char(10)) +' '+@Property
fetch next from ST INTO @Cno,@Cname,@Credit,@Property,@Tname;
end

close ST
deallocate ST
2.使用游标将SPJ表中的偶数行输出。
use gongcheng1;
DECLARE So CURSOR FOR
select Sno,Pno,Jno,QTY from
(select *,row_number() over(order by getdate()) 'rn' from SPJ) t
where t.rn%2=0

DECLARE @Sno char(10)
DECLARE @Pno char(10)
DECLARE @Jno char(10)
DECLARE @QTY INT

OPEN So
fetch next from So INTO @Sno,@Pno,@Jno,@QTY
WHILE @@FETCH_STATUS = 0
begin
PRINT @Sno+' '+@Pno+' '+@Jno++' '+cast(@QTY as char(10))
fetch next from So INTO @Sno,@Pno,@Jno,@QTY
end

close So
deallocate So

3.创建存储过程，查询赵永亮所修课程的课程信息，将课程号和课程名输出。
CREATE PROCEDURE Cno_Cname @NAME VARCHAR(20)
AS
SELECT C.Cno,C.Cname
FROM SC,C,S
WHERE(S.Sname=@NAME AND SC.Cno=C.Cno AND SC.Sno=S.Sno)

EXEC Cno_Cname'赵永亮'
DROP PROCEDURE Cno_Cname
4．创建存储过程，统计指定学生修课的平均成绩和选课门数，将统计的结果用
输出参数返回。
CREATE PROCEDURE AvgCount
@sn varchar(20),
@avg_grade int output,
@total int output
AS
SELECT @avg_grade = AVG(Grade),
@total = COUNT(S.Sno)
FROM SC,S
where S.Sno = @sn and SC.Sno = S.Sno

DECLARE @Avggrade int, @total int
Exec AvgCount '09105107',@avggrade output ,@total output

Select @avggrade,@total

5．创建存储过程，在学生表Student中插入一条完整的元组。
CREATE PROCEDURE Insert_S
@Sno1 nchar(8),@Sname1 nchar(10),@Ssex1 nchar(2),@Age
smallint,@Deptment1 nchar(20)
AS
INSERT INTO S(Sno,Sname,Ssex,Sage,Deptment)
VALUES(@Sno1,@Sname1,@Ssex1,@Age,@Deptment1)

EXEC Insert_S '09105110','唐烨','男',19,'信息'

6．创建存储过程，根据用户指定的供应商号删除SPJ表中相应的供货信息。
CREATE PROCEDURE Del_SPJ
@Sel_Sno nchar(10)
AS
DELETE FROM SPJ
WHERE @Sel_Sno=Sno

EXEC Del_SPJ 'S1'

7．创建存储过程，将指定零件的重量增加指定的值。
CREATE PROCEDURE Add_weight
@Pno1 char(10),@add_weight INT
AS
UPDATE P
SET Weight=Weight+@add_weight
WHERE P.Pno=@Pno1
EXEC Add_weight 'P1',40

drop PROCEDURE Add_weight
8．用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少
于300，如果少于则自动改为300。
CREATE TRIGGER TY ON SPJ
AFTER UPDATE,INSERT
AS
DECLARE @sNO char(10),@Qty INT
SELECT @sNO=Sno,@Qty=QTY
FROM INSERTED IF
@Qty<300 AND @sNO IN(
SELECT Sno
FROM S
WHERE City='北京')
UPDATE SPJ
SET QTY=300

INSERT INTO SPJ(Sno,Pno,Jno,QTY)
VALUES ('S2','P2','J1',100)

DROP TRIGGER TY
9．在SC关系中增加新属性列Status，用来记录课程成绩的等级，0－59分
为“不合格”，60－69为“合格”，70－89为“良好”，90以上为“优秀”。要求
status属性列的值由用户在插入、更新选课成绩时系统自动填写和更新。
ALTER TABLE SC ADD status char(10)
CREATE TRIGGER TY_add ON SC
FOR UPDATE,INSERT
AS
DECLARE @SNO nchar(8),@CNO nchar(10),@GRADE INT,@STATUS char(10)
SELECT @SNO=Sno,@CNO=Cno,@GRADE=Grade
FROM INSERTED
SELECT @STATUS=
CASE
WHEN @GRADE<60 THEN '不合格'
WHEN @GRADE>=60 and @GRADE<70 THEN '合格'
WHEN @GRADE>=70 and @GRADE<90 THEN '良好'
ELSE '优秀'
end
UPDATE SC
SET status=@STATUS
WHERE Sno=@SNO AND Cno=@CNO AND Grade=@GRADE

INSERT INTO SC(Sno,Cno,Grade)
VALUES ('09105101','001',66)

DROP TRIGGER TY_add

三、实验总结
这是数据结构最后一次实习。
在本次实习中遇到了很多问题，在游标，存储过程以及触发器的使用过程中
多多少少都存在一些问题。比如，使用游标将SPJ表中的偶数行输出，这个问题
中就不知道怎么输出偶数行。但是最终在同学和老师的帮助下成功的完成了本次
实验。