SQL Server 2012数据库——存储过程和触发器的创建
1.用SQL语句求1+2+3+…+100。
–求1+2+3+…+100。
declare @sum int,@i int
set @sum=0
set @i=1
while (@i<=100)
begin
select @[email protected][email protected],@[email protected]+1
end
print @sum
select @sum
2.创建满足下述要求的存储过程，并查看存储过程的执行结果。
（1）查询某学生（学号）的学号、姓名、课程号、成绩，学号的默认值为“0611101”。执行此存储过程，并分别指定不同的输入参数值，查看执行结果（至少执行两次，默认值和非默认值）。
create procedure p_Studentm3
@sno1 char(7)=‘0611101’
as
select distinct Student.Sno,Sname,Cno,Grade from Student
join SC on Student.Sno=SC.Sno
where [email protected]
(默认值)
exec p_Studentm3
（非默认值）
create procedure p_Studentm3
@sno1 char(7)=‘0611101’
as
select distinct Student.Sno,Sname,Cno,Grade from Student
join SC on Student.Sno=SC.Sno
where [email protected]
exec p_Studentm3 ‘0611102’
（2）查询指定系的男生人数。
create procedure p_Studentnum2
@dept nvarchar(20),@total int output
as
select @total=count(*) from Student
where [email protected] and Ssex=‘男’
declare @total1 int
exec p_Studentnum2 ‘计算机系’,@total1 output
print @total1
（3）查询某学生（姓名）的选课门数。
create proc p_Studentnum3
@sno1 char(7),@total tinyint output
as
select count(*) from SC
where [email protected] and Grade is not null
declare @total2 tinyint
exec p_Studentnum3 ‘0611101’,@total2 output
print @total2
（4）删除指定学生（姓名）的修课记录。
create proc p_deleteSC1
@sname1 nchar(5)
as
delete from SC
from SC join Student on Student.Sno=SC.Sno
where [email protected]
exec p_deleteSC1 ‘李勇’
（5）修改某门课程（课程号）的开课学期。
–修改某门课程（课程号）的开课学期
create proc p_updatesemester1
@cno1 char(6),@semester1 tinyint
as
update Course
set [email protected]
where [email protected]
exec p_updatesemester1 ‘C001’,2
select * from Course
（6）在学生表中插入一行数据，只插入学号、姓名、性别三列，各列数据均通过输入参数获得（每个人输入自己的信息）。
/在学生表中插入一行数据，只插入学号、姓名、性别三列，
各列数据均通过输入参数获得（每个人输入自己的信息）。/
create proc p_insertStudentm4
@sno1 char(7),@sname1 nchar(5),@sex1 nchar(1)
as
insert into Student
values(@sno1,@sname1,@sex1,null,null)
exec p_insertStudentm4 ‘0611106’,‘龙龙’,‘男’
select * from Student
（7）查询某门课（课程号）的平均成绩。如果没有提供输入参数值，返回1，否则返回2。(参考实验教材P234例题)
/查询某门课（课程号）的平均成绩。
如果没有提供输入参数值，返回1，否则返回2。/
go
create proc p_avgscore2
@cno1 char(6)=null,@savg1 tinyint output
as
begin
if @cno1 is null
return 1
else
begin
select @savg1=avg(Grade) from SC
where [email protected]
return 2
end
end
go
go //(默认值)
declare @return1 int ,@savg1 tinyint
exec @return1=p_avgscore2 @[email protected] output
select @return1 as 返回值,@savg1 as 平均成绩
go //(非默认值)
declare @return1 int ,@savg1 tinyint
exec @return1=p_avgscore2 @[email protected] output,@cno1=‘C002’
select @return1 as 返回值,@savg1 as 平均成绩
3.创建满足下述要求的触发器，并验证触发器执行情况。
（1）限制学生的年龄在15~45之间。(用两种触发器做，并比较结果，截图)
1.后触发器
–限制学生的年龄在15~45之间。(用两种触发器做，并比较结果，截图)
create trigger trigger_studentage1 on Student
after insert,update
as
if exists(select * from inserted
where Sage not between 15 and 45)
rollback
insert into Student values(‘0611106’,‘龙龙’,‘男’,3,‘计算机系’)
insert into Student values(‘0611106’,‘龙龙’,‘男’,20,‘计算机系’)
2.前触发器
create trigger trigger_studentage2 on Student
instead of insert,update
as
if exists(select * from inserted
where Sage not between 15 and 45)
print ‘不在规定范围内’
insert into Student values(‘0611106’,‘龙龙’,‘男’,3,‘计算机系’)
insert into Student values(‘0611106’,‘龙龙’,‘男’,20,‘计算机系’)
（2）限制学生所在系的取值范围为{计算机系，信息管理系，数学系，通信工程系}
create trigger trigger_sdept2 on Student
after insert,update
as
if exists(select * from inserted
where Sdept not in(‘计算机系’,‘信息管理系’,‘数学系’,‘通信工程系’))
begin
print ‘不在所在系取值范围内，禁止插入！！’
rollback
end
insert into Student values(‘0611107’,‘蛋蛋’,‘男’,20,‘软件工程系’)
（3）限制每个学期开设的课程总学分在20~30范围内。
create trigger trigger_credit1 on course
after insert,update
as
if exists (select Semester from Course
where Semester in(select Semester from inserted)
group by Semester
having sum(Credit) not between 20 and 30)
begin
print ‘每个学期开设的课程总学分不在20~30范围内!’
rollback
end
insert into Course values(‘C009’,‘数据库’,4,4)
（4）限制每个学生每学期选课门数不能超过6门（设只针对插入操作）。
create trigger trigger_countc1 on SC
after insert
as
if exists(select count(distinct cno) from SC
where Sno in(select Sno from inserted)
group by Sno
having count(distinct cno)>3)
begin
print ‘选课门数超过6门，无法插入！’
rollback
end
insert into SC values(‘0611101’,‘C004’,80)
insert into SC values(‘0611101’,‘C006’,80)
（5）当更改学生表中的学号时，选课表中的学号也作相应修改。
create trigger trigger_sno1 on Student
after update
as
begin
declare @newsno char(7)
declare @oldsno char(7)
select @newsno=Sno from inserted
select @oldsno=Sno from deleted
update SC set [email protected]
where [email protected]
end
update Student set Sno=‘0666606’
where Sno=‘0611101’
select * from Student where Sno=‘0666606’
select * from SC where Sno=‘0666606’
（6）当删除学生表中某个学生信息后，选课表中该学生的选课信息也自动删除。
create trigger trigger_deletem1 on Student
after delete
as
begin
delete from SC where Sno=(select Sno from deleted)
end
delete from Student where Sno=‘0666606’
select * from Student where Sno=‘0666606’
select * from SC where Sno=‘0666606’
（7）为防止其他人修改成绩，在grade列上创建触发器，要求不能更新sc表中的grade列。（update(grade)）
create trigger trigger_grade on SC
after update
as
if update(Grade)
begin
print ‘不能修改成绩’
rollback
end
update SC set Grade=100
where Sno=‘0611102’
（8）创建一个触发器，不允许修改和删除course表中的信息。
create trigger trigger_course on Course
after update,insert,delete
as
begin
print ‘不能修改信息’
rollback
end
update Course set Credit=10
where Cno=‘C001’
（9）创建一个触发器，不允许删除SC表。
create trigger trigger_sc on database
after drop_table
as
begin
print ‘不允许删除SC表！’
rollback
end
drop table SC