SQL之存储过程

存储过程就是封装的一条或多条SQL语句，相当于函数，批处理等概念。是一种SQL语言的代码封装和重用。
为什么需要存储过程？
因为简单、安全、高性能。

简单：如果我们的常用需求是复杂的且多变的，即SQL语句执行顺序是不固定的。那么我们可以把这些语句封装成存储过程。当需要用时，直接调用存储过程就可以，不需要再写复杂的SQL语句。另外，如果某些表名，列名，逻辑发生变化，只需要管理员修改存储过程的内容即可，每个使用它的人不需要做出改变。
安全：封装存储过程后，只需要调用存储过程，大家都一样，犯错的概率低。如果每个人都写自己的SQL语句，很有可能某些人出现错误，导致数据不一致。同时，这也可以防止某些人访问底层表，无意有意地篡改底层数据。
高性能：存储过程是编译过后存在数据库中，速度快。并且节省应用程序和数据库服务器之间的流量，因为不需要复杂的语句，只需要过程名和参数即可。

当然，存储过程缺点如下：
存储过程不可移植，每个数据库不一致。比如SQLite根本就不支持存储过程；
第二，编写复杂逻辑的存储过程需要丰富经验，如果没写好，调试很困难，后期维护也难。

存储过程的简单使用

创建存储过程

create procedure mypro(in a int,in b int,out sum int) 
begin 
set sum = a+b; 
end;
1
2
3
4

调用存储过程

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果
1
2

参数问题，存储过程参数有三种类型，IN代表传入的参数，可以是常量或变量，OUT是返回参数，只能是变量。INOUT既表示传入也表示传出，只能是变量。
通过select显示结果。注意SQL sever中局部变量必须以@开头。

create procedure mypro1()
begin
declare name varchar(20);
set name = 'tom';
select * from studentinfo where studentname = name;
end;
-- 调用过程
call mypro1();
1
2
3
4
5
6
7
8

declare代表声明一个局部变量，set给局部变量赋值。

当然，存储过程里也可以加判断，循环等语句。就跟写函数一样。具体需要根据业务需求编写。

触发器其实也是一个特殊的存储过程。
将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚。常常用于强制业务规则（或者安全性）和数据完整性。
注意，它比约束提供了更强大的约束，因为它可以利用引用其它表中的列，例如触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作。触发器也可以根据数据修改前后的表状态，再行采取对策。。
DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中，我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后，它们也就会被自动删除：INSERED表用于存放你在操件insert、update、delete语句后，更新的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表。

create trigger OrderDetailUpd
on OrderDetail for insert
as
begin
	declare @ProductQuantity int,@productStock  numeric(7,2),@productMinstock  numeric(7,2)
	select @ProductQuantity = quantity from OrderDetail
	select @productMinstock = productMinstock,@productStock = productStock from Product
	if exists (select @ProductQuantity from inserted where @ProductQuantity > @productStock)
		rollback
	else
	begin
		update Product
		set @productStock = @productStock - @ProductQuantity
		if (@productStock < @productMinstock)
			print('当前库存量低于该产品最低库存量，请增加库存！')
	end
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这个例子展示了触发器的作用，比如如果订单数量超过库存数量，可以利用触发器回滚，防止数据不一致。语法和存储过程类似。