1、存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。Transact SQL存储过程可以接受输入参数，以参数形式返回输出值，或者返回成功、失败的状态信息。,第12章 存储过程和用户存储过程设计,1,12.1 存储过程概述 12.2 系统存储过程12.3 创建和执行用户存储过程12.4 带状态参数的存储过程及实例分析12.5 修改和删除存储过程12.6 存储过程设计实例分析,2,12.1 存储过程概述,12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完
2、成，以实现某个任务，这种方法就是存储过程。 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。,3,12.1 存储过程概述,12.1.1 存储过程的概念和分类 用户或应用程序通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 在SQL Server中存储过程分为两类： 系统提供的存储过程 用户自定义的存储过程。,4,12.1 存储过程概述,12.1.2 存储过程的优点（1）存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储
3、过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。,5,（2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。 （3）存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL语句，从而大大增加了网
4、络流量，降低网络负载。,6,（4）存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。 （5）自动完成需要预先执行的任务.存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务,7,12.2 系统存储过程,12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。系统过程以“sp_开头,在Master数据库中
5、创建并保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。,8,12.2.2 一些常用的系统存储过程,9,10,如果没有指定数据库名，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库。 【例12-1】返回pubs数据库的信息 exec sp_helpdb pubs 【例12-2】返回有关所有数据库的信息 exec sp_helpdb,11,12.3.1 创建用户存储过程用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQL Server中，可以使用以下方
6、法创建存储过程： 利用SQL Server管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。,12.3 创建和执行用户存储过程,12,1使用SQL Server管理器创建存储过程 在SQL Server管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”
7、选项，从弹出的快捷菜单中选择“新建存储过程”选项，出现创建存储过程对话框。,13,2使用CREATE PROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几个事项： 在一个批处理中，Create Procedure 语句不能与其它SQL语句合并在一起； 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建当前数据库的存储过程。 一个存储过程的最大尺寸为128M。,14,语法格式： CREATE PROC EDURE 存储过程名 ；版本号 ( 参数名 数据类型 VARYING =default
8、 OUTPUT ，)WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS SQL语句,15,【例12-3】创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。 USE 教学管理 GO CREATE PROCEDURE P_学生部分信息 AS SELECT 学号,姓名,专业,所在院系,籍贯 FROM 学生表,16,USE 教学管理IF EXISTS(SELECT* FROM sysobjects WHERE name=SC_infor AND type=p) BEGIN
9、 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=smajor AND cname=cname AND S.sno=E.sno AND E.ono=O.ono AND O.cno=C.cnoGO,【例12-4】创建带参数的存储过程，实现对指定
10、的某一专业 的学生某门课程的选课信息和成绩。,17,12.3.2 执行用户存储过程执行已创建的存储过程使用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）按参数位置传递值 EXEC P_学生选课信息 计算机,数据结构,或者：,DECLARE 专业
11、CHAR(20), 课名 CHAR(20)SET 专业=计算机SET 课名=数据结构EXEC P_学生选课信息 专业,课名,或者：,DECLARE 专业 CHAR(20)SET 专业=计算机EXEC P_学生选课信息 专业,数据结构,19,（2）按参数名传递值EXEC P_学生选课信息 专业 =计算机, 课名=数据结构按参数名传递值可以改变参数的顺序：EXEC P_学生选课信息 课名=数据结构, 专业=计算机,20,（3）也可以两种方法混合使用，一旦使用了 name = value 形式之后，所有后续的参数就必须以 name = value 的形式传递。 比如： EXEC P_学生选课信息 计
12、算机, 课名=数据结构,21,【例12-7】使用OUTPUT参数的存储过程及其执行,首先创建存储过程USE 教学管理GOCREATE PROCEDURE P_成绩检索和平均 (学号 CHAR(7), 平均成绩 FLOAT OUTPUT)ASSELECT S.学号,姓名,课号,成绩FROM 学生表 S, 开课表 O, 选课表 EWHERE S.学号=学号 AND E.学号=S.学号 AND E.开课号=O.开课号SELECT 平均成绩 =AVG(成绩)FROM 学生表 S, 开课表 O, 选课表 EWHERE S.学号=学号 AND E.学号=S.学号 AND E.开课号=O.开课号RETURN
13、GO,22,然后在查询分析器中调用 Scorequery 存储过程DECLARE 学号 CHAR(7),平均成绩 FLOATSET 学号=S060102EXEC P_成绩检索和平均 学号, 平均成绩 OUTPUTIF 平均成绩 =90 SELECT 该学生的成绩=优秀,平均成绩=rtrim(cast(平均成绩 as VARCHAR(20)IF 平均成绩 =80 AND 平均成绩 =70 AND 平均成绩 =60 AND 平均成绩 70 SELECT 该学生的成绩=及格,平均成绩= rtrim(cast(平均成绩 as VARCHAR(20)IF 平均成绩 =60 AND 学号=学号,38,-打
14、开计算累计学分_cur，逐门显示该学生所选的课程及成绩并根据条件计算该学生获取学分总数OPEN 计算累计学分_curFETCH NEXT FROM 计算累计学分_cur INTO 课号,课名,成绩,学分SELECT 累计学分=0IF FETCH_STATUS 0 PRINT 没有课程被选修WHILE fetch_status=0BEGINSELECT message=课号+ + 课名+ +CONVERT(CHAR(8), 成绩)+ + CONVERT(CHAR(8), 学分)PRINT message,39,SELECT 累计学分=累计学分+学分FETCH NEXT FROM 计算累计学分_c
15、ur INTO 课号,课名,成绩,学分END-显示当前学生所获取的总的学分SELECT message=累计的学分是: + CONVERT(CHAR(8), 累计学分)PRINT messageCLOSE 计算累计学分_curDEALLOCATE 计算累计学分_cur,40,-如果计算出的新的学分总数和原学分数不一样的话，进行游标定位修改IF 累计学分累计学分BEGINUPDATE 学生表SET 累计学分=累计学分WHERE CURRENT OF P_检查学分登记_curENDFETCH NEXT FROM P_检查学分登记_cur INTO 学号, 姓名,累计学分END-关闭游标CLOSE
16、P_检查学分登记_cur-释放游标DEALLOCATE P_检查学分登记_curEND,41,要检查并修改信息学院学生的学分获取情况，可以如下调用执行存储过程：DECLARE return_status INT, 所在院系 CHAR(20)SET 所在院系=信电学院EXEC return_status = P_检查学分登记 所在院系IF (return_status =1) PRINT 没有输入学院名称IF (return_status =2) PRINT 所输入学院名称不存在IF (return_status =0) PRINT 所在院系+学生学分检查修改成功GO,42,小 结,存储过程是一组实现特定功能的Transact SQL 语句集，经编译后存储在数据库中，它与其他程序设计语言中的过程类似，也可以接受输入参数，以参数形式返回输出值，或者返回成功、失败的状态信息。每次存储过程运行时，无须重新编译（除非强制要求）。本章主要讨论了存储过程的创建、修改、执行和管理，并列举了大量的示例，最后对两个应用实例进行分析。,43,Thank you very much!,谢谢您的光临！,下一章,44,
