实验7存储过程和触发器
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．实验内容及步骤
请先附加studentsdb数据库，然后完成以下实验。
（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示个小写字母。
答：
（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。 语句：
CREATE PROCEDURE stu_info
@name varchar(40)='刘卫平'--将@name值设为默认值'刘卫平' AS
答： 请完善 执行
EXEC stu_info
EXEC stu_info '马东'
（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。
答：
（4）使用grade表。
①创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
②执行存储过程stu_g_r，输入学号'0002'。 ③显示'0002'号学生的平均成绩。 语句：
--select * from grade CREATE PROCEDURE stu_g_r @sid char(4) ,
答： 请完善 AS
答： 请完善 执行
DECLARE @point decimal(3,1)
EXEC stu_g_r '0002' ,@point output
select @point 各门课程的平均成绩
（5）使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_g_r。
答：
（6）输入以下代码，复制student_info表命名为stu2，为stu2表创建一个触发器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列数据的最大值加。 语句：
--复制student_info表命名为stu2
SELECT * INTO stu2 FROM student_info GO
--为stu2表创建一个INSERT型触发器stu_tr CREATE TRIGGER stu_tr ON stu2 FOR INSERT AS
DECLARE @max char(4)
SET @max=(SELECT MAX(学号) FROM stu2) SET @max=@max+1
UPDATE stu2 SET 学号=REPLICATE('0',4-len(@max))+@max--replicate(要重复的字符，重复的次数)
FROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号
select * from stu2
执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。
在查询设计器的编辑窗口输入以下代码：
INSERT INTO stu2(学号,姓名,性别) VALUES('0001','张主','女')
运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？ （7）自己举例，说明 INSTEAD OF触发器与AFTER触发器不同点。
答：
实验名称 实验日期 实验目的 存储过程和触发器 2013年6月5日 掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。 掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。 掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。 掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。 掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。 掌握引发触发器的方法。 掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。 掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。 实验环境 实验内容 Windows 7 ，SQL Server 2008 按实验步骤完成存储过程和触发器的创建。 实验中要求在查询设计器中用语句完成的实验，请在这里填写他们的语句，并标上题号。 (1) CREATE PROCEDURE letters_print AS DECLARE @count int SET @count=0 WHILE @count<26 BEGIN PRINT CHAR(ASCII('a')+ @count) SET @count=@count +1 END exec letters_print (2) CREATE PROCEDURE stu_info @name varchar(40)='刘卫平'--将@name值设为默认值'刘卫平' AS select 学号,姓名,课程编号,分数 from student_info a inner join grade b on a.学号=b.学号 where 姓名=@name 执行 EXEC stu_info EXEC stu_info '马东' (3) sp_rename stu_info,stu_g (4) --select * from grade CREATE PROCEDURE stu_g_r @sid char(4) , @point decimal(3,1) output AS select @point=AVG(分数) from grade group by 学号 having 学号=@sid DECLARE @point decimal(3,1) EXEC stu_g_r '0002' ,@point output select @point 各门课程的平均成绩 （5）drop proc stu_g_r （6） --复制student_info表命名为stu2 SELECT * INTO stu2 FROM student_info GO --为stu2表创建一个INSERT型触发器stu_tr CREATE TRIGGER stu_tr ON stu2 FOR INSERT AS DECLARE @max char(4) SET @max=(SELECT MAX(学号) FROM stu2) SET @max=@max+1 UPDATE stu2 SET 学号=REPLICATE('0',4-len(@max))+@max--replicate(要重复的字符，重复的次数) FROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号 select * from stu2 试验思考 回答以下问题： (1) 不带参数，带参数的存储过程各举一例。 1、无参数的存储过程。 CREATE PROC proc_GetAllScore AS SELECT a.学号,a.姓名, b.课程号,b. 成绩 FROM 学生表a JOIN 成绩表b ON a.学号=b.学号 EXEC proc_GetAllScore 2、带一个输入参数的存储过程举例:用于查询某学生的各科成绩的存储过程 CREATE PROCEDURE proc_GetScoreBySid @sid char(10) AS SELECT a.学号,a.姓名, b.课程号,b. 成绩 FROM 学生表a JOIN 成绩表b ON a.学号=b.学号 WHERE a.学号=@sid EXEC proc_GetScoreBySid '001' （2）解说触发器的两种类型，各举例。 1、INSTEAD OF 触发器——对数据增删改时，只执行触发器中的操作，而不执行（ UPDATE、INSERT、DELETE ）操作。 例子： Create trigger 学生表修改通知 On 学生表 instead of insert As Print '学生表发生了变化' select * from deleted select * from inserted go select * from 学生表 --delete from 学生表where 姓名='刘席' --DROP TRIGGER 学生表修改通知 Insert 学生表 Values('016','刘席','男',16,'05002',60,'北京','清华大学') 2、AFTER触发器——数据增删改操作被临时存放，然后与触发器中的操作一起提交，也可以检查错误从而回退。相当于增删改操作与触发器中的操作构成一个事务。 例子：USE student IF EXISTS (SELECT name FROM sysobjects WHERE name = '学生表修改通知' AND type = 'TR') DROP TRIGGER 学生表修改通知 GO Create trigger 学生表修改通知 On 学生表 For insert,update As Print '学生表发生了变化' select * from deleted select * from inserted go select * from 学生表 --delete from 学生表where 姓名='刘席' --DROP TRIGGER 学生表修改通知 插入记录 Insert 学生表 Values('016','刘席','男',16,'05002',60,'北京','清华大学')