SQLServer存储过程介绍

SQL Server存储过程是一个被命名的存储在服务器上的Transacation-Sql语句集合,是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。

SQL Server存储过程相对于其他的数据库访问方法有以下的优点：

(1)重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

(2)提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。

(3)减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

(4)安全性。参数化的存储过程可以防止SQL注入式的攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

SQL Server存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。

其中，用户定义的存储过程又分为Transaction-SQL和CLR两种类型。

1.Transaction-SQL 存储过程是指保存的Transaction-SQL语句集合，可以接受和返回用户提供的参数。

2.CLR存储过程是指对.Net Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。他们在.Net Framework程序集中是作为类的公共静态方法实现的。(本文就不作介绍了)

创建SQL Server存储过程的语句如下：

以下为引用的内容：

 
 
 
    
  
  
  
  1. CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]  
    2. 
  
  
  
  2.     [ { @parameter [ type_schema_name. ] data_type }  
    3. 
  
  
  
  3.         [ VARYING ] [ = default ] [ [ OUT [ PUT ]  
    4. 
  
  
  
  4.     ] [ ,n ]  
    5. 
  
  
  
  5. [ WITH < procedure_option> [ ,n ] 
    6. 
  
  
  
  6. [ FOR REPLICATION ]  
    7. 
  
  
  
  7. AS { < sql_statement> [;][ n ] | < method_specifier> } 
    8. 
  
  
  
  8. [;] 
    9. 
  
  
  
  9. < procedure_option> ::=  
    10. 
  
  
  
  10.     [ ENCRYPTION ] 
    11. 
  
  
  
  11.     [ RECOMPILE ] 
    12. 
  
  
  
  12.     [ EXECUTE_AS_Clause ] 
    13. 
  
  
  
  13. 
  
  
  
  14. < sql_statement> ::=  
    15. 
  
  
  
  15. { [ BEGIN ] statements [ END ] } 
    16. 
  
  
  
  16. 
  
  
  
  17. < method_specifier> ::= 
    18. 
  
  
  
  18. EXTERNAL NAME assembly_name.class_name.method_name 
    19. 
  
  
  
  19.

[schema_name]: 代表的是存储过程所属的架构的名称

例如：

 
 
 
    
  
  
  
  1. Create Schema yangyang8848       
    2. 
  
  
  
  2. Go 
    3. 
  
  
  
  3. Create Proc yangyang8848.AllGoods 
    4. 
  
  
  
  4. As Select * From Master_Goods 
    5. 
  
  
  
  5. Go
    6.

执行：Exec AllGoods 发生错误。

 执行：Exec yangyang8848.AllGoods 正确执行。

[；Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

例如：

 
 
 
    
  
  
  
  1. Create Proc S1 ;1 
    2. 
  
  
  
  2. AS 
    3. 
  
  
  
  3.        Select * From Master_Goods 
    4. 
  
  
  
  4. Go 
    5. 
  
  
  
  5. Create Proc S1 ;2 
    6. 
  
  
  
  6. As  
    7. 
  
  
  
  7.        Select * From Master_Location 
    8. 
  
  
  
  8. Go
    9.

创建完毕了两个存储过程。它们在同一个组S1里，如果执行Exec S1 则存储过程默认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行Exec S1 ;2。当我们要删除存储过程的时候，只能执行Drop Exec S1 则该组内所有的存储过程被删除。

[@ parameter]: 存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。

SQL Server存储过程最多有2100个参数。

例如：

 
 
 
    
  
  
  
  1. Create Proc yangyang8848.OneGoods 
    2. 
  
  
  
  2. @GoodsCode varchar(10) 
    3. 
  
  
  
  3. As  
    4. 
  
  
  
  4.        Select * From Master_Goods Where GoodsCode = @GoodsCode 
    5. 
  
  
  
  5. Go
    6.

调用的代码：

 
 
 
    
  
  
  
  1. Declare @Code varchar(10) 
    2. 
  
  
  
  2. Set @Code = '0004'
    3. 
  
  
  
  3. Exec yangyang8848.OneGoods @Code
    4.

在参数的后边加入Output 表明该参数为输出参数。

 
 
 
    
  
  
  
  1. Create Proc yangyang8848.OneGoods 
    2. 
  
  
  
  2. @GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011'
    3. 
  
  
  
  3. As  
    4. 
  
  
  
  4.        Select * From Master_Goods Where GoodsCode = @GoodsCode 
    5. 
  
  
  
  5.        Set @GoodsCode2 = '0005'
    6. 
  
  
  
  6. Go
    7.

调用方法：

 
 
 
    
  
  
  
  1. Declare @VV2 varchar(10) 
    2. 
  
  
  
  2. Exec yangyang8848.OneGoods  @Code out
    3.

注意：如果存储过程的两个参数一个有默认值一个没有，那么我们要把有默认值得放在后边，不然会出问题哦~~

细心的朋友，可能看到上边的语句有一些不同，比如，存储过程用的是output，而调用语句用的是out。我要告诉您，两者是一样的。

 [RECOMPILE]：指示数据库引擎 不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定 RECOMPILE。

这个说一个非常好用的函数 OBJECT_ID ：返回架构范围内对象的数据库对象标识号。

例如：我们创建存储过程时，可以如下写代码

 
 
 
    
  
  
  
  1. If Object_ID('yangyang8848.OneGoods') Is Not Null 
    2. 
  
  
  
  2.        Drop Proc yangyang8848.OneGoods 
    3. 
  
  
  
  3. Go 
    4. 
  
  
  
  4. 
  
  
  
  5. Create Proc yangyang8848.OneGoods 
    6. 
  
  
  
  6. @GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
    7. 
  
  
  
  7. As  
    8. 
  
  
  
  8.        Select * From Master_Goods Where GoodsCode = @GoodsCode 
    9. 
  
  
  
  9.        Set @GoodsCode2 = '0005'
    10. 
  
  
  
  10. Go 
    11.

针对于上边的这个存储过程，我们调用以下SQL查询

 
 
 
    
  
  
  
  1. Select definition From sys.sql_modules 
    2. 
  
  
  
  2.       Where object_id = Object_ID('yangyang8848.OneGoods');
    3.

我们是可以查到结果的。

可是如果我们对该存储过程加入[ ENCRYPTION ] 那么你将无法看到任何结果

 
 
 
    
  
  
  
  1. If Object_ID('yangyang8848.OneGoods') Is Not Null 
    2. 
  
  
  
  2.          Drop Proc yangyang8848.OneGoods 
    3. 
  
  
  
  3.   Go 
    4. 
  
  
  
  4. 
  
  
  
  5.   Create Proc yangyang8848.OneGoods 
    6. 
  
  
  
  6.   @GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
    7. 
  
  
  
  7. 
  
  
  
  8.   With Encryption 
    9. 
  
  
  
  9.   As  
    10. 
  
  
  
  10.          Select * From Master_Goods Where GoodsCode = @GoodsCode 
    11. 
  
  
  
  11.          Set @GoodsCode2 = '0005'
    12. 
  
  
  
  12.   Go 
    13.

然后我们查询 sys.sql_modules 目录视图，将返回给你Null。

然后我们执行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods'

你将得到以下结果：The text for object 'yangyang8848.OneGoods' is encrypted.

说到这里你应该明白了，参数[ ENCRYPTION ]：是一种加密的功能， 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。

前两天写了一篇关于游标的介绍文章 ，下边写一个例子，将游标与存储过程一起使用上：

 
 
 
    
  
  
  
  1. If Object_ID('dbo.GetMasterGoods') Is Not Null 
    2. 
  
  
  
  2.        Drop Proc dbo.GetMasterGoods 
    3. 
  
  
  
  3.  Go 
    4. 
  
  
  
  4. 
  
  
  
  5.  Create Proc GetMasterGoods 
    6. 
  
  
  
  6.  @MyCursor Cursor Varying Output 
    7. 
  
  
  
  7.  With Encryption 
    8. 
  
  
  
  8.  As  
    9. 
  
  
  
  9.         Set @MyCursor = Cursor 
    10. 
  
  
  
  10.         For 
    11. 
  
  
  
  11.                Select GoodsCode,GoodsName From Master_Goods 
    12. 
  
  
  
  12.  Open @MyCursor 
    13. 
  
  
  
  13.  Go 
    14.

下边建立另外一个存储过程，用于遍历游标输出结果

 
 
 
    
  
  
  
  1. Create Proc GetAllGoodsIDAndName 
    2. 
  
  
  
  2. As 
    3. 
  
  
  
  3. 
  
  
  
  4. Declare @GoodsCode varchar(18) 
    5. 
  
  
  
  5. Declare @GoodsName nvarchar(20) 
    6. 
  
  
  
  6. Declare @MasterGoodsCursor Cursor 
    7. 
  
  
  
  7. Exec GetMasterGoods @MasterGoodsCursor out
    8. 
  
  
  
  8. Fetch Next From @MasterGoodsCursor 
    9. 
  
  
  
  9. InTo @GoodsCode,@GoodsName 
    10. 
  
  
  
  10. While(@@Fetch_Status = 0) 
    11. 
  
  
  
  11. Begin 
    12. 
  
  
  
  12.        Begin 
    13. 
  
  
  
  13.               Print @GoodsCode + ':' + @GoodsName 
    14. 
  
  
  
  14.        End 
    15. 
  
  
  
  15.        Fetch Next From @MasterGoodsCursor 
    16. 
  
  
  
  16.        InTo @GoodsCode,@GoodsName 
    17. 
  
  
  
  17. End 
    18. 
  
  
  
  18. Close @MasterGoodsCursor 
    19. 
  
  
  
  19. Deallocate @MasterGoodsCursor 
    20. 
  
  
  
  20. Go 
    21.

最后执行Exec GetAllGoodsIDAndName结果为以下内容

 
 
 
    
  
  
  
  1. 0003:品0003 
    2. 
  
  
  
  2.  0004:品0004 
    3. 
  
  
  
  3.  0005:123123 
    4. 
  
  
  
  4.  0006:品0006 
    5. 
  
  
  
  5.  0007:品0007 
    6. 
  
  
  
  6.  0008:品0008 
    7. 
  
  
  
  7.  0009:品0009 
    8. 
  
  
  
  8.  0010:品0010 
    9. 
  
  
  
  9.  0011:品0011 
    10. 
  
  
  
  10.  0012:品0012 
    11. 
  
  
  
  11.  0013:品0013 
    12. 
  
  
  
  12.  0014:品0014 
    13.

分享文章：SQLServer存储过程介绍
地址分享：http://www.shufengxianlan.com/qtweb/news37/362187.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

响应式网站知识

分类信息网

崇州做网站　　 　德阳东方电机技改服务有限公司　　 　网站优化公司　　 　小谭网创广告　　 　电商网站　　 　营销型网站建设　　 　成都企业网站建设　　 　成都网站改版　　 　seo优化　　 　成都木托盘　　 　移动网站建设　　 　成都免费自助建站　　 　成都seo优化　　 　官网SEO排名　　 　盐亭网站建设　　 　成都微信开发　　 　成都网站建设　　 　边坡防护网　　 　成都力比特　　 　成都网站维护