MSSQL存储过程详解

SQL Server 存储过程详解

存储过程的优缺点

　　◆优点：
　　执行速度更快。存储过程只在创造时进行编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程执行速度更快。
　　存储过程用于处理复杂的操作时，程序的可读性更强、网络的负担更小。
　　使用存储过程封装事务性能更佳。
　　能有效的放注入，安全性更好。
　　可维护性高，在一些业务规则发生变化时，有时只需调整存储过程即可，而不用改动和重编辑程序。
　　更好的代码重用。

　　◆ 缺点：
　　存储过程将给服务器带来额外的压力。
　　 存储过程多多时维护比较困难。
　　移植性差，在升级到不同的数据库时比较困难。
　　调试麻烦，SQL语言的处理功能简单。

　　总之复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL语句不建议使用存储过程。

存储过程定义

　　存储过程是一组 Transact-SQL 语句，它们只需编译一次，以后即可多次执行。因为 Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。
　　触发器是一种特殊的存储过程，不由用户直接调用。创建触发器时，将其定义为在对特定表或列进行特定类型的数据修改时激发。

存储过程的设计规则

　　CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，但以下语句除外。

　　不能在存储过程的任何位置使用这些语句。
　　CREATE AGGREGATE、 CREATE RULE、CREATE DEFAULT、 CREATE SCHEMA、CREATE 或 ALTER FUNCTION、CREATE 或 ALTER TRIGGER、CREATE 或 ALTER PROCEDURE、CREATE   或 ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、 SET SHOWPLAN_XML、USE database_name
  
　　其他数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
　　可以在存储过程内引用临时表。
　　如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
　　如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。
　　如果执行对远程 Microsoft SQL Server 2005 实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
　　存储过程中的参数的最大数目为 2100。
　　存储过程中的局部变量的最大数目仅受可用内存的限制。
　　根据可用内存的不同，存储过程最大可达 128 MB

实现存储过程

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
             [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] --名称、类型、默认值、方向
             [ ,...n ]
         [ WITH <procedure_option> [ ,...n ]
         [ FOR REPLICATION ]
         AS
           { <sql_statement> [;][ ...n ] | <method_specifier> } --SQL语句
         [;]
         <procedure_option> ::=
             [ ENCRYPTION ]
             [ RECOMPILE ] --运行时编译
             [ EXECUTE_AS_Clause ]
         <sql_statement> ::= { [ BEGIN ] statements [ END ] }
         <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name

执行存储过程

　　使用 Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程
　　使用 sp_procoption 让SQLSERVER 自动执行存储过程
           sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ] 'option'    , [ @OptionValue = ] 'value' --过程的名称、option 的唯一值为 startup、设置为开启（true 或 on）还是关闭（false 或 off）。

用TSQL语句编写存储过程

一、变量和参数
           DECLARE 语句通过以下操作初始化 Transact-SQL 变量：
           指定名称。名称的第一个字符必须为一个 @。
           指定系统提供的或用户定义的数据类型和长度。对于数值变量还指定精度和小数位数。对于 XML 类型的变量，可以指定一个可选的架构集合。
           将值设置为 NULL。
           如：DECLARE @MyCounter int
           第一次声明变量时，其值设置为 NULL。若要为变量赋值，请使用 SET 语句。这是为变量赋值的首选方法。也可以通过 SELECT 语句的选择列表中当前所引用值为变量赋值。
           参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据：
           输入参数允许调用方将数据值传递到存储过程或函数。
           输出参数允许存储过程将数据值或游标变量传递回调用方。用户定义函数不能指定输出参数。
           每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。

二、流程控制语句

           1、BEGIN 和 END 语句
               BEGIN 和 END 语句用于将多个 Transact-SQL 语句组合为一个逻辑块。在控制流语句必须执行包含两条或多条 Transact-SQL 语句的语句块的任何地方，都可以使用 BEGIN 和 END 语句。
如：
IF (@@ERROR <> 0)
BEGIN
      SET @ErrorSaveVariable = @@ERROR
      PRINT 'Error encountered, ' +
     CAST(@ErrorSaveVariable AS VARCHAR(10))
END
           2、GOTO 语句
               GOTO 语句使 Transact-SQL 批处理的执行跳至标签。不执行 GOTO 语句和标签之间的语句。
       IF(1=1)
    GOTO calculate_salary
    print 'go on' --条件成立则跳过此句。
       calculate_salary:
     print 'go to'
           3、IF...ELSE 语句
               IF 语句用于条件的测试。得到的控制流取决于是否指定了可选的 ELSE 语句：
    if(1=1)
     print 1
    else if(2=2)
     print 2
    else if(3=3)
     print 3
    else
     print 0
           4、RETURN 语句
                 RETURN 语句无条件终止查询、存储过程或批处理。存储过程或批处理中 RETURN 语句后面的语句都不执行。当在存储过程中使用 RETURN 语句时，此语句可以指定返回给调用应用程序、批处理或过程的整数值。如果 RETURN 未指定值，则存储过程返回 0
           5、WAITFOR 语句
                 WAITFOR 语句挂起批处理、存储过程或事务的执行，直到发生以下情况：
     已超过指定的时间间隔。
     到达一天中指定的时间。
     指定的 RECEIVE 语句至少修改一行或并将其返回到 Service Broker 队列。
                 WAITFOR 语句由下列子句之一指定：
             DELAY 关键字后为 time_to_pass，是指完成 WAITFOR 语句之前等待的时间。完成 WAITFOR 语句之前等待的时间最多为 24 小时。
     如：
      WAITFOR DELAY '00:00:02'
      SELECT EmployeeID FROM    Employee;
             TIME 关键字后为 time_to_execute，指定 WAITFOR 语句完成所用的时间。
      GO
      BEGIN
          WAITFOR TIME '22:00';
          DBCC CHECKALLOC;
      END;
      GO
             RECEIVE 语句子句，从 Service Broker 队列检索一条或多条消息。使用 RECEIVE 语句指定 WAITFOR 时，如果当前未显示任何消息，该语句将等待消息到达队列。
             TIMEOUT 关键字后为 timeout，指定 Service Broker 等待消息到达队列的时间长度（毫秒）。可以在 RECEIVE 语句或 GET CONVERSATION GROUP 语句中指定 TIMEOUT。
           6、WHILE...BREAK 或 CONTINUE 语句
                 只要指定的条件为 True 时，WHILE 语句就会重复语句或语句块。REAK 或 CONTINUE语句通常和WHILE一起使用。BREAK 语句退出最内层的 WHILE 循环，CONTINUE 语句则重新开始 WHILE 循环。

  go
  declare @Num int
  declare @ID int
  declare @i int
  set @i=1
  while(exists(select * from T where Num<5    )) --获取数量小于5的记录
  begin
   select @Num=Num,@ID=ID from T where Num<5 order by ID desc
   print Str(@i)+ '编号：'+Str(@ID)+ ' 值'+str(@Num)
   update T set Num=Num*2 where ID=@ID
   set @i=@i+1
   if(@i>3)
     break --退出循环
   
  end

           7、CASE 语句
            CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是将代码或缩写替换为可读性更强的值

--用法一：
  select ID,
    Grade=Case Num  
  when  1 then '不及格'  
  when  2 then '不及格'
  when  3 then '不及格'
  when  4 then '良好'
  else '优秀'
  end  
  from T
  ---用法二：
  select ID,
    Grade=Case   
  when    Num<3 then '不及格'  
  when    Num=3 then '及格'
  when    Num=4 then '良好'
  when    Num>4 then '优秀'
  end  
  from T

三、运行时生成语句

           Transact-SQL 支持使用下列两种方法于运行时在 TTransact-SQL 脚本、存储过程和触发器中生成 SQL 语句：
使用 sp_executesql 系统存储过程执行 Unicode 字符串。sp_executesql 支持与 RAISERROR 语句类似的参数替换。
           使用 EXECUTE 语句执行字符串。EXECUTE 语句不支持已执行字符串中的参数替换。

四、处理数据库引擎错误
          在 Transact-SQL 中有两种方式可以获取错误信息：
          1、在 TRY...CATCH 构造的 CATCH 块的作用域内，您可以使用以下系统函数：
            ERROR_LINE()，返回出现错误的行号。
            ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。
            ERROR_NUMBER() 返回错误号。
            ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。
            ERROR_SEVERITY() 返回严重性。
            ERROR_STATE()，返回状态。
           2、在执行任何 Transact-SQL 语句之后，您可以立即使用 @@ERROR 函数测试错误并检索错误号。
           RAISERROR
             RAISERROR 用于将与 SQL Server Database Engine 生成的系统错误或警告消息使用相同格式的消息返回到应用程序中。
           3、PRINT
             PRINT 语句用于将消息返回到应用程序。PRINT 采用字符或 Unicode 字符串表达式作为参数，并将字符串作为消息返回到应用程序。