sqlserver24学时教程连载20-第20学时 存储过程

 

    20.3.1 创建存储过程

    CREATE PROCEDURE语句用于从已测试过的 SQL语句来实际创建一个存储过程 .事实

    上这是一个相当简单的过程。

    CREATE PROCEDURE语句的语法如下：

    选项如下：

    选    项                                            描     述

    <procedure_name>                      这是你想创建的存储过程的名字。我们稍后将介绍命名存储过程

    的约定

    <@parameter>                          如果你想向存储过程传递参数，你必须在存储过程的声明部分定

    义它们。声明包括参数名、参数的数据类型以及一些其他的特殊选

    项

    <datatype>                            如果你想指定一个参数，你必须声明它的数据类型。它可以是任

    何有效的数据类型，包括文本和图像类型

    [VARYING]                             当你把游标作为参数返回时，要指定该选项。这个选项告诉 SQL Server对于返回游标的行集合将会发生改变

    [= default]                           这个选项用于指定特定参数的缺省值。如果过程被执行的时候这

    个参数没有赋值，将使用本缺省值来取代。本值可以是 NULL值，或是其他符合该数据类型的合法值。对于字符串数据，如果该参数

    是与LIKE参数联合使用的话，该值可以包括含通配符

    [OUTPUT]                              这一可选关键字用于指定该参数是输出参数。当过程执行完成后，

    该参数值能被返回到正在执行的过程里。文本或图像数据类型不能

    作为输出参数使用

    [，…]                                这一符号指明你可以在一个存储过程中指定多个参数。SQL Server在单个存储过程中最多可有1 024个参数

    WITH RECOMPILE                        这个选项强制SQL Server在每一次执行存储过程时都重新编译。当使用临时值和对象时，你应该使用它

    WITH ENCRYPTION                       这一选项强制SQL Server对存储在系统备注表中的存储过程文本进行加密。这就允许你创建和重新分布数据库，而不用担心用户

    会获得存储过程的原始代码

    WITH RECOMPILE，ENCRYPTION            这一选项强制SQL Server重新编译和加密存储过程

    AS                                    表明存储过程的定义将要开始

    <sql_statements>                      它是组成存储过程的不同语句

    在我们更进一步讨论创建存储过程之前，需要回过头来看看存储过程的命名约定。所有

    SQL Server附带的存储过程都有 sp_前缀。这一规则是为系统存储过程保留的。但这并不意味

    着SQL Server将阻止你创建带有sp_前缀的用户存储过程。它将仍然允许你创建它们，但是可

    能会在使用第三方工具时出问题。大多数第三方工具将忽略全部系统对象，并且因为 SQL

    Server系统存储过程有 sp_前缀，这些工具也将忽略他们。经验告诉我们，命名用户存储过程

    时，应当使用其他的前缀。在这一学时里，我们将把 usp_前缀指定给用户存储过程。

    现在，你已经知道了创建一个存储过程所需要的命令，让我们来看看实际上是如何做的。

    我们需要使用一些SELECT语句，这些语句在第17学时的“数据查询”中已有说明。在程

    序清单20-1所示的例子中，我们将通过查询获得所有作者的名字和电话号码，并把它们转化

    197第20学时 存 储 过 程

    为一个名叫usp_get_phone_list 的存储过程。

    程序清单20-1 创建简单的存储过程

    这将会产生一个名叫usp_get_phone_list 的存储过程。为了运行并检测这个存储过程，打

    开另一个查询窗口，键入存储过程名，然后执行它。 执行结果如图20-1所示，正和直接运行

    SELECT语句的结果一样。

    图20-1 程序清单20-1的查询

    结果

    既然存储过程的运行非常有用，那么你可以用存储过程多做点事。例如，如果想通过某

    一位作者的姓查知他的电话号码，你该如何做呢 ？ 我们可以传递一个参数到存储过程之中。在

    程序清单 20-2中，我们将创建一个名为 usp_get_phone 的用户存储过程，使用一个名为

    @last_name能够传送进存储过程的参数。我们就可以通过这个参数在 authors表中找到他的电

    话号码。

    程序清单20-2 向存储过程传递参数

    在这个存储过程中我们所做的是，当 SQL Server工作时，告诉SQL Server我们将要传送作

    者的姓给它，让它用SELECT语句中的一个WHERE子句去找该作者的电话号码。当运行这个

    存储过程时，必须传递作者的姓，否则 SQL Server将会返回出错信息，告诉你需要传递参数。

    198 SQL Server 7 24学时教程

    执行结果如图20-2所示。

    图20-2 程序清单20-2的查询

    结果

    在大多数情况下，这是一个非常有用的查询方式。比如说，当我们不知道该如何拼写作

    者的姓时，可以通过在 SELECT查询中输入一个LIKE子句来获取。我们将不得不对用户已传

    递到存储过程中的查询字符串做一点控制。所要做的是在这类字符串结尾处加上百分号 （％），

    以便SQL Server把它作为通配符。程序清单20-3就显示了这个存储过程。

    图20-3 程序清单20-3的查询

    结果

    程序清单20-3 存储过程中的通配符

    199第20学时 存 储 过 程

    这个存储过程允许我们以从姓的第一个字母开始的任何一个部分查询作者姓名，图 20-3

    就是查询以D开头的作者姓名的存储过程运行结果。

    至此为止，我们已经学习了创建从表中检索数据的存储过程。但是，如果想创建一个将

    数据插入表中的存储过程，该怎样做呢？让我们来创建一个向 authors表里插入数据的存储过

    程。在这个存储过程中，我们要求用户提供作者的标识、名和姓。如果用户传递了另外的信

    息，会被插入到表中。如果用户没有传递任何其他信息，我们将通过在存储过程的参数中设

    置缺省值来处理。程序清单20-4是创建的一个叫作usp_insert_new_author的存储过程的情况。

    程序清单20-4 使用存储过程插入数据

    在这个存储过程中我们所要做的是创建所有需要的参数。任何我们不强迫用户插入表的

    参数就用缺省值NULL创建。contract列是用缺省值0创建的。当执行这个存储过程时必须传递

    作者的标识、姓名，其他的列则是可选择的。我们可以看到图 20-4中已经执行了这个过程在

    图20-4 程序清单20-4的查询

    结果

    200 SQL Server 7 24学时教程

    表中插入名叫Bill Jones的作者，然后通过运行 usp_get_author_phone2可以看到插入到表中的

    内容。

    也许你会说，你只了解了存储过程的基本知识。是的，但经过大量的练习，我们会熟练

    掌握的。

    20.3.2 修改存储过程

    创建了存储过程之后，不可避免地，根据不同的情况需要对它做出相应的修改。在 SQL

    Server以前的版本中，当需要修改存储过程时，不得不清除该过程再重新创建。当清除存储过

    程时，既会失去所有赋予原过程的许可又会丢失所有的关联信息，所谓关联信息就是 SQL

    Server收集的数据库中所有对象的信息，如果存储过程引用了一个表，就说它与这个表关联。

    想知道更多有关关联信息的内容，可以在 SQL Server Books Online中查询sp_depends.

    微软在SQL Server 7.0中加入了一个非常有用的命令，它可以使用户避免丢失所有这些信

    息。这就是ALTER PROCEDURE命令。为了使用这个命令，用户需要重新创建所有与存储过

    程有关的代码，但不会丢失许可和关联信息。

    一个系统存储过程会帮助用户重新创建与存储过程有关的代码，这个系统存储过程叫

    sp_helptext.假设存储过程未加密，这个系统存储过程将返回所有与存储过程有关的代码。

    这个命令的语法是：

    sp_helptext <procedure_name>

    这里<procedure_name> 就是用户需要从中获取代码的存储过程的名字。从图 20-5可以看

    到这个命令的一个例子的输出结果。

    图20-5 sp_helptext的运行

    结果

    现在你有了存储过程中的代码，你能很容易地对其进行修改， ALTER PROCEDURE命令

    的语法如下：

    201第20学时 存 储 过 程

上一页  [1] [2] [3] 下一页