数据库原理与应用(14)第14章 存储过程

1、第第14章存储过程章存储过程14.1 概概 述述存储过程可不仅可以提高应用程序的处理能力，降低编写存储过程可不仅可以提高应用程序的处理能力，降低编写数据库应用程序的难度，同时还可以提高应用程序的效率。归数据库应用程序的难度，同时还可以提高应用程序的效率。归纳起来存储过程具有如下优点：纳起来存储过程具有如下优点：l 执行速度快。执行速度快。l 采用模块化程序设计。采用模块化程序设计。l 减少网络通信量。减少网络通信量。l 保证系统的安全性。保证系统的安全性。SQL Server 2005提供了提供了3种存储过程，即用户存储过程、种存储过程，即用户存储过程、系统存储过程和扩展存储过程。系统存储过程

2、和扩展存储过程。14.2 创建存储过程创建存储过程14.2.1. 使用使用SQL Server管理控制器创建存储过程管理控制器创建存储过程通过一个简单的示例说明使用通过一个简单的示例说明使用SQL Server管理控制器创管理控制器创建存储过程的操作步骤。建存储过程的操作步骤。【例【例14.1】 使用使用SQL Server管理控制器创建存储过程管理控制器创建存储过程maxdegree，用于输出所有学生的最高分。，用于输出所有学生的最高分。解：解：其操作步骤如下：其操作步骤如下：（1）启动）启动SQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中展开中展开“LCB

3、-PC”服务器节点。服务器节点。（2）展开）展开“数据库数据库”|“school”|“存储过程存储过程”节点，单击节点，单击鼠标右键，在出现的快捷菜单中选择鼠标右键，在出现的快捷菜单中选择“新建存储过程新建存储过程”命令。命令。（3）出现存储过程编辑窗口，其中含有一个存储过程模）出现存储过程编辑窗口，其中含有一个存储过程模板，用户可以参照模板在其中输入存储过程的板，用户可以参照模板在其中输入存储过程的T-SQL语句，这语句，这里输入的语句如下（其中红字部分为主要输入的里输入的语句如下（其中红字部分为主要输入的T-SQL语句）：语句）： set ANSI_NULLS ONset QUOTED_I

4、DENTIFIER ONGOCREATE PROCEDURE maxdegreeASBEGINSET NOCOUNT ONSELECT MAX(分数分数) AS 最高分最高分 FROM score /*从从score表中查询表中查询最高分最高分*/ENDGO（4）单击工具栏中的）单击工具栏中的“执行执行”按钮，将其保存在数据库中。按钮，将其保存在数据库中。此时选中此时选中“存储过程存储过程”节点，单击鼠标右键，在出现的快捷菜单节点，单击鼠标右键，在出现的快捷菜单中选择中选择“刷新刷新”命令，会看到命令，会看到“存储过程存储过程”的下方出现了的下方出现了maxdegree存储过程，如图存储过程，

5、如图14.1所示。所示。这样就完成了这样就完成了maxdegree存储过程的创建过程。存储过程的创建过程。14.2.2 使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程使用使用CREATE PROCEDURE语句的基本语法格式如下：语句的基本语法格式如下：CREATE PROCEDURE 存储过程名存储过程名 ; number parameter 数据类型数据类型 = 默认值默认值 OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL语句语句 n 【例【例1

6、4.2】 编写一个程序，创建一个简单的存储过程编写一个程序，创建一个简单的存储过程stud_degree，用于检索所有学生的成绩记录。，用于检索所有学生的成绩记录。解：解：对应的程序如下：对应的程序如下：USE schoolGO-若存在存储过程若存在存储过程stud_degree，则删除之，则删除之IF EXISTS(SELECT * FROM sysobjects WHERE name=stud_degree AND type=P) DROP PROCEDURE stud_degreeGO-注意，注意，CREATE PROCEDURE必须是一个批处理必须是一个批处理的第一个语句，故此的第一个

7、语句，故此GO不能缺不能缺-创建存储过程创建存储过程stud_degreeCREATE PROCEDURE stud_degree AS SELECT student.学号学号,student.姓名姓名,course.课程名课程名,score.分数分数 FROM student,course,score WHERE student.学号学号=score.学号学号AND course.课程号课程号=score.课程号课程号 ORDER BY student.学号学号GO14.3 执行存储过程执行存储过程可以使用可以使用EXECUTE或或EXEC语句来执行存储在服务器上语句来执行存储在服务器上的存

8、储过程，其完整语法格式如下：的存储过程，其完整语法格式如下： EXECUTE return_status = 存储过程名存储过程名 ;number | procedure_name_var parameter = 值值 | variable OUTPUT | DEFAULT ,n WITH RECOMPILE 【例【例14.3】 执行例执行例14.1中创建的存储过程中创建的存储过程maxdegree并查并查看输出的结果。看输出的结果。解：执行解：执行maxdegree存储过程的程序如下：存储过程的程序如下：USE schoolGOEXEC maxdegreeGO其执行结果如图其执行结果如图14

9、.2所示。从结果看到，查询的最高分为所示。从结果看到，查询的最高分为92。 14.4 存储过程的参数存储过程的参数14.4.1 在存储过程中使用参数在存储过程中使用参数在设计存储过程时可以带有参数，这样增加存储过程的灵在设计存储过程时可以带有参数，这样增加存储过程的灵活性。带参数的存储过程的一般格式如下：活性。带参数的存储过程的一般格式如下：CREATE PROCEDURE 存储过程名存储过程名( 参数列表参数列表 )AS SQL语句语句在调用存储过程时，有两种传递参数的方式。在调用存储过程时，有两种传递参数的方式。第第1种方式是在传递参数时，使传递的参数和定义时的参种方式是在传递参数时，使传

10、递的参数和定义时的参数顺序一致。其一般格式如下：数顺序一致。其一般格式如下：EXEC 存储过程名存储过程名 实参列表实参列表第第2种方式是采用种方式是采用“参数参数=值值”的形式，此时，各个参数的的形式，此时，各个参数的顺序可以任意排列。其一般格式如下：顺序可以任意排列。其一般格式如下：EXEC 存储过程名存储过程名 参数参数1=值值1,参数参数2=值值2,【例【例14.5】 设计一个存储过程设计一个存储过程maxno，以学号为参数，输，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。出指定学号学生的所有课程中最高分和对应的课程名。解：解：采用采用CREATE PROCEDUR

11、E语句设计该存储过程如语句设计该存储过程如下：下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=maxno AND type=P) DROP PROCEDURE maxnoGOCREATE PROCEDURE maxno(no char(10) AS /*声明声明no为参数为参数*/ SELECT s.学号学号,s.姓名姓名,c.课程名课程名,sc.分数分数 FROM student s,course c,score sc WHERE s.学号学号=no AND s.学号学号=sc.学号学号AND c.课程号课程号=sc.课

12、程号课程号AND sc.分数分数= (SELECT MAX(分数分数) FROM score WHERE 学号学号=no)GO采用第采用第1种方式执行存储过程种方式执行存储过程maxno的程序如下：的程序如下：USE schoolGOEXEC maxno 103GO采用第采用第2种方式执行存储过程种方式执行存储过程maxno的程序如下：的程序如下：USE schoolGOEXEC maxno no=103GO14.4.2 在存储过程中使用默认参数在存储过程中使用默认参数在设计存储过程时，可以为参数提供一个默认值，默认值必在设计存储过程时，可以为参数提供一个默认值，默认值必须为常量或者须为常量或

13、者NULL。其一般格式如下：。其一般格式如下：CREATE PROCEDURE 存储过程名存储过程名( 参数参数1=默认值默认值1, 参数参数2=默认值默认值2, )AS SQL语句语句在调用存储过程时，如果不指定对应的实参值，则自动用对在调用存储过程时，如果不指定对应的实参值，则自动用对应的默认值代替。应的默认值代替。【例【例14.6】 设计类似例设计类似例14.5功能的存储过程功能的存储过程maxno1，指定，指定其默认学号为其默认学号为101。解：解：设计一个新的存储过程设计一个新的存储过程maxno1，对应的程序如下：，对应的程序如下：USE schoolGOIF EXISTS(SEL

14、ECT * FROM sysobjects WHERE name=maxno1 AND type=P) DROP PROCEDURE maxno1GOCREATE PROCEDURE maxno1(no int=101) AS /*声明声明no为参数为参数*/ SELECT s.学号学号,s.姓名姓名,c.课程名课程名,sc.分数分数 FROM student s,course c,score sc WHERE s.学号学号=no AND s.学号学号=sc.学号学号AND c.课程号课程号=sc.课程号课程号AND sc.分数分数= (SELECT MAX(分数分数) FROM score

15、WHERE 学号学号=no)GO当不指定实参调用当不指定实参调用maxno1存储过程时，其结果如图存储过程时，其结果如图14.5所示。所示。 当指定实参为当指定实参为105调用调用maxno1存储过程时，其结果如图存储过程时，其结果如图14.6所示。所示。结论：从执行结果可以看到，结论：从执行结果可以看到，当调用存储过程时，没有指当调用存储过程时，没有指定参数值时就自动使用相应定参数值时就自动使用相应的默认值。的默认值。14.4.3 在存储过程中使用返回参数在存储过程中使用返回参数在创建存储过程时，可以定义返回参数。在执行存储过在创建存储过程时，可以定义返回参数。在执行存储过程时，可以将结果返

16、回给返回参数。返回参数应用程时，可以将结果返回给返回参数。返回参数应用OUTPUT进行说明。进行说明。【例【例14.7】 创建一个存储过程创建一个存储过程average，它返回两个参数，它返回两个参数st_name和和st_avg，分别代表了姓名和平均分。并编写，分别代表了姓名和平均分。并编写T-SQL语句执行该存储过程和查看输出的结果。语句执行该存储过程和查看输出的结果。解：解：建立存储过程建立存储过程average的程序如下：的程序如下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=average AND type=P)

17、 DROP PROCEDURE averageGOCREATE PROCEDURE average(st_no int,st_name char(8) OUTPUT,/*返回参数返回参数*/st_avg float OUTPUT/*返回参数返回参数*/) AS SELECT st_name=student.姓名姓名,st_avg=AVG(score.分数分数) FROM student,score WHERE student.学号学号=score.学号学号 GROUP BY student.学号学号,student.姓名姓名 HAVING student.学号学号=st_noGO执行该存储过程

18、，来查询学号为执行该存储过程，来查询学号为“105”的学生姓名和平均分：的学生姓名和平均分：DECLARE st_name char(10)DECLARE st_avg floatEXEC average 105,st_name OUTPUT,st_avg OUTPUTSELECT 姓名姓名=st_name,平均分平均分=st_avgGO4. 存储过程的返回值存储过程的返回值存储过程在执行后都会返回一个整型值（称为存储过程在执行后都会返回一个整型值（称为“返回代返回代码码”），指示存储过程的执行状态。），指示存储过程的执行状态。如果执行成功，返回如果执行成功，返回0；否则返回；否则返回-1-9

19、9之间的数值之间的数值（例如（例如-1表示找不到对象，表示找不到对象，-2表示数据类型错误，表示数据类型错误，-5表示语表示语法错误等）。也可以使用法错误等）。也可以使用RETURN语句来指定一个返回值。语句来指定一个返回值。【例【例14.9】 编写一个程序，创建存储过程编写一个程序，创建存储过程test_ret，根据，根据输入的参数来判断返回值。并执行该存储过程和查看输出的输入的参数来判断返回值。并执行该存储过程和查看输出的结果。结果。解：解：建立存储过程建立存储过程test_ret如下：如下：USE testGOIF EXISTS(SELECT * FROM sysobjects WHER

20、E name=test_ret AND type=P) DROP PROCEDURE test_retGOCREATE PROC test_ret(input_int int = 0) AS/*指定默认参数值指定默认参数值*/ IF input_int=0 RETURN 0 -如果输入的参数等于如果输入的参数等于0，则返回，则返回0 IF input_int0 RETURN 1000 -如果输入的参数大于如果输入的参数大于0，则返回，则返回1000 IF input_int0 RETURN -1000 -如果输入的参数小于如果输入的参数小于0，则返回，则返回-1000GO执行该存储过程：执行该

21、存储过程：USE TestDECLARE ret_int intEXEC ret_int=test_ret 1PRINT 返回值返回值PRINT -PRINT ret_intEXEC ret_int=test_ret 0PRINT ret_intEXEC ret_int=test_ret -1PRINT ret_int14.5 存储过程的管理存储过程的管理14.5.1 查看存储过程查看存储过程在创建存储过程后，它的名称就存储在系统表在创建存储过程后，它的名称就存储在系统表sysobjects中，中，它的源代码存放在系统表它的源代码存放在系统表syscomments中。可以使用中。可以使用SQL

22、 Server管理控制器或系统存储过程来查看用户创建的存储过程。管理控制器或系统存储过程来查看用户创建的存储过程。1. 使用使用SQL Server管理控制器查看存储过程管理控制器查看存储过程通过一个例子说明使用通过一个例子说明使用SQL Server管理控制器查看存储过管理控制器查看存储过程的操作步骤。程的操作步骤。【例【例14.10】 使用使用SQL Server管理控制器查看例管理控制器查看例14.8所创建所创建的存储过程的存储过程stud1_degree。解：解：其操作步骤如下：其操作步骤如下：（1）启动）启动SQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理

23、器”中展开中展开“LCB-PC”服务器节点。服务器节点。（2）展开）展开“数据库数据库”|“school”|“可编程性可编程性”|“存储过存储过程程”|“dbo.stud1_degree”节点，单击鼠标右键，在出现的快捷节点，单击鼠标右键，在出现的快捷菜单中选择菜单中选择“编写存储过程脚本为编写存储过程脚本为(S)|CREATE到到(C)|新查询编新查询编辑器窗口辑器窗口”命令。命令。（3）在右边的编辑器窗口中出现存储过程）在右边的编辑器窗口中出现存储过程stud_degree源源代码，如图代码，如图14.9所示。此时用户只能查看其代码。所示。此时用户只能查看其代码。2. 使用系统存储过程来查

24、看存储过程使用系统存储过程来查看存储过程SQL Server 2005提供了如下系统存储过程用于查看用户提供了如下系统存储过程用于查看用户创建的存储过程。创建的存储过程。（1）sp_help用于显示存储过程的参数及其数据类型，其语法如下：用于显示存储过程的参数及其数据类型，其语法如下：sp_help objname= name其中，参数其中，参数name为要查看的存储过程的名称。为要查看的存储过程的名称。（2）sp_helptext用于显示存储过程的源代码，其语法如下：用于显示存储过程的源代码，其语法如下：sp_helptext objname= name其中，参数其中，参数name为要查看的

25、存储过程的名称。为要查看的存储过程的名称。（3）sp_depends用于显示和存储过程相关的数据库对象，其语法如下：用于显示和存储过程相关的数据库对象，其语法如下：sp_depends objname=object其中，参数其中，参数object为要查看依赖关系的存储过程的名称。为要查看依赖关系的存储过程的名称。（4）sp_stored_procedures用于返回当前数据库中的存储过程列表，其语法如下：用于返回当前数据库中的存储过程列表，其语法如下：sp_stored_procedure sp_name= name ,sp_owner=owner ,sp_qualifier= qualifi

26、er【例【例14.11】 使用相关系统存储过程查看例使用相关系统存储过程查看例14.2所创建的存所创建的存储过程储过程stud_degree的相关内容。的相关内容。解：解：对应的程序如下：对应的程序如下：USE schoolGOEXEC sp_help stud_degreeEXEC sp_helptext stud_degreeEXEC sp_depends stud_degree14.5.2 修改存储过程修改存储过程在创建存储过程之后，用户可以对其进行修改。可以使用在创建存储过程之后，用户可以对其进行修改。可以使用SQL Server管理控制器或使用管理控制器或使用ALTER PROCED

27、URE语句修改用户创建语句修改用户创建的存储过程。的存储过程。1. 使用使用SQL Server管理控制器修改存储过程管理控制器修改存储过程通过一个例子说明使用通过一个例子说明使用SQL Server管理控制器修改存储过程的管理控制器修改存储过程的操作步骤。操作步骤。【例【例14.12】 使用使用SQL Server管理控制器修改例管理控制器修改例14.2所创建的存所创建的存储过程储过程stud_degree。解：解：其操作步骤如下：其操作步骤如下：（1）启动）启动SQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PC”服务器节点。服务器节点

28、。（2）展开）展开“数据库数据库”|“school”|“可编程性可编程性”|“存储过存储过程程”|“dbo.stud_degree”节点，单击鼠标右键，在出现的快捷菜单中节点，单击鼠标右键，在出现的快捷菜单中选择选择“修改修改”命令。命令。（3）此时右边的编辑器窗口出现）此时右边的编辑器窗口出现stud_degree存储过程的存储过程的源代码（将源代码（将“CREATE PROCEDURE”改为改为“ALTER PROCEDURE”），如图），如图14.11所示，用户可以直接进行修改。所示，用户可以直接进行修改。修改完毕，单击工具栏中的修改完毕，单击工具栏中的“执行执行”按钮执行该存储过程，按

29、钮执行该存储过程，从而达到修改的目的。从而达到修改的目的。2. 使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程使用使用ALTER PROCEDURE语句可以更改先前通过执行语句可以更改先前通过执行CREATE PROCEDURE语句创建的过程，但不会更改权限，语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器，其语法形式如下：也不影响相关的存储过程或触发器，其语法形式如下：ALTER PROCEDURE 存储过程名存储过程名参数列表参数列表 AS SQL语句语句当使用当使用ALTER PROCEDURE语句时，如果在语句时，如果在CREATE PROCEDU

30、RE语句中使用过参数，那么在语句中使用过参数，那么在ALTER PROCEDURE语句中也应该使用这些参数。每次只能修改一个语句中也应该使用这些参数。每次只能修改一个存储过程。存储过程。【例例14.13】 编写一个程序，先创建一个存储过程编写一个程序，先创建一个存储过程studproc，输出，输出“1031”班的所有学生，利用班的所有学生，利用sysobjects和和syscomments两个系统表输出该存储过程的两个系统表输出该存储过程的id和和text列。然后利列。然后利用用ALTER PROCEDURE语句修改该存储过程，将其改为加密语句修改该存储过程，将其改为加密方式，最后再输出该存储

31、过程的方式，最后再输出该存储过程的id和和text列。列。解：解：创建存储过程创建存储过程studproc的语句如下：的语句如下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=studproc AND type=P) DROP PROCEDURE studprocGOCREATE PROCEDURE studproc AS SELECT * FROM student WHERE 班号班号=1031GO通过以下语句输出通过以下语句输出studproc存储过程的存储过程的id和和text列：列：SELECT sysobjects

32、.id,syscomments.textFROM sysobjects,syscommentsWHERE =studproc AND sysobjects.type=P AND sysobjects.id=syscomments.id其执行结果如图其执行结果如图14.12所示。所示。 修改该存储过程的语句如下：修改该存储过程的语句如下：USE schoolGOALTER PROCEDURE studproc WITH ENCRYPTION AS SELECT * FROM student WHERE 班号班号=1031GO再次执行前面的输出再次执行前面的输出stu

33、dproc存储过程的存储过程的id和和text列的语句，列的语句，其执行结果如图其执行结果如图14.13所示。从中看到，加密过的存储过程查询所示。从中看到，加密过的存储过程查询出的源代码是空值，从而起到保护源程序的作用。出的源代码是空值，从而起到保护源程序的作用。14.5.3 重命名存储过程重命名存储过程重命名存储过程也有两种方法：使用重命名存储过程也有两种方法：使用SQL Server管理控制管理控制器或使用系统存储过程。器或使用系统存储过程。1. 使用使用SQL Server管理控制器重命名存储过程管理控制器重命名存储过程通过一个例子说明使用通过一个例子说明使用SQL Server管理控制

34、器重命名存储管理控制器重命名存储过程的操作步骤。过程的操作步骤。【例【例14.14】 使用使用SQL Server管理控制器将存储过程管理控制器将存储过程studproc重命令为重命令为studproc1。解：解：其操作步骤如下：其操作步骤如下：（1）启动）启动SQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中展开中展开“LCB-PC”服务器节点。服务器节点。（2）展开）展开“数据库数据库”|“school”|“可编程性可编程性”|“存储过存储过程程”|“dbo.studproc”节点，单击鼠标右键，在出现的快捷菜单节点，单击鼠标右键，在出现的快捷菜单中选择中选择“重命名重命名”命令。命令。（3）此时存储过程名称）此时存储过程名称“studproc”变成可编辑的，可以变成可编辑的，可以直接修改该存储过程的名称