【T-SQL】存储过程和触发器使用实例

一、本文列举了一些T-SQL存储过程和触发的例题以及解答

背景知识：
一、存储过程：封装了可重用的代码块和模块，通过接收参数然后返回结果集来执行。存储过程经过数据库编译后存储在服务器端。
二、触发器：由SQL自动执行，当发生特定操作（update、delete、insert）时自动执行事前编辑好的代码块。原理是使用两个临时表inserted、deleted进行操作。

二、名称解释（不区分大小写）

• student学生表：
  • 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、birthday生日
• sc选课信息表：
  • 包含属性列：sno学号、cno课程号、grade成绩
• dept学院信息表：
  • 包含属性列：dno学院编号、dname学院名称、dean学院负责人
• course课程信息表：
  • 包含属性列：cno课程号、cname课程名称、tname老师名称、credit学分、room教室

下面语句可以直接复制到SQL Server运行

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

运行时注意：由于函数、视图等必须是批处理语句中仅有的语句，可能需要单独创建SQL文件。
且变量命名不可重复，需要运行请自行创建新的SQL文件。

• 运行方法，选中需要执行的语句

三、代码

–创建一个按名字模糊查询学生基本信息的存储过程
create procedure check_name(@sname varchar(20))
as
select sno,sname,age,dno from student where sname like @sname
–执行
exec check_name ‘王%’

–创建一个依据学生学号返回其所有课程平均分的存储过程 student_average
create procedure student_average(@sno char(8))
as
return(select avg(grade) from sc where sno = @sno)
–执行
DECLARE @no char(8) ,@avg float
SET @no=‘20002059’
EXEC @avg=student_average @no
PRINT CONVERT(CHAR(10),@avg)
SELECT SNAME ,@avg AS ‘平均分’ FROM student WHERE SNO=@no

–创建一个执行插入功能的存储过程，可以向 chengjiao 表中插入一条学生记录，该存储过程包含三个参数，分别表示学生学号、学生姓名、学生出生日期，其中学生出生日期参数默认值为’1990-1-1’。
create procedure insert_proc(@sno varchar(8),@sname varchar(8),@date datetime = ‘1909-1-1’)
as
insert into chengjiao(sno,sname,birthday) values(@sno,@sname,@date)
–执行
EXEC INSERT_chengjiao ‘20081001’, ‘jiang’
EXEC INSERT_chengjiao ‘20081002’,‘jiang’,‘1991-12.1’

–创建一个存储过程，可以根据指定的学生学号，通过参数返回该学生的姓名和所选课程的平均分。
create procedure check_avg(@sno char(8))
as
select sname,avg(grade) from student,sc where sc.sno = student.sno and student.sno = @sno
group by sname,student.sno

–存储过程在执行后都会返回一个整型值。如果成功执行，返回 0；否则，返回-1----99之间的数值。下例根据例 3 创建的存储过程，判断该存储过程是否执行成功。
DECLARE @x Int
exec @x=INSERT_chengjiao ‘20082001’, ‘jiang’
PRINT @x --输出-4，不能执行成功，因为原表中已经有了该学号，违反了主码的唯一性

–创建触发器，要求在学生表中删除一个学生时，同时从选课表中将其所有选课信息删除
create trigger on_delete on student
for delete
as
delete from sc
where sc.sno in (select sno from deleted)

–创建触发器限定一个学生最多只能选择 4 门课
create trigger select_four on sc
for insert
as
declare @count int
set @count = (select count(*) from sc where sno in (select sno from inserted))
if @count >= 5
begin
rollback
print(‘选课超过四门，不可再选’)
end

–利用触发器限定修改后的分数只能比原来高
create trigger high_than_before on sc
for update
as
–利用count来记录是否有超过原来的分数的
if(select count(*) from sc where grade > (select grade from deleted)) = 0
begin
rollback
print(‘插入后的分数不可以比原来的分数低’)
end

–（1）创建一个存储过程 del_course，根据指定课程号删除 course 表中的相应记录。
create procedure del_course (@cno char(8))
as
delete from course
where cno = @cno

–（2）在表 DEPT 上创建一个触发器 depart_update，当更改学院编号时，同步更改 student表中的学院编号。
create trigger depart_update on dept
for update
as
update student
–先从inserted表中获取更新值
set dno = (select dno from inserted)
–条件是当前的dno与被删除的dno相同
where dno in (select dno from deleted)

–（3）对课程 course 表创建触发器限定一个教师一学期最多只能上 2 门课
create trigger teach on course
for insert
as
declare @count int
set @count = (select count(*) from course where cno in (select cno from inserted))
if @count > 2
begin
rollback
print(‘只能上两门课’)
end

–（4）创建一个存储过程，可以根据指定的教师名，通过参数返回该教师所上课程的门数及平均分。
create procedure count_avg(@tname char(8))
as
return select course.cno,count(distinct course.cname) as ‘课程数’,avg(grade) from sc,course where course.cno = sc.cno and tname = @tname group by course.cno

–（5）创建一个按教师名查询教师上课信息的存储过程
create procedure check_lesson(@tname char(20))
as
select * from course where tname = @tname

–（6）创建一个按部门查询学生信息的存储过程
create procedure check_student_info(@dno char(8))
as
select * from student where dno = @dno

四、SQL代码块

--创建一个按名字模糊查询学生基本信息的存储过程
create procedure check_name(@sname varchar(20))
as
	select sno,sname,age,dno from student where sname like @sname
--执行
exec check_name '王%'

--创建一个依据学生学号返回其所有课程平均分的存储过程 student_average
create procedure student_average(@sno char(8))
as
	return(select avg(grade) from sc where sno = @sno)
--执行
DECLARE @no char(8) ,@avg float
SET @no='20002059'
EXEC @avg=student_average @no
PRINT CONVERT(CHAR(10),@avg)
SELECT SNAME ,@avg AS '平均分' FROM student WHERE SNO=@no

--创建一个执行插入功能的存储过程，可以向 chengjiao 表中插入一条学生记录，该存储过程包含三个参数，分别表示学生学号、学生姓名、学生出生日期，其中学生出生日期参数默认值为'1990-1-1'。
create procedure insert_proc(@sno varchar(8),@sname varchar(8),@date datetime = '1909-1-1')
as
	insert into chengjiao(sno,sname,birthday) values(@sno,@sname,@date)
--执行
EXEC INSERT_chengjiao '20081001', 'jiang'
EXEC INSERT_chengjiao '20081002','jiang','1991-12.1'

--创建一个存储过程，可以根据指定的学生学号，通过参数返回该学生的姓名和所选课程的平均分。
create procedure check_avg(@sno char(8))
as
	select sname,avg(grade) from student,sc where sc.sno = student.sno and student.sno = @sno
	group by sname,student.sno

--存储过程在执行后都会返回一个整型值。如果成功执行，返回 0；否则，返回-1----99之间的数值。下例根据例 3 创建的存储过程，判断该存储过程是否执行成功。
DECLARE @x Int
exec @x=INSERT_chengjiao '20082001', 'jiang'
PRINT @x --输出-4，不能执行成功，因为原表中已经有了该学号，违反了主码的唯一性

--创建触发器，要求在学生表中删除一个学生时，同时从选课表中将其所有选课信息删除
create trigger on_delete on student
for delete
as
	delete from sc
	where sc.sno in (select sno from deleted)

--创建触发器限定一个学生最多只能选择 4 门课
create trigger select_four on sc
for insert
as
	declare @count int
	set @count = (select count(*) from sc where sno in (select sno from inserted))
	if @count >= 5
	begin 
		rollback
		print('选课超过四门，不可再选')
	end

--利用触发器限定修改后的分数只能比原来高
create trigger high_than_before on sc
for update
as
	--利用count来记录是否有超过原来的分数的
	if(select count(*) from sc where grade > (select grade from deleted)) = 0
	begin
		rollback
		print('插入后的分数不可以比原来的分数低')
	end

--（1）创建一个存储过程 del_course，根据指定课程号删除 course 表中的相应记录。
create procedure del_course (@cno char(8))
as
	delete from course
	where cno = @cno

--（2）在表 DEPT 上创建一个触发器 depart_update，当更改学院编号时，同步更改 student表中的学院编号。
create trigger depart_update on dept
for update
as
	update student
	--先从inserted表中获取更新值
	set dno = (select dno from inserted)
	--条件是当前的dno与被删除的dno相同
	where dno in (select dno from deleted)

--（3）对课程 course 表创建触发器限定一个教师一学期最多只能上 2 门课
create trigger teach on course
for insert
as
	declare @count int
	set @count = (select count(*) from course where cno in (select cno from inserted))
	if @count > 2
		begin
			rollback
			print('只能上两门课')
		end

--（4）创建一个存储过程，可以根据指定的教师名，通过参数返回该教师所上课程的门数及平均分。
create procedure count_avg(@tname char(8))
as
	return select course.cno,count(distinct course.cname) as '课程数',avg(grade) from sc,course where  course.cno = sc.cno and tname = @tname group by course.cno

--（5）创建一个按教师名查询教师上课信息的存储过程
create procedure check_lesson(@tname char(20))
as
	select * from course where tname = @tname

--（6）创建一个按部门查询学生信息的存储过程
create procedure check_student_info(@dno char(8))
as
	select * from student where dno = @dno