第6章 存 储 过 程6.1 存储过程简介6.2 使用存储过程6.3 可编程化存储过程6.4 上机实验习题 存储过程是一个T-
SQL语句的预编译集合，它创建于数据库服务器并且以一个名称存储为一个单元，可以被应用程序调用，也可以被另一个存储过程或触发器调用。
本章学习目标： (1) 掌握创建、修改和删除存储过程的方法。 (2) 理解存储过程的输入参数、输出参数、
默认参数、返回值等重要概念，并掌握它们的运用技巧。 (3) 能够灵活运用存储过程来解决实际问题。 存储过程具有以下
优点： (1) 存储过程允许模块化编程。存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。
(2) 存储过程能够实现较快的执行速度。如果某一操作包含大量的T-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快
很多。6.1 存储过程简介 (3) 存储过程能够减少网络流量。对于同一个针对数据库对象的操作(如查询、修改)，如果这一操作所涉
及到的T-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句
，从而大大增加了网络流量，降低网络负载。 (4) 存储过程可被作为一种安全机制来充分利用。系统管理员通过对执行某一存储过程的权
限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。 6.2.1 创建存储过
程 创建存储过程的语法如下： CREATE PROC [ EDURE ] procedure_name [
{ @parameter data_type } [ OUTPUT ] ] [，...n ]
AS sql_statement [ ...n ]6.2 使用存储过程 参数的含义如下： procedure_n
ame：新存储过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。 @ parameter：过程中的参数。在C
REATE PROCEDURE语句中可以声明一个或多个参数。 通过使用at符号(@)作为第一个字符来指定参数名称。参数名称必须
符合有关标识符的规则。 data_type：参数以及所属架构的数据类型。 OUTPUT：指示参数是输出参数。此选项的值可以返
回给调用EXECUTE的语句。使用 OUTPUT 参数将值返回给过程的调用方。 ：要包含在过程中
的一个或多个T-SQL语句。 下面将演示怎样创建一个存储过程，假设想要列出2002级8班(也就是学号以“200208”开头)学
生的学号、姓名和性别，只需要一条SELECT语句就可以完成，输入代码清单6-1所示的代码并执行。 当上面的代码被执行完
毕后，可以看到已经存在名称为“spStudents028”的存储过程，如图6-1所示。 图6-1 被创建的spStudents
028存储过程6.2.2 运行存储过程 执行存储过程的语法也很简单，只需要在EXECUTE语句(或简写为EXEC)后面加上存
储过程名称就可以了，比如要执行上面刚创建的存储过程，输入代码清单6-2所示的代码并执行，执行结果如图6-2所示。 图6-2
存储过程执行结果6.2.3 修改存储过程 存储过程被创建之后，有时候由于种种原因还要对它进行修改，修改存储过程可以使用A
LTER PROCEDURE语句，语法结构如下： ALTER PROC [ EDURE ] procedure_name
[ { @parameter data_type } [ OUTPUT ] ] AS s
ql_statement [ ...n ] 其实这个语法与创建存储过程的语法很类似，只是把CREATE改成了ALTER，其余部分
的含义在上一节中已经介绍，这里不再重复。现在我们把上一个例子作一点改动，即列出2002级8班(也就是学号以“200208”开头)女
同学的学号、姓名和性别，输入代码清单6-3所示的代码并执行，执行结果如图6-3所示，从结果图可以看出，对存储过程的修改已经起作用了
。 图6-3 存储过程执行结果6.2.4 删除存储过程 当不需要某个存储过程时，就可以将它从数据库中删除，删除存储过程的
语句为DROP PROCEDURE，具体语法结构如下： DROP PROCEDURE <存储过程名称> 注意，对存储过程的
删除操作是永久且无法恢复的，所以在删除之前一定要谨慎。比如要把前面例子所创建的存储过程spStudents028删除，然后试图再次
运行它，但是系统给出了找不到存储过程的信息，输入代码清单6-4所示的代码并执行，结果如图6-4所示。 图6-4 找不到存储过程
spStudents028 在存储过程中编写的代码可以是很复杂的，功能也可以是很强大的。6.3 可编程化存储过程6
.3.1 设置参数 在声明参数时需要注意，必须使用@符号作为第一个字符来指定参数名称，参数名称必须符合标识符的规则，在一个存
储过程中可以定义一个或多个参数，每个参数仅用于该存储过程的局部变量。 下面举例说明带参数存储过程的创建，即创建一个能根据所指定的
科目名称来查询考生信息的存储过程，输入代码清单6-5所示的代码并执行，即可创建并运行该存储过程。 在代码中，我们指定了一个
名为@subjectName的参数，它的参数类型为char(50)(必须注意数据类型应该和表中相应的字段的数据类型一致)，这个参数
被用在WHERE从句中，用来筛选出相应的考试科目的记录。而在执行语句中，只需在存储过程名称后面直接输入参数值“全国计算机等级考试”
就可以把它传送给参数了，执行结果如图6-5所示。 图6-5 执行带单个参数的存储过程spGetInfoBySubjectNam
e 接下来查询另一科目“局域网管理员考试”的信息，输入代码清单6-6所示的代码并执行，结果如图6-6所示。 图6-6
执行带单个参数的存储过程spGetInfoBySubjectName 存储过程是可以指定多个参数的，比如想查询指定的科目名称和指
定级别的考试信息，这里就要用到两个参数：一个是@subjectName，用来存放科目信息；另一个是@degree，用来存放级别信息
。输入代码清单6-7所示的代码并执行，在代码中先创建存储过程，然后执行两次，但每次执行所输入的参数值是不同的，所返回的两个结果集如
图6-7所示。 图6-7 执行带两个参数的存储过程spGetInfoBySubjectNameAndDegree 这里需要强
调的是两个参数值的参数问题，在调用存储过程的语句中指定参数的时候，可以只指定参数值而不用指出参数名，但这时各参数值的顺序必须和存储
过程定义中各相应参数的顺序保持一致，否则会导致逻辑上的不正确。但也可以指出参数名与值(即显式写出“@参数名称=参数值”的格式)，这
时候参数的顺序可以是任意的，例如下面的例子，对同一个存储过程分别以不同方式调用了两次，两次调用中各参数值的顺序和存储过程定义中各相
应参数的顺序都不一致，但第一次没有指出参数名，所以没有检索到数据，而第二次明确指出参数名，所以得到预期的结果，输入代码清单6-8所
示的代码并执行，所返回的两个结果集如图6-8所示。 图6-8 两次执行存储过程的比较6.3.2 设置默认参数 如果在调用
一个带有参数的存储过程时没有指定参数值，通常存储过程是不能执行的，比如调用上面创建的存储过程spGetInfoBySubjectN
ameAndDegree却没有指定参数，就会返回错误信息。输入代码清单6-9所示的代码并执行，返回的错误信息如图6-9所示。
图6-9 没有指定参数值出错 还有另外一种情况也会出错，就是所指定的参数个数与存储过程所声明的参数个数不同，例如存储过程s
pGetInfoBy SubjectNameAndDegree有两个参数，下面的例子在调用它时却只输入了一个参数，结果是系统也返回
错误信息，但这次的错误信息指出的是缺少第二个参数@degree。输入代码清单6-10所示的代码并执行，返回的错误信息如图6-10所
示。 图6-10 没有指定足够个数的参数值时出错 其实，在声明存储过程的参数时是可以为其指定默认值的，如果在调用存储过
程时没有指定参数值，那么认为参数的值是默认值，否则就是所指定的值。对于有多个参数的情况，也可以指定部分参数值，其余的参数则采用默认
值。 为参数指定默认值的语法是在所声明的参数名称后面加上“=<参数值>”。例如，对存储过程spGetInfoBySubject
Name AndDegree进行少许修改，为两个参数@subjectName和@degree分别指定默认值为“全国英语等级考试”和
“一级”，输入代码清单6-11所示的代码并执行。 接下来进行三种方式的调用：第一种方式是两个参数都不指定参数值(即全部采用默认值
)；第二种方式是@subjectName采用默认值，而指定@degree的值；第三种方式是两个参数都采用指定的值，具体代码如代码清
单6-12所示，结果如图6-11所示。 图6-11 几种调用方式的比较6.3.3 设置输出参数 在前面的两小节中介绍
了存储过程的参数，但都是输入参数，如果有必要，也可以声明输出参数，声明输出参数的语法为在参数后面加一个OUTPUT关键字。例如继续
对上面所创建的存储过程spGetInfoBySubjectNameAndDegree进一步进行修改，添加一个返回参加指定科目和级别
的所有考生的平均成绩的输出参数@averageScore，输入代码清单6-13所示的代码并执行。 注意，在上面的代码中“@ave
rageScore = AVG(Score)”是指把函数AVG(Score)的结果赋给变量@averageScore，这时候SEL
ECT所返回的就只是一个单个的数值。 接下来将调用修改过后的存储过程，首先声明一个数据类型为FLOAT的临时变量@AvgScr
e，用来存放输出参数的值，在调用时，对于@AvgScre参数也必须在后面加上OUTPUT关键字，我们将使用不同的参数调用两次，以便
对结果进行比较，每次调用后都用SELECT语句来显示输出结果，具体代码如代码清单6-14所示，输出参数的结果如图6-12所示。
图6-12 显示输出参数的结果6.3.4 存储过程的返回值 实际上存储过程是可以有返回值的，尽管在前面的例子中创建存储过程
时没有显式的返回值语句，而在执行存储过程的时候也没有刻意去获取它的返回值，在这种情况下，如果存储过程能成功执行，则默认自动返回一个
整数0，通过检测这个返回值来确定存储过程的执行状态。若有必要，也可以指定返回值，以反映标识值或影响的行数等信息。 若要获取存储
过程的返回值，可以使用以下语法： EXEC <变量>=<存储过程名> 下面进行一个获取存储过程返回值的测试，以上一小节所创
建的存储过程为例，具体代码如代码清单6-15所示，执行结果如图6-13所示。 图6-13 显示存储过程的返回值 上面的代码与
之前调用存储过程的方式有少许不同，就是专门声明了一个变量@ReturnValue用来存放返回值，由图可知，存储过程返回了整数值0。
如本小节开始所述，是可以自定义存储过程的返回值的，只需要在存储过程中使用RETURN 语句，具体语法为 RETURN [
<返回的整数值>] 这里要注意，返回值必须是整数值，当执行到RETURN 语句时，存储过程就会无条件退出，而不管后面是否还有语
句。 这里创建一个按照学号来查找考生的存储过程，如果找不到，则返回-200，表示没有匹配的记录；如果找到，则显示相应的记录，这
时系统就会默认返回0，具体代码如代码清单6-16所示。 上述代码中，EXISTS子句的作用是用来检测是否存在相应的记录，但这里在
它的前面还加了一个NOT，意思就变为如果下面的语句： SELECT FROM TestInformation WHERE
StudentID =@studentID 没有匹配的记录，就执行RETURN -200，然后退出存储过程，否则就显示结果集
。 现在对这个存储过程进行调用测试，具体代码如代码清单6-17所示，结果如图6-14所示。 图6-14 显示存储过程的
返回值 在代码中，先声明一个变量用来存放返回值，然后进行了两次调用，第一次为查找学号为“20020504”的考生，但是没有找到匹
配的记录，所以返回值为-200(见图6-14上面方框部分)；而第二次为查找学号为“20020745”的考生，找到了两条记录，所以先
显示出结果集，然后显示出返回值为0(见图6-14下面方框部分)。 在本章的实验中，利用第3章实验中所创建的两张表Tea
chers2008和Course2008，由于它们的数据在第3章实验中已经被改变，因此在本章实验前需要重新创建它们。重新创建这两张
表的代码参见第3章的代码清单3-52，可按以下步骤运行这段代码：6.4 上 机 实 验 进入“Microsoft SQL Se
rver Management Studio”界面，选择“文件”|“打开”|“文件…”，在弹出的“打开文件”对话框中定位到随本书配
套资源中的代码文件“3-52.sql”，然后单击“连接”，就会在所打开的代码窗口中显示出代码清单3-52的代码，单击工具栏中的
，这样Teachers2008和Course2008就会被重新创建并恢复最原始的数据了。 1．实验一：创建和使用简单存储过程
1) 实验要求 (1) 熟练运用CREATE PROCEDURE创建存储过程。 (2) 对创建、运行、修改和删除存储过
程有初步的认识。 2) 实验目的 掌握存储过程的基本操作。 3) 实验步骤 进入“Microsoft SQL Se
rver Management Studio”界面，在对象资源管理器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“
WxdStudent”，再单击工具栏按钮“ ”，在所打开的查询窗口中完成以下任务： (1) 创建一个不带任何参数的存储过程
，名称为ShowAllTeachers，可以输出所有教师的全部数据。 在代码窗口中输入代码清单6-18所示的代码并运行，消息窗
口显示出“命令已成功完成”，则说明存储过程已经被成功创建了。如果展开“WxdStudent”|“可编程性”|“存储过程”节点，可以
看到存储过程ShowAllTeachers，如图6-15所示。 图6-15 存储过程ShowAllTeachers (2)
运行存储过程ShowAllTeachers，观察结果。 在代码窗口中输入代码清单6-19所示的代码并运行，运行结果如图6-16
所示。 图6-16 运行结果 (3) 修改存储过程ShowAllTeachers，改为只显示姓名(Name)和职称(A
cademicTitle)两列数据，然后运行修改过后的存储过程。 在代码窗口中输入代码清单6-20所示的代码并运行，运行结果如
图6-17所示。 图6-17 运行结果 (4) 删除存储过程ShowAllTeachers，然后尝试运行存储过程，观察
错误信息。 在代码窗口中输入代码清单6-21所示的代码并运行，弹出图6-18所示的错误消息，说明存储过程ShowAllTeac
hers已经从数据库中删除了。 图6-18 错误消息提示 2．实验二：创建和使用带参数的存储过程 1) 实验要求
(1) 熟悉创建带参数的存储过程的语法。 (2) 理解默认参数的功能。 2) 实验目的 掌握带参数的存储过程的运用
。 3) 实验步骤 进入“Microsoft SQL Server Management Studio”界面，在对象资源管理
器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“WxdStudent”，再单击工具栏按钮“ ”，在所打开的查
询窗口中完成以下任务： (1) 创建一个带1个参数的存储过程FindTeacherBySex，在参数中指定性别(1表示男，0表
示女)，存储过程可以列出相应的老师，并且运行存储过程进行测试。 在代码窗口中输入代码清单6-22所示的代码并运行，运行结果如图
6-19所示。 图6-19 运行结果 (2) 创建一个带一个参数的存储过程FindCourseByLesson Pla
ce，在参数中指定上课地点，存储过程可以列出相应的课程。若不指定参数，则默认为列出在“课室”上课的课程，并且运行存储过程进行测试。
在代码窗口中输入代码清单6-23所示的代码并运行，运行结果如图6-20所示。 图6-20 运行结果 (3) 创建一个
带两个参数的存储过程ModifyCourseByTeacher，在参数中指定课程名称和教师姓名，存储过程可以将相应课程的授课教师更
改为参数中指定姓名的教师，并且运行存储过程进行测试。 在代码窗口中输入代码清单6-24所示的代码并运行，运行结果如图6-21所
示。 图6-21 运行结果 一、判断题(正确的，在题后括号内画“√”；错误的，在题后括号内画“×”) 1．
如果要执行大量的T-SQL 代码，最好使用存储过程，而不要将代码存放在客户端。 ( ) 2．若某个存
储过程定义有输入参数，则调用它时一定要指明参数的值，否则会出错。 ( ) 习 题 3．若某个存储
过程定义有几个参数，则在调用时如果显式指明参数的名称和参数值，则参数的排列顺序可以和参数在存储过程内的声明顺序不一致。 ( ) 4．若没有在存储过程内使用RETURN语句，则存储过程就没有返回值。 ( ) 5．若执行存储过程失败，则返回0。 ( ) 二、填空题 1．创建存储过程的语句为____________，修改存储过程的语句为_______________，删除存储过程的语句为__________________。 2．在创建或修改存储过程的时候可以声明参数，参数分为_________和_________两种类型。 3．若在执行存储过程的同时要获取返回值，则调用的语法为____________________。 三、简答题 1．为什么不把T-SQL程序存放在客户端，而是尽量使用存放在服务器的存储过程？ 2．执行存储过程时，若省略EXECUTE关键字，会有什么结果? 3．简述存储过程的输入参数和输出参数的作用，以及默认参数的机制。 4．简述如何设置存储过程的返回值。 |
|
