下载文档原格式
数据库技术与应用实验报告七班级: 机械因材学号: 16 姓名:高永吉一:实验名称:存储过程及触发器二,实验目的:⑴使用系统常用的存储过程;⑵掌握存储过程的创建及应用(3) 理解触发器的概念;(4) 掌握触发器的创建及应用。
三.实验内容、过程与结果:存储过程1创建一个存储过程,查瞧学号为1(根据实际情况取)的学生的信息,包括该学生的学号,班级编号,姓名。
(提示:查询涉及到表Student)2执行1中创建的存储过程。
3使用输入参数创建题1中的存储过程。
题1中所创建的存储过程只能学号为1的学生信息进行查瞧,要想对其她学生进行查瞧,需要进行参数传递。
4执行3中创建的存储过程,(1)按位置传递参数;(2)通过参数名传递参数;5触发器1)在课程表Course上创建一个触发器,该触发器被操作DELETE所触发,且要求触发触发器的DELETE语句在执行被取消。
2)在表Student中建立插入触发器, 插入一条记录时,若年龄>100或者年龄<=0,拒绝插入记录并显示:“年龄不符合规定,无法插入此记录！”;3) 创建一个触发器,如果在Student表中添加或更改数据,向客户端显示一条消息“您正在插入或修改学生表的数据”,要求触发触发器的DELETE、UPDATE语句被执行。
4 )为Course表创建一个名称为my_trig的触发器,当用户成功删除该表中的一条或多条记录时,触发器自动删除Student表中与之有关的记录。
5 )使用系统存储过程查瞧创建的触发器。
图一:创建一个存储过程,查瞧Tno为1(根据实际情况取)的教师的信息,包括该教师的姓名,sal图二执行1中创建的存储过程。
图三使用输入参数创建题1中的存储过程。
图四执行3中创建的存储过程,(按位置传递参数)图五执行3中创建的存储过程通过参数名传递参数;图六使用系统存储过程查瞧3中创建的存储过程图七删除3中创建的存储过程。
图八在Teacher上创建一个触发器,该触发器被操作DELETE所触发,且要求触发触发器的DELETE语句在执行被取消。
存储过程、触发器练习1、在学生选课数据库中，创建一存储过程deptmale，查询指定系的男生人数，其中系为输入参数，人数为输出参数。
create proc p_dept@dept char(20),@mannum int outputasselect @allcre=count(sno) from studentwhere sdept=@dept and ssex='男'declare @num intexec p_dept '计算机系',@num outputprint @num2、在s_c数据库中，创建一个存储过程totalcredit,根据输入的学生姓名，计算其总学分。
(使用输出参数)。
并执行该存储过程。
create proc p_cou@name char(10),@allcre int outputasselect @allcre=sum(ccredit)from student,course,scwhere student.sno=sc.sno and o=oand sname=@name group by sc.snodeclare @asum intexec p_cou '刘晨',@asum outputprint @asum3、创建一更新触发器upd_grade，设置sc表的grade字段不能被更新，并显示信息“学生成绩不能被修改，请与教务处联系”。
CREATE TRIGGER mes_scON scFOR UPDATEASIF UPDATE（grade）BEGINROLLBACK TRANPRINT '学生成绩不能被修改，请与教务处联系'END4、创建一个insert触发器uninsertstu，当在student表中插入一条新纪录时，如果是“计算机系”的学生，则撤销该插入操作，并返回“此系人数已满，不能再添加”信息。
存储过程、触发器练习1、在学生选课数据库中，创建一存储过程deptmale，查询指定系的男生人数，其中系为输入参数，人数为输出参数。
create proc p_dept@dept char(20),@mannum int outputasselect @allcre=count(sno) from studentwhere sdept=@dept and ssex='男'declare @num intexec p_dept '计算机系',@num outputprint @num2、在s_c数据库中，创建一个存储过程totalcredit,根据输入的学生姓名，计算其总学分。
(使用输出参数)。
并执行该存储过程。
create proc p_cou@name char(10),@allcre int outputasselect @allcre=sum(ccredit)from student,course,scwhere student.sno=sc.sno and o=oand sname=@name group by sc.snodeclare @asum intexec p_cou '刘晨',@asum outputprint @asum3、创建一更新触发器upd_grade，设置sc表的grade字段不能被更新，并显示信息“学生成绩不能被修改，请与教务处联系”。
CREATE TRIGGER mes_scON scFOR UPDATEASIF UPDATE（grade）BEGINROLLBACK TRANPRINT '学生成绩不能被修改，请与教务处联系'END4、创建一个insert触发器uninsertstu，当在student表中插入一条新纪录时，如果是“计算机系”的学生，则撤销该插入操作，并返回“此系人数已满，不能再添加”信息。
实验8 存储过程和触发器1．实验目的（1）掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE 创建存储过程的方法和步骤。
（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。
（3）掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE 修改存储过程的方法。
（4）掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。
（5）掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。
（6）掌握引发触发器的方法。
（7）掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。
（8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。
2．实验内容及步骤（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示26个小写字母。
CREATE PROCEDURE letters_printASDECLARE @count intSET @count=0WHILE @count<26BEGINPRINT CHAR(ASCII('a')+ @count)SET @count=@count +1END单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print。
使用EXECUTE命令执行letters_print存储过程。
（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。
CREATE PROCEDURE stu_info @name varchar(40)ASSELECT a.学号,姓名,课程编号,分数FROM student_info a INNER JOIN grade taON a.学号= ta.学号WHERE 姓名= @name使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。
上机实验七1．声明一个字符串型的局部变量，并对其赋值：‘我的变量’，然后显示出此值。
declare @a char(10)set @a='我的变量'--select @a as 变量的值print @a2．编程实现如下功能：1）声明两个整形的局部变量：@i1和@i2,对@i1赋初值：10，@i2的值为：@i1乘以5，再显示@i2的结果值。
declare @i1 int,@i2 intset @i1=10set @i2=@i1*5print @i22）用While语句实现5000减1，减2，……一直减到50的计算，并显示最终的结果。
declare @sum int,@i intset @sum=5000set @i=1while(@i<=50)beginset @sum=@sum-@iset @i=@i+1endprint @sum3）输出100以内的素数。
declare @i smallint,@j smallint,@k smallintset @i=2while(@i<=100)beginset @k=0set @j=2while(@j<@i)beginif(@i%@j=0)beginset @k=1set @j=@Iendset @j=@j+1endif @k=0print @Iset @i=@i+1end4）将字符数在20以内的字符串变量C的值逆序输出。
要求输出界面为：declare @i varchar(20),@j int,@k varchar(20)set @i='jnbugswi'set @j=1set @k=''while @j<=len(@i)beginset @k=substring(@i,@j,1)+@kset @j=@j+1endprint '字符串C的值:'+@iprint 'C的逆序字符串:'+@k5）从SC表中查询所有学生的选课成绩情况，分别统计各分数段人数，并输出统计结果。
1 实验八 存储过程和触发器 一、目的与要求 1. 正确理解存储过程和触发器的概念、功能和类型； 2. 掌握使用SSMS和T-SQL语句创建和管理存储过程和触发器。 二、上机准备 利用教师提供的XSGL数据库，该库中有3个表：student，course，sc。 三、实验内容 1. 将教师提供的XSGL数据库附加到本地数据库中。
2. 分别使用SSMS和T-SQL语句创建和管理存储过程和触发器。 （1）创建一个存储过程proc_stud_sc_info，查询学号、姓名、性别、系、课程号和成绩等信息。 use xsgl go create procedure proc_stud_sc_info as select student.sno,sname,sex,dept,cno,grade from student left join sc on student.sno=sc.sno go
（2）创建一个存储过程proc_stud_info，根据输入的学号，查询学生的基本信息。 use xsgl go create procedure proc_stud_info @sno char(5)='95001' as select * from student where sno=@sno go
（3）创建一个存储过程proc_stud_birth_year，根据输入的学生姓名，计算该学生的出生年份。 use xsgl go create procedure proc_stud_birth_year @sname varchar(6)='张立' as select sname,year(getdate())-age as 出生年份 from student where sname=@sname go 2
（4）创建一个存储过程proc_GetAvgScByCno，根据输入的课程号返回该课程的平均成绩。 use xsgl go create procedure proc_GetAvgScByCno @cno char(3)='1' as select @cno as 课程号,avg(grade) as 平均成绩 from sc where cno=@cno go 或
use xsgl go create procedure proc_GetAvgScByCno @cno char(3)='1',@aver smallint output as select @aver=avg(grade) from sc where cno=@cno go
（5）创建一个Insert触发器tri_StudentInsert，当向student表插入一条记录时，向客户端显示一条“您正在插入学生的数据”的信息。 create trigger tri_StudentInsert on student for insert as print '您正在插入学生的数据' go
--验证插入 --查看插入前状态 select * from student --执行插入 insert into student values('95006','范冰冰','f',35,'IS') --查看插入后状态 select * from student
（6）创建一个Update触发器tri_StudentUpdate，当修改student表的记录时，向客户端显示一条“原姓名与新姓名”的消息，并执行修改语句，验证触发器的运行。 create trigger tri_StudentUpdate on student for update as begin declare @oldname varchar(6),@newname varchar(6) select @oldname=sname from deleted select @newname=sname from inserted print '原姓名为：'+@oldname+space(3)+'新姓名为：'+@newname end go
--验证更新 --查看更新前状态 select * from student --执行更新 3
update student set sname='张小立' where sname='张立' --查看更新前状态 select * from student
（7）创建一个Delete触发器tri_StudentDelete，当学生表的数据删除时，该数据被自动地增加到毕业生表（需要新建）中，并删除student表中的数据，并返回毕业生表中的数据，验证触发器的操作。 --创建毕业生表 create table grad_student (sno char(5),sname varchar(6),sex char(1),age tinyint,dept char(2)) Go
--创建触发器 create trigger tri_StudentDelete on student for delete as begin declare @sno char(5),@sname varchar(6),@sex char(1),@age tinyint,@dept char(2) if exists(select * from Deleted) begin select @sno=sno,@sname=sname,@sex=sex,@age=age,@dept=dept from Deleted insert into grad_student values(@sno,@sname,@sex,@age,@dept) end end
go
--返回毕业生表中的数据，验证触发器的操作 delete from student where sname='范冰冰' select * from grad_student go
（8）创建一个Instead of触发器tri_ScInsert,当向sc表插入数据时，先检索student表和course表中是否有该同学以及该课程号的课程。如果没有，给出提示“学生表中没有该学号的同学！课程表中没有该课程号的课程”；有，则插入该数据，并执行插入语句测试触发器的动作。 create trigger tri_ScInsert on sc instead of insert as begin declare @sno char(5),@cno char(3),@grade tinyint select @sno=sno,@cno=cno,@grade=grade from inserted if (not exists(select sno from student where sno=@sno)) and (not exists(select cno from course where cno=@cno)) print '学生表中没有该学号的同学！课程表中没有该课程号的课程!' else if (exists(select sno from student where sno=@sno)) and (exists(select cno from course where cno=@cno)) begin insert into sc values(@sno,@cno,@grade) print '已成功插入!' end end
--插入数据，测试触发器的动作。 insert into sc values('95002','3',null) --成功插入数据 4
insert into sc values('95008','8',null) --显示“学生表中没有该学号的同学！课程表中没有该课程号的课程!”
3. 使用SSMS和T-SQL语句完成如下操作： （1）创建一个库存管理数据库，并添加如下三个基本表：
入库表 出库表 库存表 --创建数据库和3个表，并为库存表插入数据 create database 库存管理 on primary (name='库存管理',filename='h:\hyr\库存管理.mdf') log on (name='库存管理_log',filename='h:\hyr\库存管理_log.ldf') Go
use 库存管理 go create table 库存表 (商品名 varchar(20) primary key, 库存数量 int null) go create table 入库表 (入库号 int identity primary key, 商品名 varchar(20) null, 入库数量 int null, foreign key(商品名) references 库存表(商品名)) go create table 出库表 (出库号 int identity primary key, 商品名 varchar(20) null, 出库数量 int null, foreign key(商品名) references 库存表(商品名))
--为库存表插入数据 insert into 库存表 values('电视机',0)
