存储过程

存储过程是一组SQL语句集合，该组语句用于完成特定的功能。存储过程在数据库中经过第一次编译后再次调用则不需要再次编译，用户只需通过使用存储过程并给定参数即可进行操作。

总的来讲可以分为3大类：
➢系统存储过程
➢用户存储构成
➢扩展存储过程

存储过程通过T-SQL语句进行设定。在声明存储过程中可以对变量、条件判断语句等其他编程功能进行设置。

 

优点：
（1）使用存储过程可以加快系统的运行速度，因为使用存储过程只需要在第一次进行时编译，再次使用则不需要重新编译。
（2）使用存储过程可以将复杂的数据库进行封装，对操作流程进行简化，例如对多个表的更改和删除等。
（3）可实现模块化的程序设计，存储过程可以多次调用，有着统一的数据接口，增加应用程序的可维护性。
（4）由于用户不能直接操作存储过程中所引用的对象，增加了数据访问的安全性。
（5）存储过程减轻了网络流量，对于同一个针对数据库对象的操作，如果这一操作所涉及的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。

 

1、使用CREATE PROCEDURE语句创建存储过程

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

➢procedure_name：新建存储过程名。过程名必须符合SQL Server 2016标识符规则，并且对于数据库及所有者必须唯一。
➢;number：可选整数，用来对同名过程进行分组，而使用DROP PROCEDURE语句可以对同组的过程一起删除。
➢@parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100个参数。
➢datatype：参数的数据类型。SQL Server 2016中所有的数据类型都可以作为存储过程的参数。但是，cursor数据类型只能作为OUTPUT参数。如果指定了cursor作为数据类型，那么就必须同时指定VARYING和OUTPUT关键字。
➢ VARYING：指定作为输出参数支持的结果集。
➢default：参数默认值。如果设置了默认值，可以在不指定参数的情况下执行过程。默认值只能是常量或NULL。
➢OUTPUT：表示参数为输出参数。可以返回给EXEC[UTE]，使用OUTPUT可以将信息返回调用过程。
➢FOR REPLICATION：设定不能再订阅服务器上对存储过程进行复制操作。
➢AS：指定存储过程要执行的操作。
➢sql_statement：存储过程中要包含的T-SQL语句，但有一定限制。

创建存储过程实例

1、创建查看xsxk数据库中xs表的存储过程

CREATE PROCEDURE xs_proc
AS 
SELECT * FROM xs

2、创建统计xsxk数据库中xs表内男同学个数的存储过程

CREATE PROCEDURE count_proc
AS 
SELECT COUNT (*) AS 男同学 FROM xs WHERE 性别='男'

3、创建一个存储过程，可以根据用户输入的姓名得到该记录的相关信息

CREATE PROCEDURE QueryByName 
@name char(8)
AS
SELECT * FROM xs WHERE "姓名"=@name

4、创建一个存储过程，根据用户输入的班级返回班级总共有多少人

CREATE PROCEDURE QueryGrade
@s_grade char(10)='14信管',
@grade_count INT OUTPUT
AS
SELECT @grade_count=COUNT(*) FROM xs WHERE 班级=@s_grade

尽量不要创建任何使用sp作为前缀的存储过程。SQL Server 使用sp前缀指定系统存储过程。sp开头的存储过程可能会与以后的某些系统过程发生冲突。

 

2、执行存储过程

[ [ EXEC [ UTE ] ]
{ 
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var 
}
[ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] 
[ ,...n ]
[ WITH RECOMPILE ]

➢@return_status：可选整型变量，用于存储模块返回的状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明。在用于调用标量值用户定义函数时@return_status变量可以为任意数据类型。
➢procedure_name：指定要调用存储过程的名称。
➢;number：可选整数，可用于同名过程的分组。该参数不能在扩张存储过程中使用。
➢@procedure_name_var：定义局部变量的名称，代表模块名称。
➢@parameter：存储过程中所使用的参数，与模块中定义的相同。
➢ value：传递给模块或传递命令的参数值。如果参数名没有被指定，参数值要按在模块中定义的顺序提供。
➢@variable：用于存储参数或返回参数变量。
➢OUTPUT：指定模块或命令字符串返回一个参数。该模块或命令字符串中的匹配参数也必须已使用关键字OUTPUT创建。使用游标变量作为参数时使用该关键字。
➢DEFAULT：根据模块的定义，提供参数的默认值。
➢WITH RECOMPILE：执行存储过程后，强制编译、使用和放弃新计划。如果该模块存在现有查询计划，则该计划保留在缓存。如果所提供的参数为非典型参数或数据有很大的改变，使用该选项。

执行存储过程实例

1、执行1、2的存储过程

CREATE PROCEDURE xs_proc
AS 
SELECT * FROM xs

CREATE PROCEDURE count_proc
AS 
SELECT COUNT (*) AS 男同学 FROM xs WHERE 性别='男'

EXEC xs_proc
EXEC count_proc

2、执行3的存储过程

CREATE PROCEDURE QueryByName 
@name char(8)
AS
SELECT * FROM xs WHERE "姓名"=@name

EXECUTE QueryByName 郝若馨

 3、执行4的存储过程

CREATE PROCEDURE QueryGrade
@s_grade char(10)='14信管',
@grade_count INT OUTPUT
AS
SELECT @grade_count=COUNT(*) FROM xs WHERE 班级=@s_grade

DECLARE @grade_count INT;
DECLARE @s_grade char(10) = '14信管';
EXEC QueryGrade @s_grade,@grade_count OUTPUT
SELECT '该班级一共有'+ LTRIM(STR(@grade_count))+'人'

 

 3、使用 SQ 语句查看存储过程

SELECT OBJECT_DEFINITION(OBJECT_ID('QueryByName'))
EXEC sp_help QueryByName
EXEC sp_helptext QueryByName

 

4、T-SQL修改存储过程

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
{ @parameter [ type_schema_name. ] data_type }
 [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
[ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

ALTER PROCEDURE [dbo].[count_proc]
AS 
SELECT COUNT (*) AS 女同学 FROM xs WHERE 性别='女'

 

5、重命名存储过程

sp_rename oldObjectName,newObjectName

 

6、删除存储过程

DROP {PROC | PROCEDURE} {[schema_name.] procedure} [,…n]

DROP PROCEDURE xs_proc

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

 

练习：

1、创建存储过程 Pro_ select_User，实现查询User_Info数据库 user表数据。仅显示手机号、用户标识字段即可。 

CREATE PROCEDURE Pro_ select_User
@UserId char(10)=' ',
@UserName varchar(50)=' ',
@Phone varchar(11)=' '
AS
SELECT @UserId,@UserName,@Phone FROM User

2、修改存储过程 Pro_ select_User 添加省份字段。 

ALTER PROCEDURE Pro_ select_User
@UserId char(10)=' ',
@UserName varchar(50)=' ',
@Phone varchar(11)=' ',
@Province varchar(30)=' '
AS
SELECT @UserId,@UserName,@Phone,@Province FROM User

3、重命名存储过程 Pro_ select_User 为存储过程 Pro_ select_UserInfo。

sp_rename Pro_ select_User,Pro_ select_UserInfo