SQL Server之存储过程

一、什么是存储过程

　　存储过程是存储在服务器上的T-SQL语句的命名集合，封装重复性任务的方法，支持用户声明变量，条件控制执行等编程特性。

二、存储过程的好处

• 　　存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
• 　　当对数据库进行复杂逻辑操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
• 　　存储过程可以重复使用,可减少数据库开发人员的工作量。
• 　　安全性高,可设定只有某些用户才具有对指定存储过程的使用权。
• 　　客户端用一条语句调用存储过程，就可以完成大量语句，减少了客户端和服务器之间的请求包，即网络流量。

三、存储过程的分类

　　1. 系统存储过程

　　　　由系统定义，存放在系统master数据库中，以'sp_'或‘xp_'开头：

　　　　

 

　　2. 用户自定义存储过程

　　　　由用户在自己的数据库中创建的存储过程

四、存储过程的使用

　　1. 存储过程的创建

　　创建与使用存储过程的通用语法：

/*创建存储过程*/
CREATE  PROC[EDURE]  存储过程名 
             @参数1  数据类型 = 默认值 OUTPUT,  --参数为可选项
             @参数2  数据类型 = 默认值   
             …… ,
AS
      T-SQL语句
/*使用存储过程*/
DECLARE @参数1 数据类型，@参数2 数据类型，...
EXEC 存储过程名 @参数1 OUTPUT，@参数2，...    --参数为可选项，和具体存储过程有关

　　存储过程的参数分为两种：输入参数、输出参数

　　输入参数：用来向存储过程传递值

　　输出参数：在调用存储过程后返回结果

　　(1) 不带参的存储过程

CREATE PROC proc_test2
AS
    DECLARE @test INT
    SELECT @test = Sno FROM Student WHERE Sage=24    
GO
EXEC proc_test2

　　(2) 带输入参数的存储过程

CREATE PROCEDURE proc_stu 
    @writtenPass int,  
    @labPass int    
AS
    print '--------------------------------------------------' 
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,
       labExam  FROM  stuInfo
          INNER JOIN stuMarks ON               
             stuInfo.stuNo=stuMarks.stuNo
                 WHERE writtenExam<@writtenPass 
                                                  OR labExam<@labPass 
GO
/*以下二选一*/
EXEC proc_stu 60,55  
EXEC proc_stu @labPass=55,@writtenPass=60　　

　　(3) 带输出参数的存储过程

CREATE PROCEDURE proc_stu 
    @notpassSum int OUTPUT, 
    @writtenPass int,   
    @labPass int
AS
     ……
     SELECT stuName,stuInfo.stuNo,writtenExam,
        labExam FROM  stuInfo   INNER JOIN stuMarks
          ON stuInfo.stuNo=stuMarks.stuNo
            WHERE writtenExam<@writtenPass
              OR labExam<@labPass 
     SELECT @notpassSum=COUNT(stuNo) 
       FROM stuMarks  WHERE writtenExam < @writtenPass  
                                                   OR labExam<@labPass 
GO
DECLARE @sum int   
EXEC proc_stu @sum OUTPUT,64,40

　　(4) 参数为默认值的存储过程

CREATE PROCEDURE proc_stu 
    @writtenPass int=60,  
    @labPass int=60    
AS
    print '--------------------------------------------------' 
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,
       labExam  FROM  stuInfo
          INNER JOIN stuMarks ON               
             stuInfo.stuNo=stuMarks.stuNo
                 WHERE writtenExam<@writtenPass 
                                                  OR labExam<@labPass 
GO
EXEC PROC proc_stu   --使用默认值则无需传入任何参数

　　2. 存储过程的修改

ALTER PROC[EDURE] 存储过程名
AS
  SQL语句

　　3. 存储过程的删除

DROP PROC 存储过程名

五、处理存储过程中的错误

　　可以在存储过程中使用PRINT语句显示错误信息，但这些信息都是临时的，只能显示给用户

　　引入了RAISERROR来显示用户定义的错误信息，RAISERROR可以指定严重级别，设置系统变量@@ERROR，记录所发生的错误等。

　　语法：

RAISERROR (msg_id | msg_str,severity,
   state WITH option[,...n]]) 

　　参数说明：

• msg_id：在sysmessages系统表中指定用户定义错误信息
• msg_str：用户定义的特定信息，最长255个字符
• severity：定义严重性级别。用户可使用的级别为0–18级
• state：表示错误的状态，1至127之间的值
• option：指示是否将错误记录到服务器错误日志中

/*引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值 */
RAISERROR (‘及格线错误，请指定0－100之间的分 
                     数，统计中断退出‘,16,1)

 

 

　　

转载于:https://www.cnblogs.com/chenloveslife/p/9034153.html