SQLServer数据库基础教程：快速创建存储过程

可以使用T-SQL语句中的CREATE PROCEDURE命令创建存储过程。创建存储过程前，应该注意下列几个事项。

●不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。

●创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。

●存储过程是数据库对象，其名称必须遵守标识符规则。

●只能在当前数据库中创建存储过程。

创建存储过程的T-SQL语句是CREATE PROCEDURE，它的语法形式如下。

在上述数据参数中：

●procedure_name：存储过程的名称，要符合标识符规则，少于128个字符。如果存储过程名前带有一个或两个编号符“#”，则表示存储过程存于临时数据库中。前面加“#”表示为一个局部临时过程，只能在创建它的连接会话中被引用；前面加“##”表示为全局临时过程，可以在所有的连接会话中被引用。

●[；Number]：用于把多个存储过程合成一个组，取相同的名称，用“；序号”区分。如Userproc；1、Userproc；2等。这样合成一组的目的，是便于将来可以使用一个DROP PROCEDURE语句将这组存储过程一起删除。

●@parameter_name：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100个参数。

●data_type：参数的数据类型。

●default：参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。

●OUTPUT：表明参数是一个返回参数。

●RECOMPLE：表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时使用。

●ENCRYTION：表示SQL Server加密syscomments表中包含语句文本的条目。

●FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。FOR REPLICATION不能和WITH RECOMPLE选项一起使用，FOR REPLICATION是指该存储过程只被复制任务执行，不能在订阅服务器上执行。WTTH RECOMPLE指示系统不把该存储过程的执行计划存于内存，而是每次执行都重编译一次。这个选项对于执行环境不断变化、无法预先确定最优的执行计划的情况最适用。如果一个表的结构被改变，则引用此表的存储过程会被自动重新编译。

●AS用于指定该存储过程要执行的操作。

●SQL_statement：存储过程的内容，即包含在存储过程中的一个或多个Transact⁃SQL语句。

【例7-2】 使用T⁃SQL语句在“Teaching”数据库中创建返回全部男学生信息的存储过程“up_MXs”。

1）单击“SQL Server Management Studio”窗口中工具栏上的按钮，在右侧窗格中将显示一个“查询”窗格，在其中输入如下代码。

(www.xing528.com)

图7-6 up_MXs存储过程创建成功

2）输入上述代码后，单击工具栏中的“分析”按钮，对输入的代码进行语法分析检查，检查通过后，单击工具栏中的“执行”按钮，即成功创建“up_MXs”存储过程，并在“消息”窗格中显示“命令已成功完成”信息。右键单击“对象资源管理器”窗格中的“存储过程”结点，在弹出的快捷菜单中选择“刷新”命令时就会看到所创建的存储过程，结果如图7-6所示。

另外，在SQLServer中创建存储过程时可以使用参数。通过存储过程每次执行时使用不同的参数，实现其灵活性。

SQL Server 2005的存储过程可以使用两种类型的参数：输入参数和输出参数。参数用于在存储过程以及应用程序之间交换数据。

●输入参数允许用户将数据值传递到存储过程或函数。

●输出参数允许存储过程将数据值或游标变量传递给用户。

●每个存储过程向用户返回一个整数代码，如果存储过程没有显示设置返回代码的值，则返回代码为0。

存储过程的参数在创建时应在CREATE PROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型，参数名必须以@符号为前缀，可以为参数指定默认值；如果是输出参数，则应用OUTPUT关键字描述。各个参数定义之间用逗号隔开，具体语法如下。

1.输入参数

输入参数，即在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息。使用输入参数可以向同一存储过程多次查找数据库。

【例7-3】 使用T⁃SQL语句在Teaching数据库中创建一个名为up_getScore的存储过程，作用是通过输入的学号信息显示出该学生的所有科目成绩。

该例可以通过为同一存储过程指定不同的学号信息，来返回不同学生的所有科目成绩。为了实现这一功能，学生学号就应该是可变的，需设计一个参数StuXh，编写该存储过程的T⁃SQL语句如下。

2.输出参数

通过定义输出参数，可以从存储过程中返回一个或多个值。为了使用输出参数，必须在CREATE PROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍然会执行但没有返回值。

【例7-4】 使用T-SQL语句在Teaching数据库中创建一个名为up_getOneScore的存储过程，作用是通过输入的学号和课程编号信息，显示出该学生的指定科目的成绩。

编写该存储过程的T-SQL语句如下。

该例子中，@StuXh和@kch为输入参数，用于传入学生学号和课程编号，@cj为输出参数，用于返回该学生该门课程的成绩，请注意其后面的OUTPUT表明此参数为输出参数，即该值由存储过程传出。