SQL——存储过程

1. 为什么使用存储过程

     应用程序通过T-SQL语句到服务器的过程是不安全的。

     1） 数据不安全

      2）每次提交SQL代码都要经过语法编译后在执行，影响应用程序的运行性能

      3） 网络流量大

 

2. 什么是存储过程

        存储过程是SQL语句和控制语句的预编译集合，保存在数据库里，可由应用程序调用执行，而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。保存在SQLServer中，通过名称和参数执行，也可一返回结果。对于存储过程我更倾向于把他理解成方法。它里面可以只有一条查询语句，也可以包含一系列使用控制流的SQL语句。

 

3. 存储过程的优点

     1） 模块化呈现设计

     2） 执行速度快，效率高

     3） 减少网络流量

     4） 具有良好的安全性

4. 存储过程的分类

      1）系统存储过程

      2）扩展存储过程（属于系统存储过程的一种）

      3）用户自定义存储过程

 

5. 系统存储过程

      它一般以"sp_"开头，是由SQL Server创建、管理和使用，它存放在Resource数据库中。类似C#语言类库中的方法，暂时先不考虑它是如何编写的，先了解常用的系统存储过程及调用方法。

     常见的系统存储过程，见下一篇文章

     调用方法：exec[ute]  存储过程名  [参数值]

 

6.  常用的扩展存储过程   xp_cmdshell

     xp_cmdshell  它可以完成DOS命令下的一些操作。

     exec  xp_cmdshell  DOS命令  [no_output]

     说明  no_output是可选参数，表示设置执行DOS命令后是否输出返回信息。

     示例： exec xp_cmdshell  'mkdir  D:\newdir'  output

     强调： 因为用户可以通过xp_cmdshell对操作系统做一些操作，如果该存储过程被黑客使用对操作系统做操作就麻烦了，所以通常会把xp_cmdshell 关闭掉：

     方法一： 

     SQL Server 2008版本及以上， 通过数据库右击  选择“方面”   ，在下拉列表中选择 “服务器安全‘ ， 下面的列表项中可以看到xmcmdshellEnable 设置。

     SQL Server2005版本及以下，通过开始- SQLServer- 外围设备查找

     方法二：

    关闭xp_cmdshell

    EXEC sp_configure 'show advanced options', 1;

    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 1;

    RECONFIGURE;

    开启xp_cmdshell

    EXEC sp_configure 'show advanced options', 1;

    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell', 0;

     RECONFIGURE;

 

7. 用户自定义存储过程

   语法：

   create  proc[edure] 存储过程名

            @参数1  数据类型 = 默认值 output, 

            ……

            @参数n  数据类型 = 默认值 output

  as  

              <SQL 语句>

  go

 

 一个完成的存储过程包含以下3部分：

   1） 输入参数、输出参数

   2） 在存储过程中执行的T-SQL语句

   3） 存储过程的返回值

其中输入参数允许有默认值。

    删除存储过程

    drop proc  存储过程名

    if  exists (select * from sysobject where name = 存储过程名)

             drop proc  存储过程名

    go

 

8.  注意事项

       存储过程的声明： 输入参数可以有默认值，输出参数也可以有默认值

      create proc  usp_name

                  @age int = 5,

　　　　　　 @name varchar(10)        

       as 

           ……

       go

        执行语句：  

           exec  pr_name  18 , 'zm'

            exec  default  , 'zm'

            exec  @name = 'zm'

       说明： 为了调用方便，最好将有默认值的存储过程参数列表放到最后。

 

       带输出参数的存储过程

       create proc usp_name

               @num1  int,

               @sum int output

       as

             <SQL语句>

       go 

     调用存储过程 

      declare @sum int 

      exec  usp_name  5, @sum  output

      注意， 调用带有输出参数的存储过程参数后面必须带output关键字

 

9. 处理存储过程中的错误

    raiserror  （ {msg_id  | msg_str} {, serverity, state } [with option [,……]]）

    其中:

    msg_id: 在sysmessage系统表中指定用户定义错误信息

    msg_str: 用户定义的特定信息，最长为255个字符

    serverity: 与特定信息相关联，表示用户定义的严重性级别。用户可选用的级别是0~18。数字越大，表示越严重。

     state : 表示错误的状态， 1~255中的值

     option: 错误的自定义选项，可以使一下任意一值

　　　　LOG: 在Microsoft SQl Server 数据库引擎示例的错误日志和应用程序日志中记录错误

          NOWAIT:将消息立即发送给客户端

 　　    SETERROR:将@@error值和 ERROR_NUMBER 值设置为msg_id 或5000， 不用考虑严重级别。

          例如： raiserror ('错误信息', 16,1)