四、SQLSERVER中存储过程的使用场景

• 当 一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;
• 当在一个事务的完成需要很复杂的商业逻辑时(比如&＃xff0c;对多个数据的操作&＃xff0c;对多个状态的判断更改等)要考虑;
• 还有就是比较复杂的统计和汇总也要考虑。

优点&＃xff1a;

• 减少了服务器/客户端网络流量

过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量&＃xff0c;因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装&＃xff0c;每个单独的代码行都不得不跨网络发送。

• 更强的安全性

1、恶意用户看不到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
2、使用过程参数有助于避免 SQL 注入攻击。由于参数输入被视为文本值而不是可执行代码&＃xff0c;因此攻击者很难将命令插入 Transact-SQL 语句

• 代码的重复使用

任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性

• 更容易维护

对于基础数据库中的任何更改&＃xff0c;只有过程是必须更新的。 应用程序层保持独立&＃xff0c;并且不必知道对数据库布局、关系或进程的任何更改的情况。

• 提高了性能

默认情况下&＃xff0c;在首次执行过程时将编译过程&＃xff0c;并且创建一个执行计划&＃xff0c;供以后的执行重复使用。 因为查询处理器不必创建新计划&＃xff0c;所以&＃xff0c;它通常用更少的时间来处理过程。
注意&＃xff1a;如果过程引用的表或数据有显著变化&＃xff0c;则预编译的计划可能实际上会导致过程的执行速度减慢。 在此情况下&＃xff0c;重新编译过程和强制新的执行计划可提高性能。

缺点&＃xff1a;

• 随着SQL行数的增加&＃xff0c;维护复杂度呈线性提升
• 无法调试&＃xff0c;迭代过程中风险较高
• 过多的使用存储过程会降低系统的移植性

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name[ { &＃64;parameter data_type } [ NULL | NOT NULL ] [ &＃61; 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创建存储过程&＃xff08;修改存储过程把CREATE改为ALTER&＃xff09;

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 &＃64;LastName nvarchar(50), &＃64;FirstName nvarchar(50)
AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName &＃61; &＃64;FirstName AND LastName &＃61; &＃64;LastName AND EndDate IS NULL;

执行存储过程

EXECUTE HumanResources.uspGetEmployeesTest2 N&＃39;Ackerman&＃39;, N&＃39;Pilar&＃39;;
-- Or
EXEC HumanResources.uspGetEmployeesTest2 &＃64;LastName &＃61; N&＃39;Ackerman&＃39;, &＃64;FirstName &＃61; N&＃39;Pilar&＃39;;

删除存储过程

DROP PROCEDURE [<stored procedure name>]

重命名存储过程

EXEC sp_rename &＃39;old_procedurename&＃39;, &＃39;new_procedurename&＃39;;