数据库实验系列之3存储过程和触发器实验（至少包括完整的调试通过的8个存储过程和5个触发器）

数据库实验系列之3存储过程和触发器实验（至少包括完整的调试通过的8个存储过程和5个触发器）

数据库实验系列之3存储过程和触发器实验（至少包括完整的调试通过的8个存储过程和5个触发器）

目录

• 实验9：存储过程实验
• 实验10：触发器实验

实验9：存储过程实验

自拟题目完成8个存储过程的编写及调试，熟练掌握存储过程的使用。也可采用下图中作业上的题目。

SQL语句代码

--1.例1use 学生作业管理数据库;select * from 学生表;select * from 课程表;select * from 学生作业表;--先查看是否存在名字为student_course的存储过程，如果有，删除if exists (select name from sysobjects where name='student_course' and type='P')drop procedure stuent_course;--创建存储过程create procedure student_courseas	select 学生表.学号,姓名,课程名,作业1成绩	from 学生表,课程表,学生作业表	where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名='张艳'--执行存储过程execute student_course;--带输入参数的存储过程create procedure student_course1@Studentname varchar(10)as	select 学生表.学号,姓名,课程名,作业1成绩	from 学生表,课程表,学生作业表	where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname;--调用student_course1execute student_course1 '于兰兰';--创建一个存储过程用于向学生表中插入记录create procedure student_insert@学号 int,@姓名 nvarchar(10),@性别 nvarchar(2),@专业班级 nvarchar(10),@出生日期 nvarchar(20),@联系电话 nvarchar(20)as	INSERT into 学生表	values(@学号,@姓名,@性别,@专业班级,@出生日期,@联系电话);--执行student_insertexecute student_insert '007','阿刚','男','电子06','2000-1-1','13333333333';--创建存储过程，若没有给出学生姓名，则返回所有学生情况create procedure student_course2@StudentName nvarchar(20)=nullas	if @StudentName is null	begin 		select 学生表.学号,姓名,课程名,作业1成绩		from 学生表,课程表,学生作业表		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号	end	else	begin		select 学生表.学号,姓名,课程名,作业1成绩		from 学生表,课程表,学生作业表		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname;	endexecute student_course2 '张志国';execute student_course2;			--使用默认参数值--使用输出参数CREATE procedure student_count@CourseName varchar(20),@StudentSum int outputas	select @StudentSum=COUNT(*)	from 课程表,学生作业表	where 课程表.课程号=学生作业表.课程号 and 课程名=@CourseName;--对于带有输出参数的存储过程，调用时需要定义相应的变量用于接收从存储过程返回的参数值declare @StudentSum1 int;execute student_count '数据结构',@StudentSum1 OUTPUT;SELECT @StudentSum1 as 选数据结构的人数;--创建一个存储过程，输出学生的基本情况alter procedure student_query@学号 int,@姓名 nvarchar(8) output,@性别 char(2) outputas	select @姓名=姓名,@性别=性别	from 学生表	where 学号=@学号;--调用存储过程，查看基本情况declare @姓名1 nvarchar(20);declare @性别1 char(2);execute student_query '7',@姓名1 output,@性别1 output;select @姓名1 as 学生姓名,@性别1 as 学生性别;select * from 学生表;--删除存储过程是--drop procedure 存储过程名;--market数据库中use market;--存储过程shanghai,查看上海客户信息select * from Customers;insert into Customers VALUES(3,'阿美','上海');create procedure shanghai as	select * from Customers	where City='上海'execute shanghai;--存储过程Goods，查看指定商品信息，商品编号作为输入参数select * from Goods;insert into Goods values(1,'牙膏',2.5,'牙膏厂',400,'在售');insert into Goods values(2,'牙刷',5,'牙刷厂',1200,'热卖');create procedure cunchuGoods@商品编号 intas	select * from Goods	where GoodID=@商品编号;execute cunchuGoods @商品编号=2;--存储过程GoodsSum,查看指定客户的所有订单的订货总金额，客户编号作为输入参数，订货总金额作为输出参数select * from Orders;insert into Orders values(1,1,1,2,5,'2020-1-1');insert into Orders values(2,1,2,2,10,'2020-1-1');insert into Orders values(3,2,2,2,10,'2020-1-1');create procedure cunchuGoodsSum@客户编号 int,@订货总金额 float outputas 	select @订货总金额=OrderSum from Orders	where Orders.CustomerID=@客户编号;declare @订货总金额 float;    --切记勿忘声明变量execute cunchuGoodsSum 2,@订货总金额 output;select @订货总金额 as '订货总金额';--存储过程insert_Goods,向Goods表中插入一条记录select * from Goods;alter procedure insert_Goods@商品编号 int,@商品名称 nvarchar(20),@价格 float,@供货商 nvarchar(20),@库存量 int,@商品状态 nvarchar(20)as 	insert into Goods(GoodID,Gname,Price,Provider,Stocks,Status) 	values(@商品编号,@商品名称,@价格,@供货商,@库存量,@商品状态);execute insert_Goods 3,'牙刷杯',10,'牙刷杯厂',5,'即将断货';--创建存储过程Goods_Orders1，查看任何指定货品的订单情况，包括订单号，订货客户姓名以及订货数量（使用输入参数）select * from Orders;select * from Goods;select * from Customers;CREATE procedure Goods_Orders1@指定货品 nvarchar(20)as	select OrderID,Cname,Quantity from Orders,Goods,Customers	where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定货品;EXECUTE Goods_Orders1 @指定货品='牙膏';--执行存储过程，如果不给出参数则报错，如果希望不输入参数，即默认值，得到所有货品订单，则新建表Goods_Orders2execute Goods_Orders1 @指定货品; --报错create procedure Goods_Orders2@指定货品 nvarchar(20)=nullas	if @指定货品 is null	begin 		select OrderID,Cname,Quantity from Orders,Goods,Customers		where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID	end	else	begin	select OrderID,Cname,Quantity from Orders,Goods,Customers	where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定货品	endexecute Goods_Orders2;execute Goods_Orders2 @指定货品='牙刷';--创建存储过程Goods_OrderSum，来获得某个货品的订单总额（使用输入输出参数）alter PROCEDURE Goods_OrderSum@货品名称 nvarchar(20),@订单总额1 float outputas	select @订单总额1=sum(OrderSum) from Orders,Goods	where Goods.GoodID=Orders.GoodID and Gname=@货品名称;declare @订单总额1 float;execute Goods_OrderSum '牙膏',@订单总额1 output;select @订单总额1 as '订单总额';

实验10：触发器实验

自拟题目完成5个触发器的编写及调试，熟练掌握触发器的使用。也可采用下图中作业上的题目。

SQL语句代码

use 学生作业管理数据库;--例8 创建一个触发器，当 学生表 中的记录被更新时，显示表中的所有记录create trigger student_change	on 学生表 after insert,update,delete	as		select * from 学生表;--查看下变化select * from 学生表;insert into 学生表 values(1,'阿美','女','计科06','2002-1-1','13312313213');--例9 在 学生表 中创建DELETE触发器，实现对 学生表 和 学生作业表 的级联删除create trigger studentdelete on 学生表	after deleteas	delete from 学生作业表	where 学号 in		(select deleted.学号 from deleted);--查看下变化select * from 学生作业表;insert into 学生作业表 values('K001',1,99,99,99);select * from 学生作业表;delete from 学生表 where 姓名='阿美';select * from 学生作业表;--例10 在学生作业表上创建insert 触发器，当向学生作业表 中添加学生的选课记录时，--检查该学生的学号是否存在，若不存在，则不能将记录插入create trigger sc_insert on 学生作业表	after insertas	if(select count(*) from 学生表,inserted where 学生表.学号=inserted.学号)=0	begin 		print '学号不存在，不能插入'		rollback transaction	end;--查看效果insert into 学生作业表 values('K001',1,99,99,99);--例11 创建update触发器，禁止对学生表 中学生的性别进行修改create trigger student_update on 学生表	after updateas 	if update(性别)	begin		print '禁止对学生学号修改'		rollback transaction	end;--查看效果select * from 学生表;update 学生表 set 性别='男' where 性别='女' and 学号=7;select * from 学生表;--例12 在学生作业表上创建触发器，当一次向学生作业表中添加多个记录时，删除学号在学生表中不存在的记录，--从而保证数据的一致性，注意，不能在学生作业表中定义外键约束create trigger sc_insert1 on 学生作业表 	after insertas 	if(select count(*) from 学生表,inserted where inserted.学号=学生表.学号)<>@@ROWCOUNT	BEGIN		delete from 学生作业表		where 学号 not in (select 学号 from 学生表)	END;--例13 在视图上定义instead of 触发器select * from 学生表;create view birth_view(学号,姓名,性别,生日,专业班级)as 	select 学号,姓名,性别,出生日期,专业班级	from 学生表;create trigger birth_view_insert on birth_viewinstead of insertas	declare @学号 int	declare @姓名 varchar(20);	declare @性别 varchar(20);	declare @生日 varchar(20);	declare @专业班级 varchar(20);	select 	@学号=学号,@姓名=姓名,@性别=性别,@专业班级=专业班级	from inserted;	insert into 学生表(学号,姓名,性别,专业班级) values(@学号,@姓名,@性别,@专业班级);--查看效果insert into birth_view(学号,姓名,性别,专业班级) values(2,'阿红','女','软件04');use market;--第四章第五题（5）在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除select * from Customers;select * from Orders;create trigger customers_delete on Customers	after deleteas	delete from Orders	where CustomerID in (select deleted.CustomerID FROM DELETED);--第四章第五题（6）在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为即将断货（Status='即将断货'）--则不能插入该条记录select * from Customers;select * from Orders;select * from Goods;CREATE trigger orders_insert on Orders	AFTER INSERTAS 	if(select Status from Goods,inserted where Goods.GoodID=inserted.GoodID) in ('即将断货')	begin		print '即将断货，不能订购'		rollback transaction	end;	--试试效果insert into Orders values(4,3,3,2,20,'2020-2-2');--第四章第五题（7）在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量select * from Customers;select * from Orders;select * from Goods;create trigger orders_insert1 on Ordersafter insertas 	UPDATE Goods SET Stocks=Stocks-inserted.Quantity	FROM Goods,inserted	WHERE Goods.GoodID=inserted.GoodID;	--第四章第五题（8）在Orders表上建立触发器，不允许对订单日期进行修改select * from Customers;select * from Orders;select * from Goods;CREATE TRIGGER orders_create ON Orders	AFTER UPDATEas	IF UPDATE(Date)	BEGIN	RAISERROR('不能手动修改',10,1)	ROLLBACK TRANSACTION	END;--第四章第五题（9）建立触发器，实现参照完整性约束，即若在Orders表中添加一条记录时，则该订单中的商品也必须在--Goods表中存在，否则不许添加该记录；select * from Customers;select * from Orders;select * from Goods;CREATE TRIGGER orders_create1 ON Orders	AFTER INSERTAS	IF (SELECT COUNT(*) from Goods,inserted		WHERE Goods.GoodId=inserted.GoodID)=0	BEGIN		print '这种货物不存在'		rollback transaction	END;