为了正常的体验网站,请在浏览器设置里面开启Javascript功能!

Oracle存储过程和触发器

2012-01-12 37页 pdf 1MB 27阅读

用户头像

is_743517

暂无简介

举报
Oracle存储过程和触发器 主要内容 第第第第第第第第1111111122222222章章章章章章章章 存存储储过过程程和和触触发发器器 返回总目录返回总目录返回总目录返回总目录 ↵ 存储过程概述 ↵ 创建存储过程 ↵ 触发器的概念和作用 ↵ 创建触发器 ↵ 触发器的应用 ↵ 触发器的高级应用 ↵ 查看 修改 删除触发器 š 在大型数据库系统中 存储过程和触发器具有很重要的作用 无 论是存储过程还是触发器 都是 SQL 语句和流程控制语句的集合 就 本质而言 触发器也是一种存储过程 存储过程在运算时生成执行方 式 所以 以...
Oracle存储过程和触发器
主要内容 第第第第第第第第1111111122222222章章章章章章章章 存存储储过过程程和和触触发发器器 返回总目录返回总目录返回总目录返回总目录 ↵ 存储过程概述 ↵ 创建存储过程 ↵ 触发器的概念和作用 ↵ 创建触发器 ↵ 触发器的应用 ↵ 触发器的高级应用 ↵ 查看 修改 删除触发器 š 在大型数据库系统中 存储过程和触发器具有很重要的作用 无 论是存储过程还是触发器 都是 SQL 语句和流程控制语句的集合 就 本质而言 触发器也是一种存储过程 存储过程在运算时生成执行方 式 所以 以后对其再运行时其执行速度很快 SQL Server 2000 不仅 提供了用户自定义存储过程的功能 而且也提供了许多可作为工具使 用的系统存储过程 1122..11 存储过程概述 12.1.1 存储过程的概念 存储过程 Stored Procedure 是一组为了完成特定功能的 SQL 语句集 经编译后存 储在数据库中 用户通过指定存储过程的名字并给出参数 如果该存储过程带有参数 来 执行它 在 SQL Server 的系列版本中存储过程分为两类 系统提供的存储过程和用户自定义 存储过程 系统过程主要存储在 master 数据库中并以 sp_为前缀 并且系统存储过程主 要是从系统表中获取信息 从而为系统管理员管理 SQL Server 提供支持 通过系统存储 过程 MS SQL Server 中的许多管理性或信息性的活动 如了解数据库对象 数据库信息 都可以被顺利有效地完成 尽管这些系统存储过程被放在 master 数据库中 但是仍可以 在其它数据库中对其进行调用 在调用时不必在存储过程名前加上数据库名 而且当创建 一个新数据库时 一些系统存储过程会在新数据库中被自动创建 用户自定义存储过程是 由用户创建并能完成某一特定功能 如查询用户所需数据信息 的存储过程 在本章中所 涉及到的存储过程主要是指用户自定义存储过程 12.1.2 存储过程的优点 当利用 MS SQL Server 创建一个应用程序时 Transaction-SQL 是一种主要的编程语 言 若运用 Transaction-SQL 来进行编程 有两种方法 其一是 在本地存储 Transaction- SQL 程序 并创建应用程序向 SQL Server 发送命令来对结果进行处理 其二是 可以把 部分用 Transaction-SQL 编写的程序作为存储过程存储在 SQL Server 中 并创建应用程序 来调用存储过程 对数据结果进行处理 存储过程能够通过接收参数向调用者返回结果集 结果集的格式由调用者确定 返回状态值给调用者 指明调用是成功或是失败 包括针对 数据库的操作语句 并且可以在一个存储过程中调用另一存储过程 我们通常更偏爱于使用第二种方法 即在 SQL Server 中使用存储过程而不是在客户 计算机上调用 Transaction-SQL 编写的一段程序 原因在于存储过程具有以下优点 1 存储过程允许标准组件式编程 存储过程在被创建以后 可以在程序中被多次调用 而不必重新编写该存储过程的 SQL 语句 而且数据库专业人员可随时对存储过程进行修改 但对应用程序源代码毫无影响 因 为应用程序源代码只包含存储过程的调用语句 从而极大地提高了程序的可移植性 2 存储过程能够实现较快的执行速度 如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行 那么存储过程要 比批处理的执行速度快很多 因为存储过程是预编译的 在首次运行一个存储过程时 查 询优化器对其进行 优化 并给出最终被存在系统表中的执行 而批处理的 Tran- saction-SQL 语句在每次运行时都要进行编译和优化 因此速度相对要慢一些 3 存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作 如查询 修改 如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程 那么当在客户计算机上调用该存储过程时 网络中传送的只是该调用语句 否则将是多条 SQL 语句 从而大大增加了网络流量 降 低网络负载 4 存储过程可被作为一种安全机制来充分利用 系统管理员通过对执行某一存储过程的权限进行限制 从而能够实现对相应的数据访 问权限的限制 避免非授权用户对数据的访问 保证数据的安全 我们将在 14 章 SQL Server 的用户和安全性管理 中对存储过程的这一应用作更为清晰的介绍������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ 存储过程虽然既有参数又有返回值 但是它与函数不同 存储过程的返回值只是指明执行������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������是否成功 并且它不能像函数那样被直接调用 也就是在调用存储过程时 在存储过程名������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������字前一定要有 EXEC保留字 如何执行存储过程见本章下一节 1122..22 创建存储过程 在 MS SQL Server 2000 中 创建一个存储过程有两种方法 一种是使用 Transaction-SQL 命令 Create Procedure 另一种是使用图形化管理工具 Enterprise Manager 用 Transaction- SQL 创建存储过程是一种较为快速的方法 但对于初学者 使用 Enterprise Manager 更易 理解 更为简单 当创建存储过程时 需要确定存储过程的三个组成部分 所有的输入参数以及传给调用者的输出参数 被执行的针对数据库的操作语句 包括调用其它存储过程的语句 返回给调用者的状态值 以指明调用是成功还是失败 12.2.1 使用 Enterprise Manager创建存储过程 按照下述步骤用 Enterprise Manager 创建一个存储过程 1 启动 Enterprise Manager 登录到要使用的服务器 2 选择要创建存储过程的数据库 在左窗格中单击 Stored Procedure文件夹 此 时在右窗格中显示该数据库的所有存储过程 如图 12-1所示 3 右击 Stored Procedure文件夹 在弹出菜单中选择 New Stored Procedure, 此 时打开创建存储过程对话框 如图 12-2所示 图12-1 EM中显示的存储过程的详细信息 图12-2 创建存储过程对话框 4 输入存储过程正文 5 单击 Check Syntax 检查语法是否正确 6 单击 OK 保存 7 在右窗格中 右击该存储过程 在弹出菜单中选择 All task, 选择 Manage Permissions 设置权限 如图 12-3所示 图12-3 EM中显示的设置存储过程权限的对话框 12.2.2 用 CREATE PROCEDURE命令创建存储过程 通过运用 Create Procedure 命令能够创建存储过程 在创建存储过程之前 应该考虑 到以下几个方面 在一个批处理中 Create Procedure语句不能与其它 SQL语句合并在一起 数据库所有者具有默认的创建存储过程的权限 它可把该权限传递给其它的用 户 存储过程作为数据库对象其命名必须符合命名规则 只能在当前数据库中创建属于当前数据库的存储过程 用 Create Procedure 创建存储过程的语法规则如下 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 各参数的含义如下 procedure_name 是要创建的存储过程的名字 它后面跟一个可选项 number 它是一个整数 用来区 别一组同名的存储过程 存储过程的命名必须符合命名规则 在一个数据库中或对其所有 者而言 存储过程的名字必须惟一 @parameter 是存储过程的参数 在 Create Procedure 语句中 可以声明一个或多个参数 当调用 该存储过程时 用户必须给出所有的参数值 除非定义了参数的缺省值 若参数的形式以 @parameter=value 出现 则参数的次序可以不同 否则用户给出的参数值必须与参数列表 中参数的顺序保持一致 若某一参数以@parameter=value 形式给出 那么其它参数也必须 以该形式给出 一个存储过程至多有 1024 个参数 Data_type 是参数的数据类型 在存储过程中 所有的数据类型包括 text 和 image 都可被用作参 数 但是 游标 cursor 数据类型只能被用作 OUTPUT 参数 当定义游标数据类型时 也 必须对 VARING 和 OUTPUT 关键字进行定义 对可能是游标型数据类型的 OUTPUT 参 数而言 参数的最大数目没有限制 VARYING 指定由 OUTPUT 参数支持的结果集 仅应用于游标型参数 Default 是指参数的缺省值 如果定义了缺省值 那么即使不给出参数值 则该存储过程仍能 被调用 缺省值必须是常数 或者是空值 OUTPUT 表明该参数是一个返回参数 用 OUTPUT 参数可以向调用者返回信息 Text 类型参 数不能用作 OUTPUT 参数 RECOMPILE 指明 SQL Server 并不保存该存储过程的执行计划 该存储过程每执行一次都又要重 新编译 ENCRYPTION 表明 SQL Server 加密了 syscomments 表 该表的 text 字段是包含有 Create procedure 语句的存储过程文本 使用该关键字无法通过查看 syscomments 表来查看存储过程内容 FOR REPLICATION 选项指明了为复制创建的存储过程不能在订购服务器上执行 只有在创建过滤存储过 程时 仅当进行数据复制时过滤存储过程才被执行 才使用该选项 FOR REPLICATION 与 WITH RECOMPILE 选项是互不兼容的 AS 指明该存储过程将要执行的动作 Sql_statement 是任何数量和类型的包含在存储过程中的 SQL 语句 另外应该指出 一个存储过程的最大尺寸为 128M 用户定义的存储过程必须创建在 当前数据库中 下面将给出几个例子 用来详细介绍如何创建包含有各种保留字的存储过程 例 12-1 该存储过程返回所有作者 以及他们的文章和出版者 use pubs if exists select name from sysobjects where name=’author_infor’ and type=’p’ drop procedure author_infor go create procedure author_infor as select au_lname, au_fname, title, pub_name from authors a inner join titleauthor ta on a.au_id=ta.au_id inner join titles t on t.title_id=ta.title_id inner join publishers p on t.pub_id=p.pub_id go 例 12-2 在该存储过程中使用了参数 use pubs if exists select name from sysobjects where name=’author_infor and type=’p’ drop procedure author_infor go use pubs go create procedure author_infor @lastname varchar 40 , @firstname varchar 20 as select au_lname,au_fname,title, pub_name from authors a inner join titleauthor ta on a.au_id=ta.au_id inner join ttitles t on t.title_id=ta.title_id inner join publishers p on t.pub_id=p.pub_id where au_fname=@firstname and au_lname=@lastname go 例 12-3 在该存储过程中使用了 OUTPUT 保留字 首先创建存储过程 use pubs go if exists select name from sysobjects where name = 'titles_sum' and type = 'p' drop procedure titles_sum go use pubs go create procedure salequa @stor_id char 4 ,@sum smallint output as select ord_num, ord_date, payterms, title_id, qty from sales where stor_id = @stor_id select @sum = sum qty from sales where stor_id = @stor_id go 然后在 Query Analyzer 中调用例 12-3 的存储过程 declare @totalqua smallint execute salequa '7131',@totalqua output if @totalqua<=50 select '销售信息'='销售等级为 3 销售量为'+rtrim cast @totalqua as varchar 20 if @totalqua>50 and @totalqua<=100 select '销售信息'='销售等级为 2 销售量为'+rtrim cast @totalqua as varchar 20 if @totalqua>100 select '销售信息'='销售等级为 1 销售量为'+rtrim cast @totalqua as varchar 20 运行结果为 ord_num ord_date payterms title_id qty -------------------- --------------------------- ------------ -------- ------ N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20 N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25 P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20 P3087a 1993-05-29 00:00:00.000 Net 60 PS2106 25 P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15 P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 25 6 row s affected 销售信息 ----------------------------------------- 销售等级为 1 销售量为 130 1 row s affected 1122..33 管理存储过程 12.3.1 查看存储过程 存储过程被创建以后 它的名字存储在系统表 sysobjects 中 它的源代码存放在系统 表 syscomments 中 可以通过 MS SQL Server 提供的系统存储过程来查看关于用户创建的 存储过程信息 1 通过 Enterprise Manager管理工具同样可以查看存储过程的源代码 其操作如下 1 启动 Enterprise Manager 登录到要使用的服务器 2 选择要创建存储过程的数据库 在左窗格中单击 Stored Procedure文件夹 此 时在右窗格中显示该数据库的所有存储过程 3 在右窗格中 右击要查看源代码的存储过程 在弹出的菜单中选择 Properties 选项 此时便可看到存储过程的源代码 如图 12-4所示 图12-4 查看存储过程源代码对话框 2 使用 sp_helptext 存储过程查看存储过程的源代码 其语法格式如下 sp_helptext 存储过程名称存储过程名称存储过程名称存储过程名称 例如要查看数据库 pubs 是否是存储过程 reptq1 的源代码 则执行 sp_helptext reptq1 ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ! 如果在创建存储过程时使用了 WITH ENCRYPTION 选项 那么无论是使用 Enterprise������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������Manager还是系统存储过程 sp_helptext都无法查看到存储过程的源代码 12.3.2 重新命名存储过程 修改存储过程的名字使用系统存储过程 sp_rename 其命令格式为 sp_rename 原存储过程名原存储过程名原存储过程名原存储过程名, 新存储过程名新存储过程名新存储过程名新存储过程名 例 12-4 将存储过程 reptq1 修改为 newproc 其语句为 sp_rename reptq1, newproc 另外 通过 Enterprise Manager 也可修改存储过程的名字 其操作过程与 WINDOWS 下修改文件名字的操作类似 即首先选中需修改名字的存储过程 然后右击鼠标 在弹出 菜单中选取 rename 选项 最后输入新存储过程的名字 12.3.3 删除存储过程 删除存储过程使用 drop 命令 drop 命令可将一个或多个存储过程或者存储过程组从 当前数据库中删除 其语法规则为 DROP PROCEDURE {procedure}} [,…n] 例 12-5 如将存储过程 reptq1 从数据库中删除 则执行 drop procedure reptq1 go 12.3.4 执行存储过程 执行已创建的存储过程使用 EXECUTE 命令 其语法如下 [EXECUTE] {[@return_statur=] {procedure_name[;number] | @procedure_name_var} [[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n] [WITH RECOMPILE] 各参数的含义如下 @return_status 是可选的整型变量 用来存储存储过程向调用者返回的值 @procedure_name_var 是一变量名 用来代表存储过程的名字 其它参数据和保留字的含义与 CREATE PROCEDURE 中介绍的一样 例 12-6 该存储过程被用来将两个字符串连接成一个字符串 并将结果返回 创建存储过程 create procedure strconnect @str1 varchar 20 , @str2 varchar 20 , @connect varchar 40 output as select @connect=@str1 + @str2 如果我们提供三个字符串来执行这一存储过程 我们将看不到字符串相加的结果 虽 然 Select 语句用来对 result 变量赋值 但 result 结果并没有显示 再执行以下语句 declare @result varchar 40 execute strconnect 'I am', ' John', ' string' select 'The result'=@result 则其运行结果为 The result ---------------------------------------- NULL 1 row s affected 若增加 OUTPUT 保留字到 EXECUTE 语句中便可显示返回参数 result 的值 OUTPUT 要求参数值被作为一个变量传送 而不是作为一个常量 下面的例子说明@result 变量来 存放由存储过程 strconnect 通过@connect 返回给调用者的结果值 从而使 SQL Server 能 够显示出存储过程的返回值 例 12-7 declare @result varchar 40 execute strconnect 'I am', 'John ', @result output select 'The result'=@result 运行结果为 The result ----------------------------------- I am John 1 row s affected 12.3.5 修改存储过程 修改以前用 CREATE PROCEDURE 命令创建的存储过程 并且不改变权限的授予情 况以及不影响任何其它的独立的存储过程或触发器常使用 ALTER PROCEDURE 命令 其 语法规则是 ALTER PROC[EDURE] procedure_name [;number] [ {@parameter data_type } [VARYING] [= default] [OUTPUT]] [,...n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}] [FOR REPLICATION] AS sql_statement [...n] 其中各参数和保留字的具体含义请参看 CREATE PROCEDURE 命令 下面将举一个例子 使读者对该命令有更为深刻的理解 例 12-8 use pubs go if exists select name from sysobjects where name = 'oakland_authors' and type = 'p' drop procedure oakland_authors go /* 创建一个存储过程 该存储过程获取所有居住在加里福尼亚 奥克兰城的作者的信息 */ use pubs go create procedure oakland_authors as select au_fname, au_lname, address, city, zip from pubs..authors where city = 'oakland' and state = 'ca' order by au_lname, au_fname go /* 查看该存储过程的源代码 */ select o.id, c.text from sysobjects o inner join syscomments c on o.id = c.id where o.type = 'p' and o.name = 'oakland_authors' /* 将执行该存储过程的权限授予 public 角色 */ 关于角色在 17 章介绍 grant execute on oakland_authors to public go /* 下面对该存储过程进行修改 使其能够显示出所有居住在加里福尼亚的作者 而不考虑其它地 区居住的作者 */ alter procedure oakland_authors with encryption as select au_fname, au_lname, address, city, zip from pubs..authors where state = 'ca' order by au_lname, au_fname go /* 查看该存储过程的源代码 */ select o.id, c.text from sysobjects o inner join syscomments c on o.id = c.id where o.type = 'p' and o.name = 'oakland_authors' go 将该段代码输入到 SQL Server Query Analyzer 窗口中 运行后结果为 id text -------------------------------------------------------------- 1557580587 create procedure oakland_authors as select au_fname, au_lname, address, city, zip from pubs..authors where city = 'oakland' and state = 'ca' order by au_lname, au_fname 1 row s affected -------------------------------------------------------------------------------------------------------- id text 1557580587 ???芹??????负?彣??????????蟸?? 泐摍?賯?鎨???????懡 ??跍旟?迨 輵?椿蚿?閗椌 洿?謋鈲?酄衎战胰嵨?襩??噍???秘???糡?媁???? ????妓??识墥 ? ??? 牙阾喯隶堠?潪?????????国卍 嫟??T 沁??????癦? ??傜貒???芦?簳?????窳遳?????诵? 宅?殴?鞧櫰??? ?漨?窦筘???飔??苜址?蛌?? ????技???裗唘??紃��铀????楢? 凂??? ???澵???噉骆盲 ??喺尔??沷鸱??蘩??婨辷?卓??秥蛹??? ?? 圱?? 1 row s affected ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ 由于在 ALERT PROCEDURE 中使用了 WITH ENCTYPTION 保留字 所以在查看修改后������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ ������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������ 的存储过程源代码时看到是一些乱码 1122..44 系统存储过程 系统存储过程就是系统创建的存储过程 目的在于能够方便地从系统表中查询信息或 完成与更新数据库表相关的管理任务或其它的系统管理任务 系统过程以 sp_ 为开头 在 Master 数据库中创建并保存在该数据库中 为数据库管理者所有 一些系统过程只能 由系统管理员使用 而有些系统过程通过授权可以被其它用户所使用 系统存储过程主要包括以下几类 这里主要给出每类系统过程中经常使用的系统过 程 目录存储过程 sp_column_privileges sp_special_columns sp_columns sp_sproc_columns sp_databases sp_statistics sp_fkeys sp_stored_procedures sp_pkeys sp_table_privileges sp_Server_info sp_tables 复制类存储过程 sp_addarticle sp_adddistpublisher sp_adddistributiondb sp_adddistributor sp_addpublication sp_help_agent_profile sp_addpublication_snapshot sp_help_publication_access sp_addpublisher70 sp_helparticle sp_addpullsubscription sp_addpullsubscription_agent sp_helpdistpublisher sp_addsubscriber sp_addsubscription sp_helpdistributiondb sp_addsubscriber_schedule sp_helpdistributor sp_helppublication sp_helppullsubscription sp_dropsubscriber sp_helpreplicationdboption sp_changedistpublisher sp_helpsubscription sp_changedistributiondb sp_changedistributor_password sp_link_publication sp_refreshsubscriptions sp_droparticle sp_dropdistpublisher sp_dropdistributiondb sp_dropdistributor sp_droppublication sp_droppullsubscription 类存储过程 sp_addalias sp_droprole sp_addapprole sp_droprolemember sp_addgroup sp_dropServer sp_addlinkedsrvlogin sp_dropsrvrolemember sp_addlogin sp_dropuser sp_addremotelogin sp_grantdbaccess sp_addrole sp_grantlogin sp_addrolemember sp_helpdbfixedrole sp_addServer sp_helpgroup sp_addsrvrolemember sp_helplinkedsrvlogin sp_adduser sp_helplogins sp_approlepassword sp_helpntgroup sp_change_users_login sp_helpremotelogin sp_changedbowner sp_helprole sp_changegroup sp_helprolemember sp_changeobjectowner sp_dbfixedrolepermission sp_helpsrvrole sp_defaultdb sp_helpsrvrolemember sp_dropremotelogin sp_defaultlanguage sp_helpuser sp_denylogin sp_password sp_dropalias sp_remoteoption sp_dropgroup sp_revokelogin sp_droplinkedsrvlogin sp_droplogin 分布式查询存储过程 sp_addlinkedServer sp_indexes sp_addlinkedsrvlogin sp_linkedServers sp_catalogs sp_primarykeys sp_foreignkeys 在以后的章节中 我们会使用或讲解这些存储过程 1122..55 触发器概述 在上面几节我们介绍了一般意义的存储过程 即用户自定义的存储过程和系统存储过 程 本节将介绍一种特殊的存储过程 即触发器 在余下各节中我们将对触发器的概念 作用以及对其的使用方法作详尽介绍 使读者了解如何定义触发器 创建和使用各种不同 复杂程度的触发器 12.5.1 触发器的概念及作用 触发器是一种特殊类型的存储过程 它不同于我们前面介绍过的存储过程 触发器主 要是通过事件进行触发而被执行的 而存储过程可以通过存储过程名字而被直接调用 当 对某一表进行诸如 UPDATE INSERT DELETE 这些操作时 SQL Server 就会自动执行 触发器所定义的 SQL 语句 从而确保对数据的处理必须符合由这些 SQL 语句所定义的规 则 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数 据的一致性 除此之外 触发器还有其它许多不同的功能 1 强化约束强化约束强化约束强化约束 Enforce restriction 触发器能够实现比 CHECK 语句更为复杂的约束 2 跟踪变化跟踪变化跟踪变化跟踪变化 Auditing changes 触发器可以侦测数据库内的操作 从而不允许数据库中未经许可的指定更新和变化 3 级联运行级联运行级联运行级联运行 Cascaded operation 触发器可以侦测数据库内的操作 并自动地级联影响整个数据库的各项内容 例如 某个表上的触发器中包含有对另外一个表的数据操作 如删除 更新 插入 而该操作 又导致该表上触发器被触发 4 存储过程的调用存储过程的调用存储过程的调用存储过程的调用 Stored procedure invocation 为了响应数据库更新 触发器可以调用一个或多个存储过程 甚至可以通过外部过程 的调用而在 DBMS 数据库管理系统 本身之外进行操作 由此可见 触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等 一些方面的问题 例如 触发器能够找出某一表在数据修改前后状态发生的差异 并根据 这种差异执行一定的处理 此外一个表的同一类型 INSERT UPDATE DELETE 的 多个触发器能够对同一种数据操作采取多种不同的处理 总体而言 触发器性能通常比较低 当运行触发器时 系统处理的大部分时间花费在 参照其它表的这一处理上 因为这些表既不在内存中也不在数据库设备上 而删除表和插 入表总是位于内存中 可见触发器所参照的其它表的位置决定了操作要花费的时间长短 12.5.2 触发器的种类 SQL Server 2000 支持两种类型的触发器 AFTER 触发器和 INSTEAD OF 触发器 其 中 AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器 该类型触发器要求只 有执行某一操作 INSERT UPDATE DELETE 之后 触发器才被触发 且只能在表 上定义 可以为针对表的同一操作定义多个触发器 对于 AFTER 触发器 可以定义哪一 个触发器被最先触发 哪一个被最后触发 通常使用系统过程 sp_settriggerorder 来完成此 任务 INSTEAD OF 触发器表示并不执行其所定义的操作 INSERT UPDATE DELETE 而仅是执行触发器本身 既可在表上定义 INSTEAD OF 触发器 也可以在视图上定义 INSTEAD OF 触发器 但对同一操作只能定义一个 INSTEAD OF 触发器 1122..66 创建触发器 上面介绍了有关触发器的概念 作用和一些基本问题 下面我们将分别介绍在 MS SQL Server 中如何用 SQL Server 管理工具 Enterprise Manager 和 Transaction_SQL 来创建触发 器 在创建触发器以前必须考虑到以下几个方面 CREATE TRIGGER语句必须是批处理的第一个语句 表的所有者具有创建触发器的缺省权限 表的所有者不能把该权限传给其它用 户 触发器是数据库对象 所以其命名必须符合命名规则 尽管在触发器的 SQL 语句中可以参照其它数据库中的对象 但是 触发器只能 创建在当前数据库中 虽然触发器可以参照视图或临时表 但不能在视图或临时表上创建触发器 而只 能在基表或在创建视图的表上创建触发器 一个触发器只能对应一个表 这是由触发器的机制决定的 尽管 TRUNCATE TABLE 语句如同没有 WHERE 从句的 DELETE 语句 但是由 于 TRUNCATE TABLE 语句没有被记入日志 所以该语句不能触发 DELETE 型 触发器 WRITETEXT语句不能触发 INSERT或 UPDATE型的触发器 当创建一个触发器时 必须指定触发器的名字 在哪一个表上定义触发器 激活触发 器的修改语句 如 INSERT DELETE UPDATE 当然两个或三个不同的修改语句也可 以都触发同一个触发器 如 INSERT 和 UPDATE 语句都能激活同一个触发器 12.6.1
/
本文档为【Oracle存储过程和触发器】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。 本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。 网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。

历史搜索

    清空历史搜索