SQL存储过程学习

1   存储过程语法表
存储过程就是作为可执行对象存放在 数据库中的一个或多个SQL命令 。
     定义总是很抽象 。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在 数据库中的(这里我们只谈SQL Server) 。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起 。这样做的好处至少有三个：
     第一、大大提高效率 。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同 数据库的交互次数 。
     第二、提高安全性 。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密 。
     第三、有利于SQL语句的重用 。
    
     在ASP中，一般通过command对象调用存储过程，根据不同情况，本文也介绍其它调用方法 。为了方便说明，根据存储过程的输入输出，作以下简单分类：
     1. 只返回单一记录集的存储过程
     假设有以下存储过程(本文的目的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)：

     /*SP1*/
     CREATE PROCEDURE dbo.getUserList
     as
     set nocount on
     begin
        select * from dbo.[userinfo]
     end
     go

     以上存储过程取得userinfo表中的所有记录，返回一个记录集。通过command对象调用该存储过程的ASP代码如下:
    
     '**通过Command对象调用存储过程**
     DIM MyComm,MyRst
     Set MyComm = Server.CreateObject("ADODB.Command")
     MyComm.ActiveConnection = MyConStr           'MyConStr是数据库连接字串
     MyComm.CommandText       = "getUserList"      '指定存储过程名
     MyComm.CommandType       = 4                  '表明这是一个存储过程
     MyComm.Prepared          = true               '要求将SQL命令先行编译
     Set MyRst = MyComm.Execute
     Set MyComm = Nothing

     存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。
     在以上代码中，CommandType属性表明请求的类型，取值及说明如下：
       -1    表明CommandText参数的类型无法确定

      1     表明CommandText是一般的命令类型
       2     表明CommandText参数是一个存在的表名称
       4     表明CommandText参数是一个存储过程的名称
    
     还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：

 

     '**通过Connection对象调用存储过程**
     DIM MyConn,MyRst
     Set MyConn = Server.CreateObject("ADODB.Connection")
     MyConn.open MyConStr                             'MyConStr是数据库连接字串
     Set MyRst   = MyConn.Execute("getUserList",0,4)   '最后一个参断含义同CommandType
     Set MyConn = Nothing

     '**通过Recordset对象调用存储过程**
     DIM MyRst
     Set MyRst = Server.CreateObject("ADODB.Recordset")
     MyRst.open "getUserList",MyConStr,0,1,4
     'MyConStr是数据库连接字串,最后一个参断含义与CommandType相同

    
     2. 没有输入输出的存储过程
     请看以下存储过程：

     /*SP2*/
     CREATE PROCEDURE dbo.delUserAll
     as
     set nocount on
     begin
        delete from dbo.[userinfo]
     end
     go

     该存储过程删去userinfo表中的所有记录，没有任何输入及输出，调用方法与上面讲过的基本相同，只是不用取得记录集：

     '**通过Command对象调用存储过程**
     DIM MyComm
     Set MyComm = Server.CreateObject("ADODB.Command")
     MyComm.ActiveConnection = MyConStr           'MyConStr是数据库连接字串
     MyComm.CommandText       = "delUserAll"       '指定存储过程名
     MyComm.CommandType       = 4                  '表明这是一个存储过程
     MyComm.Prepared          = true               '要求将SQL命令先行编译
     MyComm.Execute                               '此处不必再取得记录集

     Set MyComm = Nothing  

 

     当然也可通过Connection对象或Recordset对象调用此类存储过程，不过建立Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利用Command对象吧。

     3. 有返回值的存储过程
     在进行类似SP2的操作时，应充分利用SQL Server强大的事务处理功能，以维护数据的一致性。并且，我们可能需要存储过程返回执行情况，为此，将SP2修改如下：

     /*SP3*/
     CREATE PROCEDURE dbo.delUserAll
     as
     set nocount on
     begin
        BEGIN TRANSACTION
        delete from dbo.[userinfo]
        IF @@error=0
           begin
              COMMIT TRANSACTION
              return 1
           end
        ELSE
           begin
              ROLLBACK TRANSACTION
              return 0
           end         
        return
     end
     go

     以上存储过程，在delete顺利执行时，返回1，否则返回0，并进行回滚操作。为了在ASP中取得返回值，需要利用Parameters集合来声明参数：

     '**调用带有返回值的存储过程并取得返回值**
     DIM MyComm,MyPara
     Set MyComm = Server.CreateObject("ADODB.Command")
     MyComm.ActiveConnection = MyConStr           'MyConStr是数据库连接字串
     MyComm.CommandText       = "delUserAll"       '指定存储过程名
     MyComm.CommandType       = 4                  '表明这是一个存储过程
     MyComm.Prepared          = true               '要求将SQL命令先行编译
     '声明返回值
     Set Mypara = MyComm.CreateParameter("RETURN",2,4)

     MyComm.Parameters.Append MyPara
     MyComm.Execute
     '取得返回值
     DIM retValue
     retValue = MyComm(0)     '或retValue = MyComm.Parameters(0)
     Set MyComm = Nothing
    
     在MyComm.CreateParameter("RETURN",2,4)中，各参数的含义如下：
     第一个参数("RETURE")为参数名。参数名可以任意设定，但一般应与存储过程中声明的参数名相同。此处是返回值，我习惯上设为"RETURE"；
     第二个参数(2)，表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常用的类型代码：
     adBigInt: 20 ;
     adBinary : 128 ;
     adBoolean: 11 ;
     adChar: 129 ;
     adDBTimeStamp: 135 ;
     adEmpty: 0 ;
     adInteger: 3 ;
     adSmallInt: 2 ;
     adTinyInt: 16 ;
     adVarChar: 200 ;
     对于返回值，只能取整形，且-1到-99为保留值；
     第三个参数(4)，表明参数的性质，此处4表明这是一个返回值。此参数取值的说明如下：
     0 : 类型无法确定； 1: 输入参数；2: 输入参数；3：输入或输出参数；4: 返回值
    
     以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实

 

     Set Mypara = MyComm.CreateParameter("RETURN",2,4)
     MyComm.Parameters.Append MyPara
        
     可以简化为

     MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)

     甚至还可以继续简化，稍后会做说明。
     对于带参数的存储过程，只能使用Command对象调用(也有资料说可通过Connection对象或Recordset对象调用，但我没有试成过)。

     4. 有输入参数和输出参数的存储过程
     返回值其实是一种特殊的输出参数。在大多数情况下，我们用到的是同时有输入及输出参数的存储过程，比如我们想取得用户信息表中，某ID用户的用户名，这时候，有一个输入参数----用户ID，和一个输出参数----用户名。实现这一功能的存储过程如下：

     /*SP4*/
     CREATE PROCEDURE dbo.getUserName
        @UserID int,
        @UserName varchar(40) output
     as
     set nocount on
     begin
        if @UserID is null return
        select @UserName=username
            from dbo.[userinfo]

 

2  设计存储过程
　几乎任何可写成批处理的 Transact-SQL 代码都可用于创建存储过程。
　　
　　存储过程的设计规则
　　存储过程的设计规则包括：
　　
　　CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句，存储过程中的任意地方都不能使用下列语句：
　　　
　　可在存储过程中创建其它数据库对象。可以引用在同一存储过程中创建的对象，前提是在创建对象后再引用对象。
　　
　　
　　可以在存储过程内引用临时表。
　　
　　
　　如果在存储过程内创建本地临时表，则该临时表仅为该存储过程而存在；退出该存储过程后，临时表即会消失。
　　
　　
　　如果执行调用其它存储过程的存储过程，那么被调用存储过程可以访问由第一个存储过程创建的、包括临时表在内的所有对象。
　　
　　
　　如果执行在远程 Microsoft%26reg; SQL Server%26#8482; 2000 实例上进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
　　
　　
　　存储过程中参数的最大数目为 2100。
　　
　　
　　存储过程中局部变量的最大数目仅受可用内存的限制。
　　
　　
　　根据可用内存的不同，存储过程的最大大小可达 128 MB。
　　有关创建存储过程的规则的更多信息，请参见 CREATE PROCEDURE。
　　
　　限定存储过程内的名称
　　在存储过程内部，如果用于诸如 SELECT 或 INSERT 这样的语句的对象名没有限定用户，那么用户将默认为该存储过程的所有者。在存储过程内部，如果创建存储过程的用户没有限定 SELECT、INSERT、UPDATE 或 DELETE 语句中引用的表名，那么通过该存储过程对这些表进行的访问将默认地受到该过程的创建者权限的限制。
　　
　　如果有其他用户要使用存储过程，则用于语句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的对象名必须用该对象所有者的名称限定。例如，Mary 拥有表 marytab，如果她希望其他用户能够执行使用该表的存储过程，必须在该表用于上述某一条语句时对其表名进行限定。
　　
　　此规则是必需的，因为运行存储过程时将解析对象的名称。如果未限定 marytab，而 John 试图执行该过程，SQL Server 将查找 John 所拥有的名为 marytab 的表。
　　
　　加密过程定义
　　如果要创建存储过程，并且希望确保其他用户无法查看该过程的定义，那么可以使用 WITH ENCRYPTION 子句。这样，过程定义将以不可读的形式存储。
　　
　　存储过程一旦加密其定义即无法解密，任何人（包括存储过程的所有者或系统管理员）都将无法查看存储过程定义。
　　
　　SET 语句选项
　　当 ODBC 应用程序与 SQL Server 连接时，服务器将自动设置会话的下列选项：
　　
　　SET QUOTED_IDENTIFIER ON
　　
　　
　　SET TEXTSIZE 2147483647
　　
　　
　　SET ANSI_DEFAULTS ON
　　
　　
　　SET CURSOR_CLOSE_ON_COMMIT OFF
　　
　　
　　SET IMPLICIT_TRANSACTIONS OFF
　　这些设置将提高 ODBC 应用程序的可移植性。由于基于 DB-Library 的应用程序通常不设置这些选项，所以应在上述所列 SET 选项打开和关闭的情况下都对存储过程进行测试。这样可确保存储过程始终能正确工作，而不管特定的连接在唤醒调用该存储过程时可能设置的选项。需要特别设置其中一个选项的存储过程，应在开始该存储过程时发出一条 SET 语句。此 SET 语句将只对该存储过程的执行保持有效，当该存储过程结束时，将恢复原设置。
　　
　　示例
　　A. 创建使用参数的存储过程
　　下例创建一个在 pubs 数据库中很有用的存储过程。给出一个作者的姓和名，该存储过程将显示该作者的每本书的标题和出版商。
CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
　　AS
　　SELECT au_lname, au_fname, title, pub_name
　　FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
　　JOIN titles ON titleauthor.title_id = titles.title_id
　　JOIN publishers ON titles.pub_id = publishers.pub_id
　　WHERE au_fname = @firstname
　　AND au_lname = @lastname
　　GO
　　
　　将出现一条说明该命令未返回任何数据也未返回任何行的消息，这表示已创建该存储过程。
　　
　　现在执行 au_info 存储过程：
　　
　　EXECUTE au_info Ringer, Anne
　　GO
　　
　　下面是结果集：
　　　
　　
　　(2 row(s) affected)
　　
　　B. 创建使用参数默认值的存储过程
　　下例创建一个存储过程 pub_info2，该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名。如果未提供出版商的名称，该存储过程将显示由 Algodata Infosystems 出版的书籍的作者。
　　
　　CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
　　AS
　　SELECT au_lname, au_fname, pub_name
　　FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
　　JOIN titles t ON ta.title_id = t.title_id
　　JOIN publishers p ON t.pub_id = p.pub_id
　　WHERE @pubname = p.pub_name
　　
　　执行未指定参数的 pub_info2：
　　
　　EXECUTE pub_info2
　　GO
　　
　　下面是结果集：
　　　
　　(9 row(s) affected)
　　
　　C. 执行用显式值替代参数默认值的存储过程
　　在下例中，存储过程 showind2 的 @table 参数默认值是 titles。
　　
　　CREATE PROC showind2 @table varchar(30) = 'titles'
　　AS
　　SELECT TABLE_NAME = sysobjects.name,
　　INDEX_NAME = sysindexes.name, INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
　　WHERE sysobjects.name = @table
　　
　　列标题（例如，TABLE_NAME）可使结果更具可读性。下面是该存储过程显示的 authors 表的情况：
　　
　　EXECUTE showind2 authors
　　GO
　　
　　　
　　(2 row(s) affected)
　　
　　如果用户未提供值，则 SQL Server 将使用默认表 titles：
　　
　　EXECUTE showind2
　　GO
　　
　　下面是结果集：
　　　
　　(2 row(s) affected)
　　
　　D. 使用参数默认值 NULL 创建存储过程
　　参数默认值可以是 NULL 值。在这种情况下，如果未提供参数，则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。
　　
　　过程定义还可指定当不给出参数时要采取的其它某种措施。例如：
　　
　　CREATE PROC showind3 @table varchar(30) = NULL
　　AS IF @table IS NULL
　　PRINT 'Give a table name'
　　ELSE
　　SELECT TABLE_NAME = sysobjects.name,
　　INDEX_NAME = sysindexes.name, INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects
　　ON sysobjects.id = sysindexes.id
　　WHERE sysobjects.name = @table
　　
　　E. 使用包含通配符的参数默认值创建存储过程
　　如果存储过程将参数用于 LIKE 关键字，那么默认值可包括通配符（%、_、[] 和 [^]）。例如，可将 showind 修改为当不提供参数时显示有关系统表的信息：
　　
　　CREATE PROC showind4 @table varchar(30) = 'sys%'
　　AS SELECT TABLE_NAME = sysobjects.name,
　　INDEX_NAME = sysindexes.name, INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects
　　ON sysobjects.id = sysindexes.id
　　WHERE sysobjects.name LIKE @table
　　
　　在存储过程 au_info 的下列变化形式中，两个参数都有带通配符的默认值：
CREATE PROC au_info2 @lastname varchar(30) = 'D%',
　　@firstname varchar(18) = '%'
　　AS
　　SELECT au_lname, au_fname, title, pub_name
　　FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
　　JOIN titles ON titleauthor.title_id = titles.title_id
　　JOIN publishers ON titles.pub_id = publishers.pub_id
　　WHERE au_fname LIKE @firstname
　　AND au_lname LIKE @lastname
　　
　　如果执行 au_info2 时不指定参数，将显示姓以字母 D 开头的所有作者：
　　
　　EXECUTE au_info2
　　GO
　　
　　下面是结果集：
　　　
　　(3 row(s) affected)
　　
　　下例在两个参数的默认值已定义的情况下，省略了第二个参数，因此可找到姓为 Ringer 的所有作者的书和出版商：
　　
　　EXECUTE au_info2 Ringer
　　GO
　　
　　(4 row(s) affected)