Transact-SQL语句的高级应用.ppt

SQLServer2005数据库应用与开发 第06章Transact SQL语句的高级应用 6 1多表连接6 2使用子查询6 3利用游标处理结果集6 4管理大对象类型数据6 5小结 第06章Transact SQL语句的高级应用 内容提要1 利用Transact SQL语句查询的相关技巧和高级应用2 连接与子查询的区别3 游标的概念和应用4 大对象类型数据的管理 6 1多表连接 6 1 1连接概述连接可以根据各个表之间的逻辑关系来利用一个表中的数据选择另外的表中的行 连接条件可在FROM或WHERE子句中指定 连接条件与WHERE和HAVING搜索条件组合 用于控制FROM子句引用的基表中所选定的行 6 1多表连接 6 1 1连接概述SQLServer处理连接时 查询引擎从多种可能的方法中选择最高效的方法处理连接 尽管不同连接的物理执行采用多种不同的优化 但是逻辑序列都应用下列子句实现 FROM子句中的连接条件 WHERE子句中的连接条件和搜索条件 HAVING子句中的搜索条件 6 1多表连接 6 1 2内连接内连接 INNERJOIN 通过比较数据源表间共享列的值 从多个源表检索符合条件的行的操作 可以使用等号运算符的连接 也可以连接两个不相等的列中的值 例6 1查询选修课程号为c05109的学生的学号 姓名和期末成绩 分析 本例中要求所输出的列分别在student表和score表中 可以通过studentno列 使用内连接的方式连接两个表 找出选修课程号为c05109的行 程序中两个表存在相同的列 引用时需要标明该列所属的源表 6 1多表连接 程序代码如下 SELECTstudent studentno sname finalFROMstudentINNERJOINscoreONstudent studentno score studentnoWHEREscore courseno c05109 程序执行结果如下 studentnosnamefinal 0822111208韩吟秋91 000824113307崔岩坚79 00 0935222201夏文斐92 000937221508平靖91 00 7行受影响 6 1多表连接 6 1 2内连接 例6 2查询选修课程号为c05103且平时成绩高于80分的学生的学号 姓名 平时成绩和期末成绩 分析 本例通过studentno列连接两个表 找出选修课程号为c05103的行 同时要求输出行中的平时成绩高80分 则可以使用不是用等号的比较运算符实现 关键词INNER也可以省略 程序代码如下 SELECTstudent studentno sname usually finalFROMstudentJOINscoreONstudent studentno score studentnoandusually 80WHEREscore courseno c05103 程序执行结果如下 studentnosnameusuallyfinal 0823210007宿致远82 0069 000824113307崔岩坚85 0077 000922210009许海冰87 0082 000922221324何影88 0062 00 4行受影响 6 1多表连接 6 1 3外连接外部连接 outerjoin 包括满足搜索条件的连接表中的所有行 甚至包括在其他连接表中没有匹配行的一个表中的行 1 左外连接 leftouterjoin 包括JOIN子句中左侧表中的所有行 右表中的行与左表中的行不匹配时 将为来自右表的所有结果集列赋以NULL值 6 1多表连接 6 1 3外连接 例6 3利用左外连接方式查询08级学生的学号 姓名 平时成绩和期末成绩 分析 左外连接方式将会对右表中的行与左表中的行不匹配时 将右表的所有结果集列赋以NULL值 程序代码如下 SELECTstudent studentno sname usually finalFROMstudentLEFTJOINscoreONstudent studentno score studentnoWHEREsubstring student studentno 1 2 08 程序执行结果如下 studentnosnameusuallyfinal 0822111208韩吟秋85 0091 000822111208韩吟秋89 0095 00 0824113307崔岩坚66 0082 000828261367赵毓NULLNULL 10行受影响 6 1多表连接 6 1 3外连接 2 右外连接 rightouterjoin 外部连接的一种 其中包含JOIN子句中最右侧表的所有行 如果右侧表中的行与左侧表中的行不匹配 则将为结果集中来自左侧表的所有列分配NULL值 6 1多表连接 6 1 3外连接 例6 4利用右外连接方式查询教师的排课情况 分析 右外连接方式将会对左表中的行与右表中的行不匹配时 将左表的所有结果集列赋以NULL值 程序代码如下 SELECTcourseno tname teacher teacherno majorFROMteach classRIGHTJOINteacherONteach class teacherno teacher teacherno 程序执行结果如下 coursenotnamemajorteacherno c05109韩晋升软件工程t05001 c06172姚思远铸造工艺t06023NULL马爱芬经济管理t07019c08123田有余金融管理t08017c08106韩慧娟货币学t08019 10行受影响 6 1多表连接 6 1 3外连接 3 完全外连接 若要通过在连接的结果中包括不匹配的行来保留不匹配信息 请使用完全外部连接 SQLServer提供了完全外部连接运算符FULLOUTERJOIN 它将包括两个表中的所有行 不论另一个表中是否有匹配的值 6 1多表连接 6 1 3外连接 例6 5利用完全外连接方式查询教师的排课情况 分析 完全外部连接是右外连接与左外连接的并集 无论是左表中的行还是右表中的行不匹配时 将所有结果集中没有匹配值的列赋以NULL值 程序代码如下 SELECTcourseno tname major teacher teachernoFROMteach classFULLJOINteacherONteach class teacherno teacher teacherno 程序执行结果如下 coursenotnamemajorteacherno c05109韩晋升软件工程t05001c05127韩晋升软件工程t05001 c08106韩慧娟货币学t08019c05109NULLNULLNULLNULL马爱芬经济管理t07019 11行受影响 6 1多表连接 6 1 4交叉连接 笛卡尔连接 交叉连接 CrossJoin 是在没有WHERE子句的情况下 产生的表的笛卡儿积 两个表作交叉连接时 结果集大小为二者行数之积 该种方式在实际过程中用的很少 6 1多表连接 6 1 4交叉连接 例6 6显示student表和score表的笛卡儿积 分析 student表10数据 score表有24行数据 其结果集应为240行数据 程序代码如下 SELECTstudent studentno sname score FROMstudentCROSSJOINscore 程序执行结果如下 studentnosnamestudentnocoursenousuallyfinal 0822111208韩吟秋0822111208c0510985 0091 000822111208韩吟秋0822111208c0610889 0095 00 0937221508平靖0937221508c0810678 0095 000937221508平靖0937221508c0812378 0089 000937221508平靖0937221508c0817188 0098 00 240行受影响 6 1多表连接 6 1 5连接多个表使用SELECT语句进行连接的表数目没有上限 但在一条SELECT语句中连接的表多于10个 那么数据库就很可能达不到最优化设计 SQLServer2005引擎的执行计划会变得非常繁琐 需要注意 对于3个以上关系表的连接查询 一般遵循下列规则 连接n个表至少需要n 1个连接条件 以避免笛卡儿积的出现 为了缩小结果集 采用多于n 1个连接条件或使用其他条件都是允许的 6 1多表连接 6 1 5连接多个表 例6 7查询08级学生的学号 姓名 课程名 期末成绩及学分 分析 本例要求输出的各项分别存在于student course和score等3个表中 因此至少需要创建2个连接条件 程序代码如下 SELECTstudent studentno sname cname final creditFROMscoreJOINstudentONstudent studentno score studentnoJOINcourseONscore courseno course coursenowheresubstring student studentno 1 2 08 程序执行结果如下 studentnosnamecnamefinalcredit 0822111208韩吟秋C语言91 004 50822111208韩吟秋机械制图95 003 5 0824113307崔岩坚数据结构87 004 00824113307崔岩坚机械制图82 003 5 9行受影响 6 1多表连接 6 1 5连接多个表 例6 8查询计算机学院的老师的教师号 姓名 上课班级号 课程名和学分 分析 本例要求输出的各项分别存在于teacher class和course等3个表中 因为各个表要通过teach class纽带表进行连接 因此至少需要创建3个连接条件 程序代码如下 SELECTteacher teacherno tname class classno cname creditFROMteach classJOINteacherONteach class teacherno teacher teachernoJOINclassONteach class classno class classnoJOINcourseONteach class courseno course coursenowhereteacher department 计算机学院 程序执行结果如下 teachernotnameclassnocnamecredit t05001韩晋升090502数据结构4 0t05003刘元朝090501数据结构4 0t05011海封090502软件工程3 0t05017卢明欣090501数据结构4 0 4行受影响 6 1多表连接 6 1 6合并多个结果集UNION操作符可以将多个SELECT语句的返回结果组合到一个结果集中 该结果集包含联合查询中的所有查询的全部行 下面列出了使用UNION合并两个查询结果集的基本规则 所有查询中的列数和列的顺序必须相同 数据类型必须兼容 UNION操作符基本语法格式如下 SELECT statementUNION all SELECT statement 6 1多表连接 6 1 5连接多个表 例6 9合并结果集示例 分析 虽然2个表的结构不同 但需要合并的两个结果集结构和列的数据类型兼容 程序代码如下 CREATETABLEt1 aint bnchar 4 cnchar 4 INSERTINTOt1VALUES 1 aaa jkl INSERTINTOt1VALUES 2 bbb mno INSERTINTOt1VALUES 3 ccc pqr CREATETABLEt2 anchar 4 bfloat INSERTINTOt2VALUES kkk 1 000 INSERTINTOt2VALUES mmm 3 000 SELECTa bFROMt1UNIONSELECTb aFROMt2 程序执行结果如下 ab 1aaa1kkk2bbb3ccc3mmm 5行受影响 6 2使用子查询 6 2 1子查询介绍子查询就是一个嵌套在SELECT INSERT UPDATE或DELETE语句或其他子查询中的查询子查询可以把一个复杂的查询分解成一系列的逻辑步骤 利用单个语句的组合解决复杂的查询问题 可以使用连接替代子查询 也可以使用子查询替代表达式 SQLServer2005对嵌套查询的处理过程是从内层向外层处理 即先处理最内层的子查询 然后把查询的结果用于其外查询的查询条件 再层层向外求解 最后得出查询结果 6 2使用子查询 6 2 1子查询介绍使用子查询时应该注意如下的事项 子查询需要用括号括起来 当需要返回一个值或一个值列表时 可以利用子查询代替一个表达式 也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能 子查询不能够检索数据类型为varchar max nvarchar max 和varbinary max 的列 子查询中可以再包含子查询 嵌套层数可以达到16层 6 2使用子查询 6 2 2利用子查询做表达式在Transact SQL语句中 可以把子查询的结果当成一个普通的表达式来看待 用在其外查询的选择条件中 此时子查询必须返回一个值或单个列值列表 此时的子查询可以替换WHERE子句中包含IN关键字的表达式 例6 10查询学号为的学生的入学成绩 所有学生的平均入学成绩及该学生成绩与所有学生的平均入学成绩的差 分析 利用子查询求学生的平均入学成绩 作为SELECT语句的输出项表达式 6 2使用子查询 6 2 2利用子查询做表达式 程序代码如下 SELECTstudentno sname point SELECTAVG point FROMstudent AS 平均成绩 point SELECTAVG point FROMstudent AS 分数差值 FROMstudentWHEREstudentno 0828261367 程序运行结果如下 studentnosnamepoint平均成绩分数差值 0828261367赵毓88778899 1行受影响 6 2使用子查询 6 2 2利用子查询做表达式 例6 11获取期末成绩中含有高于93分的学生的学号 姓名 电话和Email 分析 利用操作符IN可以允许指定一个表达式 或常量 集合 可以利用SELECT语句的子查询输出表达式 或常量 集合 程序代码如下 SELECTstudentno sname phone EmailFROMstudentWHEREstudentnoIN SELECTstudentnoFROMscoreWHEREfinal 93 程序运行结果如下 studentnosnamephoneEmail 0822111208韩吟秋15878945612han 0925111109敬秉辰15678945623jing 0937221508平靖12367823453ping 3行受影响 6 2使用子查询 6 2 2利用子查询做表达式 例6 12查询选修课程的多于2门 且期末成绩均在85分以上的学生的学号 姓名 电话和Email 分析 在score表中通过studentno列分组 同时利用WHERE限定85分以上 利用HAVING句检测选修课程的多于2门的学生 符合条件的输出相关选项 程序代码如下 SELECTstudentno sname phone EmailFROMstudentWHEREstudentnoIN SELECTstudentnoFROMscoreWHEREfinal 85GROUPBYstudentnoHAVINGcount 2 程序运行结果如下 studentnosnamephoneEmail 0925111109敬秉辰15678945623jing 0937221508平靖12367823453ping 2行受影响 6 2使用子查询 6 2 3利用子查询关联数据子查询可以作为动态表达式 该表达式可以随着外层查询的每一行的变化而变化 创建关联子查询时 外部查询有多少行 子查询就执行多少次 例6 13查询期末成绩比该选修课程平均期末成绩低的学生的学号 课程号和期末成绩 分析 在本例中 对score表采用别名形式 一个表就相当于2个表 子查询执行时使用的a courseno相当于一个常量 在别名为b的表中根据分组计算平均分 然后与外层查询的值进行比较 该过程很费时间 6 2使用子查询 6 2 3利用子查询关联数据 程序代码如下 SELECTstudentno courseno finalFROMscoreasaWHEREfinal SELECTAVG final FROMscoreasbWHEREa courseno b coursenogroupbycourseno 程序运行结果如下 studentnocoursenofinal 0823210007c0510369 000823210007c0512786 00 0937221508c0812389 00 10行受影响 6 2使用子查询 6 2 4利用子查询生成派生表利用子查询可以生成一个派生表 用于替代FROM子句中的数据源表 派生表可以定义一个别名 即子查询的结果集可以作为外层查询的源表 实际上是在FROM子句中使用子查询 例6 14查询期末成绩高于85分 总评成绩高于90分的学生的学号 课程号和总评成绩 分析 利用子查询过滤出期末成绩高于85分的结果集 以TT命名 然后再对结果集TT中的数据进行查询 6 2使用子查询 6 2 4利用子查询关联数据 程序代码如下 SELECTTT studentno TT courseno TT final 0 8 TT usually 0 2AS 总评成绩 FROM SELECT FROMscoreWHEREfinal 85 ASTTWHERETT final 0 8 TT usually 0 2 90 程序运行结果如下 studentnocourseno总评成绩 0822111208c0610893 800 0937221508c0810691 6000937221508c0817196 000 6行受影响 6 2使用子查询 6 2 5利用子查询修改表数据利用子查询修改表数据就是利用一个嵌套在INSERT UPDATE或DELETE语句的子查询成批的添加 更新和删除表中的数据 INSERT语句中的SELECT子查询可用于将一个或多个其他的表或视图的值添加到表中 使用SELECT子查询可同时插入多行 例6 15创建一个表sc 将score表中学生的相关数据添加到sc表中 并要求计算总评成绩 分析 子查询的选择列表必须与INSERT语句列的列表匹配 如果INSERT语句没有指定列的列表 则选择列表必须与正向其插入的表或视图的列匹配且顺序一致 6 2使用子查询 6 2 3利用子查询关联数据 程序代码如下 CREATETABLEsc studentnonchar 10 notnull coursenonchar 6 notnull totalnumeric 6 2 notnull GOINSERTINTOsc studentno courseno total SELECTstudentno courseno final 0 8 usually 0 2FROMscoreWHEREsubstring studentno 1 2 08 GOSELECT FROMsc 程序运行结果如下 studentnocoursenototal 0822111208c0510989 80 0824113307c0512788 200824113307c0610878 80 9行受影响 6 2使用子查询 6 2 5利用子查询修改表数据UPDATE语句中的SELECT子查询可用于将一个或多个其他的表或视图的值进行更新 使用SELECT子查询可同时更新多行数据 在DELETE语句中利用子查询可以删除符合条件的数据行 实际上是通过将子查询的结果作为删除条件表达式中的一部分 例6 16将sc表中含有总分低于80课程的所有学生总分增加5 分析 利用UPDATE成批修改表数据 可以在WHERE子句的利用子查询实现 6 2使用子查询 6 2 5利用子查询修改表数据 程序代码如下 UPDATEscSETtotal total 1 05WHEREcoursenoin SELECTcoursenoFROMscwheretotal 80 select fromsc 程序运行结果如下 studentnocoursenototal 0822111208c0510989 800822111208c0610898 490822111208c0612772 660823210007c0510375 180823210007c0510986 200824113307c0510382 530824113307c0510980 800824113307c0610882 74 8行受影响 6 2使用子查询 6 2 6EXISTS和NOTEXISTS子句EXISTS是SQL语句中的运算符号 在子查询中 如果存在一些匹配的行 结果为TURE 在执行过程中 一旦查找到第1个匹配的行 查询就结束 NOTEXISTS与EXISTS的工作方式类似 例6 17查询student表中是否存在1990年12月12日以后出生的学生 如果存在 输出学生的学号 姓名 生日和电话 分析 只要存在一行数据符合条件 则WHERE条件就返回TURE 于是输出所有行 6 2使用子查询 6 2 6EXISTS和NOTEXISTS子句 程序代码如下 SELECTstudentno sname birthday phoneFROMstudentWHEREEXISTS SELECT FROMstudentWHEREbirthday 1990 12 12 程序运行结果如下 studentnosnamebirthdayphone 0822111208韩吟秋1989 12 1800 00 00 000158789456120823210007宿致远1988 12 0700 00 00 00012545678998 0935222201夏文斐1990 08 0900 00 00 000159789456450937221508平靖1989 12 1700 00 00 00012367823453 10行受影响 6 3利用游标处理结果集 6 3 1游标的概念SELECT语句一般返回的是包含多条记录的 存放在客户机内存中的结果集 当用户需要访问一个结果集中的某条具体记录时 就需要使用游标功能 SQLServer2005使用英文单词CURSOR来表示游标 使用关键字GLOBAL和LOCAL表示一个游标声明为全局游标和局部游标 6 3利用游标处理结果集 6 3 1游标的概念作为全局游标 一旦被创建就可以在任何位置上访问 当多个不同的过程或函数需要访问和管理同一结果集时 应使用全局游标 局部游标管理起来更容易一些 因而其安全性也相对较高 局部游标可以在一个存储过程 触发器或用户自定义的函数中声明 6 3利用游标处理结果集 6 3 1游标的概念使用游标 CURSOR 的步骤如下 声明游标 在使用游标之前 首先需要声明游标 打开游标 打开一个游标意味着在游标中输入了相关的记录信息 获取记录信息 首先将游标当前指向的记录保存到一个局部变量中 然后游标将自动移向下一条记录 将一条记录读入某个局部变量后 就可以根据需要对其进行处理了 关闭游标 释放游标锁定的记录集 释放游标 释放游标自身所占用的资源 6 3利用游标处理结果集 6 3 2游标的运用1 声明游标声明游标的格式如下 DECLAREcursor nameCURSOR LOCAL GLOBAL FORWARD ONLY SCROLL STATIC KEYSET DYNAMIC FAST FORWARD READ ONLY SCROLL LOCKS OPTIMISTIC TYPE WARNING FORselect statement FORUPDATE OFcolumn name n 6 3利用游标处理结果集 6 3 2游标的运用1 声明游标 例6 18使用STATIC关键字声明全局游标cEmploy 分析 使用STATIC关键字声明全局游标cEmploy 该游标与表student中的所有男生记录相关联 程序代码如下 DECLAREcEmployCURSORSTATICFORSELECTstudentno snmeFROMstudentWHEREsex 男 ORDERBYstudentno 6 3利用游标处理结果集 6 3 2游标的运用2 打开游标使用OPEN语句打开一个以STATIC或KEYSET定义的游标 SQLServer数据会自动在TempDB数据库中创建一个工作表来保存与该游标相关的数据集 设计可以使用全局函数 CURSOR ROWS来指定或获取与游标关联的数据记录行数 使用OPEN语句打开上例中游标cEmploy的代码如下 OPENcEmploy 6 3利用游标处理结果集 6 3 2游标的运用3 使用FETCH获取记录信息获取游标指定的记录的格式如下 FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM cursor name INTO variable name n 6 3利用游标处理结果集 6 3 2游标的运用3 使用FETCH获取记录信息默认情况下 使用OPEN命令打开该游标后 游标不指向结果集中的任何一条记录 此时需要使用FETCH函数将游标定位到记录集中的一条记录上 可以使用FETCHNEXT和FETCHPRIOR移向当前记录的下一条和上一条记录 使用FETCHFIRST和FETCHLAST来移至首条记录或尾记录 FETCH同样可以实现绝对位移和相对位移 此时可以使用FETCHABSOLUTEn或FETCHRELATIVEn 6 3利用游标处理结果集 6 3 2游标的运用3 使用FETCH获取记录信息例6 19使用FETCH访问游标中的记录 分析 使用FETCH命令访问游标中的每条记录 列出cEmploy游标中的所有记录 程序代码如下 DECLARE StudentnoASnchar 10 DECLARE SnameASnchar 8 FETCHFROMcEmployINTO Studentno SnameSET RecCount RecCount 1PRINT 学号 CONVERT nchar 10 Studentno 学生姓名 Sname 6 3利用游标处理结果集 6 3 2游标的运用4 关闭游标关闭游标意味着解锁该游标占用的所有记录集资源关闭一个游标只是意味着释放其所控制的所有数据集资源 但游标自身所占有的系统资源并没有被释放 使用如下命令可以关闭打开的cEmploy游标 CLOSEcEmploy 6 3利用游标处理结果集 6 3 2游标的运用5 释放游标使用DEALLOCATE语句 可以进一步释放游标本身占有的系统资源 合理地使用游标的声明 打开 关闭和释放可以达到有效重复利用游标的目的 使用DEALLOCATE语句彻底释放该游标自身所占有的系统资源 DEALLOCATEcEmploy还可以将游标作为存储过程的输出参数 随着离开该存储过程 离开了代表游标变量的作用域 该游标将被自动地释放 6 3利用游标处理结果集 6 3 2游标的运用例6 20使用游标输出teacher表 分析 通过游标访问SELECT语句的结果集 使用FETCH访问游标中的每条记录 利用 FETCH STATUS测试游标状态 1程序代码如下 USEteachingGO 打印表标题PRINT PRINT 教师信息表 PRINT PRINT PRINT 教师编号 教师姓名 所学专业 教师职称 部门 PRINT 声明变量DECLARE teachernonchar 6 tnamenchar 8 majornchar 10 profnchar 10 departmentnchar 12 2程序代码如下 声明游标DECLAREteacher cursorCURSORFORSELECTteacherno tname major prof departmentFROMteacher 打开游标OPENteacher cursor 提取第一行数据并赋给变量FETCHNEXTFROMteacher cursorINTO teacherno tname major prof department 利用 FETCH STATUS测试游标状态WHILE FETCH STATUS 0 6 3利用游标处理结果集 6 3 2游标的运用例6 20使用游标输出teacher表 分析 通过游标访问SELECT语句的结果集 使用FETCH访问游标中的每条记录 利用 FETCH STATUS测试游标状态 3程序代码如下 打印数据BEGINPRINT teacherno tname major prof department PRINT 提取下一行数据FETCHNEXTFROMteacher cursorINTO teacherno tname major prof departmentEND 关闭和释放游标CLOSEteacher cursorDEALLOCATEteacher cursor本例程序的运行结果如图所示 6 3利用游标处理结果集 6 3 3游标的嵌套SQLServer2005数据库中的游标是可以嵌套使用的 例6 21使用嵌套游标生成报表输出08级每个学生的学号 各科总评成绩和电话分析 本例介绍如何嵌套游标以生成复杂的报表 先定义外层游标student cursor 然后再为每个学生声明内部游标score cursor 输出各人的课程号和总评成绩 代码见教材 6 3利用游标处理结果集 6 3 4查看游标的信息1 利用函数察看游标的状态SQLServer2005服务器为编程人员提供3个用于处理游标的函数 1 CURSOR STATUS函数 CURSOR STATUS函数可以返回一个游标的当前状态SQLServer2005的游标状态包括以下5种情况 如表所示 6 3利用游标处理结果集 6 3 4查看游标的信息1 利用函数察看游标的状态CURSOR STATUS函数的声明形式 CURSOR STATUS 6 3利用游标处理结果集 6 3 4查看游标的信息1 利用函数察看游标的状态 2 CURSOR ROWS函数 CURSOR ROWS可用于返回当前游标最后一次被打开时所含的记录数 可使用该函数来设置 并控制打开一个游标时要包含的记录数 对于一个动态游标 该函数将返回 1 因为对于一个动态游标来说 是不可能准确地获取其全部记录的信息的 该函数的返回值代表最后一次打开游标时所包含的记录数 6 3利用游标处理结果集 6 3 4查看游标的信息1 利用函数察看游标的状态例6 22声明游标 利用函数查看游标对teacher表进行检索的状态 分析 CURSOR STATUS 需要2个参数 如CURSOR STATUS local teacher cursor 而 CURSOR ROWS实际上是一个全局变量 需要声明2个变量记录函数的当前值 代码见教材 6 3利用游标处理结果集 6 3 4查看游标的信息1 利用函数察看游标的状态 3 FETCH STATUS函数 FETCH STATUS函数可以用于检查上一次执行的FETCH语句是否成功 返回值的含义如表所示 6 3利用游标处理结果集 6 3 4查看游标的信息2 利用系统存储过程察看游标属性在声明游标后 可使用下表列出的系统存储过程确定游标的特性 6 3利用游标处理结果集 6 3 4查看游标的信息2 利用系统存储过程察看游标属性例6 23利用sp cursor list系统存储过程显示游标的属性 程序代码如下 USEteachingGO 声明变量DECLARE teachernonchar 6 tnamenchar 8 声明游标DECLAREteacher cursorCURSORFORSELECTteacherno tnameFROMteacher 声明游标变量DECLARE teacher cursorCURSOR 执行sp cursor list系统存储过程EXECteaching dbo sp cursor list cursor return teacher cursorOUTPUT cursor scope 2 程序代码如下 打开游标OPENteacher cursor 提取第一行数据并赋给变量FETCHNEXTFROMteacher cursorINTO teacherno tname 利用 FETCH STATUS测试游标状态WHILE FETCH STATUS 0BEGIN 提取下一行数据FETCHNEXTFROM teacher cursorENDCLOSE teacher cursorDEALLOCATE teacher cursor 关闭和释放游标CLOSEteacher cursorDEALLOCATEteacher cursor程序运行结果如图所示 6 4管理大对象类型数据 大对象 LargeObject 简称LOB 实际上是指那些包含任何数字化信息的数据字段 数字化信息可以是音频 视频 图像以及文档等 这类数据多以大容量文件的形式出现 如声音文件或图像文件等 SQLServer2005能够更高效地存储和检索大型字符 Unicode和二进制数据 包括varchar max nvarchar max 和varbinary max 等大值数据类型 由此可以使用大值数据类型来存储最大为2 31 1个字节的数据 6 4管理大对象类型数据 1 LOB数据类型的种类大对象数据又可分为3种数据类型 BLOB BinaryLargeObject 用于保存长度可变的字符串数据 以字节为量度单位 字符串最长可达2GB BLOB也可以用于保存诸如图像 jpg gif bmp 和声音 wav wma mp3 等多媒体数据 以及保存诸如Word一类的文档 doc txt pdf 数据 6 4管理大对象类型数据 1 LOB数据类型的种类CLOB CharacterLargeObject 类型的字段主要用于保存大容量的文本数据 即经常出现在其他数据库系统 例如Access数据库 中的备注字段 CLOB型的字段没有对长度进行任何限制 CLOB字段中保存的字符串可以是变长的 该字段的度量单位为字节 最大能够保存高达4GB的字符串型文本 DBCLOB Double ByteCharacterLargeObject 用于保存变长的双字节Unicode字符串数据 最多可以保存4GB的字符串数据 例如文档等 6 4管理大对象类型数据 2 大对象数据的使用方法Transact SQL提供了BULKINSERT命令 BULKINSERT命令可以按照用户指定的格式将包括LOB文件在内的数据文件加载到数据表或视图中BULKINSERT命令为用户提供了大量的参数 因此应用起来非常灵活和方便 这里介绍该命令提供的两种主要形式 BULKINSERTTableNameFROMDataFileWITH FIELDTERMINATOR delimeter 或 BULKINSERTTableNameFROMDataFileWITH FORMATFILE format file path 6 4管理大对象类型数据 3 文本文件的导入第1种格式常用于向数据表中导入结构化的文本文件 如Text文件 等 例6 24利用BULKINSERT命令向teaching数据库中的st score数据表添加数据 该表的结构如图所示 6 4管理大对象类型数据 3 文本文件的导入如果需要批量向该表输入记录 可以为此创建一个文本文件test101 txt 且位于D SQLTXT文件夹之下 将其按照如下形式进行排列 1025121107 梁欣那 c05109 62 001035222201 夏文开 c05109 92 001135222201 夏文格 c08171 82 001137221508 平元 c05109 91 001137221508 平冬 c08106 95 001137221508 平钒 c08123 89 00 6 4管理大对象类型数据 3 文本文件的导入此时可以使用BULKINSERT命令将test101 txt中的记录直接插入到数据表st score中 由于在test101 txt文件中 数据字段间以 为分隔符 具体代码如下所示 BULKINSERTst scoreFROM D SQLTXT test101 txt WITH FIELDTERMINATOR 通过上述代码即可将test101 txt中保存的数据填入数据表st score的相应字段中 结果如图所示 6 4管理大对象类型数据 4 图像文件的导入第2种格式中 需要为BULKINSERT命令的WITH参数提供用于说明插入数据方式的格式文件format file path 该格式文件需要提供的信息如表所示 6 4管理大对象类型数据 4 图像文件的导入例6 25在teaching数据库中创建expic表 然后向该表添加新的记录 例6 26使用OPENROWSET命令的方法添加大容量数据 6 5小结 Transact SQL语句功能强大 能够编写高级的SQL应用脚本 利用SELECT语句中的多表连接 子查询等特性 可以利用多个表的数据进行查询并获取结果集 利用游标处理结果集可以获得报表等更友好的输出方式 学习本章后 重点掌握如下内容 多表连接 子查询 游标 大对象类型数据的概念 利用多表连接方式查询数据 利用子查询方式查询数据 利用游标处理结果集的基本过程