存储过程

**存储过程：**是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是可以访问关系数据库的应用程序。
通常，存储过程用作验证数据和控制对数据库的访问。如果某些数据处理操作需要执行多个SQL语句，则此类操作将作为存储过程实现。调用存储过程时，必须使用CALL或EXECUTE语句。
存储过程可以返回结果（例如SELECT语句的结果）。这些结果可以由其他存储过程或应用程序使用。用于编写存储过程的语言通常支持控制结构，例如if，while，for等。
根据所使用的数据库系统，可以使用多种语言来实现存储过程。

优点：
1允许模块化程序设计（创建一次多次使用）
2允许更快执行
3减少网络流量
4更好的安全机制

格式：
DELIMITER //
CREATE PROCEDURE 储存名([ IN ,OUT ,INOUT ]?参数名?数据类形…)
BEGIN
SQL语句
END //
DELIMITER ;

调用过程:

用call 过程名（ ）

查看所有的存储过程show procedure status;
查看创建的存储过程show create procedure 过程名;
删除过程 drop procedure 过程名

In 表示参数从外部传入到里面使用（过程内部使用）
Out 表示参数从过程里边把数据保存到变量中，交给外部使用，所有传入的必须是变量 如果说传入的out变量本身在外部有数据，那么在进入过程之后，第一件事就是被清空,设为null
Inout 数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返回给外部

触发器

触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。它又不同于存储过程，触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

触发器是数据库中的表/视图发生某些特定事件时自动执行的过程（代码段）。触发器主要用于维护数据库中的完整性。触发器还用于实施业务规则，审核数据库中的更改以及复制数据。最常见的触发器是在操作数据时触发数据操作语言（DML）触发器。某些数据库系统支持非数据触发器，这些触发器在发生数据定义语言（DDL）事件时触发。这些触发器可以特别用于审计。Oracle数据库系统支持模式级触发器。

作用：
1.可在写入数据表前，强制检验或转换数据
2.触发器发生错误时，异动的结果会被撤销

格式
DELIMITER //
Create trigger 触发器名字 触发时机 触发事件 on 表 for each
row
Begin
操作的内容
End //
DELIMITER ;

触发对象 ：on 表 for each row 触发器绑定实质是表中的所有行，因此当每一行发生改变的时候，就会触发触发器 。

触发时机：每张表中对应的行都会有不同的状态，当SQL 指令发生的时候，都会令行中的数据发生改变，每一行总会有两个状态。操作数据之前(before)，操作数据(after)之后

触发事件:
Mysql中触发器针对的目标是数据发生改变，对应的操作只有（增，删，改）查询不发生数据的改变，
所以查询没有触发事件

注意事项:
一张表中，每一个触发器时机绑定的触发事件对应的触发器类型只能有一个；
一张表中只能有一个after insert 触发器 因此，一张表中最多的触发器只能有六个。

存储过程和触发器的区别

（1）存储过程是一组已创建并存储在数据库中的SQL语句。所以我们可以一遍又一遍地重用代码。而触发器是一种特殊的不是由用户直接调用存储过程，创建触发器时，会定义在针对特定表或列进行特定类型的数据修改时触发。
（2）用户可以使用Execute或Exec语句来直接调用或执行存储过程，而无法直接调用或执行触发器。触发相关事件时，只会自动执行触发器。
（3）存储过程可以采用输入参数，而触发器中不能将参数作为输入我们不能将参数作为输入传递给触发器。
（4）存储过程可以返回零或n值，触发器无法返回值。
（5）我们可以在存储过程中使用事务，触发器内不允许进行事务处理
（6）存储过程通常用于执行用户指定的任务，触发器通常用于审计工作

本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客

实验9：存储过程实验

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

SQL语句代码

--1.例1
use 学生作业管理数据库;
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_course
as
	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_course1
execute student_course1 '于兰兰';

--创建一个存储过程用于向学生表中插入记录
create procedure student_insert
@学号 int,@姓名 nvarchar(10),@性别 nvarchar(2),@专业班级 nvarchar(10),@出生日期 nvarchar(20),@联系电话 nvarchar(20)
as
	INSERT into 学生表
	values(@学号,@姓名,@性别,@专业班级,@出生日期,@联系电话);

--执行student_insert
execute student_insert '007','阿刚','男','电子06','2000-1-1','13333333333';


--创建存储过程，若没有给出学生姓名，则返回所有学生情况
create procedure student_course2
@StudentName nvarchar(20)=null
as
	if @StudentName is null
	begin 
		select 学生表.学号,姓名,课程名,作业1成绩
		from 学生表,课程表,学生作业表
		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号
	end
	else
	begin
		select 学生表.学号,姓名,课程名,作业1成绩
		from 学生表,课程表,学生作业表
		where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname;
	end

execute student_course2 '张志国';
execute student_course2;			--使用默认参数值


--使用输出参数
CREATE procedure student_count
@CourseName varchar(20),
@StudentSum int output
as
	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) output
as
	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
@商品编号 int
as
	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 output
as 
	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_Orders2
execute Goods_Orders1 @指定货品;  --报错
create procedure Goods_Orders2
@指定货品 nvarchar(20)=null
as
	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=@指定货品
	end
execute Goods_Orders2;
execute Goods_Orders2 @指定货品='牙刷';

--创建存储过程Goods_OrderSum，来获得某个货品的订单总额（使用输入输出参数）
alter PROCEDURE Goods_OrderSum
@货品名称 nvarchar(20),@订单总额1 float output
as
	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 delete
as
	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 insert
as
	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 update
as 
	if update(性别)
	begin
		print '禁止对学生学号修改'
		rollback transaction
	end;
--查看效果
select * from 学生表;
update 学生表 set 性别='男' where 性别='女' and 学号=7;
select * from 学生表;

--例12 在学生作业表上创建触发器，当一次向学生作业表中添加多个记录时，删除学号在学生表中不存在的记录，
--从而保证数据的一致性，注意，不能在学生作业表中定义外键约束
create trigger sc_insert1 on 学生作业表 
	after insert
as 
	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_view
instead of insert
as
	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 delete
as
	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 INSERT
AS 
	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 Orders
after insert
as 
	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 UPDATE
as
	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 INSERT
AS
	IF (SELECT COUNT(*) from Goods,inserted
		WHERE Goods.GoodId=inserted.GoodID)=0
	BEGIN
		print '这种货物不存在'
		rollback transaction
	END;

本文最初由security9968发布于security9968的csdn博客，禁止任何形式的剽窃行为
转载原创文章请注明，转载自：security的博客