存储过程函数与触发器操作答案.docx

1、存储过程函数与触发器操作答案存储过程、函数与触发器操作实验一、实验目的与要求1、掌握存储过程的使用。2、掌握函数的使用。3、掌握触发器操作。二、实验平台1、操作系统：Windows XP或Windows 20032、数据库管理系统：SQL Server 2005三、实验内容一、存储过程1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。CREATE PROCEDURE Proc_StudentInfo ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息2、用EXECUTE执行Proc

2、_StudentInfo存储过程。EXECUTE Proc_StudentInfo3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息。CREATE PROCEDURE Proc_GetClassStudent1 ClassID varchar(14)AS SELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=ClassID4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为20031340000102 。CREATE PROCEDURE Proc_GetClassStudent2 Cla

3、ssID varchar(14)= 20031340000102 ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=ClassID EXECUTE Proc_GetClassStudent2 200313400001038、创建一个返回执行代码为100的存储过程。CREATE PROCEDURE Proc_GetClassStudent4 ClassID varchar(14) AS SELECT 学号,姓名,性别,班级编号,年级,籍贯 FROM 学生信息 WHERE 班级编号=ClassID RETURN 1009、执行存储过程Proc_GetC

4、lassStudent2和Proc_GetClassStudent4，并定义两个个变量存储执行返回代码。DECLARE return1 int,return2 intEXEC return1=Proc_GetClassStudent2 20031340000102EXEC return2=Proc_GetClassStudent4 20031340000102SELECT return1SELECT return210、在TeachingManageSYS中创建一个名为GetStudentScore的存储过程，用于实现：由执行该存储过程时提供的学生的Sno(学号)查询其每门课程的Score(成

5、绩)，并返回学生的Sno(学号)、Sname(姓名)、Ssex(性别)、Cname(课程的课程名)(使用“学生成绩查询”存储过程，查询学号为“010101”和“010201”的学生的成绩。)create procedure GetStudentScoreStudentID varchar(14)asselect StudentInfo.Sno 学号,Sname 姓名,Ssex 性别,Cname 课程名,Score 成绩from StudentInfo,CourseInfo,StudentCoursewhere StudentCourse.Sno=StudentInfo.Sno and Stud

6、entCourse.Cno=CourseInfo.Cnoand StudentInfo.Sno=StudentID exec GetStudentScore 010101 exec GetStudentScore 010201 11、在TeachingManageSYS中创建一个名为GetTeacherOrStudentInfo的存储过程，用于实现：由执行该存储过程时提供的查询类别(“教师”或“学生”，默认值为“学生”)和学生姓名或教师姓名，查询学生或教师信息。(使用教师姓名“邵军”和学生姓名“付佳燕”分别执行存储过程)create procedure GetTeacherOrStudentI

7、nfo 姓名char (10), class char(4)asif(class=教师) select * from TeacherInfo where Tname=姓名else select * from StudentInfo where Sname=姓名exec GetTeacherOrStudentInfo 邵军,教师exec GetTeacherOrStudentInfo 付佳燕,学生12、在commoditysell中创建一个名为GetFactProd的存储过程，用于实现：由执行该存储过程时提供的厂家的名称，查询其生产的产品信息，返回产品的ProdID、ProdName、UnitP

8、rice和StockAmount。(使用“四川绵阳”和“上海黄埔”两个厂家的名称执行存储过程)create proc GetFactProdFactAddr char(20)as select ProdID,ProdName,UnitPrice,StockAmountfromProductInfowhere FactAddr=FactAddr exec GetFactProd 四川绵阳exec GetFactProd 上海黄埔13、在commoditysell中创建一个名为SellProdAmount的存储过程，用于实现：由执行该存储过程时提供的商场的名称，查询其销售的产品数量，并用输出参数输

9、出销量数。(使用“双桥子商场”和“十陵商场”两个商场名称执行程存储过程)create procedure SellProdAmount EmpName char(20)as select EmporiumInfo.EmpName,sum(SellAmout) as 销售量from EmporiumSell,EmporiumInfowhere EmporiumSell.EmpID=EmporiumInfo.EmpIDand EmpName=EmpNamegroup by EmporiumInfo.EmpNameexec SellProdAmount 双桥子商场exec SellProdAmoun

10、t 十陵商场14、在教务管理系统中创建一个名为GetStudentScoreInfo的存储过程，用于实现：由执行该存储过程时提供的学生姓名，使用输出参数输出学生成绩的总分、最高分和最低分。(使用“冬云”和“张宇宏”两个学生姓名执行存储过程)create procedure GetStudentScoreInfostudentname char(8)as select sum(成绩表.成绩) as 总分,max(成绩表.成绩) as 最高分,min(成绩表.成绩) as 最低分from 成绩表,学生信息where 成绩表.学号=学生信息.学号and 学生信息.姓名=studentnamegrou

11、p by 成绩表.学号exec GetStudentScoreInfo studentname =朱志exec GetStudentScoreInfo studentname =张宇宏二、函数练习1、TeachingManageSYS创建一个内嵌表值函数chengji1，实现根据姓名查询该学生所有课程的成绩。CREATE FUNCTION chengji1(xname_in char(10)RETURNS TABLEAS RETURN (select Cname, Score from StudentCourse, StudentInfo, CourseInfoWHERE StudentInf

12、o.Sno=StudentCourse.Sno ANDCourseInfo.Cno=StudentCourse.Cno AND Sname=xname_in)select * from chengji1(魏士斌)2、TeachingManageSYS创建一个内嵌表值函数TeacherCourse，实现根据输入的Tno(教师编号)该教师的姓名和所授课程的课程号。CREATE FUNCTION TeacherCourse(number char(25)RETURNS TABLEASRETURN (SELECT TnameFROM TeacherInfo WHERE Tno=numberUNION

13、SELECT CnoFROM TeachWHERE Tno=number)3、TeachingManageSYS创建一个多语句表值函数，实现查询某一课程的考试成绩。CREATE FUNCTION all_score(cname_in char(10)RETURNS all_score_tab table(Sno char(6) primary key,Sname char(10) not null,Ssex char(2),Score real)ASBEGININSERT all_score_tabSELECT a.Sno,a.Sname,a.Ssex,b.ScoreFROM StudentI

14、NFO a,StudentCourse b,CourseINFO cWHERE a.Sno=b.Sno AND b.Cno=c.Cno AND c.Cname=cname_inRETURNEND select * from all_score(数据库原理)三、触发器练习1、设计一个简单的AFTER INSERT触发器，这个触发器的作用是：在插入一条记录的时候，发出“又添加了一个学生的成绩”的友好提示。 CREATE TRIGGER score_insert ON 成绩表 AFTER INSERT AS BEGIN PRINT 又添加了一个学生的成绩 END GOinsert into 成绩表(

15、学号,课程编号,成绩) values(200130000146,51,87)2、设计一个简单的AFTER UPDATE触发器，这个触发器的作用是：在修改一条记录的时候，发出“又修改了一个学生的成绩”的友好提示。 CREATE TRIGGER score_update ON 成绩表 AFTER UPDATE AS BEGIN PRINT 又修改了一个学生的成绩 ENDupdate 成绩表set 成绩=93 where 学号=200130000146 and 课程编号=513、设计一个简单的AFTER DELETE触发器，这个触发器的作用是：在删除一条记录的时候，发出“又删除了一个学生的成绩”的友

16、好提示。 CREATE TRIGGER score_delete ON 成绩表 AFTER DELETE AS BEGIN PRINT 又删除了一个学生的成绩 ENDdelete 成绩表where 学号=200130000146 and 课程编号=515、在score表上创建一个instead of insert触发器，实现：当向表score插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。create trigger score_insert_insteadon 成绩表instead of insertasbeginset nocount ondeclare score in

17、tselect score=成绩 from insertedif(score100) print 分数不合理else insert into 成绩表(学号,课程编号,成绩) select 学号,课程编号,成绩from inserted endinsert into 成绩表(学号,课程编号,成绩) values(200130000146,51,87)6、在score表上创建一个instead of delete触发器，实现：当从表score删除记录时检查各课程的成绩是否为空，如果为空就不允许进行删除操作，否则允许。create trigger score_delete_insteadon 成绩表

18、instead of deleteasbeginset nocount ondeclare id int,score intselect id=编号,score=成绩from deletedif(score=0) print 成绩正常，不能删除该记录else delete from 成绩表where 编号=idenddelete from 成绩表where 编号=10207、在score表上创建一个instead of update触发器，实现：当从表score更新记录时检查当前用户是否是dbo，如果不是dbo就不允许进行更新操作，否则允许。create trigger score_updat

19、e_insteadon 成绩表instead of updateasbeginset nocount ondeclare score1 int,score2 int,current_user varchar(10)select score1=成绩from insertedselect score2=成绩from deletedif(current_user !=dbo1) print 不是dbo用户不能修改学生姓名信息elseprint current_userupdate 成绩表set 成绩=score1 where 成绩=score2end update 成绩表set 成绩=93 wher

20、e 学号=200130000146 and 课程编号=518、在视图v_score创建一个instead of insert触发器，实现：从视图v_score插入记录时，提示从视图v_score插入一条记录。create view v_scoreasselect 学号,课程编号,成绩from 成绩表create trigger v_score_inserton v_scoreinstead of insertasbegininsert into 成绩表(学号,课程编号,成绩) select * from insertedprint 从视图v_score插入了一条记录endinsert into

21、 v_score values(200130000146,51,97)9、建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。 CREATE TRIGGER 禁止对数据表操作 ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 对不起，您不能对数据表进行操作 ROLLBACKALTER Table 成绩表ADD CONSTRAINT Score_CHECKCheck(课程编号=0 and 课程编号=100)10、建立一个DDL触发器，用于保护当前SQL Server服务器里所有数据库不能被删除。 CREATE TRIGGER 不允许删

22、除数据库 ON all server FOR DROP_DATABASE AS PRINT 对不起，您不能删除数据库 ROLLBACK drop database TeachingManageSYS11、给score表创建一个after insert触发器，实现：当向score插入记录时，提示向score表插入了内容为：的记录，也就是把插入表中的记录信息显示出来。CREATE TRIGGER 显示_Insert ON 成绩表 AFTER INSERT AS BEGIN Declare 编号int,学号varchar(14),课程编号int,成绩int select 编号=编号,学号=学号,课程

23、编号=课程编号,成绩=成绩from inserted print 插入的记录为：+CAST(编号 AS varchar(16)+， +学号+， +CAST(课程编号AS varchar(16)+， +CAST(成绩AS varchar(6) ENDinsert into 成绩表(学号,课程编号,成绩) values(200130000146,51,87)12、给score表创建一个after update触发器，实现：更新score表时，提示把score表内容为：的记录更新为：，也就是把更新表中的记录信息显示出来。CREATE TRIGGER 显示_update ON 成绩表 AFTER up

24、date AS Declare 编号int,学号varchar(14),课程编号int,成绩int,学号varchar(14),课程编号int,成绩int select 编号=编号,学号=学号,课程编号=课程编号,成绩=成绩from deleted select 学号=学号,课程编号=课程编号,成绩=成绩from inserted print 编号为：+CAST(编号AS varchar(16) print 修改前的记录为：+学号+， +CAST(课程编号AS varchar(16)+， +CAST(成绩AS varchar(6) print 修改后的记录为：+学号+， +CAST(课程编号A

25、S varchar(16)+， +CAST(成绩AS varchar(6)update 成绩表set 成绩=100 where 编号=102513、给score表创建一个after delete触发器，实现：当向score删除记录时，提示从score表删除了内容为：的记录，也就是把删除的记录信息显示出来。CREATE TRIGGER 显示_delete ON 成绩表 AFTER delete AS Declare 编号int,学号varchar(14),课程编号int,成绩int select 编号=编号,学号=学号,课程编号=课程编号,成绩=成绩from deleted print 删除的记

26、录为：+CAST(编号AS varchar(16)+， +学号+， +CAST(课程编号AS varchar(16)+， +CAST(成绩AS varchar(6)delete from 成绩表where 编号=104114、commoditysell库中给sell表创建一个after insert 触发器，实现：当向sell插入一条记录时，修改stock表中的stock_amount(库存数量)，值等于stock_amount-sell_amount，还有修改时间modified_date，并显示相应的提示信息：“库存量还剩余stock_amount-sell_amount”，当(stock

27、_amount-sell_amount)0时，不允许进行插入操作，即操作回滚，并显示相应的提示信息：“库存量不足，只有stock_amount”。附stock、sell表信息stock(prod_id char(10),prod_name varchar(10),unit_price int,stock_amount int,modified_date datetime)sell(order_id int identity(1,1),prod_id char(10),sell_amount int,sell_date datetime,Saler varchar(10)create table

28、stock(prod_id char(10) primary key,prod_name varchar(10),unit_price int,stock_amount int,modified_date datetime)create tablesell(order_id int identity(1,1) primary key,prod_id char(10) foreign key references stock(prod_id),sell_amount int ,sell_date datetime,Saler varchar(10)insert into stock values

29、(10001,三星手机,2999,10,2013-4-10)CREATE TRIGGER sell_Insert ON sell AFTER INSERT AS Declare prod_id int,sell_amount int,stock_amount int select prod_id=prod_id,sell_amount=sell_amount from inserted select stock_amount=stock_amount-sell_amount from stock -stock_amount=6 if(stock_amount0) begin ROLLBACK

30、transaction print 库存量还剩余，只有+CAST(stock_amount+2 AS varchar(6) end else begin print 库存量还剩余+CAST(stock_amount AS varchar(6) update stock set stock_amount=stock_amount where prod_id=prod_idend insert into sell values(10001,2,2013-4-11,101) 15、在sell表中创建一个instead of insert触发器，实现14题的功能。16、在stock表中创建一个after delete触发器，实现：当删除stock表中记录时，如果记录的stock_amount不为零，