友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
SQL 21日自学通(V3.0)(PDF格式)-第37部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
了 FETCH 命令以外其他的命令不得修改 @@sqlstatus 变量 该变量可以取下表三个值中
的一个 下表是在 Transcat…SQL 参考手册中给出的
Status Meaning
0 Successful pletion of the FETCH statement。
1 The FETCH statement resulted in an error。
2 There is no more data in the result set。
而变量 @@rowcount 则返回上次一 FETCH 命令设置的行号 你可以用它来确定当前
游标结果集的行数
下边的代码给出了 FETCH 命令的扩充使用方法 你现在可以使用 While Loop 命令和
变量@@sqlstatus 来翻阅当前的游标
INPUT
1》 declare @name char（30）
2》 declare @homebase char（40）
3》 declare @style char（20）
4》 declare @artist_id int
5》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
6》 while （@@sqlstatus = 0）
7》 begin
8》 print @name
264
…………………………………………………………Page 265……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
9》 print @homebase
10》 print @style
11》 print char（@artist_id）
12》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
13》 end
14》 go
分析
现在你已经有了一个全功能的游标 下边要做的工作就是关闭游标
关闭游标
关闭游标是一个非常简单的工作 它的语句如下
SYNTAX
close cursor_name
这时游标依然存在 但是 它必须被再次打开方可使用 关闭一个游标从本质上来说
是关闭了它的结果集 而并不是它的全部内容 如果你已经完全结束了对一个游标的使用
的话 DEALLOCATE 命令将释放让游标所占用的内存并且可以让游标的名字可以被再次
使用 这是该命令的语法格式
SYNTAX
deallocate cursor cursor_name
例 13。4 给出了用 Transcat…SQL 写的创建 使用 关闭 释放一个游标的完整过程
Example 13。4
INPUT
1》 declare @name char（30）
2》 declare @homebase char（40）
3》 declare @style char（20）
4》 declare @artist_id int
5》 create Artists_Cursor cursor
6》 for select * from ARTISTS
7》 open Artists_Cursor
265
…………………………………………………………Page 266……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
8》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
9》 while （@@sqlstatus = 0）
10》 begin
11》 print @name
12》 print @homebase
13》 print @style
14》 print char（@artist_id）
15》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
16》 end
17》 close Artists_Cursor
18》 deallocate cursor Artists_Cursor
19》 go
注 下边是示例所用的数据
OUTPUT：
Soul Asylum Minneapolis Rock 1
Maurice Ravel France Classical 2
Dave Matthews Band Charlottesville Rock 3
Vince Gill Nashville Country 4
Oingo Boingo Los Angeles Pop 5
Crowded House New Zealand Pop 6
Mary Chapin…Carpenter Nashville Country 7
Edward MacDowell U。S。A。 Classical 8
游标的适用范围
与表 索引以及其它的对象如触发机制和存贮过程不同 游标在创建以后并不作为一
个数据库对象来看待 所以 游标的使用会受到一些限制
警告 切记 无论何时要注意游标分配过的内存 尽管它的名字可能已经不存在了 当不
在使用游标的时候 或在进行游标能力之外的工作时 一定要记得关闭游标并将它
释放掉
可以在下列三种情况下创建游标
l 在会话中 — — 会话在用户登录以后开始 如果用户在登录进行 SQL SERVER 以后创建
了一个游标 那么游标的名字将一直存在到用户退出登录 用户不能再一次使用在本
266
…………………………………………………………Page 267……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
次登录中创建的游标名
l 在存贮过程中— — 游标在存贮过程的内部创建的好处在于只有当过程运行时它才真正
起作用 一旦过程退出了 则游标的名字将不再有效
l 在触发机制中— — 在触发机制中创建游标与在存贮过程中创建游标所受到的限制是相
同的
创建和使用存贮过程
存贮过程是一个专业数据库编程人员必须掌握的概念 存贮过程可以在最大程序上发
挥出 SQL 的潜能 该功能可以被如 C FORTRAN 或 VISUAL BASIC 象调用或执行自己
的函数一样地调用或执行 存贮过程应该是一组经过压缩处理的经常使用的一组命令 如
交叉表的查询 更新和插入操作 存贮过程允许程序员简单地将该过程作为一个函数来调
用而不是重复地执行过程内部的语句 而且 存贮过程还有些附加的优点
SyBase 工业有限公司是使用存贮过程的先驱 它早在 1980 年就在它的 SQL SERVER
中提供了存贮过程功能 这种过程是作为数据库的一部分被创建的 它与表 索引一样是
存贮在数据库的内部的 Transcat…SQL 允许在过程调用中提供输入或输出的参数 这种机
制可以让你写出通用的存贮过程并将变量传递给它
使用存贮过程的一个最大的优点在于它可以在设计的阶段执行 当在一个网站中执行
大批量的 SQL 语句时 你的应用程序会不停地不 SQL SERVER 进行通讯 这会使得网站
的负荷迅速增大 在多用户环境下通讯将异常繁忙 你的服务器将变得越来越慢 而使用
存贮过程可以在最大程序中减轻通讯负荷
当存贮过程执行时 SQL 语句将在服务器中继续运行 一些数据信息将会返回给用户
的电脑直至过程执行完毕 这会极大地提高性能并带来了附加的好处 存贮过程在第一次
执行时在数据库经过了编译操作 编译的映象将存贮在服务器的过程中 因此 你不必在
每一次执行它的时候都对它进行优化 这也使性能得到了提高
使用 Transcat…SQL 来创建存贮过程的语法如下
SYNTAX
create procedure procedure_name
＇＇（＇@parameter_name
datatype ＇（length） ｜ （precision ＇； scale＇）
267
…………………………………………………………Page 268……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
＇= default＇＇output＇
＇； @parameter_name
datatype ＇（length） ｜ （precision ＇； scale＇）
＇= default＇＇output＇＇。。。＇）＇＇
＇with repile＇
as SQL_statements
运行存贮过程的 EXECUTE 命令的语法如下
SYNTAX
execute ＇@return_status = ＇
procedure_name
＇＇@parameter_name =＇ value ｜
＇@parameter_name =＇ @variable ＇output＇。。。＇＇
＇with repile＇
例 13。5
本例使用例 13。4 的内容来创建一个简单的过程
INPUT
1》 create procedure Print_Artists_Name
2》 as
3》 declare @name char（30）
4》 declare @homebase char（40）
5》 declare @style char（20）
6》 declare @artist_id int
7》 create Artists_Cursor cursor
8》 for select * from ARTISTS
9》 open Artists_Cursor
10》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
11》 while （@@sqlstatus = 0）
12》 begin
13》 print @name
14》 fetch Artists_Cursor into @name； @homebase； @style； @artist_id
268
…………………………………………………………Page 269……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
15》 end
16》 close Artists_Cursor
17》 deallocate cursor Artists_Cursor
18》 go
你可以使用 EXECUTE 命令来执行 Print_Artists_Name 过程
INPUT
1》 execute Print_Artists_Name
2》 go
OUTPUT
Soul Asylum
Maurice Ravel
Dave Matthews Band
Vince Gill
Oingo Boingo
Crowded House
Mary Chapin…Carpenter
Edward MacDowell
例 13。5 是一个很小的存贮过程 但是 一个存贮过程中可以包含许多条语句 也就是说你
不必逐条地执行这些语句
在存贮过程中使用参数
例 13。5 是重要的第一步 因为它给出的CREATE PRODUCE 语句的最简单的用法 但
是 在看过它的语法以后你会发现 CREATE PRODUCE 语句有着比例 13。5 更多的内容 存
贮过程也可以接受参数并把它们输入到其中的 SQL 语句中 此外 数据可以通过输出参数
从存贮过程中返回
输入参数必须以@提示符开始 而且这些参数必须是 Transcat…SQL 的合法数据类型
输出参数也必须以@提示符开始 此外 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 脚本文件时 你可以要对表或数据库对象来运行它以检证问
题 在调用它们之前你必须先创建一个嵌套的存贮过程来调用它 但是 主调过程可能会
创建临时表或游标以在调用过程中使用它们 而被调用的过程则不知道这些将在脚本文件
的稍后时创建临时表和游标 对于这个问题最容易的办法是在创建所有的存贮过程之前选
创建这些临时对象 然后在存贮过程再次创建它们之前删除这些临时的对象 在脚本文件
中 你是不是对些有点迷糊了
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！