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

存储过程 StoredProcedure 是一组为了完成特定功能的SQL语句集 经编译后存储在数据库中 TransactSQL存储过程可以接受输入参数 以参数形式返回输出值 或者返回成功 失败的状态信息 第12章存储过程和用户存储过程设计 1 12 1存储过程概述12 2系统存储过程12 3创建和执行用户存储过程12 4带状态参数的存储过程及实例分析12 5修改和删除存储过程12 6存储过程设计实例分析 2 12 1存储过程概述 12 1 1存储过程的概念和分类SQLServer提供了一种方法 它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成 以实现某个任务 这种方法就是存储过程 存储过程 StoredProcedure 是一组为了完成特定功能的SQL语句集 经编译后存储在数据库中 3 12 1存储过程概述 12 1 1存储过程的概念和分类用户或应用程序通过指定存储过程的名字并给出参数 如果该存储过程带有参数 来执行它 而且允许用户声明变量 有条件执行以及其它强大的编程功能 在SQLServer中存储过程分为两类 系统提供的存储过程用户自定义的存储过程 4 12 1存储过程概述 12 1 2存储过程的优点 1 存储过程允许标准组件式编程存储过程在被创建以后可以在程序中被多次调用 而不必重新编写该存储过程的SQL语句 而且数据库专业人员可随时对存储过程进行修改 但对应用程序源代码毫无影响 因为应用程序源代码只包含存储过程的调用语句 从而极大地提高了程序的可移植性 5 2 存储过程能够实现较快的执行速度如果某一操作包含大量的Transaction SQL代码或分别被多次执行 那么存储过程要比批处理的执行速度快很多 3 存储过程能够减少网络流量对于同一个针对数据数据库对象的操作 如查询 修改 如果这一操作所涉及到的Transaction SQL语句被组织成一存储过程 那么当在客户计算机上调用该存储过程时 网络中传送的只是该调用语句 否则将是多条SQL语句 从而大大增加了网络流量 降低网络负载 6 4 存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制 从而能够实现对相应的数据访问权限的限制 避免非授权用户对数据的访问 保证数据的安全 5 自动完成需要预先执行的任务 存储过程可以在系统启动时自动执行 而不必在系统启动后再进行手工操作 大大方便了用户的使用 可以自动完成一些需要预先执行的任务 7 12 2系统存储过程 12 2 1系统存储过程分类系统存储过程就是系统创建的存储过程 目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务 系统过程以 sp 开头 在Master数据库中创建并保存在该数据库中 为数据库管理者所有 一些系统过程只能由系统管理员使用 而有些系统过程通过授权可以被其它用户所使用 8 12 2 2一些常用的系统存储过程 9 10 如果没有指定数据库名 则sp helpdb报告master dbo sysdatabases中的所有数据库 例12 1 返回pubs数据库的信息execsp helpdbpubs 例12 2 返回有关所有数据库的信息execsp helpdb 11 12 3 1创建用户存储过程用户自定义存储过程是由用户创建并能完成某一特定功能 如查询用户所需数据信息 的存储过程 在SQLServer中 可以使用以下方法创建存储过程 利用SQLServer管理器创建存储过程 使用Transact SQL语句中的CREATEPROCEDURE命令创建存储过程 创建存储过程时 需要确定存储过程的三个组成部分 所有的输入参数以及传给调用者的输出参数 被执行的针对数据库的操作语句 包括调用其它存储过程的语句 返回给调用者的状态值 以指明调用是成功还是失败 12 3创建和执行用户存储过程 12 1 使用SQLServer管理器创建存储过程 在SQLServer管理器中 选择指定的服务器和数据库 用右键单击要创建存储过程的数据库 在弹出的快捷菜单中选择 新建 选项 再选择下一级菜单中的 存储过程 选项 从弹出的快捷菜单中选择 新建存储过程 选项 出现创建存储过程对话框 13 2 使用CREATEPROCEDURE命令创建存储过程创建存储过程前 应该考虑下列几个事项 在一个批处理中 CreateProcedure语句不能与其它SQL语句合并在一起 创建存储过程的权限默认属于数据库所有者 该所有者可将此权限授予其他用户 存储过程是数据库对象 其名称必须遵守标识符规则 只能在当前数据库中创建当前数据库的存储过程 一个存储过程的最大尺寸为128M 14 语法格式 CREATEPROC EDURE 存储过程名 版本号 参数名数据类型 VARYING default OUTPUT WITHRECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASSQL语句 15 例12 3 创建一个简单的存储过程 返回所有学生的基本信息 包括学生的学号 姓名 所学专业 所在二级学院 来自的城市 USE教学管理GOCREATEPROCEDUREP 学生部分信息ASSELECT学号 姓名 专业 所在院系 籍贯FROM学生表 16 USE教学管理IFEXISTS SELECT FROMsysobjectsWHEREname SC infor ANDtype p BEGINDROPPROCEDURESC inforENDGOCREATEPROCEDURESC infor smajorCHAR 20 cnameCHAR 20 ASSELECTS sno sname smajor sdepa O cno cname gradeFROMstudentS enrollmentE offeringO courseCwheresmajor smajorANDcname cnameANDS sno E snoANDE ono O onoANDO cno C cnoGO 例12 4 创建带参数的存储过程 实现对指定的某一专业的学生某门课程的选课信息和成绩 17 12 3 2执行用户存储过程执行已创建的存储过程使用EXECUTE命令 语法格式 EXEC UTE return status 版本号 procedure name var 参数 value variable OUTPUT DEFAULT n WITHRECOMPILE 18 例12 5 对存储过程P 学生部分信息的执行 EXECP 学生部分信息 例12 6 带输入参数的存储过程P 学生选课信息的执行 1 按参数位置传递值EXECP 学生选课信息 计算机 数据结构 或者 DECLARE 专业CHAR 20 课名CHAR 20 SET 专业 计算机 SET 课名 数据结构 EXECP 学生选课信息 专业 课名 或者 DECLARE 专业CHAR 20 SET 专业 计算机 EXECP 学生选课信息 专业 数据结构 19 2 按参数名传递值EXECP 学生选课信息 专业 计算机 课名 数据结构 按参数名传递值可以改变参数的顺序 EXECP 学生选课信息 课名 数据结构 专业 计算机 20 3 也可以两种方法混合使用 一旦使用了 name value 形式之后 所有后续的参数就必须以 name value 的形式传递 比如 EXECP 学生选课信息 计算机 课名 数据结构 21 例12 7 使用OUTPUT参数的存储过程及其执行 首先创建存储过程USE教学管理GOCREATEPROCEDUREP 成绩检索和平均 学号CHAR 7 平均成绩FLOATOUTPUT ASSELECTS 学号 姓名 课号 成绩FROM学生表S 开课表O 选课表EWHERES 学号 学号ANDE 学号 S 学号ANDE 开课号 O 开课号SELECT 平均成绩 AVG 成绩 FROM学生表S 开课表O 选课表EWHERES 学号 学号ANDE 学号 S 学号ANDE 开课号 O 开课号RETURNGO 22 然后在查询分析器中调用Scorequery存储过程DECLARE 学号CHAR 7 平均成绩FLOATSET 学号 S060102 EXECP 成绩检索和平均 学号 平均成绩OUTPUTIF 平均成绩 90SELECT 该学生的成绩 优秀 平均成绩 rtrim cast 平均成绩asVARCHAR 20 IF 平均成绩 80AND 平均成绩 70AND 平均成绩 60AND 平均成绩 70SELECT 该学生的成绩 及格 平均成绩 rtrim cast 平均成绩asVARCHAR 20 IF 平均成绩 60SELECT 该学生的成绩 不及格 平均成绩 rtrim cast 平均成绩asVARCHAR 20 23 学号姓名课号成绩 1S060102张小芬C0100193 02S060102张小芬C01003NULL3S060102张小芬C02001NULL该学生成绩平均成绩 优秀93 24 12 4带状态参数的存储过程及实例分析 12 4 1存储过程执行状态值的返回 1 系统自动返回无论什么时候执行存储过程 总要返回一个结果码 用以指示存储过程的执行状态 如果存储过程执行成功 返回的结果码是0 如果存储过程执行失败 返回的结果码目前是一个0到 14的负数 按以下语法只要执行存储过程并用 return status接收状态值即可 EXECUTE return status procedure name 25 2 用RETURN语句RETURN语句的功能是 从查询或过程中无条件退出 RETURN即时且完全 可在任何时候用于从过程 批处理或语句块中退出 不执行位于RETURN之后的语句 语法格式 RETURN integer expression 26 参数说明 integer expression是返回的整型值 存储过程可以给调用过程或应用程序返回整型值 返回类型可以选择是否返回int 除非特别指明 所有系统存储过程返回0值表示成功 返回非零值则表示失败 注释当用于存储过程时 RETURN不能返回空值 如果过程试图返回空值 例如 使用RETURN status且 status是NULL 将生成警告信息并返回0值 在执行当前过程的批处理或过程内 可以在后续Transact SQL语句中接收用RETURN语句返回的状态值 但必须以下列格式执行当前存储过程 EXECUTE return status procedure name 27 例12 8 从过程返回 状态值有系统返回过程执行成功的默认值 CREATEPROCEDUREP 查询教师开课 姓名CHAR 10 NULL ASIF 姓名ISNULLBEGINPRINT 必须指定教师姓名 ENDELSEBEGINSELECTT 工号 姓名 所在院系 职称 O 开课号 O 课号 课名FROM教师表T 开课表O 课程表CWHERET 工号 O 工号ANDO 课号 C 课号ANDT 姓名 姓名END 28 例12 9 用RETURN语句返回自己定义的状态代码以下存储过程检查指定学生的平均成绩 如果执行存储过程时没有输入学生学号 则返回状态码1 如果所查询的学生不存在 则返回状态码2 CREATEPROCEDUREP 计算平均成绩 学号CHAR 7 平均成绩TINYINTOUTPUT ASIF 学号ISNULLRETURN1IFNOTEXISTS SELECT FROM选课表WHERE学号 学号 RETURN2SELECT 平均成绩 avg 成绩 FROM选课表WHERE学号 学号 29 12 5修改和删除存储过程 12 5 1修改存储过程修改以前用CREATEPROCEDURE命令创建的存储过程 并且不改变权限的授予情况以及不影响任何其它的独立的存储过程或触发器 常使用ALTERPROCEDURE命令 语法格式 ALTERPROC EDURE 存储过程名 版本号 参数名数据类型 VARYING default OUTPUT WITHRECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASSQL语句 30 其中各参数和保留字的具体含义请参看CREATEPROCEDURE命令 参数说明 如果原来的过程定义是用WITHENCRYPTION或WITHRECOMPILE创建的 那么只有在ALTERPROCEDURE中也包含这些选项时 这些选项才有效 权限 ALTERPROCEDURE权限默认授予sysadmin固定服务器角色成员 db owner和db ddladmin固定数据库角色成员和过程的所有者且不可转让 用ALTERPROCEDURE更改的过程的权限和启动属性保持不变 31 例12 10 用ALTERPROCEDURE修改例12 9存储过程 增加WITHENCRYPTION选项 ALTERPROCEDUREP 计算平均成绩 学号CHAR 7 平均成绩TINYINTOUTPUT WITHENCRYPTIONASIF 学号ISNULLRETURN1IFNOTEXISTS SELECT FROM选课表WHERE学号 学号 RETURN2SELECT 平均成绩 avg 成绩 FROM选课表WHERE学号 学号GO 32 12 5 2删除存储过程删除存储过程可以使用DROP命令 DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除 语法格式 dropprocedure 存储过程名 n 当然 利用企业管理器也可以很方便地删除存储过程 例如 DROPPROCEDUREP 学生部分信息GO 33 12 6存储过程设计实例分析 回顾第11章提出的应用实例 在学生选课管理中 为了维护数据的一致性 必须保证SGPA的数量应该等于学生所选的所有成绩已经及格的课程的学分总数 需逐个检查并修改信息学院每个学生的学分获取情况 考虑到每门课程的学分获得条件以及数据库数据存放特点 学生学分获取情况的修改与检查是一件复杂的工作 不能由简单的查询完成 我们通过游标来逐个检查所有学生学分获取情况及定位修改 但是游标在定义它的批处理结束便离开作用域 故我们将设计一个使用游标的存储过程 完成该项工作 该存储过程 在需要时可以进行多次调用执行 34 例12 11 创建存储过程 在过程中使用嵌套游标 逐个检查并修改指定学院的每个学生选修的每门课程的成绩及学分获取情况 显示输出 并维护数据的一致性 解决思路 我们通过定义存储过程来实现 该过程中定义两个个输入变量 学号和课程计划号 用来传递元组的属性值 在完成插入操作前 进行检查是否满足实体完整性和参照完整性 确保不重复选修某门课程 并返回状态值 35 USE教学管理GOIFEXISTS SELECTnameFROMsysobjectsWHEREname P 检查学分登记 ANDtype P DROPPROCEDUREP 检查学分登记GO 创建存储过程CREATEPROCEDUREP 检查学分登记 所在院系CHAR 20 ASBEGINIF 所在院系ISNULLRETURN1IFNOTEXISTS SELECT FROM学生表WHERE所在院系 所在院系 RETURN2 36 DECLARE 学号CHAR 7 姓名CHAR 10 累计学分INTDECLARE 课号CHAR 6 课名CHAR 20 成绩FLOAT 学分INTDECLARE messageCHAR 80 创建包含每个学生学号 姓名和所获取学分信息的游标P 检查学分登记 curDECLAREP 检查学分登记 curCURSORFORSELECT学号 姓名 累计学分FROM学生表WHERE所在院系 所在院系FORUPDATEOF累计学分 37 打开游标 提取第一个学生的数据OPENP 检查学分登记 curFETCHNEXTFROMP 检查学分登记 curINTO 学号 姓名 累计学分WHILE fetch status 0BEGIN 显示学生学号 姓名SELECT message 学号 姓名PRINT message 定义当前学生所选课程及成绩的游标计算累计学分 curDECLARE计算累计学分 curCURSORFORSELECTO 课号 课名 成绩 学分FROM选课表E 课程表C 开课表OWHEREE 开课号 O 开课号ANDO 课号 C 课号AND成绩 60AND学号 学号 38 打开计算累计学分 cur 逐门显示该学生所选的课程及成绩并根据条件计算该学生获取学分总数OPEN计算累计学分 curFETCHNEXTFROM计算累计学分 curINTO 课号 课名 成绩 学分SELECT 累计学分 0IF FETCH STATUS0PRINT 没有课程被选修 WHILE fetch status 0BEGINSELECT message 课号 课名 CONVERT CHAR 8 成绩 CONVERT CHAR 8 学分 PRINT message 39 SELECT 累计学分