友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
SQL 21日自学通(V3.0)(PDF格式)-第36部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
第 13 天 高级 SQL
目标
在之前的 12 天中 我们学习了许多关于如何写出强在的 SQL 查询以从数据库中获得
数据 我们也简要的学习了如何进行数据库的设计以数据库安全性问题 而在今天的高级
SQL 部分 我们将主要学习以下内容
l 临时表
l 游标
l 存贮过程
l 触发机制
l 内嵌 SQL
注 在今天的例子中我们使用 ORACLE 的 PL/SQL 和 MicroSoft SQL Server 的 Transact…SQL
来实现 我们尽可能使我们的例子可以适用于这两种风格的 SQL 你不必为此需要得
到一个 ORACLE 或 SQL Server 的副本 实际的数据库版本的选择是依据你的需要而定
的 如果你已经阅读了足够的可以创建一个项目所需要的知识 那你是没有选择的机
会的
注 尽管你可以在大多数的数据库产品上应用本书中所给出的例子 但是今天的内容
并不适用于这句话 许多数据库供应商仍没有提供临时表 存贮过程以及触发机制 请检
查你的数据库文档 看你所喜爱的数据库系统是否支持这些特性
临时表
我们要讨论的第一个高级主题是临时表的用法 这是一种简单的临时存在于数据库系
统当中的表格 当结束数据库的联接或退出登录以后它们会被自动地删除 Transact…SQL 在
TempDB 中创建临时表 这个数据库是在你安装 SQL…SERVER 时创建的 创建临时表可以
使用两种语法格式
SYNTAX
SYNTAX 1
256
…………………………………………………………Page 257……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
create table #table_name （field1 datatype；
。
。
。
fieldn datatype
语法 1 用以在 TempDB 中创建一个表 该表由 Create Table 命令以及创建表时的日期
和时间组合而成一个唯一的表名 临时表只可由它的创建者使用 五十个用户可以在同时
运行下边的命令
1》 create table #albums （
2》 artist char（30）；
3》 album_name char（50）；
4》 media_type int）
5》 go
表名开头的#标志是 SQL 用以标识临时表的标志 五十个用户中每一个都可以获得一
个他可以使用的表 每一个用户都可以放心地插入 更新 删除表中的数据而不必担心其
它的用户使该表中的数据失效 该表也可以使用下边的命令来手动删除
1》 drop table #albums
2》 go
当用户退出 SQL…SERVER 时该表也可以被自动地删除 如果你是在自态 SQL 联接情
况下使用该语句 例如 SQL…SERVER 的 DB…LIBRARY 那么当动态联接被终止时该表也
会被自动地删除
语法 2 给出了另一种在 SQL…SERVER 中创建临时表的方法 该语法与使用语法 1 有着
不同的结果 所以你要注意这两种语法之间的差别
SYNTAX
SYNTAX 2：
create table tempdb。。tablename （field1 datatype；
。
fieldn datatype）
使用语法 2 来创建临时表的结果与使用语法 1 相同 临时表的名称格式也与语法 1 的
相同 它们的不同之处在于当用户退出 SQL 或切断联接时该表不会被自动地删除 用户必
257
…………………………………………………………Page 258……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
须使用 DROP 命令在 TEMPDB 中将其手动删除
技巧 另外一种将使用语法 2 创建的临时表删除的方法是将 SQL…SERVER 关闭并重新启动
这将会把所有在 TEMPDB 中的表都删除掉
例 13。1 和 13。2 表明使用这两种格式的临时表的确是货真价实的临时的表 在这两个例
子以后 例 13。3 给出的临时的最为通常的用途 用于暂时存贮从查询中返回的数据 这些
数据可以在其它的查询中使用
为了验证这些例子你需要创建一个数据库 在 MUSIC 数据库中需要创建以下三个表
l ARTISTS
l MEDIA
l RECORDINGS
创建这些表需要使用下边的 SQL 语句
INPUT
1》 create table ARTISTS （ 4》 price float）
2》 name char（30）； 5》 go
3》 homebase char（40）； 1》 create table RECORDINGS （
4》 style char（20）； 2》 artist_id int；
5》 artist_id int） 3》 media_type int；
6》 go 4》 title char（50）；
1》 create table MEDIA （ 5》 year int）
2》 media_type int； 6》 go
3》 description char（30）；
注 表 13。1 13。2 13。3 给出的这些表中的示例数据
表 13。1
Name Homebase Style Artist_ID
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
258
…………………………………………………………Page 259……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
Edward MacDowell U。S。A。 Classical 8
表 13。2
Description
Media_Typ Price
e
1 Record 4。99
2 Tape 9。99
3 CD 13。99
4 CD…ROM 29。99
5 DAT 19。99
表 13。3
Artist_Id Media_Type
Title Year
1 2 Hang Time 1988
1 3 Made to Be Broken 1986
2 3 Bolero 1990
3 5 Under the Table and Dreaming 1994
4 3 When Love Finds You 1994
5 2 Boingo 1987
5 1 Dead Man's Party 1984
6 2 Woodface 1990
6 3 Together Alone 1993
7 5 e On； e On 1992
7 3 Stones in the Road 1994
8 5 Second Piano Concerto 1985
例 13。1
你可以在 TEMPDB 数据库中创建一个临时表 在向这些表中插入一些虚拟的数据以后
退出登录 然后再重新登录 SQL SERVER 试着从表中选取临时的数据 注意结果
INPUT：
1》 create table #albums （
2》 artist char（30）；
3》 album_name char（50）；
4》 media_type int）
5》 go
1》 insert #albums values （〃The Replacements〃； 〃Pleased To Meet Me〃； 1）
2》 go
259
…………………………………………………………Page 260……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
现在请使用 EXIT 或者 QUIT 来退出 SQL SERVER 的联接 在重新登录并选择了
你在上一次时使用的数据库以后 试一下下边的命令
INPUT：
1》 select * from #albums
2》 go
分析
在当前的数据库中并不存在该表
例 13。2
现在使用语法 2 来创建表
INPUT：
1》 create table tempdb。。albums （
2》 artist char（30）；
3》 album_name char（50）；
4》 media_type int）
5》 go
1》 insert #albums values （〃The Replacements〃； 〃Pleased To Meet Me〃； 1）
2》 go
在退出登录并重新登录进入以后 切换到你在 CREATE TABLE TEMPDB。。ALBUMS
命令中指明的数据库 然后请验证下边的命令
INPUT：
1》 select * from #albums
2》 go
这次 你会得到下边的结果
OUTPUT：
Artist Album_name media_type
The Replacements Pleased To Meet Me 1
例 13。3
本例给出一临时表的最为通常的用法 在复合查询中存贮查询的结果为之后的查询使
用
INPUT
260
…………………………………………………………Page 261……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
1》 create table #temp_info （
2》 name char（30）；
3》 homebase char（40）；
4》 style char（20）；
5》 artist_id int）
6》 insert #temp_info
7》 select * from ARTISTS where homebase = 〃Nashville〃
8》 select RECORDINGS。* from RECORDINGS； ARTISTS
9》 where RECORDINGS。artist_id = #temp_info。artist_id
10》 go
上边的这一组命令选出了所有的居住在 Nashville 的艺术家的记录信息
下边的这此命令则是例 13。3 语句的另外一种写法
1》 select ARTISTS。* from ARTISTS； RECORDINGS where ARTISTS。homebase = 〃Nashville〃
2》 go
游标
数据库指针类似于字处理程序中的指针 当你按下方向键时 游标依次从各行文本中
滚动 按一下向上键游标向上跳一行 而按 PageUp 和 PageDown 则会向一次翻阅几行 数
据库游标的操作也类似
数据库游标允许你选择一组数据 通过翻阅这组数据记录 通常被称为数据集 检查
每一个游标所在的特定的行 你可以将游标和局部变量组合在一起对每一个记录进行检查
当游标移动到下一个记录时来执行一些外部操作
游标的另一个常见的用法是保存查询结果以备以后使用 一个游标结果集是通过执行
SELECT 查询来建立的 如果你的应用程序或过程需要重复使用一组记录 那么第一次建
立游标以后再重复使用将会比多次执行查询快得多 而且你还有在查询的结果集中翻阅的
好处
下边是创建 使用和关闭数据库游标的例子
1。 Create the cursor。
2。 Open the cursor for use within the procedure or application。
261
…………………………………………………………Page 262……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
3。 Fetch a record's data one row at a time until you have reached the end of the cursor's records。
4。 Close the cursor when you are finished with it。
5。 Deallocate the cursor to pletely discard it。
创建游标
如果使用 Transcat…SQL 来创建游标 其语法如下
SYNTAX
declare cursor_name cursor
for select_statement
＇for ｛read only ｜ update ＇of column_name_list＇｝＇
使用 ORACLE7 的 SQL 来创建和语法格式则如下
SYNTAX
DECLARE cursor_name CURSOR
FOR ｛SELECT mand ｜ statement_name ｜ block_name｝
在执行 DECLARE cursor_name CURSOR 语句时 你必须同时定义将要在你的所有的
游标操作中使用的结果集 一个游标有两个重要的部分 游标结果集和游标的位置
下边的语句将创建一个基于 ARTIST 表的结果集
INPUT
1》 create Artists_Cursor cursor
2》 for select * from ARTISTS
3》 go
分析
你现在已经有了一个名字为 ARTIST_Cursor 游标 它包括了所有的 ARTIST 表的内容
但是首先你必须打开游标
打开游标
最简单的打开游标命令如下
SYNTAX：
open cursor_name
262
…………………………………………………………Page 263……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
运行下列命令打开 ARTIST_Cursor 游标
1》 open Artists_Cursor
2》 go
现在你可以使用游标来翻阅结果集了
使用游标来进行翻阅
要想在游标结果集中进行翻阅操作 Transcat…SQL 提供了 FETCH 命令
SYNTAX
fetch cursor_name ＇into fetch_target_list＇
ORACLE SQL 则提供了下边的语法
FETCH cursor_name ｛INTO ： host_variable
＇＇INDICATOR＇ ： indicator_variable＇
＇； ： host_variable
＇＇INDICATOR＇ ： indicator_variable＇ ＇。。。
｜ USING DESCRIPTOR descriptor ｝
每次当 FETCH 命令运行时 游标指针的好处是每次可以在结果集中移动一行 如果
需要 移动到行的数据可以被填充到 fetch_target_list 变量中
注 Transcat…SQL 允许程序员通过下边的命令来实现一次移动多行
set cursor rows number for cursor_name
该命令不能使用 INTO 子句 但是 当向前跳动的行数已知时用它来代替重复执行
FETCH 命令则很有用
下边的语句将从 ARTIST_Cursor 的结果集中获得数据并把它返回给程序变量
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》 print @name
263
…………………………………………………………Page 264……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
7》 print @homebase
8》 print @style
9》 print char（@artist_id）
10》 go
你可以使用 WHILE 循环来循环查看整个结果集 但是你是如果知道已经到达了最后
一个记录的呢
测试游标的状态
Transcat…SQL 可以让你在任何时候通常维护 @@sqlstatus 和 @@rowcount 这两个全
局变量来检查当前游标的状态
变量@@sqlstatus 返回最后一次运行 FETCH 语句的状态信息 Transcat…SQL 规定除
了 FETCH 命令以外其�
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！