友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
SQL 21日自学通(V3.0)(PDF格式)-第51部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
输出参数也必须以@提示符开始 此外 OUTPUT 关键字必须紧跟着输出参数的名字 当
你在运行存贮过程时你必须给出 OUTPUT 关键字
例 13。6 给出了在存贮过程中使用输入参数的用法
269
…………………………………………………………Page 270……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
例 13。6
下面的存贮过程将选用所有发行媒体为 CD 的艺术家的名字
1》 create procedure Match_Names_To_Media @description char（30）
2》 as
3》 select ARTISTS。name from ARTISTS； MEDIA； RECORDINGS
4》 where MEDIA。description = @description and
5》 MEDIA。media_type = RECORDINGS。media_type and
6》 RECORDINGS。artist_id = ARTISTS。artist_id
7》 go
1》 execute Match_Names_To_Media 〃CD〃
2》 go
运行该语句将会得到下边的结果集
OUTPUT
NAME
Soul Asylum
Maurice Ravel
Vince Gill
Crowded House
Mary Chapin…Carpenter
例 13。7
本例中给出的输出参数的用法 在该例中将使用艺术家的 HOMEBASE 作为输入 过
程会将艺术家的名字作为输出
INPUT
1》 create procedure Match_Homebase_To_Name @homebase char（40）； @name char（30） output
2》 as
3》 select @name = name from ARTISTS where homebase = @homebase
4》 go
1》 declare @return_name char（30）
2》 execute Match_Homebase_To_Name 〃Los Angeles〃； @return_name = @name output
3》 print @name
4》 go
270
…………………………………………………………Page 271……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
OUTPUT
Oingo Boingo
删除一个存贮过程
现在 你大概会猜到应该如何删除一个存贮过程了 如果你猜测是使用 DROP 命令
那你是绝对正确的 下边的语句将会从数据库中删除存贮过程
SYNTAX
drop procedure procedure_name
DROP 语句是经常使用的 当一个存贮过程被重新创建之前 旧的存贮过程以及它的
名字必须被删除掉 根据我个人的经验 只有极少的存贮过程在创建之后是不需要修改的
有许多次 在语句中产生的错误会上传至过程 我们推荐你在创建存贮过程时使用 SQL 脚
本文件来存贮你在过程中的所有语句 你可以在数据库服务器中使用这个脚本文件来得到
你想要的结果或者是对存贮过程进行重新编译 该技术允许你使用通用的文本编辑器如 VI
或 WINDOWS 的记事本 但是 当你在运行脚本进行新的创建之前时 你一定要记得将原
有的存贮过程及相关表先删除 如果你忘记了执行 DROP 命令 你将会收到一个错误信息
在用 SQL 的脚本来创建数据库对象时经常会用到下边的语法
SYNTAX
if exists （select * from sysobjects where name = 〃procedure_name〃）
begin
drop procedure procedure_name
end
go
create procedure procedure_name
as
该命令会检查 SYSOBJECTS 表 这里边存贮着 SQL SERVER 的数据库对象信息 来
查看该对象是否存在 如果存在 就在创建新对象之前先将它删除 在创建脚本文件并最
后运行之前上面的工作将会花掉你大量的时间 因为可能有太多的潜在错误出现
271
…………………………………………………………Page 272……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
存贮过程的嵌套
存贮过程也可以被嵌套调用以增强程序的模块化 一个存贮过程也可以被其它的存贮
过程调用 它也可以调用其它的存贮过程 嵌套的存贮过程在很多情况下是一个非常好的
办法
l 嵌套存贮过程可以在函数级上将你的复合查询减到最小 如果在一行中需要运行
12 个查询 你或许可以将这 12 个查询精简为 3 个嵌套过程调用 当然 这要视
情况而定
l 嵌套存贮过程可以提高性能 查询优化将会更优化 许多的简明语句组可能会比
大的语句组更有效
当嵌套存贮过程时 所有的在存储过程内部创建的变量和数据库对象对于将要调用它
的过程来说都是可见的 全体局部变量或临时对象 如临时表 将由最后创建这些元素的
存贮过程来删除
当准备一个大型的 SQL 脚本文件时 你可以要对表或数据库对象来运行它以检证问
题 在调用它们之前你必须先创建一个嵌套的存贮过程来调用它 但是 主调过程可能会
创建临时表或游标以在调用过程中使用它们 而被调用的过程则不知道这些将在脚本文件
的稍后时创建临时表和游标 对于这个问题最容易的办法是在创建所有的存贮过程之前选
创建这些临时对象 然后在存贮过程再次创建它们之前删除这些临时的对象 在脚本文件
中 你是不是对些有点迷糊了 例 13。8 将会帮助你明白这一过程
例 13。8
INPUT
1》 create procedure Example13_8b
2》 as
3》 select * from #temp_table
4》 go
1》 create procedure Example13_8a
2》 as
3》 create #temp_table （
4》 data char（20）；
5》 numbers int）
272
…………………………………………………………Page 273……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
6》 execute Example13_8b
7》 drop table #temp_table
8》 go
分析
正如你所看到的 过程 Example13_8b 使用了 但是#temp_table 并没有被创建 它是
在过程 Example13_8a 中创建的 结果将会产生错误 事实上 Example13_8b 并没有被创
建 因为并没有临时表#temp_table 而过程 Example13_8a 也不会被创建 因为没有过程
Example13_8b
下边的代码通过在创建第一个过程之前先创建#temp_table 来修正这一错误 其实
#temp_table 在第二个过程创建之前被删除了
INPUT
1》 create #temp_table （
2》 data char（20）；
3》 numbers int）
4》 go
1》 create procedure Example13_8b
2》 as
3》 select * from #temp_table
4》 go
1》 drop table #temp_table
2》 go
1》 create procedure Example13_8a
2》 as
3》 create #temp_table （
4》 data char（20）；
5》 numbers int）
6》 execute Example13_8b
7》 drop table #temp_table
8》 go
273
…………………………………………………………Page 274……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
设计和使用触发机制
触发机制从本质上来说是一种特殊类型的存贮过程 它可以在下列的三种情况之一发生时
自动运行
l 更新
l 插入
l 删除
Transcat…SQL 创建触发机制的语法格式如下
SYNTAX
create trigger trigger_name
on table_name
for ｛insert； update； delete｝
as SQL_Statements
ORACLE 7 SQL 则使用下边的语法来创建触发机制
SYNTAX
CREATE ＇OR REPLACE＇ TRIGGER ＇schema。＇trigger_name
｛BEFORE ｜ AFTER｝
｛DELETE ｜ INSERT ｜ UPDATE ＇OF column＇； column＇。。。＇｝
＇OR ｛DELETE ｜ INSERT ｜ UPDATE ＇OF column ＇； column＇ 。。。＇｝＇。。。
ON ＇schema。＇table
＇＇REFERENCING ｛ OLD ＇AS＇ old ＇NEW ＇AS＇ new＇
｜ NEW ＇AS＇ new ＇OLD ＇AS＇ old＇｝＇
FOR EACH ROW
＇WHEN （condition）＇ ＇
pl/sql statements。。。
触发机制对于强制执行引用完整性非常有用 我们在第 9 天 创建和维护表 中学习
如何创建表时曾经提到过 强制执行引用完整性可以保证在多表交叉访问时数据的有效性
如果用户输入了下边的命令
INPUT
1》 insert RECORDINGS values （12； 〃The Cross of Changes〃； 3； 1994）
274
…………………………………………………………Page 275……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
2》 go
分析
这是一个有效的在表 RECORDINGS 表中插入新记录的命令 可是 对 ARTISTS 表进
行一下快速的检查后你会发现并没有 ARTIST_ID=12 的记录 用户所拥有的在
RECORDINGS 表中插入记录的权利可以彻底地破坏你的数据引用完整性
注 尽管有许多数据库系统都可以通过在创建表的时候设置约束来强制执行数据的引
用完整性 但是触发机制却提供了更为灵活的解决方法 约束将会把系统的错误信息返回
给用户 并且 你现在可能已经知道了 这些错误信息有时对你没有多大的帮助 而作为
另外一种方法 触发机制可以打印出错误信息 调用其它的存贮过程 如果有必要 它还
可以修正错误信息
触发机制与事务处理
触发机制所进行的活动是被默认为事务处理的一部分进行的 主要的事件次序如下
1 默认地自动运行 BEGIN TRANSACTION 语句 对于表和触发机制而言
2 当插入 更新 删除操作发生时
3 触发机制被调用 其中的语句被自动执行
4 由触发机制自动的完成事务处理的取消或确认操作
例 13。9
本例解决了早些时候更新 RECORDINGS 表时所带来的问题
INPUT
1》 create trigger check_artists
2》 on RECORDINGS
3》 for insert； update as
4》 if not exists （select * from ARTISTS； RECORDINGS
5》 where ARTISTS。artist_id = RECORDINGS。artist_id）
6》 begin
7》 print 〃Illegal Artist_ID！〃
8》 rollback transaction
9》 end
275
…………………………………………………………Page 276……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
10》 go
分析
类似的问题也可能在删除 RECORDINGS
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！