《SQL Server 2005基础教程及上机指导》第6章 存储过程

第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创建存储过程

创建存储过程的语法如下：

CREATEPROC[EDURE]procedure_name

[{@parameterdata_type}

[OUTPUT]

][，...n]

ASsql_statement[...n]6.2使用存储过程

参数的含义如下：

procedure_name：新存储过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。

@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。

通过使用at符号(@)作为第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。

data_type：参数以及所属架构的数据类型。

OUTPUT：指示参数是输出参数。此选项的值可以返回给调用EXECUTE的语句。使用OUTPUT参数将值返回给过程的调用方。

<sql_statement>：要包含在过程中的一个或多个T-SQL语句。

下面将演示怎样创建一个存储过程，假设想要列出2002级8班(也就是学号以“200208”开头)学生的学号、姓名和性别，只需要一条SELECT语句就可以完成，输入代码清单6-1所示的代码并执行。

当上面的代码被执行完毕后，可以看到已经存在名称为“spStudents028”的存储过程，如图6-1所示。

图6-1被创建的spStudents028存储过程6.2.2运行存储过程

执行存储过程的语法也很简单，只需要在EXECUTE语句(或简写为EXEC)后面加上存储过程名称就可以了，比如要执行上面刚创建的存储过程，输入代码清单6-2所示的代码并执行，执行结果如图6-2所示。

图6-2存储过程执行结果6.2.3修改存储过程

存储过程被创建之后，有时候由于种种原因还要对它进行修改，修改存储过程可以使用ALTERPROCEDURE语句，语法结构如下：

ALTERPROC[EDURE]procedure_name

[{@parameterdata_type}

[OUTPUT]

]ASsql_statement[...n]

其实这个语法与创建存储过程的语法很类似，只是把CREATE改成了ALTER，其余部分的含义在上一节中已经介绍，这里不再重复。现在我们把上一个例子作一点改动，即列出2002级8班(也就是学号以“200208”开头)女同学的学号、姓名和性别，输入代码清单6-3所示的代码并执行，执行结果如图6-3所示，从结果图可以看出，对存储过程的修改已经起作用了。

图6-3存储过程执行结果6.2.4删除存储过程

当不需要某个存储过程时，就可以将它从数据库中删除，删除存储过程的语句为DROPPROCEDURE，具体语法结构如下：

DROPPROCEDURE<存储过程名称>注意，对存储过程的删除操作是永久且无法恢复的，所以在删除之前一定要谨慎。比如要把前面例子所创建的存储过程spStudents028删除，然后试图再次运行它，但是系统给出了找不到存储过程的信息，输入代码清单6-4所示的代码并执行，结果如图6-4所示。

图6-4找不到存储过程spStudents028

在存储过程中编写的代码可以是很复杂的，功能也可以是很强大的。6.3可编程化存储过程6.3.1设置参数

在声明参数时需要注意，必须使用@符号作为第一个字符来指定参数名称，参数名称必须符合标识符的规则，在一个存储过程中可以定义一个或多个参数，每个参数仅用于该存储过程的局部变量。下面举例说明带参数存储过程的创建，即创建一个能根据所指定的科目名称来查询考生信息的存储过程，输入代码清单6-5所示的代码并执行，即可创建并运行该存储过程。

在代码中，我们指定了一个名为@subjectName的参数，它的参数类型为char(50)(必须注意数据类型应该和表中相应的字段的数据类型一致)，这个参数被用在WHERE从句中，用来筛选出相应的考试科目的记录。而在执行语句中，只需在存储过程名称后面直接输入参数值“全国计算机等级考试”就可以把它传送给参数了，执行结果如图6-5所示。

图6-5执行带单个参数的存储过程spGetInfoBySubjectName

接下来查询另一科目“局域网管理员考试”的信息，输入代码清单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设置默认参数

如果在调用一个带有参数的存储过程时没有指定参数值，通常存储过程是不能执行的，比如调用上面创建的存储过程spGetInfoBySubjectNameAndDegree却没有指定参数，就会返回错误信息。输入代码清单6-9所示的代码并执行，返回的错误信息如图6-9所示。

图6-9没有指定参数值出错

还有另外一种情况也会出错，就是所指定的参数个数与存储过程所声明的参数个数不同，例如存储过程spGetInfoBy

SubjectNameAndDegree有两个参数，下面的例子在调用它时却只输入了一个参数，结果是系统也返回错误信息，但这次的错误信息指出的是缺少第二个参数@degree。输入代码清单6-10所示的代码并执行，返回的错误信息如图6-10所示。

图6-10没有指定足够个数的参数值时出错

其实，在声明存储过程的参数时是可以为其指定默认值的，如果在调用存储过程时没有指定参数值，那么认为参数的值是默认值，否则就是所指定的值。对于有多个参数的情况，也可以指定部分参数值，其余的参数则采用默认值。

为参数指定默认值的语法是在所声明的参数名称后面加上“=<参数值>”。例如，对存储过程spGetInfoBySubjectName

AndDegree进行少许修改，为两个参数@subjectName和@degree分别指定默认值为“全国英语等级考试”和“一级”，输入代码清单6-11所示的代码并执行。

接下来进行三种方式的调用：第一种方式是两个参数都不指定参数值(即全部采用默认值)；第二种方式是@subjectName采用默认值，而指定@degree的值；第三种方式是两个参数都采用指定的值，具体代码如代码清单6-12所示，结果如图6-11所示。

图6-11几种调用方式的比较6.3.3设置输出参数

在前面的两小节中介绍了存储过程的参数，但都是输入参数，如果有必要，也可以声明输出参数，声明输出参数的语法为在参数后面加一个OUTPUT关键字。例如继续对上面所创建的存储过程spGetInfoBySubjectNameAndDegree进一步进行修改，添加一个返回参加指定科目和级别的所有考生的平均成绩的输出参数@averageScore，输入代码清单6-13所示的代码并执行。

注意，在上面的代码中“@averageScore=AVG(Score)”是指把函数AVG(Score)的结果赋给变量@averageScore，这时候SELECT所返回的就只是一个单个的数值。

接下来将调用修改过后的存储过程，首先声明一个数据类型为FLOAT的临时变量@AvgScre，用来存放输出参数的值，在调用时，对于@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*FROMTestInformationWHEREStudentID

=@studentID

没有匹配的记录，就执行RETURN-200，然后退出存储过程，否则就显示结果集。

现在对这个存储过程进行调用测试，具体代码如代码清单6-17所示，结果如图6-14所示。

图6-14显示存储过程的返回值在代码中，先声明一个变量用来存放返回值，然后进行了两次调用，第一次为查找学号为“20020504”的考生，但是没有找到匹配的记录，所以返回值为-200(见图6-14上面方框部分)；而第二次为查找学号为“20020745”的考生，找到了两条记录，所以先显示出结果集，然后显示出返回值为0(见图6-14下面方框部分)。

在本章的实验中，利用第3章实验中所创建的两张表Teachers2008和Course2008，由于它们的数据在第3章实验中已经被改变，因此在本章实验前需要重新创建它们。重新创建这两张表的代码参见第3章的代码清单3-52，可按以下步骤运行这段代码：6.4上机实验进入“MicrosoftSQLServerManagementStudio”界面，选择“文件”|“打开”|“文件…”，在弹出的“打开文件”对话框中定位到随本书配套资源中的代码文件“3-52.sql”，然后单击“连接”，就会在所打开的代码窗口中显示出代码清单3-52的代码，单击工具栏中的 ，这样Teachers2008和Course2008就会被重新创建并恢复最原始的数据了。

1．实验一：创建和使用简单存储过程

1)实验要求

(1)熟练运用CREATEPROCEDURE创建存储过程。

(2)对创建、运行、修改和删除存储过程有初步的认识。

2)实验目的

掌握存储过程的基本操作。

3)实验步骤

进入“MicrosoftSQLServerManagementStudio”界面，在对象资源管理器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“WxdStudent”，再单击工具栏按钮“ ”，在所打开的查询窗口中完成以下任务：

(1)创建一个不带任何参数的存储过程，名称为ShowAllTeachers，可以输出所有教师的全部数据。

在代码窗口中输入代码清单6-18所示的代码并运行，消息窗口显示出“命令已成功完成”，则说明存储过程已经被成功创建了。如果展开“WxdStudent”|“可编程性”|“存储过程”节点，可以看到存储过程ShowAllTeachers，如图6-15所示。

图6-15存储过程ShowAllTeachers

(2)运行存储过程ShowAllTeachers，观察结果。

在代码窗口中输入代码清单6-19所示的代码并运行，运行结果如图6-16所示。

图6-16运行结果

(3)修改存储过程ShowAllTeachers，改为只显示姓名(Name)和职称(AcademicTitle)两列数据，然后运行修改过后的存储过程。

在代码窗口中输入代码清单6-20所示的代码并运行，运行结果如图6-17所示。

图6-17运行结果

(4)删除存储过程ShowAllTeachers，然后尝试运行存储过程，观察错误信息。

在代码窗口中输入代码清单6-21所示的代码并运行，弹出图6-18所示的错误消息，说明存储过程ShowAllTeachers已经从数据库中删除了。

图6-18错误消息提示

2．实验二：创建和使用带参数的存储过程

1)实验要求

(1)熟悉创建带参数的存储过程的语法。

(2)理解默认参数的功能。

2)实验目的

掌握带参数的存储过程的运用。

3)实验步骤

进入“MicrosoftSQLServerManagementStudio”界面，在对象资源管理器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“WxdStudent”，再单击工具栏按钮“ ”，在所打开的查询窗口中完成以下任务：

(1)创建一个带1个参数的存储过程FindTeacherBySex，在参数中指定性别(1表示男，0表示女)，存储过程可以列出相应的老师，并且运行存储过程进行测试。

在代码窗口中输入代码清单6-22所示的代码并运行，运行结果如图6-19所示。

图6-19运行结果

(2)创建一个带一个参数的存储过程FindCourseByLesson

Place，在参数中指定上课地点，存储过程可以列出相应的课程。若不指定参数，则默认为列出在“课室”上课的课程，并且运行存储过程进行测试。

在代码窗口中输入代码清单6-23所示的代码并运行，运行结果如图6-20所示。

图6-20运行结果

(3)创建一个带两个参数的存储过程ModifyCourseByTeacher，在参数中指定课程名称和教师姓名，存储过程可以将相应课程的授课教师更改为参数中指定姓名的教师，