存储过程的创建和使用.ppt

第9章存储过程的创建和使用 本章学习目标 了解存储过程的概念了解使用存储过程的优点了解系统存储过程的特点及用途掌握创建存储过程的方法掌握执行存储过程的方法掌握查看和修改存储过程的方法掌握删除存储过程的方法 9 1概述 9 1 1存储过程的概念 在使用Transact SQL语言编程的过程中 可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程 将其保存在数据库中 并由SQLServer服务器通过过程名来调用它们 这些过程就叫做存储过程 存储过程在创建时就被编译和优化 调用一次以后 相关信息就保存在内存中 下次调用时可以直接执行 9 1 1存储过程的概念 存储过程有以下特点 存储过程中可以包含一条或多条Transact SQL语句 存储过程可以接受输入参数并可以返回输出值 在一个存储过程中可以调用另一个存储过程 存储过程可以返回执行情况的状态代码给调用它的程序 9 1 2存储过程的优点 使用存储过程有很多优点 具体如下 实现了模块化编程 一个存储过程可以被多个用户共享和重用 存储过程具有对数据库立即访问的功能 使用存储过程可以加快程序的运行速度 使用存储过程可以减少网络流量 使用存储过程可以提高数据库的安全性 9 1 3存储过程的分类 在SQLServer中的存储过程分为两类 即系统提供的存储过程和用户自定义的存储过程 系统存储过程 由系统自动创建 系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中 在SQLServer2005中 可将GRANT DENY和REVOKE权限应用于系统存储过程 用户自定义存储过程 是指封装了可重用代码的模块或例程 由用户创建 能完成某一特定的功能 可以接受输入参数 返回输出参数 9 1 3存储过程的分类 在SQLServer2005中 用户自定义存储过程有两种类型 Transact SQL存储过程和CLR存储过程 Transact SQL存储过程 是指保存的Transact SQL语句集合 CLR存储过程 是指对Microsoft NETFramework公共语言运行时 CLR 方法的引用 可以接受和返回用户提供的参数 9 2创建存储过程 在SQLServer中 可以使用两种方法创建存储过程 当创建存储过程时 需要确定存储过程的三个组成部分 所有的输入参数以及传给调用者的输出参数 被执行的针对数据库的操作语句 包括调用其他存储过程的语句 返回给调用者的状态值 以指明调用是成功还是失败 9 2 1使用SQLServer管理控制台创建存储过程 在SQLServer管理控制台中 选择指定的服务器和数据库 展开数据库中的 可编程性 文件夹 右击其中的 存储过程 在弹出的快捷菜单中选择 新建存储过程 选项 例9 2 1 创建一个名称为 StuInfo 的存储过程 要求完成以下功能 在T STUDENT表中查询05541班学生的学号 姓名 性别 出生日期和政治面貌五个字段的内容 9 2 2使用Transact SQL语句创建存储过程 可以使用CREATEPROCEDURE命令创建存储过程 考虑下列几个事项 CREATEPROCEDURE语句不能与其他SQL语句在单个批处理中组合使用 必须具有数据库的CREATEPROCEDURE权限 只能在当前数据库中创建存储过程 不要创建任何使用sp 作为前缀的存储过程 9 2 2使用Transact SQL语句创建存储过程 CREATEPROCEDURE的语法形式如下 CREATE PROC PROCEDURE schema name procedure name parameter type schema name data type VARYING default OUT OUTPUT n WITHENCRYPTION AS n BEGIN statements END 9 2 2使用Transact SQL语句创建存储过程 其中 各参数的意义如下 schema name 过程所属架构的名称 procedure name 新存储过程的名称 parameter 过程中的参数 type schema name data type 参数以及所属架构的数据类型 VARYING 指定作为输出参数支持的结果集 仅适用于cursor参数 9 2 2使用Transact SQL语句创建存储过程 default 参数的默认值 OUTPUT 指示参数是输出参数 ENCRYPTION 将CREATEPROCEDURE语句的原始文本加密 要包含在过程中的一个或多个Transact SQL语句 9 2 2使用Transact SQL语句创建存储过程 例9 2 2 创建一个存储过程StuScoreInfo 完成的功能是在表T STUDENT 表T COURSE和表t SCORE中查询以下字段 班级 学号 姓名 性别 课程名称 考试分数 例9 2 2 创建一个带有参数的存储过程Stu Info 该存储过程根据传入的学生编号 在T STUDENT中查询此学生的信息 9 2 2使用Transact SQL语句创建存储过程 例9 2 3 创建一个带有参数的存储过程Stu Age 该存储过程根据传入的学生编号 在T STUDENT中计算此学生的年龄 并根据程序的执行结果返回不同的值 程序执行成功 返回整数0 如果执行出错 则返回错误号 9 3执行存储过程 存储过程创建成功后 保存在数据库中 在SQLServer中可以使用EXECUTE命令来直接执行存储过程 语法形式如下 EXEC UTE return status procedure name procedure name var parameter value variable OUTPUT DEFAULT n 9 3执行存储过程 其中 各选项的含义如下 EXECUTE 执行存储过程的命令关键字 return status 是一个可选的整型变量 保存存储过程的返回状态 procedure name 指定执行的存储过程的名称 procedure name var 是局部定义变量名 代表存储过程名称 parameter 是在创建存储过程时定义的过程参数 9 3执行存储过程 例9 3 1 执行前面创建的StuInfo存储过程 它是一个无参的存储过程 注意 如果省略EXECUTE关键字 则存储过程必须是批处理中的第一条语句 否则会出错 例9 3 2 执行存储过程StuScoreInfo 9 3执行存储过程 例9 3 3 执行存储过程Stu Info 该存储过程有一个输入参数 学号 在执行时需要传入一个学号值 例9 3 4 执行存储过程Stu Age 该存储过程有一个输入参数 学号 另外 还有一个输出参数 Age 存储过程执行完后 有一个返回的状态值 这个值可以从变量 ErrorValue得到 9 4查看和修改存储过程 9 4 1查看存储过程 1 使用SQLServer管理控制台查看用户创建的存储过程在SQLServer管理控制台中 选择指定的服务器和数据库 展开数据库中的 可编程性 文件夹 单击其中的 存储过程 在右边的窗口中就会显示出当前数据库中的所有存储过程 9 4 1查看存储过程 2 使用系统存储过程查看用户创建的存储过程sp help 用于显示存储过程的参数及其数据类型sp help objname name 参数name为要查看的存储过程的名称 9 4 1查看存储过程 sp helptext 用于显示存储过程的源代码sp helptext objname name 参数name为要查看的存储过程的名称 9 4 1查看存储过程 sp depends 用于显示和存储过程相关的数据库对象sp depends objname object 参数object为要查看依赖关系的存储过程的名称 9 4 1查看存储过程 sp stored procedures 用于返回当前数据库中的存储过程列表sp stored procedures sp name name sp owner owner sp qualifier qualifier 其中 sp name name 用于指定返回目录信息的过程名 sp owner owner 用于指定过程所有者的名称 qualifier qualifier 用于指定过程限定符的名称 9 4 1查看存储过程 例9 4 1 使用系统存储过程查看Stu Age存储过程的参数及其数据类型 例9 4 2 使用系统存储过程查看StuScoreInfo存储过程的相关的数据库对象 9 4 2修改存储过程 1 使用SQLServer管理控制台修改存储过程使用SQLServer管理控制台可以很方便地修改存储过程的定义 在SQLServer管理控制台中 展开存储过程 右击要修改的存储过程 从弹出的快捷菜单中选择 修改 选项 则会出现与创建存储过程时类似的窗口 在该窗口中 可以直接修改定义该存储过程的Transact SQL语句 9 4 2修改存储过程 2 使用Transact SQL语句修改存储过程使用ALTERPROCEDURE语句可以更改存储过程 但不会更改权限 也不影响相关的存储过程或触发器 其语法形式如下 ALTER PROC PROCEDURE schema name procedure name parameter type schema name data type VARYING default OUT PUT n WITHENCRYPTION ASsql statement n 9 4 2修改存储过程 修改存储过程时 应该注意以下几点 如果原来的过程定义是使用WITHENCRYPTION创建的 那么只有在ALTERPROCEDURE中也包含这个选项时 这个选项才有效 每次只能修改一个存储过程 用ALTERPROCEDURE更改的存储过程的权限保持不变 9 4 2修改存储过程 例9 4 3 修改前面创建的Stu Info存储过程 使之完成以下功能 根据传入的学号 在表T STUDENT 表T COURSE和表t SCORE中查询此学生的班级 学号 姓名 性别 考试课程名称和考试分数 9 5重命名和删除存储过程 9 5 1重命名存储过程 1 使用SQLServer管理控制台修改存储过程名称通过SQLServer管理控制台可以修改存储过程的名称 方法是 在SQLServer管理控制台中 右击要操作的存储过程名称 从弹出的快捷菜单中选择 重命名 选项 当存储过程名称变成可输入状态时 就可以直接修改该存储过程的名称 9 5 1重命名存储过程 2 使用系统存储过程修改存储过程名称修改存储过程的名称也可以使用系统存储过程sp rename 其语法形式如下 sp rename原存储过程名称 新存储过程名称例9 5 1 使用系统存储过程将Stu Info存储过程的名称修改为Stu Info SCORE 9 5 2删除存储过程 1 使用SQLServer管理控制台删除存储过程在SQLServer管理控制台中 右击要删除的存储过程 从弹出的快捷菜单中选择 删除 选项 会弹出 删除对象 对话框 在该对话框中 单击 确定 按钮 即可完成删除操作 9 5 2删除存储过程 2 使用Transact SQL语句删除存储过程删除存储过程也可以使用Transact SQL语言中的DROP命令 DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除 其语法形式如下 DROP PROC PROCEDURE schema name procedure n 例9 5 2 使用DROP命令删除StuInfo和StuScoreInfo两个存储过程 9 6思考与练习 1 使用存储过程的主要优点有哪些 2 存储过程分哪两类 各有什么特点 3 创建一个无参存储过程 返回05541班的学生信息 9 6思考与练习 4 创建一个带参数的存储过程 输入参数为课程名称 查询有哪些班级的哪些学生参加了这门课程的考试及学生的考试成绩 存储过程返回0 则表示程序执行成功 返回其他整数 表示程序在执行时出现错误 5 举例说明如何查看存储过程信息 6 举例说明如何删除存储过程