第12章存储过程和用户存储过程设计

1、 存储过程（存储过程（Stored ProcedureStored Procedure）是一组为了完）是一组为了完成特定功能的成特定功能的SQL SQL 语句集，经编译后存储在数据库语句集，经编译后存储在数据库中。中。Transact SQLTransact SQL存储过程可以接受输入参数，以存储过程可以接受输入参数，以参数形式返回输出值，或者返回成功、失败的状态参数形式返回输出值，或者返回成功、失败的状态信息。信息。第第1212章章 存储过程和用户存储过程设计存储过程和用户存储过程设计1 12.1 存储过程概述存储过程概述 12.2 系统存储过程系统存储过程 12.3 创建和执行用户存储过程

2、创建和执行用户存储过程 12.4 带状态参数的存储过程及实例分析带状态参数的存储过程及实例分析 12.5 修改和删除存储过程修改和删除存储过程 12.6 存储过程设计实例分析存储过程设计实例分析212.1 12.1 存储过程概述存储过程概述 12.1.1 12.1.1 存储过程的概念和分类存储过程的概念和分类 SQL Server提供了一种方法，它可以将一提供了一种方法，它可以将一些固定的操作集中起来由些固定的操作集中起来由SQL Server数据库服数据库服务器来完成，以实现某个任务，这种方法就是务器来完成，以实现某个任务，这种方法就是存储过程。存储过程。 存储过程（存储过程（Stored

3、Procedure）是一组为了）是一组为了完成特定功能的完成特定功能的SQL 语句集，经编译后存储在语句集，经编译后存储在数据库中。数据库中。312.1 12.1 存储过程概述存储过程概述 12.1.1 12.1.1 存储过程的概念和分类存储过程的概念和分类 用户或应用程序通过指定存储过程的名字并给出用户或应用程序通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，而参数（如果该存储过程带有参数）来执行它，而且允许用户声明变量、有条件执行以及其它强大且允许用户声明变量、有条件执行以及其它强大的编程功能。的编程功能。 在在SQL ServerSQL Server中存储过程分为两类

4、：中存储过程分为两类： 系统提供的存储过程系统提供的存储过程 用户自定义的存储过程。用户自定义的存储过程。412.1 12.1 存储过程概述存储过程概述 12.1.2 12.1.2 存储过程的优点存储过程的优点 （1）存储过程允许标准组件式编程）存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的而不必重新编写该存储过程的SQL 语句。而且数语句。而且数据库专业人员可随时对存储过程进行修改，但对应据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包用程序源代码毫无影响（

5、因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序含存储过程的调用语句），从而极大地提高了程序的可移植性。的可移植性。 5 （2）存储过程能够实现较快的执行速度）存储过程能够实现较快的执行速度 如果某一操作包含大量的如果某一操作包含大量的Transaction-SQL 代代码或分别被多次执行，那么存储过程要比批处理的码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。执行速度快很多。 （3）存储过程能够减少网络流量）存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的询、修改），如果

6、这一操作所涉及到的 Transaction-SQL语句被组织成一存储过程，那么语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条的只是该调用语句，否则将是多条SQL语句，从而语句，从而大大增加了网络流量，降低网络负载。大大增加了网络流量，降低网络负载。 6 （4）存储过程可被作为一种安全机制来充分利用系）存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免从而能够实现对相应的数

7、据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。非授权用户对数据的访问，保证数据的安全。 （5）自动完成需要预先执行的任务）自动完成需要预先执行的任务.存储过程可以在存储过程可以在系统启动时自动执行系统启动时自动执行,而不必在系统启动后再进行而不必在系统启动后再进行手工操作手工操作,大大方便了用户的使用大大方便了用户的使用,可以自动完成一可以自动完成一些需要预先执行的任务些需要预先执行的任务712.2 12.2 系统存储过程系统存储过程 12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询

8、信息或完成与更新数能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。据库表相关的管理任务或其它的系统管理任务。系统过程以系统过程以“sp_开头开头,在在Master数据库中创建并数据库中创建并保存在该数据库中，为数据库管理者所有。一些保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。程通过授权可以被其它用户所使用。812.2.2 一些常用的系统存储过程分分 类类常用的系统存储过程目录过程目录过程sp_column_privilegessp_column_

9、privileges；sp_special_columnssp_special_columns；sp_columnssp_columns；sp_sproc_columnssp_sproc_columns；sp_databasessp_databases；sp_statisticssp_statistics；sp_fkeyssp_fkeys；sp_stored_proceduressp_stored_procedures；(部分部分)游标过程游标过程sp_cursor_listsp_cursor_list；sp_describe_cursor_columnssp_describe_cursor_

10、columns；sp_describe_cursorsp_describe_cursor；sp_describe_cursor_tablessp_describe_cursor_tables分布式查分布式查询过程询过程sp_addlinkedserversp_addlinkedserver；sp_indexessp_indexes；sp_addlinkedsrvloginsp_addlinkedsrvlogin；sp_linkedserverssp_linkedservers；sp_catalogssp_catalogssp_primarykeyssp_primarykeys；sp_dropl

11、inkedsrvloginsp_droplinkedsrvlogin；sp_foreignkeyssp_foreignkeys安全过程安全过程sp_addaliassp_addalias；sp_droprolemembersp_droprolemember；sp_addapprolesp_addapprole；sp_dropserversp_dropserver；sp_addgroupsp_addgroup；sp_dropsrvrolemembersp_dropsrvrolemember；sp_addlinkedsrvloginsp_addlinkedsrvlogin；sp_dropusers

12、p_dropuser；sp_addloginsp_addlogin；sp_grantdbaccesssp_grantdbaccess；sp_addremoteloginsp_addremotelogin；sp_grantloginsp_grantlogin；sp_addrolesp_addrole；sp_helpdbfixedrolesp_helpdbfixedrole；sp_addrolemembersp_addrolemember；sp_helpgroupsp_helpgroup；sp_addserversp_addserver；sp_helplinkedsrvloginsp_helpli

13、nkedsrvlogin；sp_addsrvrolemembersp_addsrvrolemember；sp_helploginssp_helplogins；sp_addusersp_adduser；sp_helpntgroupsp_helpntgroup；sp_approlepasswordsp_approlepassword；sp_helpremoteloginsp_helpremotelogin；sp_changedbownersp_changedbowner；sp_helprolesp_helprole；sp_changegroupsp_changegroup；sp_helprolem

14、embersp_helprolemember；(部分部分)9系统过程sp_add_data_file_recover_suspect_dbsp_add_data_file_recover_suspect_db；sp_helpconstraintsp_helpconstraint；sp_addextendedprocsp_addextendedproc；sp_helpdbsp_helpdb；sp_addextendedpropertysp_addextendedproperty；sp_helpdevicesp_helpdevice；sp_add_log_file_recover_suspect_

15、d bsp_add_log_file_recover_suspect_d b ；sp_helpextendedprocsp_helpextendedproc；sp_addmessagesp_addmessage；sp_helpfilesp_helpfile；sp_addtypesp_addtype；sp_helpfilegroupsp_helpfilegroup；sp_addumpdevicesp_addumpdevice；sp_helpindexsp_helpindex；sp_altermessagesp_altermessage；sp_helplanguagesp_helplanguage

16、；sp_autostatssp_autostats；sp_helpserversp_helpserver；sp_attach_dbsp_attach_db；sp_helpsortsp_helpsort；sp_attach_single_file_dbsp_attach_single_file_db；sp_helpstatssp_helpstats；sp_bindefaultsp_bindefault；sp_helptextsp_helptext；sp_bindrulesp_bindrule；sp_helptriggersp_helptrigger；s p _ b i n d s e s s i

17、 o ns p _ b i n d s e s s i o n ； s p _ i n d e x o p t i o ns p _ i n d e x o p t i o n ；sp_certify_removablesp_certify_removable；sp_invalidate_textptrsp_invalidate_textptr；sp_configuresp_configuresp_locksp_lock；（部分）；（部分）10 如果没有指定数据库名，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息

18、exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb 1112.3.1 创建用户存储过程用户自定义存储过程是由用户创建并能完成某一特定功能用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在（如查询用户所需数据信息）的存储过程。在SQL Server中，中，可以使用以下方法创建存储过程：可以使用以下方法创建存储过程： 利用利用SQL Server管理器创建存储过程。管理器创建存储过程。 使用使用Transact-SQL语句中的语句中的CREATE PROCEDURE命令命令创建存储过程。创建存储过程。

19、 创建存储过程时，需要确定存储过程的三个组成部分：创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数。所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程被执行的针对数据库的操作语句，包括调用其它存储过程的语句。的语句。 返回给调用者的状态值，以指明调用是成功还是失败。返回给调用者的状态值，以指明调用是成功还是失败。12.3 12.3 创建和执行用户存储过程创建和执行用户存储过程 12 1 1使用使用SQL ServerSQL Server管理器创管理器创建存储过程建存储过程 在在SQL Server管理器中，选择指

20、定的管理器中，选择指定的服务器和数据库，用右键单击要创建存储服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择过程的数据库，在弹出的快捷菜单中选择“新建新建”选项，再选择下一级菜单中的选项，再选择下一级菜单中的“存储过程存储过程”选项，从弹出的快捷菜单选项，从弹出的快捷菜单中选择中选择“新建存储过程新建存储过程”选项，出现创选项，出现创建存储过程对话框。建存储过程对话框。132使用CREATE PROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几个事项：创建存储过程前，应该考虑下列几个事项： 在一个批处理中，在一个批处理中，Create Procedure 语

21、句不能与其它语句不能与其它SQL语句语句合并在一起；合并在一起； 创建存储过程的权限默认属于数据库所有者，该所有者可将此创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建当前数据库的存储过程。只能在当前数据库中创建当前数据库的存储过程。 一个存储过程的最大尺寸为一个存储过程的最大尺寸为128M。14语法格式：语法格式： CREATE PROC EDURE 存储过程名存储过程名 ；版本号；版本号 ( 参数名参数名 数据类型数据类型 VA

22、RYING =default OUTPUT ，)WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS SQL语句语句15【例【例12-3】创建一个简单的存储过程，返回所有】创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。专业、所在二级学院、来自的城市。 USE 教学管理教学管理 GO CREATE PROCEDURE P_学生部分信息学生部分信息 AS SELECT 学号学号,姓名姓名,专业专业,所在院系所在院系,籍贯籍贯

23、 FROM 学生表学生表16USE 教学管理教学管理IF EXISTS(SELECT* FROM sysobjects WHERE name=SC_infor AND type=p) BEGIN DROP PROCEDURE SC_infor ENDGOCREATE PROCEDURE SC_infor(smajor CHAR(20), cname CHAR(20) AS SELECTS.sno,sname,smajor,sdepa,O.cno,cname,grade FROM student S, enrollment E, offering O, course C where smajor

24、=smajor AND cname=cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cnoGO【例【例12-4】创建带参数的存储过程，实现对指定的某一专业】创建带参数的存储过程，实现对指定的某一专业 的学生某门课程的选课信息和成绩。的学生某门课程的选课信息和成绩。1712.3.2 执行用户存储过程执行已创建的存储过程使用执行已创建的存储过程使用EXECUTE 命令。命令。语法格式：语法格式： EXEC UTE return_status = ;版本号版本号 | procedure_name_var 参数参数= value | variable

25、 OUTPUT | DEFAULT ,.n WITH RECOMPILE 18【例【例12-5】对存储过程】对存储过程P_学生部分信息学生部分信息的执行。的执行。 EXEC P_学生部分信息学生部分信息【例【例12-6】带输入参数的存储过程】带输入参数的存储过程P_学生选课信息学生选课信息的执行。的执行。（1）按参数位置传递值）按参数位置传递值 EXEC P_学生选课信息学生选课信息 计算机计算机,数据结构数据结构 或者：或者：DECLARE 专业专业 CHAR(20), 课名课名 CHAR(20)SET 专业专业=计算机计算机SET 课名课名=数据结构数据结构EXEC P_学生选课信息学生选

26、课信息 专业专业,课名课名 或者：或者：DECLARE 专业专业 CHAR(20)SET 专业专业=计算机计算机EXEC P_学生选课信息学生选课信息 专业专业,数据结构数据结构19（2 2）按参数名传递值）按参数名传递值EXEC P_学生选课信息学生选课信息 专业专业 =计算机计算机, 课名课名=数据结构数据结构按参数名传递值可以改变参数的顺序：按参数名传递值可以改变参数的顺序：EXEC P_学生选课信息学生选课信息 课名课名=数据结构数据结构, 专业专业=计算机计算机20 （3 3）也可以两种方法混合使用，一旦使用了）也可以两种方法混合使用，一旦使用了 name = value name

27、= value 形式之后，所有后续的参形式之后，所有后续的参数就必须以数就必须以 name = value name = value 的形式传递。的形式传递。 比如：比如： EXEC P_学生选课信息学生选课信息 计算机计算机, 课名课名=数数据结构据结构21【例12-7】使用OUTPUT参数的存储过程及其执行首先创建存储过程首先创建存储过程USE 教学管理教学管理GOCREATE PROCEDURE P_成绩检索和平均成绩检索和平均 (学号学号 CHAR(7), 平均成绩平均成绩 FLOAT OUTPUT)ASSELECT S.学号学号,姓名姓名,课号课号,成绩成绩FROM 学生表学生表 S

28、, 开课表开课表 O, 选课表选课表 EWHERE S.学号学号=学号学号 AND E.学号学号=S.学号学号 AND E.开课号开课号=O.开课号开课号SELECT 平均成绩平均成绩 =AVG(成绩成绩)FROM 学生表学生表 S, 开课表开课表 O, 选课表选课表 EWHERE S.学号学号=学号学号 AND E.学号学号=S.学号学号 AND E.开课号开课号=O.开课号开课号RETURNGO22然后在查询分析器中调用然后在查询分析器中调用 Scorequery 存储过程存储过程DECLARE 学号学号 CHAR(7),平均成绩平均成绩 FLOATSET 学号学号=S060102EXEC

29、 P_成绩检索和平均成绩检索和平均 学号学号, 平均成绩平均成绩 OUTPUTIF 平均成绩平均成绩 =90 S E L E C T 该 学 生 的 成 绩该 学 生 的 成 绩 = 优 秀优 秀 , 平 均 成 绩平 均 成 绩=rtrim(cast(平均成绩平均成绩 as VARCHAR(20)IF 平均成绩平均成绩 =80 AND 平均成绩平均成绩 =70 AND 平均成绩平均成绩 =60 AND 平均成绩平均成绩 70 S E L E C T 该 学 生 的 成 绩该 学 生 的 成 绩 = 及 格及 格 , 平 均 成 绩平 均 成 绩 = rtrim(cast(平均成绩平均成绩 a

30、s VARCHAR(20)IF 平均成绩平均成绩 =60 AND 学号学号=学号学号38-打开计算累计学分打开计算累计学分_cur，逐门显示该学生所选的课程及成绩并，逐门显示该学生所选的课程及成绩并根据条件计算该学生获取学分总数根据条件计算该学生获取学分总数OPEN 计算累计学分计算累计学分_curFETCH NEXT FROM 计算累计学分计算累计学分_cur INTO 课号课号,课名课名,成绩成绩,学分学分SELECT 累计学分累计学分=0IF FETCH_STATUS 0 PRINT 没有课程被选修没有课程被选修WHILE fetch_status=0BEGINSELECT messag

31、e=课号课号+ + 课名课名+ +CONVERT(CHAR(8), 成绩成绩)+ + CONVERT(CHAR(8), 学分学分)PRINT message39 SELECT 累计学分累计学分=累计学分累计学分+学分学分 FETCH NEXT FROM 计算累计学分计算累计学分_cur INTO 课号课号,课名课名,成绩成绩,学分学分 END -显示当前学生所获取的总的学分显示当前学生所获取的总的学分 SELECT message=累计的学分是累计的学分是: + CONVERT(CHAR(8), 累计学分累计学分) PRINT message CLOSE 计算累计学分计算累计学分_cur DEALLOCATE 计算累计学分计算累计学