详解数据库之存储过程与错误处理

定义：

　　为了完成特定功能，预先用SQL语句写好并经编译后存储在数据库中。

看到定义，刚开始学习的人一定有很多的疑问，到底存储过程和一般的SQL语句有什么区别呢？

存储过程的优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程一旦被创建，在程序中调用任意多次，重复使用，可减少数据库开发人员的工作量，最大的优点是允许模块化的程序设计。

4.安全性高。即时是没有访问存储过程引用表或视图的用户也可以访问执行存储过程。

5.存储过程可以减少网络流量。用户可以发送一个单独的语句实现一个复杂的操作，不需要发送多条SQL语句，较少了客户端和服务器请求的数量。

一.创建存储过程

 

create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
以上格式还可以简写成：
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/

 

二、执行存储过程

 

执行存储过程有三种方式，下面为大家一一介绍：

1.通过自身执行

基本语法：execprocedure_name[参数名]

2.在INSERT语句中执行

基本语法：

 

CREATEPROCEDURE procedure_name
@[Param1][类型],@[Param2] [类型]
AS
BEGIN
 insert into 表名(Param1,Param2,Param3) values(@Param1,@Param2,@Param3)
END

 

3.通过右键菜单执行

1.右击要执行的存储过程，如下图：

2.选择【执行存储过程】命令，打开【执行过程】窗口

三.管理存储过程

１.修改存储过程

基本语法：

Alter procedure procedure_name[参数名]

２.删除存储过程

1.基本语法：

drop procedureprocedure_name[参数名]

2.注意事项

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.错误信息处理

 

为了提高存储过程的效率，存储过程应该包含与用户进行交互的事物状态（成功或失败）的错误信息。在此，我主要为大家介绍@@error这种方式：

在做机房收费系统中添加学生信息，需要处理多个表，因此用到了存储过程，下面主要是通过创建此存储过程为大家介绍@@eeor：

 

USE [Roomcharge-sys]
GO

/****** Object:  StoredProcedure [dbo].[PROC_StudentRegister]    Script Date: 06/21/2013 16:42:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:于亮		<Author,,Name>
-- Create date: 13,06,20<Create Date,,>
-- Description:	注册用于存储学生信息<Description,,>
-- =============================================
CREATE PROCEDURE[dbo].[PROC_StudentRegister]
	-- Add the parameters for the stored procedure here
	--参数
@studentID int,@studentName varchar(10),@StudentSex varchar(10),@Department varchar(10),
@grade varchar(10),@Class varchar(10),@UserID int,@CardID int,@Cash varchar(10),@Type varchar(10),@IsCheck varchar(10),
@RegDate varchar(10),@RegTime varchar(10)
AS
BEGIN
    --事物
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRANSACTION  --开始事物
    
    --添加表
	insert into T_STUDENTINFO (StudentID,StudentName,StudentSex ,Department ,Grade ,Class,CardID ,UserID ) values (@StudentID,@StudentName,@StudentSex ,@Department ,@Grade ,@Class,@CardID,@UserID)
	insert into T_CARDINFO (CardID ,Cash ,Type ,regDate,RegTime,isCheck) values (@CardID ,@Cash ,@Type ,@regDate,@RegTime,@IsCheck)
	
	IF @@error<>0 --判断数据有没有错误
		BEGIN --有错误
			ROLLBACK TRAN --回滚事物
			commit tran --提交事物
		end
	else --没有错误
	commit tran --提交事物

END

GO

 

 

扫盲：

1.SET NOCOUNT

当 SET NOCOUNT 为 ON时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

2.SET XACT_ABORT

指定当 Transact-SQL语句出现运行时错误时，SQL Server 是否自动回滚到当前事务。

当 SET XACT_ABORT 为ON 时，如果执行 Transact-SQL 语句产生运行错误，则整个事务将终止并回滚。

当 SET XACT_ABORT 为OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为OFF，也可能回滚整个事务。OFF 是默认设置。