第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 存储过程设计实例分析存储过程设计实例分析 2 12.1 12.1 存储过程概述存储过程概述 12.1.1 12.1.1 存储过程的概念和分类存储过程的概念和分类 SQL Server提供了一种方法，它可以将一提供了一种方法，它可以将一 些固定的操作集中起来由些固定的操作集中起来由SQL Server数据库服数据库服 务器来完成，以实现某个任务，这种方法就是务器来完成，以实现某个任务，这种方法就是 存储过程。存储过程。 存储过程（存

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

4、QL Server中存储过程分为两类：中存储过程分为两类： 系统提供的存储过程系统提供的存储过程 用户自定义的存储过程。用户自定义的存储过程。 4 12.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）自动完成需要预先执行的任务）自动完成需要预先执行的任务.存储过程可以在存储过程可以在 系统启动时自动执行系统启动时自动执行,而不必在系统启动后再进行而不必在系统启动后再进行 手工操作手工操作,大大方便了用户的使用大大方便了用户的使用,可以自动完成一可以自动完成一 些需要预先执行的任务些需要预先执行的任务 7 12.2 12.2 系统存储过程系统存储过程 12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程

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

9、类常用的系统存储过程 目录过程目录过程 sp_column_privilegessp_column_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_cur

10、sor_list；sp_describe_cursor_columnssp_describe_cursor_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；

11、sp_catalogssp_catalogssp_primarykeyssp_primarykeys； sp_droplinkedsrvloginsp_droplinkedsrvlogin；sp_foreignkeyssp_foreignkeys 安全过程安全过程 sp_addaliassp_addalias；sp_droprolemembersp_droprolemember；sp_addapprolesp_addapprole；sp_dropserversp_dropserver； sp_addgroupsp_addgroup；sp_dropsrvrolemembersp_dropsrvr

12、olemember； sp_addlinkedsrvloginsp_addlinkedsrvlogin；sp_dropusersp_dropuser；sp_addloginsp_addlogin；sp_grantdbaccesssp_grantdbaccess； sp_addremoteloginsp_addremotelogin；sp_grantloginsp_grantlogin； sp_addrolesp_addrole；sp_helpdbfixedrolesp_helpdbfixedrole；sp_addrolemembersp_addrolemember；sp_helpgroupsp

13、_helpgroup； sp_addserversp_addserver；sp_helplinkedsrvloginsp_helplinkedsrvlogin； sp_addsrvrolemembersp_addsrvrolemember；sp_helploginssp_helplogins；sp_addusersp_adduser；sp_helpntgroupsp_helpntgroup； sp_approlepasswordsp_approlepassword； sp_helpremoteloginsp_helpremotelogin；sp_changedbownersp_changedb

14、owner；sp_helprolesp_helprole；sp_changegroupsp_changegroup ；sp_helprolemembersp_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_

15、addextendedproperty；sp_helpdevicesp_helpdevice； sp_add_log_file_recover_suspect_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_

16、helpindexsp_helpindex； sp_altermessagesp_altermessage；sp_helplanguagesp_helplanguage；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_helptext

17、sp_helptext；sp_bindrulesp_bindrule； sp_helptriggersp_helptrigger； s p _ b i n d s e s s i 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_l

18、ock；（部分）；（部分） 10 如果没有指定数据库名，则sp_helpdb报告 master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息 exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb 11 12.3.1 创建用户存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能用户自定义存储过程是由用户创建并能完成某一特定功能 （如查询用户所需数据信息）的存储过程。在（如查询用户所需数据信息）的存储过程。在SQL Server中，中， 可以使用以下方法创建存储过程：可以使用以下方法创建存储

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

20、.3 12.3 创建和执行用户存储过程创建和执行用户存储过程 12 1 1使用使用SQL ServerSQL Server管理器创管理器创 建存储过程建存储过程 在在SQL Server管理器中，选择指定的管理器中，选择指定的 服务器和数据库，用右键单击要创建存储服务器和数据库，用右键单击要创建存储 过程的数据库，在弹出的快捷菜单中选择过程的数据库，在弹出的快捷菜单中选择 “新建新建”选项，再选择下一级菜单中的选项，再选择下一级菜单中的 “存储过程存储过程”选项，从弹出的快捷菜单选项，从弹出的快捷菜单 中选择中选择“新建存储过程新建存储过程”选项，出现创选项，出现创 建存储过程对话框。建存储过

21、程对话框。 13 2使用CREATE PROCEDURE命令创建存储过程 创建存储过程前，应该考虑下列几个事项：创建存储过程前，应该考虑下列几个事项： 在一个批处理中，在一个批处理中，Create Procedure 语句不能与其它语句不能与其它SQL语句语句 合并在一起；合并在一起； 创建存储过程的权限默认属于数据库所有者，该所有者可将此创建存储过程的权限默认属于数据库所有者，该所有者可将此 权限授予其他用户。权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建当前数据库的存储过程。只能在当前数据库中创建

22、当前数据库的存储过程。 一个存储过程的最大尺寸为一个存储过程的最大尺寸为128M。 14 语法格式：语法格式： CREATE PROC EDURE 存储过程名存储过程名 ；版本号；版本号 ( 参数名参数名 数据类型数据类型 VARYING =default OUTPUT ，) WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENC RYPTION FOR REPLICATION AS SQL语句语句 15 【例【例12-3】创建一个简单的存储过程，返回所有】创建一个简单的存储过程，返回所有 学生的基本信息，包括学生的学号、姓名、所学学生的基本信息，包括学生的学号、姓名、

23、所学 专业、所在二级学院、来自的城市。专业、所在二级学院、来自的城市。 USE 教学管理教学管理 GO CREATE PROCEDURE P_学生部分信息学生部分信息 AS SELECT 学号学号,姓名姓名,专业专业,所在院系所在院系,籍贯籍贯 FROM 学生表学生表 16 USE 教学管理教学管理 IF EXISTS(SELECT* FROM sysobjects WHERE name=SC_infor AND type=p) BEGIN DROP PROCEDURE SC_infor END GO CREATE PROCEDURE SC_infor(smajor CHAR(20), cna

24、me CHAR(20) AS SELECTS.sno,sname,smajor,sdepa,O.cno,cname,grade FROM student S, enrollment E, offering O, course C where smajor=smajor AND cname=cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cno GO 【例【例12-4】创建带参数的存储过程，实现对指定的某一专业】创建带参数的存储过程，实现对指定的某一专业 的学生某门课程的选课信息和成绩。的学生某门课程的选课信息和成绩。 17 12.3.2 执行

25、用户存储过程 执行已创建的存储过程使用执行已创建的存储过程使用EXECUTE 命令。命令。 语法格式：语法格式： EXEC UTE return_status = ;版本号版本号 | procedure_name_var 参数参数= value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE 18 【例【例12-5】对存储过程】对存储过程P_学生部分信息学生部分信息的执行。的执行。 EXEC P_学生部分信息学生部分信息 【例【例12-6】带输入参数的存储过程】带输入参数的存储过程P_学生选课信息学生选课信息的执行。的执行。 （1）按参数位置传递值

26、）按参数位置传递值 EXEC P_学生选课信息学生选课信息 计算机计算机,数据结构数据结构 或者：或者： DECLARE 专业专业 CHAR(20), 课名课名 CHAR(20) SET 专业专业=计算机计算机 SET 课名课名=数据结构数据结构 EXEC P_学生选课信息学生选课信息 专业专业,课名课名 或者：或者： DECLARE 专业专业 CHAR(20) SET 专业专业=计算机计算机 EXEC P_学生选课信息学生选课信息 专业专业,数据结构数据结构 19 （2 2）按参数名传递值）按参数名传递值 EXEC P_学生选课信息学生选课信息 专业专业 =计算机计算机, 课名课名=数据结构

27、数据结构 按参数名传递值可以改变参数的顺序：按参数名传递值可以改变参数的顺序： EXEC P_学生选课信息学生选课信息 课名课名=数据结构数据结构, 专业专业=计算机计算机 20 （3 3）也可以两种方法混合使用，一旦使用了）也可以两种方法混合使用，一旦使用了 name = value name = value 形式之后，所有后续的参形式之后，所有后续的参 数就必须以数就必须以 name = value name = value 的形式传递。的形式传递。 比如：比如： EXEC P_学生选课信息学生选课信息 计算机计算机, 课名课名=数数 据结构据结构 21 【例12-7】使用OUTPUT参数

28、的存储过程及其执行 首先创建存储过程首先创建存储过程 USE 教学管理教学管理 GO CREATE PROCEDURE P_成绩检索和平均成绩检索和平均 (学号学号 CHAR(7), 平均成绩平均成绩 FLOAT OUTPUT) AS SELECT S.学号学号,姓名姓名,课号课号,成绩成绩 FROM 学生表学生表 S, 开课表开课表 O, 选课表选课表 E WHERE S.学号学号=学号学号 AND E.学号学号=S.学号学号 AND E.开课号开课号=O.开课号开课号 SELECT 平均成绩平均成绩 =AVG(成绩成绩) FROM 学生表学生表 S, 开课表开课表 O, 选课表选课表 E

29、WHERE S.学号学号=学号学号 AND E.学号学号=S.学号学号 AND E.开课号开课号=O.开课号开课号 RETURN GO 22 然后在查询分析器中调用然后在查询分析器中调用 Scorequery 存储过程存储过程 DECLARE 学号学号 CHAR(7),平均成绩平均成绩 FLOAT SET 学号学号=S060102 EXEC P_成绩检索和平均成绩检索和平均 学号学号, 平均成绩平均成绩 OUTPUT IF 平均成绩平均成绩 =90 S E L E C T 该 学 生 的 成 绩该 学 生 的 成 绩 = 优 秀优 秀 , 平 均 成 绩平 均 成 绩 =rtrim(cast(

30、平均成绩平均成绩 as VARCHAR(20) IF 平均成绩平均成绩 =80 AND 平均成绩平均成绩 =70 AND 平均成绩平均成绩 =60 AND 平均成绩平均成绩 70 S E L E C T 该 学 生 的 成 绩该 学 生 的 成 绩 = 及 格及 格 , 平 均 成 绩平 均 成 绩 = rtrim(cast(平均成绩平均成绩 as VARCHAR(20) IF 平均成绩平均成绩 =60 AND 学号学号=学号学号 38 -打开计算累计学分打开计算累计学分_cur，逐门显示该学生所选的课程及成绩并，逐门显示该学生所选的课程及成绩并 根据条件计算该学生获取学分总数根据条件计算该学

31、生获取学分总数 OPEN 计算累计学分计算累计学分_cur FETCH NEXT FROM 计算累计学分计算累计学分_cur INTO 课号课号,课名课名, 成绩成绩,学分学分 SELECT 累计学分累计学分=0 IF FETCH_STATUS 0 PRINT 没有课程被选修没有课程被选修 WHILE fetch_status=0 BEGIN SELECT message=课号课号+ + 课名课名+ +CONVERT(CHAR(8), 成绩成绩)+ + CONVERT(CHAR(8), 学分学分) PRINT message 39 SELECT 累计学分累计学分=累计学分累计学分+学分学分 FETCH NEXT FROM 计算累计学分计算累计学分_cur INTO 课号课号,课名课名,成绩成绩,学分学分 END -显示当前学生所获取的总的学分显示当前学生所获取的总的学分 SELECT message=累计的学分是累计的学分是: + CONVERT(CHAR(8), 累计学分累计学分) PRINT message CLOSE 计算累计学分计算累计学分_cur DEALLOCATE 计算累计学分计算累计学分_cur 40 -如果