Transact SQL教程（四）

　　使用存储过程
　　该部分将向你介绍如何创建和使用你自己的存储过程。在SQL Server中存储过程是和传统的计算机应用程序最相近的事物，并具有如下的优点：
　　假如你有一套复杂的SQL语句需要在多个Active Server Pages中执行。你可以把他们放入一个存储过程，然后执行该存储过程。这能够减少你Active Server Pages的大小。同时还能确保在每一页上执行的SQL语句都相同。
　　当 你执行一个SQL的批处理时。服务器首先必须编译在批处理中的所有语句。这不但需要时间，还要花费服务器资源。相比较而言，在存储过程第一次执行后，它就 不需要重新编译了。通过使用存储过程，你可以跨过编译这一步，更快地执行SQL语句集合。从一个Active Server页中执行一个存储过程比执行一个SQL语句的集合更有效。
　　你可以对存储过程输入输出值。这意味着存储过程非常的灵活，相同的存储过程可以根据不同的输入值返回不同的信息。
　　当你向数据库服务器传递一个SQL语句集合时，必须传递其中的每一个独立语句，而当你执行存储过程时，相反的，仅仅传递一个简单的语句。通过使用存储过程，你可以减少在网络上的阻塞。
　　你可以配置表的权限，比如用户只能通过使用存储过程来修改表。这就能增加在你数据库中表的安全性。
　　你可以在其他的存储过程内部执行你的存储过程。这种策略就允许你在非常小的存储过程上建立非常复杂的存储过程。这也意味着你可以为许多不同的编程任务使用相同的存储过程。
　　当你在Active Server页中添加SQL语句时，你必须仔细考虑能否把这些语句放置到存储过程中。上面提到的优点都是实质性的。如下一部分所示，存储过程是非常容易创建的。
　　使用CREATE PROCEDURE创建存储过程
　　你可以使用CREATE PROCEDURE来创建一个存储过程。下面就是一个非常简单的存储过程的一个例子：
　　CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors
　　当你创建存储过程时，你必须给它指定一个名称。在本例子中，存储过程的名称为retrieve_authors。你可以给存储过程赋予任何你想要的名称，但最好你能够使该名称在一定程度上描述存储过程的功能。
　　每一个存储过程都包括一个或多个SQL语句。为了指明是存储过程一部分的SQL语句，你只需简单地在关键词AS后面包含它们。在前面例子中的存储过程只包含一个SQL语句。当该存储过程执行时，它返回在Authors表中所有的记录。
　　你可以使用EXECUTE语句来执行一个存储过程。比如，为了执行retrieve_authors存储过程，你可以使用如下的语句：
　　EXECUTE retrieve_authors
　　当你执行该存储过程时，所有包括在其中的SQL语句都会执行，在上面的例子中，会返回所有在Authors表中的记录。
　　当在批处理中的第一个语句是调用存储过程时，你并不需要使用EXECUTE语句。你可以简单地提供存储过程的名称来执行存储过程。比如在ISQL/W中，可以象下面所示来执行存储过程：
　　retrieve_authors
　　这起同样的作用。存储过程会被执行，并会返回结果。然而如果在该存储过程之前还有其他的任何语句，你就会收到错误信息（一般地，语法错误）。
　　当 你创建和执行一个存储过程时，这仅仅是在某一个数据库的范围内完成。假设你在数据库MyDatabase内创建了存储过程 retrieve_authors。如果没有指明过程调用，你就不能在另一个数据库比如MyDatabase2中调用存储过程 retrieve_authors。假如你需要在Mydatabase2中执行存储过程retrieve_authors，你必须使用如下的语句（注意下 面的两个点号）：
　　EXECUTE Mydatabase..retrieve_authors
　　一旦你已经创建了一个存储过程，你就能使用系统存储过程sp_helptext来观看在该存储过程的的SQL语句。比如，如果你输入命令sp_helptext retrieve_authors，就会显示下面的结果：
　　text
　　……………………………………………
　　CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors
　　注意
　　你 可能感到奇怪的是，sp_helptext系统过程本身就是一种存储过程类型。它是一种系统的存储过程。（系统存储过程存储在Master数据库中，能够 被所有的数据库访问。）为了满足你的好奇心，你可以使用命令sp_helptext sp_helptext来观看组成sp_helptext本身的SQL语句。
　　你在创建完存储过程后，不能对其进行修改。假如你需要修改一个存储过程。你必须首先破坏它，然后重新构建之。为了破坏一个存储过程。你可以使用DROP PROCEDURE语句，例如下面的语句删除retrieve_authors存储过程：
　　DROP PROCEDURE retrieve_authors
　　注意
　　你 可以使用系统存储过程sp_help来观看在当前数据库中所有存储过程的列表。假如你不加任何修改地执行了sp_help。该过程会显示在当前数据库中所 有的存储过程、触发器和表。假如在sp_help后面跟上指定的存储过程，sp_help会仅仅显示那个存储过程的信息。
　　给存储过程传值
　　当你调用一个存储过程时，你可以使用参数来传值给它，从而使你的存储过程变得非常的灵活。比如，你想修改过程check_philosophers，使之能够检测是否存在某一个哲学家。你可以使用如下的语句进行修改：
　　CREATE PROCEDURE check_philosophers
　　(@philosopher VARCHAR(30))
　　AS
　　IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)
　　PRINT “A philosopher”
　　ELSE
　　PRINT “Not a philisopher”
　　当该过程执行时，它检查传递给变量@philosopher的姓名是否存在于表Philosophers中。假如@philosopher的值存在于表中，打印文本“A philosopher”，否则打印文本“Not a philisopher”
　　当 你在存储过程中包含参数时，你把它们列在CREATE PROCEDURE语句的后面的括号内，但必须在关键词AS之前。对于每一个参数，你都必须指明数据类型。在一个单独的过程中，你最多能设置255个参 数。比如下面的过程检查是否在传递的姓名当中，至少有一个是哲学家的姓名：
　　CREATE PROCEDURE check_philosophers
　　(@firstname VARCHAR(30)，@secondname VARCHAR(30))
　　AS
　　IF EXISTS(SELECT name FROM Philosophers
　　WHERE name=@firstname OR name=@secondname)
　　PRINT “At least one of them is a philosopher”
　　ELSE
　　PRINT “Neither one of them is a philisopher”
　　为了执行一个具有一个或多个参数的存储过程，你只需简单地在存储过程名称的后面列出参数的值。比如下面的语句检查Plato和Aristotle中是否至少有一个是哲学家：
　　EXECUTE check_philisophers “Plato”,”Aristotle”
　　假如一个存储过程具有多个参数。你必须以正确的顺序来传值。有时候这并不是很方便。你可以使用另一种方法，通过名称来传递参数，如下所示：
　　EXECUTE check_philisophers @firstname=“Plato”, @secondname=”Aristotle”
　　该语句完成于前一语句完全相同的事情。然而通过使用参数名，你可以使用任何你想要的顺序来传递参数。
　　从存储过程中获得值
　　你可以从存储过程中接受值。这些值可以直接在你的Active Server Pages中使用（参看第24章“使用Commands”）。同样，你可以在其他的存储过程中获得这些值。假如第一个过程调用了第二个存储过程，则第一个过程能接受有第二个过程设置的参数值。
　　例如，下面的存储过程输出变量@conclusion的值：
　　CREATE PROCEDURE check_philosophers
　　(@philosopher VARCHAR(30)，@conclusion VARCHAR(30) OUTPUT)
　　AS
　　IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)
　　SELECT @conclusion= “A philosopher”
　　ELSE
　　SELECT @conclusion= “Not a philisopher”
　　注 意在本例子中关键词OUTPUT的使用。该关键词紧跟在参数@conclusion的定义后面。这指明该参数将会用于从该过程中输出信息。在这个简单的例 子中，参数的值将会是“A philosopher”或“Not a philisopher”，根据变量@philosophe的值的不同而变化。
　　为了这些一个具有输出参数的存储过程，你需要在EXECUTE语句中使用关键词OUTPUT 。假如你在一个批处理或者另外一个存储过程中执行该过程时，你必须首先定义一个变量用于存储从过程中传递出的值，如下面的例子所示：
　　DELCARE @proc_results VARCHAR(30)
　　EXECUTE check_philosophers “Plato”,@proc_results OUTPUT
　　PRINT @proc_results
　　在 该例子中的第一个语句定义了将用于存储从过程check_philosophersZ中传出的参数值的变量。该变量将和输出参数的数据类型一模一样。第二 个语句执行存储过程。注意变量@proc_results后面必须紧跟关键词OUTPUT。最后变量@proc_results的值被打印到屏幕上。
　　你同样可以使用名称来接收输出参数的值，下面就是一个简单的例子：
　　DECLARE @proc_results VARCHAR(30)
　　EXECUTE check_philosophers @philosopher=”Plato”,@conclusion=@proc_results OUTPUT
　　PRINT @proc_results
　　注意在该EXECUTE语句中，参数的名称总是列在前面。你要使用@conclusion=@proc_results来接收参数@conclusion的值，而不是你可能期望的@proc_results=@conclusion。
　　在存储过程中使用RETURN语句“In the Philosophers Table”“In the Authors Table”“Not in any tables!””。RETURN语句会在一旦存在匹配时，立刻退出该过程。”来执行该过程。首先使用表Philosophers来检测是否存在“Plato”。由于该名字存在于该表中，所以过程打印文本”In the Philosophers table”，然后过程在执行到RETURN语句时，退出。你可以使用RETURN语句结束任何过程。这实际上并不完成任何事情。因为在任何情况下，过程总是要退出的。
　　在上面的文章中我们已经介绍了RETURN语句的使用。在存储过程中该语句的用法和你在批处理中的用法是一模一样的。RETURN语句会导致过程立即退出。考察下面的例子：
　　CREATE PROCEDURE check_tables
　　(@who VARCHAR(30))
　　AS
　　IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)
　　BEGIN
　　PRINT
　　RETURN
　　END
　　IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)
　　BEGIN
　　PRINT
　　RETURN
　　END
　　PRINT
　　RETURN
　　该过程检查了两个表以判断一个人是否是哲学家或作者。假如提供的名称即不是哲学家也不是作者，打印文本“Not in any tables!
　　比如，假定你使用参数“Plato
　　注意
　　
　　当你在存储过程而不是在批处理中使用RETURN语句时，你能够返回一整数值。该整数值代表一个状态码。下面就是上面的例子经过改写后，返回特定的整数值。
　　CREATE PROCEDURE check_tables
　　(@who VARCHAR(30))
　　AS
　　IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)
　　BEGIN
　　RETURN(1)
　　END
　　IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)
　　BEGIN
　　RETURN(2)
　　END
　　RETURN(3)
　　该 过程完成和前面的那个过程相同的任务。然而当在表中发现该姓名时，并不打印信息，代替地，该过程使用RETURN语句来指明从那个表中找到该名称。例如， 假如你使用参数“James Joyce”来执行该过程时。存储过程会返回值2，因为James Joyce在表Authors中，而不是在表Philosophers中。你可以在Active Server Page或其他的存储过程中使用状态值来确定该姓名所在的表。
　　当使用状态值时，你必须使用对于1或小于-99的值。SQL Server使用值0来报告一个存储过程的成功执行。它同时使用小于0对于-100的值来报告错误(参看表12。1以获得SQL Server使用的状态值的完全列表)。
　　

值	意思
0	过程成功执行。
-1	对象丢失。
-2	发生数据类型错误。
-3	处理过程被死锁。
-4	发生权限错误。
-5	发生语法错误。
-6	发生恶意用户错误。
-7	发生资源错误，比如空间不够等。
-8	遭遇非致命的内部问题。
-9	遭遇系统限制。
-10	发生致命的内部不稳定性。
-11	发生致命的内部不稳定性。
-12	表或索引被破坏。
-13	数据库被破坏。
-14	发生硬盘错误。
包含在-15到-99之间的值有SQL Server保留以在将来使用。

　　当你使用RETURN语句返回状态值时，切记不要返回NULL值。这会导致错误的结果。“James Joyce”“James Joyce”
　　表12.1 过程状态值
　　注意
　　
　　为了获得在前面例子中过程返回的状态值，你可以使用如下的语句：
　　DECLARE @conclusion INT
　　EXECUTE @conclusion=check_tables
　　SELECT @conclusion
　　变量@conclusion用于保存状态值。它必须定义成INT整数数据类型。当你执行存储过程check_tables时，使用下面的语句将状态值赋予该变量：
　　EXECUTE @conclusion=check_tables