存储过程的建立和使用

一、什么是存储过程呢？
  定义：将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
二、存储过程的优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
三、存储过程的种类：
1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
2.扩展存储过程 以XP_开头,用来调用操作系统提供的功能。

3.用户自定义的存储过程,这是我们所指的存储过程。

SQL Server 中常用格式：

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  

四、机房收费系统实例：

SQL Server中建立存储过程：

USE [JFXT]
GO
/****** Object:  StoredProcedure [dbo].[PROC_savecard]    Script Date: 2017/11/1 11:16:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<谷海涛>
-- Create date: <2017/11/01,>
-- Description:	<创建注册过程,>
-- =============================================

CREATE PROCEDURE [dbo].[PROC_savecard]  --此处开头是CREATE 才能保存该存储过程。别忘记点击执行测试是否成功。
    @cardNo char(10),    
    @studentNo char(10),    
    @cash numeric(10, 1),   
    @status varchar(10),    
    @date date,    
    @time time(7),    
    @operator varchar(10),    
    @isCheck varchar(6),    
    @state varchar(10),    
    @sex char(6),    
    @department varchar(10),    
    @grade varchar(10),    
    @studentName varchar(10)
AS 
BEGIN
	SET NOCOUNT ON;
	declare @error int  
        set @error =0  
      begin transaction
    --在表card_Info 中添加卡号    
    insert into T_CardInfo  values(@cardNo ,@SID ,@UserID ,@type ,@Cash ,@Status ,@ischeck ,@RegisterDate ,@RegisterTime )    
          
    insert into T_StudentInfo  values(@SID ,@SName , @Ssex, @Sdepartment ,@Sgrade ,@Sclass ,@explain )    
       
    insert into T_rechargeInfo(CardNo,recharge,date ,Time )values (@cardNo ,@cash,@RegisterDate ,@RegisterTime)  
	set @error =@error +@@ERROR   
       
    if @error <>0  
      rollback transaction   --如果不等于0，,则回滚事务，不能执行  
    else  
      commit transaction     --等于0，则执行该事务   
END

代码中引用存储过程：

Public Class RegisterDAL : Implements RegisterIDAL
    Public Function saveCard(ByVal cardInfo As Entity.CardEntity) As DataTable Implements RegisterIDAL.saveCard
        Dim count As Integer
        Dim sql As String = "PROC_savecard"
        Dim flag As Boolean = False
        Dim sqlParams As SqlParameter() = {New SqlParameter("@cardNo", cardInfo.CardNo),
                                           New SqlParameter("@studentNo", cardInfo.StudentNo),
                                           New SqlParameter("@cash", cardInfo.Cash),
                                           New SqlParameter("@status", cardInfo.Status),
                                           New SqlParameter("@date", cardInfo.Date),
                                           New SqlParameter("@time", cardInfo.Time),
                                           New SqlParameter("@operate", cardInfo.Operator),
                                                ................                         }
        'sql = "insert into T_CardInfo(cardNo,studentNo,cash,status,Date,......)Values(@card,@studentNo,@cash,@Status,......)"
        count = SQLHelper.sqlHelper.ExecuteNoQuery(sql, CommandType.StoredProcedure, sqlParams)
        If count <> 0 Then
            flag = True
        End If
        Return flag
    End Function

（备注：CommandType.StoredProcedure 是使用存储过程；

CommandType.Text  是使用sql语句。
        代码中的格式没有问题，代码中数据有删改，请勿直接使用！）

五、对于事务发生了错误，事务回滚其实有两种方案：

 1、第一种方案是采用 SET XACT_ABORT ON (如果产生错误自动回滚)

SET  NOCOUNT ON                   --返回计数
SET　 XACT_ABORT　 ON          
GO  
BEGIN　 TRANSACTION  
INSERT　 INTO　 A　 VALUES　 (4)     --SQL语句
INSERT　 INTO　 B　 VALUES　 (5) 
    ...... 
COMMIT　 TRANSACTION  

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

SET NOCOUNT ON
DECLARE  @error  int
SET @error = 0
BEGIN TRANSACTION  
INSERT INTO A　 values　(4)　 ----- SQL语句
....... 
IF @error<> 0 　   
　　 ROLLBACK TRANSACTION　　 --如果不等于0，则回滚事务，不执行   
　 END  
ELSE  
　  
　　 COMMIT TRANSACTION　    --如果等于0.执行事务。
　 END  

小结：对于存储过程的优点，简化了管理和操作。一个存储过程可以完成三个表的更新和插入，实现了高性能的数据操作。具体的实用经验后续分享。