一、存储过程的概念
存储过程类似于程序设计语言中的子程序,在
SQL Server中,将多次调用执行的代码编成一个子程序,保存在
数据库中,并由SQL Server服务器通过子程序名调用它们,这样的子程序称为——存储过程。
二、存储过程的优点:
1、模块化程序设计
*每个存储过程就是一个模块,可以封装出各种功能模块。
*创建一次,多次执行。
*保证数据的一致性。
*可独立于程序源代码而单独修改。
2、提高执行效率,改善系统性能
*存储过程在服务器端运行,执行速度快。
*系统创建存储过程是对其分析和优化,所以存储过程比T-SQL语句执行得更快。
*首次执行存储过程后将其驻留在高速缓冲存储器中。
* T-SQL语句,每次从客户端发出,再有系统对其编译和优化。
3、减少网络流量
一条执行存储过程语句可以代替多条T-SQL语句。
4、提供了一种安全机制
*用户没有对存储过程所参考的表或视图的权限,但有使用存储过程的权限,用户也可以执行。
*所以可以创建存储过程来进行各种操作,实现有限的、基于函数的表或试图访问。
三、存储过程的分类:
1、系统存储过程。
*由系统提供的存储过程,可以作为命令执行各种操作。
*系统存储过程在master数据库中创建并存储,带有sp_前缀。
*可从任何数据库中执行,而无需使用master名限定存储过程名。
*SQL Server按下列顺序查找sp_开关的存储过程
*注:同名时的处理。
*用于系统管理、用户登录管理、权限设置、数据库对象管理和数据复制等。
2、用户存储过程。
*用户数据库中创建的存储过程。
*其名称不能以sp_为前缀。
四、用户存储过程的创建及执行
1、命令方式创建语法:
CREATE RPOC[EDURE] 存储过程名 [;下标] /*定义过程名*/
[{@形参 数据类型} /*定义参数的类型*/
[VARYING][=默认值][OUTPUT]] /*定义参数的属性*/
[,…n]
[WITH | RECOMPILE | ENCRYPTION
| RECOMPILE,ENCRYPTION]
[FOR REPLICATION]
AS Transact-SQL语句[…n2] /*执行的操作*/
2、用户存储过程的执行语法:
[EXEC[UTE]]
{[@状态值=]
{存储过程名[;分组号]|@存储过程变量}
}
[@参数=]{参量值|@变量[OUTPUT]|[DEFAULT]}
[,…n]
[WITH PECOMPILE]
五、创建存储过程例子
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*语法参看相关日志*/
-- =============================================
-- Author: <Author,,Name> /*存储过程的创建人*/
-- Create date: <Create Date,,> /*存储过程的创建日志*/
-- Description: <Description,,> /*存储过程的描述*/
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here /*添加存储过程的的参数*/
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; /*使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息*/
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> /*查询语句*/
END
GO