Sql Server 存储过程

摘自收集引言：

 

前面我们曾提到过，T-SQL是一门结构化查询语言。我们曾学习过C#语言的函数。使用函数时，需要两步。第一步：定义函数，它允许包含参数和返回值。第二步：调用函数，如果有参数还需传递参数，这样就执行了函数中的代码。函数可以反复调用，它方便了程序的模块化设计，大大提高了执行效率。

本章将讲解什么是存储过程，介绍常用的系统存储过程最后讲解如何创建并调用自定义存储过程，包括存储过程的输入参数和输出参数。

 

课前预习：

 

1． 创建不带参数的存储过程。

2． 创建带输入参数的存储过程。

3． 创建带输出参数的存储过程。

4． 处理错误信息。

 

本章单词：

procedure、sum、output、filename、size、EXEC

 

6．1  什么是存储过程

 

存储过程（Procedure）类似于C#语言中的方法，它是SQL语句和控制流语句的预编译集合。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、逻辑控制语句以及其他强大的编程功能。

存储过程可包含逻辑控制语句和数据操作语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。存储过程在船舰时即在服务器上进行编译，所以执行起来比单个SQL语句快。

类似于C#语言中的类库，SQL Server提供了一些预编译的存储过程，用以管理SQL Server和显示有关数据库和用户的信息。这些存储过程称为“系统存储过程”。

SQL Server中的存储过程的特征如下。

(1) 接受输入参数，并向调用过程或语句返回值。

(2) 包含在数据库中执行操作或调用其他的存储过程的编程语句。

(3) 向调用过程返回状态值，指示执行过程是否成功（如果失败，还返回失败原因）。

 

存储过程可以值包含一条SELECT语句，也可以包含一系列使用控制流的SQL语句，如图6.1所示。存储过程可以包含个别或全部的控制流结构。

 

图 6.1 存储过程中的语句

 

使用存储过程有下列优点。

(1) 允许模块化程序设计。

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

(2) 允许更快地执行。

如果某操作需要大量的T-SQL 代码或需要重复执行，存储过程将比T-SQL批处理代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中的版本。但如果使用T-SQL批处理代码，每次运行T-SQL语句时，都要从客户端重复发送，并且在SQL Server每次执行这些语句时，都要对其进行编译和优化。

(3) 减少网络流量。

创建使用存储过程后，一个需要数百行T-SQL代码的操作，由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

(4) 可作为安全机制使用。

即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。

 

存储过程分为以下两类。

l 系统存储过程。

l 用户自定义存储过程。

 

6．2 常用的系统存储过程

 

SQL Server提供系统存储过程，它们是自已预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式。

通过配置SQL Server，可以生成对象、用户、群贤的信息和定义，这些信息和定义存储在系统表中。每个数据库都分别有一个包含配置信息的系统表集，用户数据库的系统表是在创建数据库时自动创建的。用户可以通过系统存储过程访问和更新系统表。

所有的系统存储过程的名称都以“sp_”开头，并存放在master数据库中。系统管理员拥有这些存储过程永恒的使用权限。可以在任何数据库中运行系统存储过程，但执行的结果会反映在当前的数据库中。

表6-1列出了这些常用的系统存储过程。

 

表6-1 常用系统存储过程

 

示例1

EXEC sp_databases

EXEC  sp_renamedb 'Northwind','Northwind1'

USE stuDB

GO

EXEC sp_tables

EXEC sp_columns stuInfo  

EXEC sp_help stuInfo

EXEC sp_helpconstraint stuInfo

EXEC sp_helpindex stuMarks

EXEC sp_helptext 'view_stuInfo_stuMarks' 

EXEC sp_stored_procedures  

 

示例1的输出结果集较多，再次不一一列举，用户可以逐句运行，查看相应的输出结果。

 

另外，还有一个常用的扩展存储过程：xp_cmdshell，它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件列表等。例如，希望创建的数据库保存在D:\project目录。如果当前没有此目录，使用CREATE DATABASE 语句创建时会报错，如何解决呢？我们就可以使用扩展存储过程来创建文件夹。

其语法如下：

EXEC xp_cmdshell DOS命令[NO_OUTPUT]

 

其中EXEC 表示调用存储过程，NO_OUTPUT 为可选参数，设置指定DOS 命令后是否输出返回信息，具体使用如示例2所示。

 

示例2

USE master

GO

EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT

IF EXISTS(SELECT * FROM sysdatabases

                            WHERE name='bankDB')

   DROP DATABASE bankDB

GO

CREATE DATABASE bankDB

 (

  …

)

GO

EXEC xp_cmdshell 'dir D:\bank\' --查看文件

 

示例2的输出结果如图6.2 所示。

 

 

图6-2 扩展存储过程 xp_cmdshell 的用法

 

 

 

6．3 用户定义的存储过程

 

除了使用系统存储过程，用户还可以创建自己的存储过程。要在SQL Server中创建存储过程，可使用Microsoft SQL Server Management Studio 或使用T-SQL 语句，使用Microsoft SQL Server Management Studio船舰存储过程步骤类似于视图，再次不在重复介绍。

用于创建存储过程的T-SQL 语句为CREATE  PROCEDURE . 所有的存储过程都创建在当前数据库中。以下章节将详细地介绍如何使用T-SQL 语句来创建存储过程。

 

6．3．1 创建不带参数的存储过程

 

使用T- SQL 语句创建存储过程的语法如下。

 

CREATE  PROC[EDURE]  存储过程名

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

               ……,

              @参数n  数据类型= 默认值OUTPUT

            AS

            SQL语句

    GO

其中，参数部分可选，我们先讨论不带参数的存储过程的用法。

例如。希望查看本次考试平均分以及未通过考试的学员名单。

我们需要先创建存储过程，然后在调用存储过程执行，该例的具体实现如示例3所示。

 

示例3

CREATE PROCEDURE 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

 

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

EXEC proc_stu

 

示例3 的输出结果如图6.3 所示。

 

图 6-3 不带参数的存储过程

 

6．3．2 创建带输入参数的存储过程

 

在C#中，调用带参数的方法是，我们需要传递实际参数值给形式参数（形参）。例如。调用求两个数只和的方法  int sum (int a ,int b ),求 5 和8 只和，则调用形式为：int c = sum(5,8),返回值将赋给变量c。

存储过程中的参数与此非常类似，存储过程中的参数分为以下两种。

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

输出参数：如果希望返回值，则可以使用输出参数，输出参数后有“OUTPUT”标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问。

 

带输入参数的存储过程T-SQL 语法如下

 

CREATE PROC[edure] 存储过程名称

@参数数据类型[= 默认值][OUTPUT]

.......

AS

SQL语句

 

其中如果参数后面有“OUTPUT”关键字，表示此参数为输出参数，否则视为普通的输入参数，输入参数还可以设置默认值。

修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致未通过学员的评判标准也相应地发生变化。我们不妨给示例3中的存储过程添加两个输入参数，分别表示比十几个分数和机试及格分数，具体T-SQL语句如示例4所示。

 

示例4

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 50 

--或者这样调用：EXEC proc_stu @labPass = 55 , @writtenPass = 60

 

示例4的输出结果如图6.4 所示。和6.3 的结果比较以下，会发现由于机试及格线调整为55分后，59分的就成了“漏网之鱼”了。

调用带参数的存储过程时，参数60 和50 将分别传递给输入参数@writtenPass 和@labPass。输入参数用于将实际参数值传入到存储过程中。

 

 

图 6.4 带参数的存储过程

 

上述带参数的存储过程确实比较方便，调用者可以随时更改每次考试的及格线。但如果考试的难易程度何时，则调用这还是必须如此调用。

 

EXEC proc_stu 60,60

 

这就显得比较麻烦，能不能在调用时，存储过程的参数变为可选呢？例如，如果调用形式如下。

EXEC proc_stu 55 --表示笔试及格线分，机试及格线默认分。

EXEC proc_stu --表示笔试和机试及格线都默认为标准的分。

 

答案是肯定的。存储过程的输入参数允许采用默认值，修改上例，如示例5所示。

 

示例5

USE stuDB

GO

/*--检测是否存在：存储过程存放在系统表sysobjects中--*/

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_sty')

DROP PROCEDURE proc_stu

GO

/*--创建存储过程--*/

CREATE PROCEDURE proc_stu

@writtenpass int = 60,

@labPass int = 60

AS

print '笔试及格线' + convert(varchar(5),@writtenPass

+ '机试及格线' + convert(varchar5),@labPass)

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

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

SELECT 视图NamestuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo

= stuMarks.stuNo WHERE writtenExam <@writtenPass OR labExam <@labPass

GO

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

EXEC proc_stu  --都采用默认值：笔试和机试及格线都为分

EXEC proc_stu 64 --机试采用默认值：笔试及格线分，机试及格线分

EXEC proc_stu 60,55  --都不采用默认值：笔试及格线分，机试及格线分

--错误的调用方式：EXEC proc_stu ,55

--正确的调用方式：EXEC proc_stu @labPass = 55

 

很显明，调整及格线后，没有通过考试的学员名单也不一样。

 

6．3．3 创建带输出参数的存储过程

 

如果希望调用存储过程后，返回一个或多个值，这是需要使用输出（OUTPUT）参数。例如：调用上述存储过程。我们还希望返回未通过考试的学员人数，以供其他T-SQL语句访问，具体的T-SQL语句如示例6所示。

 

示例6

CREATE PROCEDURE proc_stu 

  @notpassSum int OUTPUT,  --OUTPUT 关键字，否则视为输入参数

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

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

  AS

    print '笔试及格线' + convert(varchar(5),@writtenPass

+ '机试及格线' + convert(varchar5),@labPass)

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

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

     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  

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

IF @sum>=3

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

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

ELSE

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

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

GO

 

示例6输出的结果如图6.6 所示。

 

 

图 6.6 带输出参数的存储过程

 

反复强调的是，使用输出参数创建存储过程时，在参数后面需要跟随“OUTPUT”关键字，调用时也需要在变量后跟随“OUTPUT”关键字。

 

6．3．4 处理错误信息

 

如果存储过程变得越来越复杂，则需要在存储过程中加入错误检查语句。在存储过程中，可以使用PRINT 语句显示用户定义的错误信息。但是，这些信息是临时的，且只能显示给用户。RAISERROR返回用户定义的错误信息时，可指定严重级别，设置系统变量记录所发生的错误。

RAISERROR 语句的语法如下。

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

其中，

l Msg_id：在sysmessages系统表中指定的用户定义错误信息。

l Msg_str：用户定义的特定信息，最长255个字符。

l Severity：于特定信息相关联，表示用户定义的严重性级别。用户可使用的级别为0~18级。19~25级是为sysadmin固定角色的成员预留的，并且需要指定WITH LOG选项。20~25级错误被认为是知名错误。

l State：表示错误的状态，是1~127 的值。

l Option：只是是否将错误记录到服务器错误日志中。

 

完善示例6，当用户调用存储过程时，传入的及格线不在0~100之间时，将弹出错误警告，终止存储过程的执行，具体的T-SQL实现如示例7所示。

 

示例7

USE stuDB

GO

/*--检测是否存在：存储过程放在系统表sysobjects中--*/

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu')

DROP PROCEDURE proc_stu

GO

/*--创建存储过程--*/

CREATE PROCEDURE proc_stu

@notpassSum int OUTPUT ,--OUTPUT 关键字，否则视为输入参数

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

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

AS

/*---------------------------错误处理---------------------------*/

IF (NOT @writtenPass BETWEEN 0 AND 100)OR (NOT @labPass BETWEEN O AND 100)

BEGIN 

RAISERROR('及格线错误，请指定——之间的分数，统计中断推出',16,1)

RETURN --立即返回，推出存储过程

END

print '笔试及格线' + convert(varchar(5),@writtenPass

+ '机试及格线' + convert(varchar5),@labPass)

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

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

     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 ,@t int 

EXEC proc_stu @sum OUTPUT ,604  --笔试及格线错误入分

SET @T = @@ERROR --如果出现了错误，执行了RAISERROR 语句，系统全局@@ERROR将不等于，表示有错

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

IF @t <> 0 

RETURN -- 推出批处理，后续语句不在执行

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

IF @sum >= 3

print '未通过人数：'+convert(varchar(5),@sum)+'人，超过%，几个分数线还应下调'

ELSE

print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在%以下，几个分数线始终'

GO

 

示例7的输出结果如图6.7 所示。当调用者将笔试及格线错误入为604分，将执行RAISERROR(‘及格线错误，请指定0—100之间的分数，统计中断推出’,16,1)。

引发系统错误，指定错误的严重级别为16 ，调用状态为1（默认）。错误的严重界别大于10，将自动设置系统全局变量@@ERROR为非零值，表示语句执行出错。所以，我们可以在调用存储过程后，判断全局变量@ERROR 是否为0，决定是否还继续执行后续语句。

 

 

图 6.7 使用RAISERROR语句

 

 

 

本章总结：

 

u 存储过程是一组预编译的SQL语句，存储过程可以包含数据操作语句、逻辑控制语句和调用函数等。

u 存储过程可加快查询的执行速度，提高访问数据的速度，帮助实现模块化编程，保持一致性和提高安全性。

u 存储过程可分为以下两种。

A. 系统存储过程。

B. 用户自定义的存储过程。

u CREATE PROCEDURE 语句用于创建用户定义的存储过程。

u XECUTE语句用于调用执行存储过程。

u 存储过程的参数分为输入参数和输出参数，输入参数用来向存储过程中传入值，输出参数用于从存储过程中返回（输出）值，后面跟随“OUTPUT”关键字。

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

 

 

本章作业：

 

一、选择题

1. 有关存储过程的参数默认值，下面说法正确的是（    ）。

A、输入参数必须有默认值

B、带默认值的输入参数，方便用户调用

C、带默认值的输入参数，用户不能再传入参数，只能采用默认值

D、输出参数也可以带默认值

2. 下面有关存储过程的说法（    ）是错误的。

A、它可以作为一个独立的数据库对性并作为一个单元用户在应用程序中调用

B、存储过程可以传入和返回（输出）参数值

C、存储过程必须带参数、要么是输入参数，要么是输出参数

D、存储过程提高了执行效率

3. 查阅SQL Server帮助，EXEC sp_pkeys buyers的功能为（    ）。

A、查看表buyers的约束的信息

B、查看表buyers的列信息

C、查看表buyers的主键信息

D、查看表buyers的存放位置信息

4. 查阅SQL Server帮助，EXEC xp_logininfo的功能为（    ）。

A、查看表logininfo的约束信息

B、查看账户信息

C、查看当前登陆信息

D、查看当前权限

5. 调用示例6的存储过程，错误的是（    ）。

A、EXEC proc_stu  @sum OUTPUT ， 604

B、EXEC proc_stu  @sum OUTPUT ， 64，78

C、EXEC proc_stu  604 ，@sum OUTPUT

D、EXEC proc_stu  @sum OUTPUT

6. 运行以下语句，输出结果是（    ）。

CREATE PROC proc_lookup

@mystuNo varchar（8）= NULL

AS 

IF @mystuNo IS NULL

BEGIN

Print ‘您忘记了传递学号参数’

RETURN

END

SELECT * FROM stuInfo WHERE stuNo @mystuNo

GO

EXEC  proc_lookiup

A、编译错误

B、调用存储proc_lookup过程出错

C、显示“您忘记了传递学号参数”

D、显示空的学员信息记录集

 

 

二、简答题

完善本章示例，创建存储过程proc_stu，完成如下功能。

1） 查询本次考试未通过的学员，要求显示姓名、学号、笔试成绩、机试成绩、是否通过，没参加考试的学员成绩显示为“缺考”（使用做连接LEFT  JOIN，可参考第4章节示例10）。

2） 存储过程带3个参数，分别表示未通过的学员人数、笔试及格显赫机试及格线，统计未通过的学员名单，并返回未通过的人数。

3） 统计未通过学员的人数和名单时，缺考的学员也计算在内。

转载于:https://www.cnblogs.com/tutuyforever/p/3335869.html