1、第7章 T-SQL程序设计、自定义类型、函数和游标,7.1 批处理、脚本、注释与变量 7.2 T-SQL流程控制语句 7.3 用户自定义数据类型 7.4 用户自定义函数 7.5 游标的创建与使用 7.6 实训要求与习题,第7章 T-SQL程序设计、自定义类型、函数和游标,学习目的与要求程序设计流程语句、自定义数据类型、自定义函数和游标是数据库的重要组成部分。程序设计流程语句包括顺序、选择、循环结构的控制语句，是数据库应用程序设计的基础。自定义数据类型和自定义函数可实现对基本数据类型及数据处理函数的扩充，丰富数据库的数据类型及信息处理功能。游标则提供了对数据库中的数据灵活处理的方式，可实现对数据
2、信息进行复杂处理的功能。通过本章学习，读者应掌握如何用流程控制语句设计程序，掌握自定义数据类型和自定义函数的创建、编辑修改与应用，理解游标的意义，掌握游标的创建与使用。,7.1 批处理、脚本、注释与变量,7.1.1、批处理的概念批处理就是一个或多个相关SQL语句的集合，用GO语句作为批处理的结束标志。若没有GO语句，默认所有的语句属于一个批处理。SQL Server的程序发送和编译以批处理为一个程序执行单元。如果一个批处理中任何一个语句有语法错误（例如引用不存在的对象），则整个批处理都不能执行，若只是批处理中的某个语句有执行错误（例如违反约束），则该语句不能执行，其他语句仍可以正常执行。,7.
3、1.1、批处理的概念,编写SQL语句的注意事项： lCREATE DEFAULT创建默认值、CREATE RULE创建规则、CREATE VIEW创建视图、CREATE PROCEDURE创建存储过程、CREATE TRIGGER创建触发器对象等，都必须单独作为一个批处理，不能与其他语句放在一个批处理中。 l不能创建CHECK检查约束后在同一个批处理中马上使用这个约束。 l不能把默认值或规则对象绑定到字段或自定义类型上以后，在同一个批处理中马上使用它们。 l不能在修改字段的名字之后马上在同一个批处理中使用新字段名。 l在一个批处理中定义的局部变量只在该批处理中有效，不能用于其他批处理。 l批处
4、理结束语句GO必须单独一行，可在其后使用注释。如果批处理第一个语句是执行存储过程，则语句开头的EXECUTE关键字可以省略，否则不允许省略，可以使用简写EXEC。,7.1.2、SQL脚本文件,脚本就是包含一个或多个批处理的程序文件。我们可以把创建、维护、使用数据库的有关操作步骤含有一个或多个批处理的代码模块存放到磁盘上作为一个脚本文件，即可以重复使用，也可以在不同计算机上传递。 脚本可以在查询分析器中输入、创建、保存、打开并通过isqlw实用程序执行，也可以在DOS命令行中通过isql或osql实用程序来执行。,7.1.3、SQL语句的注释,注释是程序的说明或暂时禁止使用的语句而不被执行，使用
5、注释可以使程序清晰可读，有助于以后的管理维护。SQL Server支持行注释和块注释两种方式： 1、行内注释格式：- 注释内容以两个减号开始直到本行结束的全部内容都被认为是注释内容。行注释可以单独一行，也可以跟在SQL语句之后，注释内容中还可以有双减号（允许嵌套），双减号之后也可以没有内容。,7.1.3、SQL语句的注释,2、块注释格式： /* 注释内容 */以“/*”开始不论多少行，直到“*/”之间的所有文字都被作为注释内容。块注释可以从一行开头开始，也可以跟在SQL语句之后开始，注释内容中还可以有“/*”字符组合，也可以有单个“*”“/”字符，但不能有“*/”组合（不允许嵌套），中间可以没
6、有注释内容。,7.1.3、SQL语句的注释,【例7-1】注释的使用/*脚本文件名：SQL8-1.sql编写日期：2005年8月14日 星期日功能：查询2006年全部或某个月份销售商品数量和相应的营业员*/USE diannaoxs - 打开数据库GO /* 一个批处理结束 */SELECT 序号, 销售日期, 货名, 数量, 销售员 FROM 销售表2006- WHERE销售日期 beteen 2006/3/1 and 2006/4/1 - 上句去掉注释号可查询3月份销售记录GO,7.1.4、局部变量与全局变量,1、局部变量（用户自定义变量）局部变量一般用于临时存储各种类型的数据，以便在SQL
7、语句之间传递。例如作为循环变量控制循环次数，暂时保存函数或存储过程返回的值，也可以使用table类型代替临时表临时存放一张表的全部数据。 （1）用DECLARE语句声明定义局部变量DECLARE 变量名 数据类型(长度) ，n l 局部变量必须以开头以区别字段名变量。 l 变量名必须符合标识符的构成规则。 l 变量的数据类型可以是系统类型，也可以是用户自定义类型，但不允许是text、ntext、image类型。 l 系统固定长度的数据类型不需要指定长度。,1、局部变量,【例7-2】变量的定义DECLARE name char(6) - 定义name长度为6的字符型DECLARE 家庭住址 va
8、rchar(30) - 定义长度为30的变长字符型DECLARE r int , s decimal(8.4)- 定义r为整型，s为小数总长度8位-其中小数4位,1、局部变量,（2）用SET、SELECT给局部变量赋值语法格式：SET 局部变量=表达式 SELECT 局部变量=表达式 ，n l SET只能给一个变量赋值，而SELECT可以给多个变量赋值。 l 两种格式可以通用，建议首选使用SET，而不推荐使用SELECT语句。 l 表达式中可以包含SELECT语句子查询，但只能是集合函数返回的单值。且必须用圆括号括起来。 l SELECT也可以直接使用查询的单值结果给局部变量赋值。 如：SEL
9、ECT 局部变量=表达式或字段名 FROM 表名 WHERE 条件,1、局部变量,（3）用PRINT、SELECT显示局部变量的值语法格式：PRINT 表达式SELECT 表达式 ，n l 使用PRINT必须有且只能有一个表达式，其值在查询分析器的“消息”子窗口显示。 l 使用SELECT 实际是无数据源检索格式，可以有多个表达式，其结果是按数据表的格式在查询分析器的“网格”子窗口显示，若不指定别名显示标题“（无名列）”。 l 在一个程序脚本中，最好不要混用这两种输出方式，因为“消息”和“网格”子窗口不能同时显示，必须进行切换。,1、局部变量,【例7-3】自定义局部变量的使用USE diann
10、aoxsDECLARE date varchar(15), 日期 varchar(15)SET date= getdate() - 也可使用SELECTSELECT 日期=当前日期为： - 也可使用SETPRINT 日期+ date - 不能用PRINT 日期，datePRINT - 输出空串可以空行SET date=(SELECT MAX(销售日期) FROM 销售表2006 ) PRINT 2006年最后销售日期为：+ dateGO运行结果如图7-1所示。使用PRINT在“消息”子窗口输出表达式的值,1、局部变量,若将例题中的输出显示改用SELECT语句：USE diannaoxsDECL
11、ARE date varchar(15), 日期 varchar(15)SET date=GETDATE() - 也可使用SELECTSELECT 日期=当前日期为： - 也可使用SETSELECT 日期+ date - 显示为一列SET date=(SELECT MAX(销售日期) FROM 销售表2006 )SELECT 2006年最后销售日期为：+ date - 显示为一列SELECT 2006年最后销售日期为： , date - 显示为两列GO运行结果如图7-2所示。使用SELECT在“网格”子窗口输出表达式的值，如果混用PRINT和SELECT这两种输出方式，必须在“消息”和“网格”
12、子窗口中切换查看。,1、局部变量,（4）局部变量的作用域局部变量的作用域是在一个批处理、一个存储过程或一个触发器内，其生命周期从定义开始到它遇到的第一个GO语句或者到存储过程、触发器的结尾结束，即局部变量只在当前的批处理、存储过程、触发器中有效。如果在批处理、存储过程、触发器中使用其他批处理、存储过程、触发器定义的变量，则系统出现错误并提示“必须声明变量”。,2、全局变量（系统定义的无参函数）,全局变量是由系统提供的有确定值的变量，用户不能自己定义全局变量，也不能用SET语句来修改全局变量的值。系统全局变量都是以开头的，全局变量实际上是一些特殊的不需要参数、也不需要加括号调用的函数，可直接返回
13、特定的值。例如：error：其值为最后一次执行错误的SQL语句产生的错误代码max_connections：其值为SQL Server允许多用户同时连接的最大数connections：SQL Server最近一次启动后已连接或尝试连接的次数version ：本地SQL Server服务器的版本信息cursor_rows：已打开游标中当前存在的记录行数FETCH_STATUS得到游标的当前状态其他全局变量或特殊函数可参阅联机丛书。,7.2 T-SQL流程控制语句,流程控制语句是控制程序执行的命令，比如条件控制语句、无条件控制语句、循环语句等，可以实现程序的结构性和逻辑性，以完成比较复杂的操作。
14、7.2.1、BEGINEND语句块 BEGIN语句1语句2END不论多少个语句，放在BEGINEND中间就构成一个独立的语句块，被系统当作一个整体单元来处理。条件的某个分支或循环体语句中，如果要执行两个以上的复合语句，则必须将它们放在BEGINEND中间。,7.2.2、IF/ELSE条件语句,语法格式：IF 逻辑条件表达式语句块1 ELSE语句块2 l IF语句执行时先判断逻辑条件表达式的值（只能取TRUE或FLASE），若为真则执行语句块1，为假则执行语句块2，没有ELSE则直接执行后继语句。 l 条件表达式中可以包含SELECT子查询，但必须用圆括号括起来。 l 语句块1、语句块2可以是单
15、个SQL语句，如果有两个以上语句必须放在BEGINEND语句块中。,7.2.2、IF/ELSE条件语句,【例7-4】根据“商品一览表”如果有库存量小于40的商品则显示该商品清单并提示进货，否则显示：“库存量都超过40，暂时不需要进货”USE diannaoxsGOIF exists( SELECT * FROM 商品一览表 WHERE 库存量40)BEGINSELECT 货号, 货名, 库存量 FROM 商品一览表 WHERE 库存量40SELECT 数量40，需要考虑进货ENDELSE PRINT 库存量都超过40，暂时不需要进货,7.2.2、IF/ELSE条件语句,或者：IF exists
16、( SELECT * FROM 商品一览表WHERE 库存量40)SELECT 货号, 货名, 库存量, 提示= 需要考虑进货FROM 商品一览表 WHERE 库存量40 ELSE PRINT 库存量都超过40，暂时不需要进货,7.2.3、CASE表达式,CASE表达式可以根据不同的条件返回不同的值，CASE不是独立的语句，只用于SQL语句中允许使用表达式的位置。 1、简单CASE END表达式CASE 测试表达式WHEN 常量值1 THEN 结果表达式1 WHEN 常量值2 THEN 结果表达式2 n ELSE 结果表达式n END功能：根据测试表达式的值得到一个对应值。执行过程：先计算测试
17、表达式的值，将测试表达式的值按顺序依次与WHEN指定的各个常量值进行比较： l如果找到了一个相等常量值，则整个CASE表达式取相应THEN指定的结果表达式的值，之后不再比较，跳出CASE END； l如果找不到相等的常量值，则取ELSE指定的结果表达式n； l如果找不到相等的常量值也没有使用ELSE，则返回NULL。,【实例练习7-1】,创建一个完整的“员工信息”视图，按正常习惯显示“员工表”性别为“男”或“女”。以后对员工信息的查询就可以使用该视图。CREATE VIEW 员工信息ASSELECT 员工ID, 姓名, 性别= CASE 性别WHEN 1 THEN 男WHEN 0 THEN 女
18、END , -性别字段结束，逗号表示后面还有字段出生日期= Convert(varchar(12), 出生日期, 111),年龄=year(getdate()-year(出生日期), 部门,工作时间= Convert(varchar(12), 工作时间, 111),工龄=cASt(year(getdate()-year(工作时间)AS varchar(6)+年,照片, 个人简历 FROM 员工表GOSELECT * FROM 员工信息 - 查看“员工信息”视图,2、搜索CASE END表达式,CASEWHEN 条件表达式1 THEN 结果表达式1WHEN条件表达式2 THEN 结果表达式2 n
19、 ELSE 结果表达式n END功能：根据某个条件得到一个对应值。注意：搜索CASE表达式与简单CASE表达式的语法区别是CASE后没有测试表达式，WHEN指定的不是常量值而是条件表达式。执行过程： l 按顺序依次判断WHEN指定条件表达式的值，遇到第一个为真的条件表达式，则整个CASE表达式取对应THEN指定的结果表达式的值，之后不再比较，结束并跳出CASE END。 l 如果找不到为真的条件表达式，则取ELSE指定的结果表达式n。 l如果找不到为真的条件表达式也没有使用ELSE，则返回NULL。,【实例练习7-2】,【实例练习7-2】按库存量多少创建一个“进货提示”视图，根据最低进货价格显
20、示不同的进货提示信息。注意CASE中条件的设置。先用【实例练习5-11】创建的“进货信息视图2006”，创建一个显示商品与进货厂家关系的视图“进货厂家视图”。再将“商品一览表”与“进货厂家视图”通过左外连接得到一个“进货提示视图”。CREATE VIEW 进货厂家视图ASSELECT 货号, 进货厂家, 最低进价=min(单价) FROM 进货信息视图2006 Group By 货号, 进货厂家GO,【实例练习7-2】,CREATE VIEW 进货提示视图 - 创建视图不能用ORDER BYASSELECT s.货号, 货名, 规格, 库存量, 最低进价, 进货厂家, 提示信息= CASEWH
21、EN 库存量=50 THEN 货源充足，不需考虑WHEN 库存量=20 THEN 可以维持，以后再说WHEN 库存量=10 THEN 已经不多，准备进货WHEN 库存量0 THEN 马上缺货，抓紧进货！WHEN 库存量=0 THEN 已经缺货，马上进货！ ENDFROM 商品一览表 s left join 进货厂家视图 j ON s.货号=j.货号GO SELECT * FROM 进货提示视图 ORDER BY 库存量 查看视图中“库存量50”的语句：SELECT * FROM 进货提示视图WHERE 库存量50 ORDER BY 库存量,7.2.4、WAITFOR暂停语句,语句格式：WAIT
22、FOR DELAY 时间 | TIME 时间 语句功能：使程序暂停指定的时间后再继续执行。 l DELAY指定暂停的时间长短相对时间。 l TIME指定暂停到什么时间再重新执行程序绝对时间。 l 时间参数必须是datetime类型的时间部分，格式为“hh:mm:ss”，不能含有日期部分。,7.2.4、WAITFOR暂停语句,【例7-5】使用SELECT语句的无数据源检索演示WAITFOR语句SELECT 程序开始的时间=GETDATE() ,开始的时间秒数=DATEPART(SECOND, GETDATE()GOWAITFOR DELAY 00:00:20 - 延时20秒SELECT 延时以后
23、的时间=GETDATE() ,延时后时间秒数=DATEPART(SECOND, GETDATE()GO,7.2.5、WHILE循环语句,WHILE 逻辑条件表达式BEGIN循环体语句系列 BREAK CONTINUE END执行过程：先计算判断条件表达式的值 l 若条件为真则执行BEGIN END之间的循环体语句系列，执行到END时返回到WHILE再次判断条件表达式的值。 l 若值为假（条件不成立）则直接跳过BEGIN END不执行循环。 l 若在执行循环体时遇到BREAK语句，则无条件跳出BEGIN END。 l 若在执行循环体时遇到CONTINUE语句，则结束本轮循环，不再执行之后的循环体
24、语句，返回到WHILE再次判断条件表达式的值。,7.2.5、WHILE循环语句,【例7-6】计算1+2+3+100的和。DECLARE i Int, sum IntSELECT i=1, sum =0 - 可以使用两个SET语句WHILE i=100SELECT sum=sum+i, i=i+1PRINT sum注意：循环体内只有一个语句可不用BEGIN END，运行结果如图7-8所示。,7.3 用户自定义数据类型,用户自定义数据类型是SQL Server提供的一种使数据库的数据类型与基本数据类型保持一致性的机制，并不是创建一种新的数据类型，它只是在系统基本数据类型的基础上增加一些限制约束，绑
25、定约束对象，以适用某些数据的需要。自定义数据类型就是把基本数据类型、是否允许为空、约束规则、默认值对象等绑定在一起，可直接作为数据表的字段类型。使用自定义数据类型的优点： l 简化数据表对常用规则和默认值的管理。 l 使用SELECT INTO创建复制数据表时，原数据表字段上设置的规则和默认值不能被一起复制，而使用自定义类型则可以将这些规则、默认值与自定义数据类型作为一个整体复制到新的数据表中。,7.3.1、用企业管理器创建编辑自定义数据类型,1、在企业管理器中创建自定义数据类型 【实例练习7-3】创建一种char(5)类型、带有只允许5位数字的“职工编号”规则的数据类型ID_1，代替员工表“
26、员工ID”的字段类型。 （1）在企业管理器根目录中展开创建自定义数据类型的数据库diannaoxs。 （2）选中数据库下的“用户自自定义数据类型”节点，在右键快捷菜单中选择、或在“操作”菜单中选择“新建用户自自定义数据类型”命令，弹出“用户自自定义数据类型属性”对话框，如图7-9所示。,【实例练习7-3】,n 在“名称”文本框输入自定义数据类型的名称，默认ID_1。 n 在“数据类型”下拉列表中选择所用基本数据类型char。 n 在“长度”中设置自定义类型的长度为5。 n 如果允许为空，选中“允许NULL值”复选框，若不选择则不允许为空。 n 在“规则”下拉列表框中选择该类型所要绑定的规则“职
27、工编号”。如果列表框中还没有规则，可定义数据类型后再单独创建规则，右键单击规则“属性”绑定到自定义类型上。 n 在“默认值”下拉列表框中选择该类型所要使用的默认值，若还没有定义默认值，可定义数据类型后再单独创建默认值并绑定到自定义类型上。 （3）单击“确定”按钮，自定义类型创建完毕。,2、将自定义类型用于数据表字段类型,展开数据库的数据表对象，选中员工表，单击右键，选择“设计表”进入表设计器，单击“员工ID”字段，在数据类型下拉列表最后可以找到自定义数据类型“ID_1”，选中它即可。如图7-10所示。,3、查看、编辑、删除自定义数据类型,选中数据库下的“用户自自定义数据类型”节点，在右边详细信
28、息窗口中显示所有用户自定义类型的信息： l 右键单击，选择“属性”可对该自定义类型进行编辑修改。 l 右键单击，选择“删除”，在弹出的“除去对象”对话框中选择“全部除去”即可删除该自定义数据类型。注意：如果用户自自定义数据类型已经被某个数据表使用，则该类型不能删除。,7.3.2、用sp_addtype创建自定义数据类型,EXECUTE sp_addtype typename= 自定义类型名, phystype= 系统数据类型名 ,nulltype= NULL|NOT NULL - 默认NULL, owner= 所有者名简单格式: EXECUTE sp_addtype 自定义类型名, 系统数据类
29、型名 , NULL|NOT NULL|NONULL l 凡包含带有长度“()”的系统类型，必须用引号括起来。 l 用户自定义数据类型的命名必须惟一，不同名字可以定义相同的类型。,7.3.2、用sp_addtype创建自定义数据类型,【例7-7】创建两个用户自定义数据类型表示不超过24个字符的变长字符类型，telelephone电话类型不允许空值，fax传真类型允许空值。EXEC sp_addtype telelephone,varchar(24) ,not nullEXEC sp_addtype fax,varchar(24),nullSQL Server允许将默认值或规则对象绑定到自定义数据
30、类型上，通过与默认值或规则配合，可直接使用自定义数据类型定义数据表字段的数据类型。,【实例练习7-4】,创建供货商表“账户”字段的自定义数据类型“ID_2”，定义为char(15)基本类型、不允许为空、惟一约束，并将默认值“默认账户”、规则对象“仅数字”绑定到该类型上。USE diannaoxsEXEC sp_addtype ID_2 , char(15) , not nullGOEXEC sp_bindefault 默认账户 , ID_2 - 绑定默认值EXEC sp_bindrule 仅数字, ID_2 - 绑定默认值GOALTER TABLE 供货商表 - 修改字段类型ALTER col
31、umn 账户 ID_2 not nullALTER TABLE 供货商表 - 添加字段约束ADD constraint weiyi unique(账户),7.3.3、用sp_droptype删除自定义数据类型,语法格式： EXECUTE sp_droptype 自定义数据类型名如果用户自定义数据类型正被某表中的某列使用，则不能立即删除它，必须首先删除使用该数据类型的表再删除自定义数据类型。,7.4 用户自定义函数,SQL Server 2000允许用户自己定义所需要的函数。SQL Server 2000支持三种用户自定义函数，即标量函数、内嵌表值函数和多语句表值函数。本书只介绍标量函数（单数值
32、函数）。 7.4.1、用CREATE FUNCTION创建自定义函数语法格式：CREATE FUNCTION 所有者名称.函数名 ( 参数名称 AS 数据类型 =默认值 , n ) RETURNS 返回值类型ASBEGIN函数体SQL语句RETURN 数值表达式END,7.4.1、用CREATE FUNCTION创建自定义函数,说明： l 自定义函数必须在当前数据库中定义。 l 函数名：必须符合标识符构成规则，在数据库中名称必须惟一，省略所有者名称默认为系统管理员dbo。 l 参数名称：用局部变量定义的形式参数，用于接收调用函数时传递过来的参数。 l 默认值必须是常量，如果设定了默认值则调用函
33、数时若不提供参数，形式参数自动取默认值。 l RETURNS指定返回值类型，RETURN指定返回值，注意这两个关键字的区别。 l 自定义函数的调用与系统标准函数的调用相同，但必须写出“所有者名称.函数名”并在圆括号内给出参数。,7.4.1、用CREATE FUNCTION创建自定义函数,【例7-8】定义一个根据出生日期求指定年份对应年龄的函数在数据库diannaoxs中创建一个名为“相对年龄”的用户自定义函数，根据员工的“出生日期”和“指定年份”计算员工到指定年份时的年龄。USE diannaoxsGOCREATE FUNCTION 相对年龄 (出生年月 Datetime,defyear in
34、t)RETURNS intASBEGINRETURN defyear-year(出生年月)ENDGO如果在员工表查询到2008年小于30岁的员工，则可使用以下代码：SELECT 姓名,出生日期,到2008的年龄=dbo.相对年龄(出生日期, 2008)FROM 员工表 WHERE dbo.相对年龄(出生日期, 2008)30注意：函数名前的所有者名称dbo不能省略。,【实例练习7-5】,定义一个根据出生日期求当前年龄的函数“当前年龄（）”。CREATE FUNCTION 当前年龄( 日期 Datetime,当前日期 Datetime )RETURNS varchar(6)BEGINRETURN
35、 year(当前日期)-year(日期)ENDGOSELECT 员工编号=员工ID, 姓名, 出生日期=Convert(varchar(12), 出生日期, 111),年龄=dbo.当前年龄(出生日期, getdate()+岁, 部门,工作时间= Convert(varchar(12), 工作时间, 111),工龄=dbo.当前年龄(工作时间, getdate()+年FROM 员工表 WHERE dbo.当前年龄(出生日期, getdate()30注意： u 可以使用“当前年龄”函数求当前工龄。 u 返回值类型为varchar(6)可以与字符串直接连接。 u 在自定义函数内部不允许使用getd
36、ate()系统函数。,【实例练习7-6】,定义求均值（加权平均）平均进货价格的自定义函数商品一览表中的“平均进价”不是简单的平均值，而是求均值（加权平均）得到的，当新进货添加进货记录时，只要给出原表中的“平均进价、库存量”、新的“进价、数量”就可以计算新平均价格。CREATE FUNCTION 平均价格 ( 原平均进价 Smallmoney, 库存 BigInt, 进价 Smallmoney, 数量 Int )RETURNS SmallmoneyBEGINRETURN (原平均进价*库存+进价*数量)/(库存+数量)END输入以上代码，运行结果显示：命令已成功完成。注意：在【实例练习9-5】为
37、进货表2006创建触发器时，我们将使用该函数计算商品一览表的“平均进价”。,7.4.2、用SQL语句修改、删除自定义函数,1、使用ALTER FUNCTION语句修改自定义函数ALTER FUNCTION语句语法与CREATE FUNCTION基本相同，随后我们将在7.4.3节中介绍用企业管理器创建、修改自定义函数，在企业管理器中修改自定义函数时，还可进行语法检查，比使用ALTER FUNCTION语句更方便，因此我们不再介绍ALTER FUNCTION语句的语法。 2、使用DROP FUNCYION语句删除自定义函数语法格式：DROP FUNCYION 所有者名称.函数名称 , n 使用DR
38、OP FUNCYION可一次删除多个自定义函数。使用系统存储过程sp_droptype也可以删除自定义函数。,7.4.3、用企业管理器创建编辑自定义函数,1、在企业管理器中创建自定义函数 【实例练习7-7】定义常用的货币格式函数“货币格式()”。默认的货币类型数据显示4位小数，可以使用系统函数设定2位小数的输出格式，也可定义自定义函数完成该功能。 （1）在企业管理器根目录中展开创建自定义函数的数据库。 （2）选中数据库下的“用户自自定义函数”节点，在右键快捷菜单中或在“操作”菜单中选择“新建用户自自定义函数”命令，弹出“用户自自定义函数属性”对话框，并在“文本”框中给出了自定义函数语句CREA
39、TE FUNCTION的框架。如图7-13所示。,【实例练习7-7】,（3）在“文本”框自定义函数语句CREATE FUNCTION中可填入自定义函数的各个组成部分：包括所有者、函数名、参数列表，返回类型、函数体。如图7-14所示。 （4）单击“检查语法”按钮，检查定义的SQL脚本语法。 （5）语法检查通过后，单击“确定”按钮，保存自定义函数。,【实例练习7-8】,使用自定义函数“货币格式（）”查询销售表2006销售数量大于20的商品信息。SELECT 货名, 数量, 单价=dbo.货币格式(单价), 金额=dbo.货币格式(金额)FROM 销售表2006 WHERE 数量20 查询结果如图7
40、-15所示。,2、在企业管理器中查看、修改或删除自定义函数,选中数据库下的“用户自自定义函数”节点，在右边详细信息窗口中显示所有用户自定义函数的信息： （1）右键单击要修改的自定义函数，选择“属性”命令，弹出创建时使用过的“用户自自定义函数属性”对话框，即可对该自定义函数进行编辑修改。 （2）右键单击要修改的自定义函数，选择“删除”，在弹出的“除去对象”对话框中选择“全部除去”即可删除该自定义数据类型。实际上在企业管理器中创建自定义函数也是书写SQL语句，所以创建自定义函数使用SQL语句比较方便，但修改时在企业管理器中参照原定义要比使用ALTER FUNCTION方便，而且可以进行语法检查。,
41、7.5 游标的创建与使用,7.5.1、游标的概念SELECT语句是对数据表的整行（记录）整列（字段）数据进行操作的，其结果是若干行若干列的“结果集”也是一张“表格”，而不是针对某个特定的数据项进行操作。在我们实际需要中，尤其在应用程序中，并不能把“表格”作为一个整体进行处理，通常使用数组表示同种类型的某一“列”数据，使用结构体或记录类表示多个不同类型的某一“行”数据，使用结构体或记录数组可以表示一张表。我们如何把数据表中的某一行、某一列的一个数据项从一个完整的表中提取出来呢？我们可以通过定义游标实现这一功能。游标的主要用途就是在T-SQL脚本程序、存储过程、触发器中对SELECT语句返回的结果
42、集进行逐行逐字段处理，把一个完整的数据表按行分开，一行一行的逐一提取记录，并从这一记录行中逐一提取各项数据。游标与变量类似，必须先定义后使用。游标的使用过程：定义声明游标 打开游标 从游标中提取记录并分离数据 关闭游标 释放游标。,7.5.2、用DECLARE语句定义游标,1、基于SQL-92标准的DECLARE语句DECLARE 游标名 INSENSITIVE SCROLL CURSOR FOR SELECT语句FOR READ ONLY | UPDATA OF 字段名 , n 说明： lINSENSITIVE定义游标时自动在系统tempdb数据库中创建一个临时表存储游标使用的数据，在游标使
43、用过程中基表数据改变不影响游标的数据，但该游标的数据不允许修改。省略该项表示游标直接从基表中取得数据，即游标使用的数据将随基表数据的变化而动态变化。 lSCROLL表示该游标可以在FETCH语句中任意指定数据的提取方式，省略该项表示该游标仅支持NEXT顺序提取方式。 lSELECT指定该游标使用的结果集，不允许使用COMPUTE或INTO子句。 lREAD ONLY表示只读，该游标中的数据不允许修改，即不允许在UPDATE或DELETE语句中引用该游标。 lUPDATA OF 字段名 , n 表示在该游标内可以更新基本表的指定字段，省略字段名列表表示可以更新所有字段。,7.5.2、用DECLA
44、RE语句定义游标,2、SQL Server 2000 T-SQL中的扩展DECLARE语句DECLARE 游标名 CURSOR FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD READ_ONLY|OPTIMISTIC TYPE_WARNING FOR SELECT语句 FOR UPDATE OF 字段名 , n 说明： l FORWARD_ONLY指定该游标为顺序结果集，只能用NEXT向后方式顺序提取记录。 l SCROLL指定该游标为滚动结果集，可以使用向前、向后、定位方式提取记录。 l STATIC与INSENSITIVE 含义
45、相同，在系统tempdb数据库中创建临时表存储游标使用的数据，即游标不会随基本表内容而变化，同时也无法通过游标来更新基本表。,7.5.2、用DECLARE语句定义游标,l KEYSET 指定游标中列的顺序是固定的，并且在tempdb内建立一个KEYSET表，基本表数据修改时能反映到游标中。如果基本表添加符合游标的新记录时该游标无法读取(但其他语句使用WHERE CURRENT OF子句可对游标中新添加的记录数据进行修改)。如果游标中的一行被删除掉，则用游标提取时FETCH_STATUS的返回值为-2。 l DYNAMIC 指定游标中的数据将随基本表而变化，但需要大量的游标资源。 l FAST_
46、FORWARD指定FORWARD_ONLY而且READ_ONLY类型游标。使用FAST_FORWARD参数则不能同时使用FORWARD_ONLY、SCROLL、OPTIMISTIC或FOR UPDATE参数。 l OPTIMISTIC指明若游标中的数据已发生变化，则对游标数据进行更新或删除时可能会导致失败。 l TYPE_WARNING 指定若游标中的数据类型被修改成其他类型时，给客户端发送警告。 l,7.5.2、用DECLARE语句定义游标,l 若省略FORWARD_ONLY|SCROLL则不使用STATIC、KEYSET和DYNAMIC时默认为FORWARD_ONLY游标，使用STATIC
47、、KEYSET或DYNAMIC之一则默认为SCROLL游标。 l 若省略READ_ONLY|OPTIMISTIC参数，则默认选项为：n 如果未使用UPDATE参数不支持更新，则游标为READ_ONLY；n STATIC和FAST_FORWARD类型游标默认为READ_ONLY；n DYNAMIC和KEYSET类型游标默认为OPTIMISTIC。注意： u 不能将SQL-92游标语法与MS SQL Server游标的扩展语法混合使用。 u 若在CURSOR前使用了SCROLL或INSENSITIVE则为SQL-92游标语法，则不能再在CURSOR和FOR SELECT语句之间使用任何保留字，反之
48、同理。,7.5.3、用OPEN语句打开游标,语法格式：OPEN GLOBAL 游标名语句功能：打开指定的游标如果全局游标与局部游标同名时，GLOBAL表示打开全局游标，省略为打开局部游标。用DECLARE定义的游标，必须打开以后才能对游标中的结果集进行处理。就是说DECLARE只声明了游标的结构格式，打开游标才执行SELECT语句得到游标中的结果集。打开游标后，可以使用全局变量（系统的无参函数）ERROR判断该游标是否打开成功。ERROR为0则打开成功，否则打开失败。使用CURSOR_ROWS可得到打开游标中当前存在的记录行数，其返回值为：n 0：表示无符合条件的记录或该游标已经关闭或已释放n -1：表示该游标为动态的，记录行经常变动无法确定n n： 正整数n表示指定结果集已从表中全部读入，总共n条记录n -m：表示指定的结果集还没全部读入，目前游标中有m条记录,
