sql server 2014 第7章 存储过程与触发器

10
7.2.2 修改存储过程
• 注意
– 如果修改具有选项的存储过程，必须在Alter procedure 语句中包括选项以保留该选项的功能。 – Alter procedure 只能修改一个单一的过程，如果调用 其他存储过程，嵌套的存储过程不受影响。 – 默认状态下，允许语句的执行者是存储过程的初始创 建者、sysadmin服务器角色成员、db_owner和 db_ddladmin固定的数据库角色成员，用户不能授权执 行alter procedure语句
• 存储过程：存储过程中包括T-SQL语句，可以接受和返回用户提供的 参数。 • CLR存储过程：是对MICROSOFT .NET FRAMEWORK公共语言运 行时（CLR）方法的引用，可以接受和返回用户提供的参数。
4
7.2 创建存储过程
• 存储过程的定义可以通过CREATE PROCEDURE语句去创建一个的存储过程 或通过对象资源管理器创建。
Database theroy and design 数据库原理与设计 第7章 存储过程与触发器
1
第当掌握如下知识： (1)存储过程与触发器的定义 (2)存储过程的定义及使用 (3)触发器的定义及使用
2
7.1 存储过程概述
• 1. 存储过程优点 • 存储过程（Stored Procedure）是一组为了完成特定功能 的SQL语句集，经编译后存储在数据库中。存储过程可包 含程序流、逻辑以及对数据库的查询。它们可以接受参数 、输出参数、返回单个或多个结果集以及返回值。 • 存储过程的优点
8
7.2.2 修改存储过程
• 通过ALTER PROCEDURE语句，可以对已经创建的存储 过程进行调整。 • 语法格式为： • ALTER PROCEDURE procedure_name[;number] • [{@parameter data_type} • [varying]=[default][output]] • [,…….n] • [with • {recompile|encryption|recompile,encryption}] • [for replication] • As • Sql_statement [……n]
14
7.2.5 重命名存储过程
• 【例7-8】 使用系统存储过程sp_rename将上例改 名的用户存储过程studproc1再更名为studproc。 • 解：对应的程序如下： • USE school • GO • EXEC sp_rename studproc,studproc1 • 在更名时会出现警告消息“警告:更改对象名的 任一部分都可能会破坏脚本和存储过程”。
16
7.3 使用存储过程
• • • • • • • • • • • • • • • • • 下面根据不同的存储过程的创建方法分别创建不同类型选项的存储过程。 【例7-11】创建并执行简单存储过程student_info。 Create procedure student_info As Select 学号,姓名,出生日期,班级 From student Where 性别=‟女’ Exec student_info 【例7-12】创建不应被缓存的存储过程student_info1，并执行。 Create procedure student_info1 With recompile As Select 学号,姓名,出生日期,班级 From student Where 性别=‟女’ Exec student_info1 注意：如果制定了for replication 则不能使用此选项。
11
7.2.3 删除存储过程
• 使用DROP语句去删除存储过程。 • 语法格式为： • Drop procedure {[schema_name.]procedure[,……n]}
• 【例7-3】删除存储过程student_info • If exists(select name from sysobjects where name=‟student_info‟) • Drop procedure student_info • 说明：在删除存储过程时先检查存储过程是否存 在。
7
7.2.1 T-SQL命令创建存 储过程
• 注意
– 用户定义的存储过程只能在当前数据库中创建 – 成功执行create procedure后，仅仅是保存的了存储过 程，其中名称存储在sysobjects系统表中，语句文本存 储在syscomments中。 – 存储过程可以自动执行，但这些过程必须由管理员在 master中创建，并在sysadmin固定服务器角色下作为 后台过程执行。 – Create procedure的权限默认授予sysadmin固定服务 器角色成员、db_owner和db_ddladmin固定服务器角 色成员，sysadmin和db_owner具有转授权限。 – 存储过程定义只能出现在单个批处理中。
• 2. 存储过程的类型 • 存储过程分为三类：系统存储过程、扩展存储过程和用户 存储过程。
– 系统存储过程：是由SQL SERVER提供的存储过程，用户可以当 做命令执行。 – 扩展存储过程：在SQL SERVER环境以外，使用编程语言（如 C++）创建外部的例程形成的动态链接库（DLL）。使用时先要将 DLL加载到SQL SERVER系统中，按照使用系统存储过程的方法 执行。 – 用户存储过程：由用户自己编写的存储过程，用户既可以使用TSQL编写也可使用CLR编写。
13
• •
• •
7.2.5 重命名存储过程
• 重命名存储过程也有两种方法：使用SQL Server管理控制器或使用系 统存储过程。 • （1）使用SQL Server管理控制器重命名存储过程 • 【例7-7】 使用SQL Server管理控制器将存储过程studproc重命令为 studproc1。 • 解：其操作步骤如下： • 启动SQL Server管理控制器。在“对象资源管理器”中展开“LCBPC”服务器节点。 • 展开“数据库”|“school”|“可编程性”|“存储过程”|“dbo.studproc”节 点，单击鼠标右键，在出现的快捷菜单中选择“重命名”命令。 • 此时存储过程名称“studproc”变成可编辑的，可以直接修改该存储过 程的名称为studproc1。 • （2）使用系统存储过程重命名用户存储过程 • 重命名存储过程的系统存储过程为sp_rename，其语法格式如下： • sp_rename 原存储过程名称,新存储过程名称
18
7.3 使用存储过程
• • • • • • • • • 【例7-14】创建临时存储过程student_info3，并执行。 Create procedure #student_info3 With recompile As Select 学号,姓名,出生日期,班级 From student Where 性别=‟女’ Exec #student_info3 注意：‘#‟表示局部临时存储过程，‘##‟表示全局临时存 储过程。当sql server关闭时存储过程自动删除。
12
7.2.4 查看存储过程
• • • • • • • 1.通过sp_helptext查看存储过程的文本，格式如下： Sp_helptext procedure_基本信息 【例7-4】查看student_info的信息 Sp_helptext student_info 显示create procedure student_info 的创建文本。 2.通过sp_help系统查看存储过程的基本信息。 【例7-5】查看student_info的基本信息，包括名称，创建 者、类型、时间等信息 EXEC sp_help student_info 3.通过使用sys.sql_dependencies对象目录视图、 sp_depends系统查看存储过程。 【例7-6】查看student_infoxinxi Exec sp_depends student_info
7.3 使用存储过程
• • • • • • • • • • • 在数据库开发过程中，存储过程的应用非常频繁。 通过execute或EXEC语句执行一个存储过程。格式为： Exec|execute procedure_name [parameter,……n] 执行方式分为直接执行和间接执行两种。 【例7-9】直接执行student_info存储过程 Execute student_info „王林’,‟计算机基础’ 【例7-10】间接执行student_info存储过程 Declare @name1 char(8),@cname1 char(16) Set @name1=‟王林’ Set @cname1=‟计算机基础’ Exec student_info @name1,@cname
17
7.3 使用存储过程
• • • • • • • • • 【例7-13】创建加密的存储过程student_info2并执行。 Create procedure student_info2 With encryption As Select 学号,姓名,出生日期,班级 From student Where 性别=‟女’ Exec student_info2 注意：加密后的存储过程，通过SP_HELPTEXT查看显示 文本已加密（不能查看）。
– 存储过程在服务器端运行，执行速度快； – 存储过程每执行一次后，驻留高速缓存，以后的每次执行只要调 用高速缓存的代码执行，提高系统性能； – 使用存储过程可以完成所有的数据库操作，并通过编程方式控制 访问权限，确保数据库的安全； – 自动完成需要预先执行的任务，方便用户。
3
7.1 存储过程概述
9
7.2.2 修改存储过程
• • • • • • • • • 【例7-2】修改存储过程student_info ，将参数改为学号 Use 教务管理 Go Alter procedure student_info @number char(6),@cname char(16) As Select student.学号,课程名,成绩 Form student,course,score Where student.学号=score.学号and student.学号 =@number and score. 课号=Course.课号 And 课名 =@cnmae Go