存储过程的使用场景
- 当 一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;
- 当在一个事务的完成需要很复杂的商业逻辑时(比如,对多个数据的操作,对多个状态的判断更改等)要考虑;
- 还有就是比较复杂的统计和汇总也要考虑。
使用存储过程的优点和缺点
优点:
- 减少了服务器/客户端网络流量
过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量,因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装,每个单独的代码行都不得不跨网络发送。
- 更强的安全性
1、恶意用户看不到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
2、使用过程参数有助于避免 SQL 注入攻击。由于参数输入被视为文本值而不是可执行代码,因此攻击者很难将命令插入 Transact-SQL 语句
- 代码的重复使用
任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性
- 更容易维护
对于基础数据库中的任何更改,只有过程是必须更新的。 应用程序层保持独立,并且不必知道对数据库布局、关系或进程的任何更改的情况。
- 提高了性能
默认情况下,在首次执行过程时将编译过程,并且创建一个执行计划,供以后的执行重复使用。 因为查询处理器不必创建新计划,所以,它通常用更少的时间来处理过程。
注意:如果过程引用的表或数据有显著变化,则预编译的计划可能实际上会导致过程的执行速度减慢。 在此情况下,重新编译过程和强制新的执行计划可提高性能。
缺点:
- 随着SQL行数的增加,维护复杂度呈线性提升
- 无法调试,迭代过程中风险较高
- 过多的使用存储过程会降低系统的移植性
存储过程的 Transact-SQL 语法
CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
[ OUT | OUTPUT ] [READONLY]
] [ ,... n ]
WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]
创建存储过程
使用 Transact-SQL创建存储过程(修改存储过程把CREATE改为ALTER)
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
执行存储过程
EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';
删除存储过程
DROP PROCEDURE [<stored procedure name>]
重命名存储过程
EXEC sp_rename 'old_procedurename', 'new_procedurename';