什么是存储过程?

q存储过程(procedure)类似于C语言中的函数

q用来执行管理任务或应用复杂的业务规则

q存储过程可以带参数,也可以返回结果

q存储过程可以包含数据操纵语句、变量、逻辑控制语句等

存储过程的优点

(1)执行速度快。

存储过程创建是就已经通过语法检查和性能优化,在执行时无需每次编译。

存储在数据库服务器,性能高。

(2)允许模块化设计。

只需创建存储过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建,并可独立于程序源代码而单独修改

(3)提高系统安全性。

可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限,建立特定的存储过程供用户使用,完成对数据的访问。

存储过程的定义文本可以被加密,使用户不能查看其内容。

(4)减少网络流量:

一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。

存储过程的分类

q系统存储过程

q由系统定义,存放在master数据库中

q类似C语言中的系统函数

q系统存储过程的名称都以“sp_”开头或”xp_”开头

q用户自定义存储过程

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

q类似C语言中的用户自定义函数

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务器上的所有数据库。

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

回某个表列的信息

sp_help

查看某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程。

sp_password

添加或修改登录帐户的密码。

sp_helptext

显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

EXEC sp_databases /*列出当前系统中的数据库*/

EXEC  sp_renamedb 'Northwind','Northwind1' /*修改数据库的名称(单用户访问,最简单的办法就是执行SQL语句时关掉企业管理器)*/

USE stuDB

GO

EXEC sp_tables /*当前数据库中查询的对象的列表*/

EXEC sp_columns stuInfo /*返回某个表列的信息*/

EXEC sp_help stuInfo /*查看表stuInfo的信息*/

EXEC sp_helpconstraint stuInfo /*查看表stuInfo的约束*/

EXEC sp_helpindex stuMarks /*查看表stuMarks的索引*/

EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看视图的语句文本*/

EXEC sp_stored_procedures  /*查看当前数据库中的存储过程*/

常用的扩展存储过程

q常用的扩展存储过程:xp_cmdshell

q可以执行DOS命令下的一些的操作

q以文本行方式返回任何输出

q调用语法:

q  EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

USE master

GO

EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT /*创建文件夹D:/bank*/

IF EXISTS(SELECT * FROM sysdatabases

                           WHERE name='bankDB')

  DROP DATABASE bankDB

GO

CREATE DATABASE bankDB

(

 …

)

GO

EXEC xp_cmdshell 'dir D:/bank/' --查看文件 /*查看文件夹D:/bank*/

如何创建存储过程?

q定义存储过程的语法

CREATE  PROC[EDURE]  存储过程名

             @参数1  数据类型 = 默认值,

              …… ,

             @参数n  数据类型OUTPUT

           AS

           SQL语句

   GO

qC语言的函数一样,参数可选

q参数分为输入参数、输出参数

q输入参数允许有默认值

创建不带参数的存储过程

CREATE PROCEDURE proc_stu /*proc_stu为存储过程的名称*/

 AS

   DECLARE @writtenAvg float,@labAvg float /*笔试平均分和机试平均分变量*/

   SELECT @writtenAvg=AVG(writtenExam),

          @labAvg=AVG(labExam)  FROM stuMarks

print '笔试平均分'+convert(varchar(5),@writtenAvg)

   print '机试平均分'+convert(varchar(5),@labAvg)

   IF (@writtenAvg>70 AND @labAvg>70)

      print '本班考试成绩优秀' /*显示考试成绩的等级*/

   ELSE

      print '本班考试成绩较差'

   print '--------------------------------------------------'

   print '           参加本次考试没有通过的学员'

SELECT stuName,stuInfo.stuNo,writtenExam,labExam /*显示未通过的学员*/

     FROM  stuInfo  INNER JOIN stuMarks ON

         stuInfo.stuNo=stuMarks.stuNo

                WHERE writtenExam<60 OR labExam<60

GO

调用存储过程

qEXECUTE(执行)语句用来调用存储过程

q调用的语法

EXEC  过程名  [参数]

创建带参数的存储过程

q存储过程的参数分两种:

q输入参数

q输出参数

q输入参数:

用于向存储过程传入值,类似C语言的按值传递;

q输出参数:

用于在调用存储过程后,

返回结果,类似C语言的

按引用传递;    

带输入参数的存储过程

问题:

修改上例:由于每次考试的难易程度不一样,每次笔试和机试的及格线可能随时变化(不再是60分),这导致考试的评判结果也相应变化。

分析:

在述存储过程添加2个输入参数:

@writtenPass   笔试及格线

@labPass         机试及格线

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

q调用带参数的存储过程

假定本次考试机试偏难,机试的及格线定为55分,笔试及格线定为60

EXEC proc_stu 60,55

--或这样调用:

EXEC proc_stu @labPass=55,@writtenPass=60

带输出参数的存储过程

q如果希望调用存储过程后,返回一个或多个值,这时就需要使用输出(OUTPUT)参数了

问题:

修改上例,返回未通过考试的学员人数。

CREATE PROCEDURE proc_stu

 @notpassSum int OUTPUT, /*输出(返回)参数:表示没有通过的人数*/

 @writtenPass int=60,   /*推荐将默认参数放在最后*/

 @labPass int=60

 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

q调用带输出参数的存储过程

/*---调用存储过程----*/

DECLARE @sum int /*调用时必须带OUTPUT关键字,返回结果将存放在变量@sum*/

EXEC proc_stu @sum OUTPUT ,64

print '--------------------------------------------------'

IF @sum>=3 /*后续语句引用返回结果*/

 print '未通过人数:'+convert(varchar(5),@sum)+ ',

超过60%,及格分数线还应下调'

ELSE

 print '未通过人数:'+convert(varchar(5),@sum)+ ',

已控制在60%以下,及格分数线适中'

GO

注意:调用时也必须跟随关键字OUTPUT,否则SQL Server将视为输入参数。

处理存储过程中的错误

q可以使用PRINT语句显示错误信息,但这些信息是临时的,只能显示给用户

qRAISERROR 显示用户定义的错误信息时

q可指定严重级别,

q设置系统变量@@ERROR

q记录所发生的错误等

qRAISERROR语句的用法如下:

RAISERROR (msg_id | msg_str,severity,

  state WITH option[,...n]])

msg_id:在sysmessages系统表中指定用户定义错误信息

msg_str:用户定义的特定信息,最长255个字符

severity:定义严重性级别。用户可使用的级别为0–18

state:表示错误的状态,1127之间的值

option:指示是否将错误记录到服务器错误日志中

RAISERROR 语句每个参数的详细讲解,可以查阅SQL帮助!

问题:

完善上例,当用户调用存储过程时,传入的及格线参数不

0100之间时,将弹出错误警告,终止存储过程的执行。

CREATE PROCEDURE proc_stu

 @notpassSum int OUTPUT, --输出参数

 @writtenPass int=60,  --默认参数放后

 @labPass int=60       --默认参数放后

 AS

   IF (NOT @writtenPass BETWEEN 0 AND 100)

            OR (NOT @labPass BETWEEN 0 AND 100)

/*引发系统错误,指定错误的严重级别16,调用状态为1(默认),并影响@@ERROR系统变量的值 */

      BEGIN

        RAISERROR (‘及格线错误,请指定0100之间的分

数,统计中断退出',16,1)

        RETURN  ---立即返回,退出存储过程

      END

   …..其他语句同上例,略

GO

/*---调用存储过程,测试RAISERROR语句----*/

DECLARE @sum int,  @t int

EXEC proc_stu @sum OUTPUT ,604   /*笔试及格线误输入604*/

SET @t=@@ERROR

print  '错误号'+convert(varchar(5),@t )

IF @t<>0  /*如果执行了RAISERROR,系统全局@@ERROR将不等于0,表示出现错误*/

  RETURN  --退出批处理后续语句不再执行

print '--------------------------------------------------'

IF @sum>=3

 print '未通过人数'+convert(varchar(5),@sum)+ ',超过60%,及格分数线还应下调'

ELSE

 print '未通过人数'+convert(varchar(5),@sum)+ ',已控制在60%以下及格分数线适中'

GO

好,我们来做个总结:

存储过程是一组预编译的SQL语句,它可以包含数据操纵语句、变量、逻辑控制语句等

存储过程允许带参数,参数分为:

输入参数

输出参数

其中,输入参数可以有默认值。

输入参数:可以在调用时向存储过程传递参数,此类参数可用来向存储过程中传入值

输出参数从存储过程中返回(输出)值,后面跟随OUTPUT关键字

RAISERROR语句用来向用户报告错误