数据库 触发器、游标应用

1.    利用SQL Server Management Studio创建一个存储过程ProcNum，查询每个班级中学生的人数，按班级号升序排序。

答案：

create procedure procnum

as

begin

    select classid, count(*) as 学生人数

    from student

    group by classid

    order by classid asc;

end;

go

2.    利用Transact-SQL语句创建一个带有参数的存储过程ProcInsert，向score表插入一条选课记录，并查询该学生的姓名、选修的所有课程名称、平时成绩和期末成绩。

答案：

create procedure procinsert

    @studentid char(7),

    @courseid char(4),

    @regulargrade int,

    @finalgrade int

as

begin

    begin transaction;

   

    insert into score (id, cid, regulargrade, finalgrade)

    values (@studentid, @courseid, @regulargrade, @finalgrade);

   

    select s.sname as 学生姓名, c.cname as 课程名称, s.regulargrade as 平时成绩, s.finalgrade as 期末成绩

    from student s

    join course c on s.cid = c.cid

    where s.id = @studentid;

   

    commit transaction;

end;

go

3.    利用Transact-SQL语句创建一个存储过程ProcAvg，查询指定班级指定课程的平均分。班级号和课程名称由输入参数指定，计算出平均分通过输出参数返回。若该存储过程已存在，则删除后重建。

答案：

if exists (select * from sys.objects where type = 'P' and name = 'procavg')

    drop procedure procavg;

go



create procedure procavg

    @classid char(4),

    @coursename nvarchar(100),

    @averagegrade decimal output

as

begin

    select @averagegrade = avg(s.finalgrade)

    from student s

    join course c on s.cid = c.cid

    where s.classid = @classid and c.cname = @coursename;

end;

go

4.    利用SQL Server Management Studio创建一个AFTER触发器trigsex，当插入或修改student表中性别字段sex时，检查数据是否只为“男”或“女”。

答案：

if exists (select * from sys.triggers where name = 'trigsex')

    drop trigger trigsex;

go



create trigger trigsex

on student

after insert, update

as

begin

    if exists (select * from inserted where sex not in ('男', '女'))

    begin

        raiserror('性别字段只能为“男”或“女”', 16, 1);

        rollback transaction;

    end;

end;

go

5.    利用Transact-SQL语句创建一个AFTER触发器trigforeign，当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据不匹配，即没有对应的序号存在，则将此记录删除。

答案：

create trigger trigforeign

on score

after insert, update

as

begin

    delete from score

    where id not in (select id from student);

end;

go

6.    利用Transact-SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务。若该触发器已存在，则删除后重建。。

答案：

if exists (select * from sys.triggers where name = 'trigclassname')

    drop trigger trigclassname;

go



create trigger trigclassname

on class

after insert, update

as

begin

    set nocount on;

   

    if exists (select classname, count(*) from inserted group by classname having count(*) > 1)

    begin

        raiserror('班级名称重复，事务已回滚', 16, 1);

        rollback transaction;

    end;

end;

go

7.编写一个函数，形式参数为姓氏（如“李”）值，使用游标实现：如果该姓的学生为男生，则返回该生的姓名，性别，身高值；如果该姓的学生为女生，则返回姓名，性别，出生日期，家庭住址； 返回该姓氏的所有男女生的平均身高。

提示：函数返回这些数据时，可以使用一个连接起来的字符串包含所有要返回的数据，也可以使用表值函数返回以上所需要的数据。本题可能要使用游标多次遍历所有行，分别实现不同要求的操作。

答案：

create function getstudentsinfobysurname (@surname nvarchar(50))

returns table

as

return

(

    select

        case

            when s.gender = '男' then concat(s.name, ', ', s.gender, ', ', convert(nvarchar(50), s.height))

            else concat(s.name, ', ', s.gender, ', ', convert(nvarchar(50), s.birthdate), ', ', s.homeaddress)

        end as info

    from student s

    where left(s.name, 1)