SQL Server~存储过程

一.存储过程分类

存储过程与其他编程语言中的过程类似，它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句；向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。
SQL SERVER提供了3种类型的存储过程。各类型存储过程如下：

系统存储过程：用来管理SQL SERVER和显示有关数据库和用户的信息的存储过程。
自定义存储过程：用户在SQL SERVER中通过采用SQL语句创建存储过程。
扩展存储过程：通过编程语言（例如，C）创建外部例程，并将这个例程在SQL SERVER中作为存储过程使用。

二.存储过程的优点

存储过程的优点表现在以下几个方面：
（1）存储过程可以嵌套使用，支持代码重用。
（2）存储过程可以接受与使用参数动态执行其中的SQL语句。
（3）存储过程比一般的SQL语句执行速度快。存储过程在创建时已经被编译，每次执行时不需要重新编译。而SQL语句每次执行都需要编译。
（4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。
（5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。
（6）存储过程可以减少网络通信流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。
（7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL Injection攻击。
说明：
SQL Injection是一种攻击方法，它可以将恶意代码插入到以后将传递给SQL Server供分析和执行的字符串中。任何构成SQL语句的过程都应进行注入漏洞检查，因为SQL Server将执行其接收到的所有语法有效的查询。

三.使用向导创建存储过程

1.在SQL SERVER 2012中，使用向导创建存储过程的步骤如下：
（1）启动SQL SERVER MANAGEMENT STUDIO，并连接到SQL SERVER 2012中的数据库。
（2）在“对象资源管理器”中选择指定的服务器和数据库，展开数据库的“可编辑性”节点，鼠标右键单击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”命令。

（3）在弹出的“连接到数据库引擎”窗口中，单击“连接”按钮，便出现创建存储过程的窗口，如图8.2所示。

在存储过程窗口的文本框中，可以看到系统自动给出了创建存储过程的格式模板语句，工具模板格式进行修改来创建新的存储过程。

四.通过CREATE PROC语句创建存储过程

在SQL语言中，可以使用CREATE PROCEDURE语句创建存储过程，其语法格式如下。
CREATE PROC [ EDURE ] PROCEDURE_NAME [ ; NUMBER ]
[ { @PARAMETER DATA_TYPE }
[ VARYING ] [ = DEFAULT ] [ OUTPUT ]
] [ ,…N ]
AS SQL_STATEMENT
CREATE PROC语句的参数说明。

CREATE PROCEDURE：关键字，也可以写成CREATE PROC。
PROCEDURE_NAME：创建的存储过程名称。
NUMBER：对存储过程进行分组。
@PARAMETER：存储过程参数，存储过程可以声明一个或多个参数。
DATA_TYPE：参数的数据类型，所有数据类型（包括TEXT、NTEXT和IMAGE）均可以用作存储过程的参数，但是，CURSOR数据类型只能用于OUTPUT参数。
VARYING：可选项，指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化），该关键字仅适用于游标参数。
DEFAUL：可选项，表示为参数设置默认值。
OUTPUT：可选项，表明参数是返回参数，可以将参数值返回给调用的过程。
N ：表示可以定义多个参数。
AS：指定存储过程要执行的操作。
SQL_STATEMENT：存储过程中的过程体。
例：新建一个数据库，添加两张表，tb_Limit；tb_Account；如图

五.执行一个存储过程

存储过程创建完成后，可以通过EXECUTE执行，可简写为EXEC。
EXECUTE
EXECUTE用来执行 TRANSACT-SQL中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。
EXECUTE的语法如下：
[ { EXEC | EXECUTE } ]
{
[ @RETURN_STATUS = ]
{ MODULE_NAME [ ;NUMBER ] | @MODULE_NAME_VAR }
[ [ @PARAMETER = ] { VALUE
| @VARIABLE [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,…N ]
[ WITH RECOMPILE ]
}
EXECUTE语句的参数说明。

@RETURN_STATUS：可选的整型变量，存储模块的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。
MODULE_NAME：是要调用的存储过程或标量值用户定义函数的完全限定或者不完全限定名称。模块名称必须符合标识符规则。无论服务器的排序规则如何，扩展存储过程的名称总是区分大小写。
NUMBER：是可选整数，用于对同名的过程分组。该参数不能用于扩展存储过程。
@MODULE_NAME_VAR：是局部定义的变量名，代表模块名称。
@PARAMETER：MODULE_NAME 的参数，与在模块中定义的相同。参数名称前必须加上符号（@）。
VALUE：传递给模块或传递命令的参数值。如果参数名称没有指定，参数值必须以在模块中定义的顺序提供。
@VARIABLE：传递给模块或传递命令的参数值。如果参数名称没有指定，参数值必须以在模块中定义的顺序提供。
OUTPUT：指定模块或命令字符串返回一个参数。该模块或命令字符串中的匹配参数也必须已使用关键字OUTPUT创建。使用游标变量作为参数时使用该关键字。
DEFAULT：根据模块的定义，提供参数的默认值。当模块需要的参数值没有定义默认值并且缺少参数或指定了DEFAULT关键字，会出现错误。
WITH RECOMPILE：执行模块后，强制编译、使用和放弃新计划。如果该模块存在现有查询计划，则该计划将保留在缓存中。
注意：
后续版本的 MICROSOFT SQL SERVER 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。
例：执行上面的Qing

执行结果：

六.查看存储过程

许多系统存储过程、系统函数和目录视图都提供有关存储过程的信息。您可以使用这些系统存储过程来查看存储过程的定义，即用于创建存储过程的 Transact-SQL 语句。
可以通过下面3种系统存储过程和目录视图查看存储过程：
①使用sys.sql_modules查看存储过程的定义
sys.sql_modules为系统视图，通过该视图可以查看数据库中的存储过程。查看存储过程的操作方法如下：
（1）单击工具栏中“新建查询”按钮，新建查询编辑器。
（2）在新建查询编辑器输入如下代码：
select * from sys.sql_modules
（3）单击“执行”按钮，执行该查询命令。查询结果。

②使用 OBJECT_DEFINITION 查看存储过程的定义。
返回指定对象定义的 TRANSACT-SQL 源文本。
（1）语法如下：
OBJECT_DEFINITION ( OBJECT_ID )
（2）参数说明：
OBJECT_ID：要使用的对象的 ID。OBJECT_ID 的数据类型为 INT，并假定表示当前数据库上下文中的对象。
③使用 SP_HELPTEXT 查看存储过程的定义。
显示用户定义规则的定义、默认值、未加密的 TRANSACT-SQL 存储过程、用户定义 TRANSACT-SQL 函数、触发器、计算列、CHECK 约束、视图或系统对象（如系统存储过程）。
（1）语法如下：
SP_HELPTEXT [ @OBJNAME = ] ‘NAME’ [ , [ @COLUMNNAME = ] COMPUTED_COLUMN_NAME ]
（2）参数说明：

[ @OBJNAME = ] ‘NAME’：架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。NAME 的数据类型为 NVARCHAR(776)，无默认值。
[ @COLUMNNAME = ] ‘COMPUTED_COLUMN_NAME’：要显示其定义信息的计算列的名称。必须将包含列的表指定为 NAME。COLUMN_NAME 的数据类型为 SYSNAME，无默认值。
例：通过系统存储过程sp_helptext查看名为“Qing”存储过程的代码，SQL语句如下。
sp_helptext ‘Qing’
操作步骤如下：
（1）打开SQL Server Management Studio，并连接到SQL Server 2012中的数据库。
（2）选择存储过程所在的数据库，例如“DB-Stored Procedure”数据库。
（3）单击工具栏中“新建查询”按钮，新建查询编辑器。并输入如下SQL语句代码。
sp_helptext ‘Qing’
（4）单击“执行”按钮，就可以执行上述SQL语句代码。执行结果。

七.修改存储过程

修改存储过程可以改变存储过程当中的参数或者语句，可以通过SQL语句中的ALTER PROCEDURE语句实现。虽然删除并重新创建该存储过程，也可以达到修改存储过程的目的，但是将丢失与该存储过程关联的所有权限。

ALTER PROCEDURE语句
ALTER PROCEDURE语句用来修改通过执行CREATE PROCEDURE语句创建的过程。该语句修改存储过程时不会更改权限，也不影响相关的存储过程或触发器。
（1）ALTER PROCEDURE语句的语法如下：
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,…n ]
[ WITH <procedure_option> [ ,…n ] ]
[ FOR REPLICATION ]
AS
{ <sql_statement> [ …n ] | <method_specifier> }
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME
assembly_name.class_name.method_name
（2）ALTER PROCEDURE语句的参数说明。

SCHEMA_NAME：过程所属架构的名称。
PROCEDURE_NAME：要更改的过程的名称。过程名称必须符合标识符规则。
NUMBER：现有的可选整数，该整数用来对具有同一名称的过程进行分组，以便可以用一个 DROP PROCEDURE 语句全部删除它们。
@ PARAMETER：过程中的参数。最多可以指定2100个参数。
[ TYPE_SCHEMA_NAME. ] DATA_TYPE：参数及其所属架构的数据类型。
VARYING：指定作为输出参数支持的结果集。此参数由存储过程动态构造，并且其内容可以不同。仅适用于游标参数。
DEFAULT：参数的默认值。
OUTPUT：指示参数是返回参数。
FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。
AS：过程将要执行的操作。
ENCRYPTION：指示数据库引擎会将ALTER PROCEDURE语句的原始文本转换为模糊格式。
RECOMPILE：指示SQL SERVER 2012数据库引擎不会缓存该过程的计划，该过程在运行时重新编译。
EXECUTE AS：指定访问存储过程后执行该存储过程所用的安全上下文。
<SQL_STATEMENT>：过程中要包含的任意数目和类型的 TRANSACT-SQL 语句。但有一些限制。
EXTERNAL NAME ASSEMBLY_NAME.CLASS_NAME.METHOD_NAME：指定MICROSOFT .NET FRAMEWORK程序集的方法，以便CLR存储过程引用。CLASS_NAME必须为有效的SQL SERVER标识符，并且必须作为类存在于程序集中。如果类具有使用句点(.)分隔命名空间部分的命名空间限定名称，则必须使用方括号([ ])或引号(" ")来分隔类名。指定的方法必须为该类的静态方法。

除了上述方法修改存储过程外，也可以通过SQL Server 2012自动生成的ALTER PROCEDURE语句修改存储过程。以修改系统数据库“master”中系统存储过程“sp_MScleanupmergepublisher”为例，操作步骤如下：
（1）打开SQL Server Management Studio，并连接到SQL Server 2012中的数据库。
（2）展开对象资源管理器中“数据库/系统数据库/master/可编程性/系统存储过程”的节点后，在“sp_MScleanupmergepublisher”系统存储过程上单击鼠标右键，弹出快捷菜单，如图

（3）选择“修改”菜单项，在查询编辑器中自动生成修改该存储过程的语句。生成的语句。

（4）修改该段SQL语句并执行，即可完成修改该存储过程。

八.重命名存储过程

重新命名存储过程可以通过手动操作或执行sp_rename系统存储过程实现。

手动操作重新命名存储过程
（1）打开SQL Server Management Studio，并连接到SQL Server 2012中的数据库。
（2）展开对象资源管理器中“数据库”/“数据库名称”/“可编程性”/“存储过程”节点，鼠标右键单击需要重新命名的存储过程，在弹出的快捷菜单中，选择“重命名”命令。例如，修改“db_2012”数据库中的“Proc_stu”存储过程名称。

（3）此时，在存储过程名称的文本框中输入要修改的名称，即可重命名存储过程。
执行SP_RENAME系统存储过程重新命名存储过程
SP_RENAME系统存储过程可以在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或 MICROSOFT .NET FRAMEWORK 公共语言运行时 (CLR) 用户定义类型。
（1）语法如下：
SP_RENAME [ @OBJNAME = ] ‘OBJECT_NAME’ , [ @NEWNAME = ] ‘NEW_NAME’
[ , [ @OBJTYPE = ] ‘OBJECT_TYPE’ ]
（2）参数说明：

[ @OBJNAME = ] ‘OBJECT_NAME’：用户对象或数据类型的当前限定或非限定名称。如果要重命名的对象是表中的列，则 OBJECT_NAME 的格式必须是 TABLE.COLUMN。如果要重命名的对象是索引，则 OBJECT_NAME 的格式必须是 TABLE.INDEX。
[ @NEWNAME = ] ‘NEW_NAME’：指定对象的新名称。NEW_NAME 必须是名称的一部分，并且必须遵循标识符的规则。NEWNAME 的数据类型为 SYSNAME，无默认值。
[ @OBJTYPE = ] ‘OBJECT_TYPE’：要重命名的对象的类型。
使用SP_RENAME系统存储过程重新命名存储过程的步骤如下：
（1）打开SQL SERVER MANAGEMENT STUDIO，并连接到SQL SERVER 2012中的数据库。
（2）选择需要重新命名的存储过程所在的数据库，单击工具栏中“ ”按钮，新建查询编辑器，输入执行SP_RENAME系统存储过程重新命名的SQL语句。
（3）单击“ ”按钮，就可以执行上述SQL语句代码。结果。

注意：
更改对象名的任一部分都可能破坏脚本和存储过程。我们建议您不要使用此语句来重命名存储过程、触发器、用户定义函数或视图；而是删除该对象，然后使用新名称重新创建该对象。

九.删除存储过程

数据库中某些不再应用的存储过程可以将其删除，这样节约该存储过程所占的数据库空间。删除存储过程可以通过手动删除或执行DROP PROCEDURE语句实现。

手动删除存储过程
（1）打开SQL Server Management Studio，并连接到SQL Server 2012中的数据库。
（2）展开对象资源管理器中“数据库”/“数据库名称”/“可编程性”/“存储过程”节点，鼠标右键单击要删除的存储过程，在弹出的快捷菜单中，选择“删除”命令。
（3）在“删除对象”窗体中确认所删除的存储过程，单击“确定”按钮即可将该存储过程删除。
例如：删除“Proc_StudentInfo”存储过程。
执行DROP PROCEDURE语句删除存储过程
DROP PROCEDURE语句用来从当前数据库中删除一个或多个存储过程。
①语法如下：
DROP { PROC | PROCEDURE } { [ SCHEMA_NAME. ] PROCEDURE } [ ,…N ]
②参数说明：

SCHEMA_NAME：过程所属架构的名称。不能指定服务器名称或数据库名称。
PROCEDURE：要删除的存储过程或存储过程组的名称。
③执行DROP PROCEDURE语句删除存储过程的步骤如下：
（1）打开SQL SERVER MANAGEMENT STUDIO，并连接到SQL SERVER 2012中的数据库。
（2）选择需要删除的存储过程所在的数据库，单击工具栏中“ ”按钮，在新建查询编辑器中输入执行DROP PROCEDURE语句删除存储过程的SQL语句。
【例8.8】使用SQL语句删除名为“PROC_STUDENT”的存储过程，SQL语句代码如下。
DROP PROCEDURE PROC_STUDENT
（3）单击“ ”按钮，就可以执行上述SQL语句代码。将PROC_STUDENT存储过程删除。
注意：
不可以删除正在使用的存储过程，否则MICROSOFT SQL SERVER 2012将在执行调用进程时显示一条错误消息。

十.在存储过程中使用事务

十一.常见错误

创建存储过程应注意：
尽量少用可选参数。
提供返回值。
使用SET NOCOUNT ON禁止存储过程返回语句影响的行数。
使用DDL，少用DML。
少使用SP_PREFIX。
重点地方加注释。

posted on 2019-03-08 15:53 豆皮没有豆阅读(549) 评论(0) 收藏举报