SQl Server存储过程基础

 

一、存储过程的概念

　　　　存储过程是SQL语句和可选控制流语句的预编译集合，存储在数据库中，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。

在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。

       　　使用存储过程有几下几个优点：

       　　1.可以在单个存储过程中执行一系列SQL语句。

       　　2.可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。

   　　　 3.存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，而且减少网络通信的负担。

       　　4.安全性更高，使用参数，天然的避免Sql注入。

二、创建存储过程

　　　　我们在使用Sql语句来创建存储过程前，应该考虑下列几个事项：

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

　　　　　　2、存储过程可以嵌套使用，嵌套的最大深度不能超过32层。

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

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

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

　　　　　　6、一个存储过程的最大尺寸为128M。

　　　　创建存储过程的语法如下：

　　　　　　CREATE   PROCEDURE   存储过程名

         　　（

                   参数1  参数类型=参数值            参数方向

                   参数2  参数类型=参数值            参数方向

　　　　　　  )

　　　　　　AS

                  　　Begin

                            存储过程体

                            RETURN

                 　　 End

　　　　下面我们使用示例来展示如何创建一个存储过程：      　　

USE Northwind
GO 
 /*存储过程被创建之后，它的名字就存储在系统表sysobjects中，
  它的源代码存放在系统表syscomments中,所以在创建存储过程之前，
  先判断一下系统中是否已经创建过该存储过程了，如果创建过了，那么先删除该存储过程。*/
 If  Exists(select * from sysobjects where name=’ myProcedure’ and type=’p’)         
　　Drop  procedure  myProcedure;
GO
 
Create  Proc   myProcedure
( @SupplierID_2         int,
  @CategoryID_3         int,
  @ProductName_1        nvarchar(40)='无',     --该参数默认值为’无’                   
  @ProductName_2        nvarchar(40)   output)
AS          
　　Begin
　　　　　　Insert   into  Products 　　(ProductName,SupplierID,CategoryID) 
　　　　　　　　　　　　　　　values   　　(@ProductName_1,@SupplierID_2,@CategoryID_3); 
           select @ProductName_2 = ProductName   from products          
 　　　　　　　　　　　　　　　　　　where SupplierID = @SupplierID_1 ；                  
　　end
GO

　　注意：　1. 语句体中，使用SET来给参数赋值。

           　　2. 存储过程的所有变量的参数都需要用“@”作为开始符；

           　　3. 在存储过程中能声明变量，能进行循环操作，如下：

Create    procedure   BatchAddClass

As

Begin

      Declare    @count    int

      Set   @count=10

      While   @count>0

           Begin

                Insert into  tbClass(ClassName) Values(@count)

                Set   @count=@count-1

           End

      Return

End

 　　上述语句实现了批量插入10条记录到tbClass表中；

三、执行存储过程

　　直接执行存储过程可以使用EXECUTE命令来执行，下面使用示例来执行存储过程：

declare   @product   nvarchar(40)

exec  myProcedure   1, 1001,  @product output

select '产品名'= @product

go

　　使用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下：

　　　　sp_help：用于显示存储过程的参数及其数据类型

              　sp_help [name]

　　　　　　参数name为要查看的存储过程的名称。

     　　sp_helptext：用于显示存储过程的源代码

                 sp_helptext [name]

　　　　　　参数name为要查看的存储过程的名称。

     　　sp_depends：用于显示和存储过程相关的数据库对象

                 sp_depends [name]

　　　　　　参数name为要查看依赖关系的存储过程的名称。

     　　sp_stored_procedures：用于返回当前数据库中的存储过程列表

四、修改存储过程

　　存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。修改存储过程的方法和创建存储过程的方法一样，只是将创建存储过程的Create  Procedure改为Alter Procedure而已。

五、重命名和删除存储过程

　　1. 重命名存储过程

                修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：

                    　　sp_rename  原存储过程名称，新存储过程名称

           　　另外，通过企业管理器也可以修改存储过程的名称。

　　2.删除存储过程

　　　　　　删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：

         　　　　drop    procedure {procedure} [,…n]

　　　　　　当然，利用企业管理器也可以很方便地删除存储过程。

六、存储过程的重新编译

 　　在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQL Server提供三种重新编译存储过程的方法 ：

    1、在建立存储过程时设定重新编译

       语法格式：

CREATE  PROCEDURE   procedure_name    

WITH   RECOMPILE    AS   sql_statement

     2、在执行存储过程时设定重编译

       语法格式：

EXECUTE  procedure_name  WITH  RECOMPILE

    3、通过使用系统存储过程设定重编译

        语法格式

 EXEC  sp_recompile  OBJECT

七、系统存储过程与扩展存储过程

　　1.系统存储过程

　　　　系统存储过程存储在master数据库中，并以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库对象提供方便。比如用来查看数　　据库对象信息的系统存储过程sp_help、显示存储过程和其它对象的文本的存储过程sp_helptext等。

　　2.扩展存储过程：

　　　　扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用Transact-SQL过程那样从Transact-SQL语句调用这些函数。

例:  利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。

               use master

               exec   xp_cmdshell   'dir *.exe'   

    执行结果返回系统目录下的文件内容文本信息。