机房重构——存储过程详解

前言：

      最近在敲机房重构的时候在注册，需要建立注册存储过程的时候遇到了问题，由于对存储的过程的不熟悉，所以在网上和查书希望对它可以有一定的了解，对于以后的学习可以更加的方便些。  

内容： 

     存储过程是SQL语句和控制流语句预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。它使得对数据库的管理和操作更加的容易和，效率更高。

     当新建一个存储过程之后，系统会默认给出许多的代码，其中例如set nocount on 选项是防止回话中的每一条语句所影响的行数消息返回给客户机。

  举例创建一个存储过程，其实非常简单，打开建立的数据库，找到可编译性，然后建立存储过程，如下图所示：

 

     当选择新建之后，会系统会自动出现生成一些SQL语句。这些生成的语句是建立一个存储过程的模板，遵照这些固定的格式，就可以建立相应的存储过程。如下图是系统自动生成的格式，下面我自己添加的一些注释和理解。（仅供参考。）

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON            '主要是对于存在null值的设置，当为on时，丢存在空值的字段select查询结果零行
GO
SET QUOTED_IDENTIFIER ON      <span style="font-family: KaiTi_GB2312;">'，标识符可以由双引号分隔，而文字必须由单引号分隔</span>

GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>    ’这主要是创建一个存储过程的格式，包含有存储过程名，数据库名，一般只写存储民即可。
	-- Add the parameters for the stored procedure here      '添加一些必要的参数。
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from     
	-- interfering with SELECT statements.
	SET NOCOUNT ON;          '防止执行一行返回受影响的消息行数。

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>     '查询语句。
END
GO

  对于自动生成语句中：

    1.SET XACT_ABORT {on| off}指定了SQL语句运行时的错误，当选择on的时候，整个事物就会发生回滚，当现则off的时候，只回滚产生错误的Trancscat-sql语句，而事物继续进行处理。但是编译错误并不受 set XACT_ABORT的影响。set XACT_ABORT设置是在执行或运行的时候设置，而不是在分析时设置。

   2. SET ONCOUNT {ON|OFF},选项可选择防止将会话中每一条语句所影响的行数消息发回给请求的客户机，当选择为ON的时候，可以防止将每一行的影响返回给客户机，选择OFF的时候，返回计数。所以选择on的时候可以节省网络流量。

   3.SQL-92标准要求在对空值进行等于(=)或不等于(<>)比较时取值为FALSE。当SET ANSI_NULLS为ON时，无论是 =null值，还是<>null,返回结果仍然是零行。（这个还是不太懂会在哪里用到。）
    当SET ANSI_NULLS为OFF时，等于(=)和不等于(<>)比较运算符不遵从SQL-92标准。使用WHERE column_name=NULL的SELECT语句返回column_name中包含空值的行。使用WHERE column_name<>NULL的SELECT语句返回列中包含非空值的行。

 我建立的一个注册的存储过程，基本代码如下：

USE [cyl_charge_sys]
GO
/****** Object:  StoredProcedure [dbo].[cyl_RegisterCard]    Script Date: 2016/2/25 9:02:51 ******/
---- Author:		<十二期 - 常银铃>
---- Create date: <2016/2/25>
---- Description:	<创建注册过程，向cardinfo，rechargeinfo，studentinfo表中插入数据。>
---- =============================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 ALTER PROCEDURE [dbo].[cyl_RegisterCard]
    @cardID varchar(50),  
    @SID varchar(50),  
    @SName varchar(50), 
    @Ssex varchar(50),  
    @Sdepartment varchar(50),  
    @Sgrade varchar(50),  
    @Sclass varchar(50),  
    @explain varchar(50),  
    @UserID varchar(10),  
    @type varchar(10),  
    @RegisterDate varchar(50),  
    @RegisterTime varchar(50),  
    @Cash money,
	@ischeck varchar(50),
	@Status varchar(50)
AS
 begin   
  
    SET NOCOUNT OFF;         --不返回计数
    SET XACT_ABORT ON;       --发生错误，回滚事务，并且是整个事务回滚。
    BEGIN transaction  
      
    -- Insert statements for procedure here  
    --在表card_Info 中添加卡号  
    insert into cyl_Cardinfo  values(@cardID ,@SID ,@UserID ,@type ,@Cash ,@Status ,@ischeck ,@RegisterDate ,@RegisterTime )  
        
    insert into cyl_Studentinfo  values(@SID ,@SName , @Ssex, @Sdepartment ,@Sgrade ,@Sclass ,@explain )  
     
    insert into cyl_rechargeinfo(CardID,recharge,date ,Time )values (@cardID ,@cash,@RegisterDate ,@RegisterTime)  
      
     commit transaction       --执行该事务  
			
    END  
	

对于事务发生了错误，事务回滚其实有两种方案，第一种方案是采用 SET XACT_ABORT ON ( 如果产生错误自动回滚)

SET　 XACT_ABORT　 ON        
GO
BEGIN　 TRAN
INSERT　 INTO　 A　 VALUES　 (4)
INSERT　 INTO　 B　 VALUES　 (5)
COMMIT　 TRAN

第二种方案是:采用了 @@error，如果它的值是0，则没有错误。如果不是0那么就是有错误，发生回滚。

 BEGIN TRANSACTION
INSERT INTO A　 values　(4)　 ----- 该表含有触发器，UPDATE其他表
IF @@error $amp; 　 BEGIN
　　 ROLLBACK TRANSACTION　　 
　 END
ELSE
　 BEGIN
　　 COMMIT TRANSACTION　 
　 END
　

总结：

   对于存储过程的优点，简化了管理和操作，我觉得自己已经实践过了，一个存储过程可以完成三个表的更新和插入，感觉很神奇；而且通过隔离和加密的方法提高了数据库的安全性，允许进行模块化的升序设计，通过本地存储，实现了高性能的数据操作。而且存储过程是数据库管理中涉及的知识，看来对于我们学习的每一本书我们都应该时常的进行温习！