第第9章章 Transact-SQL程序设计程序设计 震头闯矮尝樱琢敲贪嫂离巩镁唇二囊绣苗单客乃惶辉壁涯晨笼爷蓉完炽炸第9章TransactSQL程序设计第9章TransactSQL程序设计本章内容本章内容9.1 数据与表达式数据与表达式9.2 函函 数数9.3 程序控制流语句程序控制流语句9.4 游标管理与应用游标管理与应用妹构宇膏媳汽拈淫恍玛猿愁铱墩毁快拇主奸汀度藕栗暑挽淖拱芳磺吭兽坎第9章TransactSQL程序设计第9章TransactSQL程序设计9.1 数据与表达式数据与表达式9.1.1 用户定义数据类型用户定义数据类型9.1.2 常量与变量常量与变量9.1.3 运算符与表达式运算符与表达式祥陆轩妊嗡晌熟傈丝严稠屏贬洒静较甭幂亮瓣服嘘赔上灼伊阳滁斧猾尖底第9章TransactSQL程序设计第9章TransactSQL程序设计9.1 9.1 数据与表达式数据与表达式9.1.1 用户定义数据类型用户定义数据类型1使用系统存储过程来创建用户定义数据类使用系统存储过程来创建用户定义数据类型型,命令格式如下：命令格式如下：sp_addtype typename= type, phystype = system_data_type , nulltype = null_type , owner = owner_name 宋逝麻眯哪敬云哗嘶畅睦贮个臆瑟戌稗磕攫只雁鞘哦肯铂愁斤侣裴对择仪第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.1 9.1.1 用户定义数据类型用户定义数据类型9.1.1 用户定义数据类型用户定义数据类型例如，为例如，为Sales数据库创建数据库创建个不允许为个不允许为NULL值值的的test_add用户定义数据类型。用户定义数据类型。 USE SalesGOEXEC sp_addtype test_add,Varchar(10),NOT NULLGOn此后，此后，test_add可用为数据列或变量的数据类型。可用为数据列或变量的数据类型。复苑棺贾哨页享贾陋滇影遭寅橡乖团烘泪择碌蒂滋悸铸形封沫菠怀考型僵第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.1 9.1.1 用户定义数据类型用户定义数据类型9.1.1 用户定义数据类型用户定义数据类型n2使用使用SQL Server管理平台创管理平台创建用户定义数据类型建用户定义数据类型n在在SQL Server管理平台管理平台中，为中，为Sales数据库创建数据库创建个不允许个不允许NULL值的值的test_add用户定义数用户定义数据类型，操作步骤如下。据类型，操作步骤如下。（1）选择Sales数据库，并展开，然后选择“可编程性”节点并展开，选择“类型”并展开，在“用户定义数据类型”节点上单击右键，选择“新建用户定义数据类型”命令。弹出 “新建用户定义数据类型”窗口。 （2）在“用户定义的数据类型属性”对话框中的名称文本框内输入test_add，在“数据类型”下拉列表框中，选择char，在“长度”文本框中输入10， 选中“允许空值”复选框。（3）单击“确定”按钮完成创建用户自定义数据类型。 犹榨扣韶洒平啡佬阻辫兢椰四垮帽肋呼蕊栗遣沿池氰殷序弧林宛秋荆棍词第9章TransactSQL程序设计第9章TransactSQL程序设计9.1 9.1 数据与表达式数据与表达式9.1.2 常量与变量常量与变量n在程序运行中保持常值的数据，即程序本在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。序中经常直接使用文字符号表示。n相应地，在程序运行过程中可以改变其值相应地，在程序运行过程中可以改变其值的数据，称为变量。的数据，称为变量。 期腹滨惋茧喉窝窿怕埔褒腊涪亦是钧驯吟爆卤抗谜栖娶贡罢焕镭袱并抱饿第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 9.1.2 常量与变量常量与变量9.1.2 常量与变量常量与变量n1常量常量n常量是表示特定数据值的符号，其格式取决于其数据类型常量是表示特定数据值的符号，其格式取决于其数据类型(1)字符串和二进制常量字符串和二进制常量字符串常量括在单引号内并包含字母数字字符字符串常量括在单引号内并包含字母数字字符(a-z、A-Z 和和 0-9)以及特殊字符，如感叹号以及特殊字符，如感叹号(!)、at 符符()和和数字号数字号(#)。例如：例如：Cincinnati、OBrien、Process X is 50% complete.、“OBrien”为字符串常量。为字符串常量。二进制常量具有前辍二进制常量具有前辍0x并且是十六进制数字字符串，并且是十六进制数字字符串，它们不使用引号。例如它们不使用引号。例如0xAE、0x12Ef、0x69048AEFDD010E、0x(空串空串)为二进制常量。为二进制常量。敏苯兄椽葛臻只坯难在尤糕蛾怪耪嘱密瘁驮卜纬县生泳皇娩靶僻世担家妻第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n(2)日期日期/时间常量时间常量ndatetime常量使用特定格式的字符日期值表示，用单引常量使用特定格式的字符日期值表示，用单引号括起来。号括起来。n输入时，可以使用输入时，可以使用“/”、“.”、“-”作日期作日期/时间常时间常量的分隔符。量的分隔符。 输入格式datetime值Smalldatetime值Sep 3, 2008 1:34:34.1222008-09-03 01:34:34.1232008-09-03 01:35:009/3/2008 1PM2008-09-03 13:00:00.0002008-09-03 13:00:009.3.2008 13:002008-09-03 13:00:00.0002008-09-03 13:00:0013:25:191900-01-01 13:25:19.0001900-01-01 13:25:009/3/20082008-09-03 00:00:00.0002008-09-03 00:00:009.1.2 9.1.2 常量与变量常量与变量英豢佛伏玲燕绍泊峪华卜渣玻捌昆苑涩僳收邓挂壕属粒壶蹬核寝纯函徐破第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量(3)数值常量数值常量整型常量由没有用引号括起来且不含小数点的一串数字表整型常量由没有用引号括起来且不含小数点的一串数字表示。例如，示。例如，1894、2为整型常量。为整型常量。浮点常量主要采用科学记数法表示，例如，浮点常量主要采用科学记数法表示，例如，101.5E5、0.5E-2为浮点常量。为浮点常量。精确数值常量由没有用引号括起来且包含小数点的一串数精确数值常量由没有用引号括起来且包含小数点的一串数字表示。例如，字表示。例如，1894.1204、2.0为精确数值常量。为精确数值常量。货币常量是以货币常量是以“$”为前缀的一个整型或实型常量数据，为前缀的一个整型或实型常量数据，不使用引号。例如，不使用引号。例如，$12.5、$542023.14为货币常量。为货币常量。uniqueidentifier常量是表示全局惟一标识符常量是表示全局惟一标识符GUID值的字值的字符串。可以使用字符或二进制字符串格式指定。符串。可以使用字符或二进制字符串格式指定。9.1.2 9.1.2 常量与变量常量与变量钨毖丈爬巷矣重急渠低砸妆扭牧积仁煮纽吟筹侩侨铅搜皑骑施赂瑞遵源表第9章TransactSQL程序设计第9章TransactSQL程序设计n(4)逻辑数据常量逻辑数据常量n逻辑数据常量使用数字逻辑数据常量使用数字0或或1表示，并且不使用引表示，并且不使用引号。非号。非0的数字当作的数字当作1处理。处理。n(5)空值空值n在数据列定义之后，还需确定该列是否允许空值在数据列定义之后，还需确定该列是否允许空值(NULL)。允许空值意味着用户在向表中插入数据。允许空值意味着用户在向表中插入数据时可以忽略该列值。空值可以表示整型、实型、时可以忽略该列值。空值可以表示整型、实型、字符型数据。字符型数据。9.1.2 常量与变量常量与变量9.1.2 9.1.2 常量与变量常量与变量溉签备监曰抄罩峭峻酱岭悟兰痢毡圆完覆仑妈误耘傲讥盒坯绎咳射绥芯湾第9章TransactSQL程序设计第9章TransactSQL程序设计n2变量变量n变量用于临时存放数据，变量中的数据随着程序变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。的运行而变化，变量有名字与数据类型两个属性。n变量的命名使用常规标识符，即以字母、下划线变量的命名使用常规标识符，即以字母、下划线(_)、at符号符号()、数字符号、数字符号(#)开头，后续字母、开头，后续字母、数字、数字、at符号、美元符号符号、美元符号($)、下划线的字符序列。、下划线的字符序列。不允许嵌入空格或其他特殊字符。不允许嵌入空格或其他特殊字符。9.1.2 常量与变量常量与变量9.1.2 9.1.2 常量与变量常量与变量觉慨厩拌胖略夷姚酌问绳荐侩喷凿质恫蔼职损藐拷匡藕臆稳萍疵峭椭腋翼第9章TransactSQL程序设计第9章TransactSQL程序设计n全局变量和局部变量全局变量和局部变量n全全局局变变量量由由系系统统定定义义并并维维护护，通通过过在在名名称称前面加前面加“”符号符号n局部变量的首字母为单个局部变量的首字母为单个“”。9.1.2 常量与变量常量与变量9.1.2 9.1.2 常量与变量常量与变量取苦捐稠琐震取耪购雍找玉侈南垃锥灾四硫元披剔焦巫詹劫举萤从倾官孔第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n(1)局部变量局部变量n局部变量使用局部变量使用DECLARE语句定义语句定义DECLARE local_variable data_type ,.nn变量名最大长度为变量名最大长度为30个字符。一条个字符。一条DECLARE语语句可以定义多个变量，各变量之间使用逗号隔开。句可以定义多个变量，各变量之间使用逗号隔开。n例如例如DECLARE name varchar(30),type int9.1.2 9.1.2 常量与变量常量与变量糟预籍茨倦妄卸娘峪品夏涌速忽怀淫飘蛙戒弘产怪阴惑扮芯悲显奄戒哟嘲第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量局部变量的赋值局部变量的赋值用用SELECT为局部变量赋值为局部变量赋值SELECT variable_name=expression , nFROM WHERE n例如例如DECLARE int_var intSELECT int_var =12 /*给给int_var赋值赋值*/SELECT int_var /*将将int_var的值输出到屏幕上的值输出到屏幕上*/9.1.2 9.1.2 常量与变量常量与变量贞肋捏谋疏讹到蚂寄右吭匀静绣吭择舀嫂蜜慨馆珊柞酪队啃贾文誉伎查犀第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n在一条语句中可以同时对几个变量进行赋值在一条语句中可以同时对几个变量进行赋值n例如例如DECLARE LastName char(8),Firstname char(8),BirthDate datetimeSELECT LastName=Smith,Firstname=David,BirthDate=1985-2-20SELECT LastName,Firstname,BirthDaten局部变量没有被赋值前，其值是局部变量没有被赋值前，其值是NULL，若要在程序中引，若要在程序中引用它，必须先赋值。用它，必须先赋值。 9.1.2 9.1.2 常量与变量常量与变量医爪事尖扮荤攒燎匣丘镀呕脊赶鸡餐乙泪掷译糕弥唬赂契烹幅炙吴纹殿窥第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量例例9-1 使用使用SELECT语句从语句从customer表中检索出顾客编号为表中检索出顾客编号为“C0002”的行，再将顾客的名字赋给变量的行，再将顾客的名字赋给变量customer。DECLARE customer varchar(40),curdate datetimeSELECT customer=customer_name,curdate=getdate()FROM customerWHERE customer_id=C00029.1.2 9.1.2 常量与变量常量与变量遂幽告潦狸伤渣坠消杖暮祸汽院峭善荚游旦鳖陋壳佳烹涛缺欲燎毗压斜淫第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量利用利用UPDATE为局部变量赋值为局部变量赋值例例9-2 将将sell_order表中的表中的transporter_id列值为列值为“T001”、goods_id列值为列值为“G00003”的的order_num列的值赋给局部列的值赋给局部变量变量order_num。DECLARE order_num floatUPDATE sell_orderSET order_num=order_num*2WHERE transporter_id=T001 AND goods_id=G000039.1.2 9.1.2 常量与变量常量与变量刀绰逞碘摘秩域扦拉譬彝拿赐汁宏盎股憨坎滩仅轨阿倪侩厌乳赤丸观脾怜第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n 用用SET给局部变量赋值给局部变量赋值nSET语句格式为：语句格式为：SET local_variable=expressionn使用使用SET初始化变量的方法与初始化变量的方法与SELECT语句相同，语句相同，但一个但一个SET语句只能为一个变量赋值。语句只能为一个变量赋值。例例9-3 计算计算employee表的记录数并赋值给局部变量表的记录数并赋值给局部变量rows。DECLARE rows intSET rows=(SELECT COUNT(*) FROM employee)SELECT rows9.1.2 9.1.2 常量与变量常量与变量赤诽切傻聊晰广惊旨臀湛颂举拒炸煽茅汤薛蝉帝憾碍粒喇使基硅奏础怠毫第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n(2)全局变量全局变量n全局变量通常被服务器用来跟踪服务器范围和特定会话期全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。间的信息，不能显式地被赋值或声明。n全局变量不能由用户定义，也不能被应用程序用来在处理全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。器之间交叉传递信息。 9.1.2 9.1.2 常量与变量常量与变量蜕庐祝呼桂浇菜恋独轨烂庇寅浆咋铜决秘式蹦娱鄙洒擞霍秧咸阅亏护水瞻第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n rowcountnrowcount存储前一条命令影响到的记录总数，除了存储前一条命令影响到的记录总数，除了DECLARE语句语句之外，其他任何语句都可以影响之外，其他任何语句都可以影响rowcount的值。的值。n例如例如DECLARE rows intSELECT rows=rowcount9.1.2 9.1.2 常量与变量常量与变量吵职逐起获暂节争弘薛给爵转哥啊随羊角忠粕健镊呕辫卓昔霍职学厄伸鸭第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量n errorn如果如果error为非为非0值，则表明执行过程中产生了错误，此值，则表明执行过程中产生了错误，此时应当在程序中采取相应的措施加以处理。时应当在程序中采取相应的措施加以处理。nerror的值与的值与rowcount一样，会随着每一条一样，会随着每一条SQL Server语句的变化而改变。语句的变化而改变。例例9-4 使服务器产生服务，并用显示错误号。使服务器产生服务，并用显示错误号。raiserror(miscellaneous error message,16,1) /*产生一个错误产生一个错误*/if error0SELECT error as last errorn运行结果：运行结果：消息 50000，级别 16，状态 1，行 1miscellaneous error messagelast error0 9.1.2 9.1.2 常量与变量常量与变量恫型谎宙溉檀朗姐菜梧闺蛮员契俱杏返吐抡猛荆鼠剁颓烫媳伯材腕篇乍垦第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量例例9-5 捕捉例捕捉例9-4中服务器产生的错误号，并显示出来。中服务器产生的错误号，并显示出来。DECLARE my_error intRAISERROR(miscellaneous error message,16,1)SELECT my_error=errorIF my_error0 SELECT my_error as last error运行结果：运行结果：消息消息 50000，级别，级别 16，状态，状态 1，行，行 2miscellaneous error messagelast error500009.1.2 9.1.2 常量与变量常量与变量炔背她盒只嫉醋习涟孵攒茫睹勺酌慕莎糊淘体讣搁佯函城赎酉三獭笨距呢第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量例如：例如：9.1.2 9.1.2 常量与变量常量与变量钻善柿恢剔孜瘁维旁蚊殃国芍路顷蕊鹅纤梁带押戒转跨羔湃舒冻轻桐应主第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量 trancounttrancount记录当前的事务数量，当某个事务当前并记录当前的事务数量，当某个事务当前并没有结束会话过程时，没有结束会话过程时，trancount的值大于的值大于0。 versionversion的值代表服务器的当前版本和当前操作系统的值代表服务器的当前版本和当前操作系统版本，是版本，是SQL Server中一项较实用的技术支持，通常中一项较实用的技术支持，通常对识别网络中某个未命名的服务器时非常有用。对识别网络中某个未命名的服务器时非常有用。 spidspid返回当前用户进程的服务器进程返回当前用户进程的服务器进程ID，可以用来，可以用来识别识别sp_who输出中的当前用户进程。输出中的当前用户进程。9.1.2 9.1.2 常量与变量常量与变量巍屈茧留了粟零肪赐米替喇坪汇呐居馅邦杭呀珠缴旋掐袖隔辐尧鞘昆逆喀第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.2 常量与变量常量与变量例例9-6 使用使用spid返回当前用户进程的返回当前用户进程的ID。SELECT spid as ID,SYSTEM_USER AS Login Name,USER AS User Namen运行结果：运行结果：ID Login Name User Name53 sa dbo9.1.2 9.1.2 常量与变量常量与变量朝秧嗣差刹鳃例街袒噪肺攫汀诡译系炕睫舵际势埂槛荣瘩级沿迂淆翅档震第9章TransactSQL程序设计第9章TransactSQL程序设计9.1 9.1 数据与表达式数据与表达式9.1.3 运算符与表达式运算符与表达式n运算符用来执行数据列之间的数学运算或比较操作。运算符用来执行数据列之间的数学运算或比较操作。n表达式是符号与运算符的组合。表达式是符号与运算符的组合。n简单的表达式可以是一个常量、变量、列或函数，复杂表简单的表达式可以是一个常量、变量、列或函数，复杂表达式是由运算符连接一个或多个简单表达式。达式是由运算符连接一个或多个简单表达式。兰肿梢制优电灭侵里料宽市位烃菜纯池岛剩裤港术忿疵料鹊蛇馁商新悬缴第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n1. 算术运算符与表达式算术运算符与表达式n算术运算符用于数值型列或变量间的算术运算。算术运算算术运算符用于数值型列或变量间的算术运算。算术运算符包括加符包括加(+)、减、减(-)、乘、乘(*)、除、除(/)和取模和取模(%)运算等。运算等。例例9-9 使用使用“+”将将goods表中高于表中高于9000的商品价格增加的商品价格增加15元：元：SELECT goods_name,unit_price,(unit_price+15) AS nowprice FROM goodsWHERE unit_price9000n运行结果如图所示。运行结果如图所示。琅片舌例资店孤蓬寇廷抡快犁野售际僚萨鞠稚晾鼓陋绞赂枷螟筐拌挑懈矫第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n2. 位运算符与表达式位运算符与表达式n位运算符用以对数据进行按位与位运算符用以对数据进行按位与()、或、或(|)、异或、异或()、求反求反()等运算。等运算。& 运算只有当两个表达式中的两个位值都为运算只有当两个表达式中的两个位值都为1时，结果时，结果中的位才被设置为中的位才被设置为1，否则结果中的位被设置为，否则结果中的位被设置为0。| 运算时，如果在两个表达式的任一位为运算时，如果在两个表达式的任一位为1或者两个位均或者两个位均为为1，那么结果的对应位被设置为，那么结果的对应位被设置为1；如果表达式中的；如果表达式中的两个位都不为两个位都不为1，则结果中该位的值被设置为，则结果中该位的值被设置为0。 运算时，如果在两个表达式中，只有一位的值为运算时，如果在两个表达式中，只有一位的值为1，则结果中位的值被设置为则结果中位的值被设置为1；如果两个位的值都为；如果两个位的值都为0或或者都为者都为1，则结果中该位的值被清除为，则结果中该位的值被清除为0。夺今钨卖式骗肌浇狼糖柬昭扮再唯瘸棒龟蜗笋锨殉痉拧寥闷坯绷皖瑶笋簇第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n例如，例如，170与与75进行进行&运算运算n先将先将170和和75转换为二进制数转换为二进制数0000 0000 1010 1010和和0000 0000 0100 1011，再进行，再进行&运算的结果是运算的结果是0000 0000 0000 1010，即十进制数，即十进制数10。n同样，表达式同样，表达式52，1，5|2的运算结果为：的运算结果为：7，-2，7。四等显碰甜撒足堕个仿却施阳铲殊而环狞船贵抑蒜辑君失详髓姿亭启觅耿第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n3. 比较运算符与表达式比较运算符与表达式n比较运算符用来比较两个表达式的值是否相同，比较运算符用来比较两个表达式的值是否相同，可用于字符、数字或日期数据。可用于字符、数字或日期数据。nSQL Server中的比较运算符有大于中的比较运算符有大于()、小于、小于(=)、小于等于、小于等于(=)和不等于和不等于(!=)等，等，比较运算返回布尔值，通常出现在条件表达式中。比较运算返回布尔值，通常出现在条件表达式中。n比较运算符的结果为布尔数据类型，其值为比较运算符的结果为布尔数据类型，其值为TRUE、FALSE及及UNKNOWN。n例如，表达式例如，表达式2=3的运算结果为的运算结果为FALSE。梳涉贡施私茫您香魔回云貉海恭评亢歼臂嫩臀棱茨机珠候救睫春联麓肇爬第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n4. 逻辑运算符与表达式逻辑运算符与表达式n逻辑运算符与逻辑运算符与(AND)、或、或(OR)、非、非(NOT)等，用于对某个条等，用于对某个条件进行测试，以获得其真实情况。件进行测试，以获得其真实情况。n逻辑运算符和比较运算符一样，返回逻辑运算符和比较运算符一样，返回TRUE或或FALSE的布的布尔数据值。尔数据值。源恬园铬诫返或菇惩嗅助者畦菲兑祁惰搂供要释秤敏滓野邓像钧亥及蛛晚第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式运算符含 义AND如果两个布尔表达式都为TRUE，那么结果为TRUE。OR如果两个布尔表达式中的一个为TRUE，那么结果就为TRUE。NOT对任何其他布尔运算符的值取反。LIKE如果操作数与一种模式相匹配，那么值为TRUE。IN如果操作数等于表达式列表中的一个，那么值为TRUE。ALL如果一系列的比较都为TRUE，那么值为TRUE。ANY如果一系列的比较中任何一个为TRUE，那么值为TRUE。BETWEEN如果操作数在某个范围之内，那么值为TRUE。EXISTS如果子查询包含一些行，那么值为TRUE。表表9-5 逻辑运算符逻辑运算符姑惨腊昌蠕跋仪愤诬琵咯本肯谐厢凋突猾阉躲疮薛羊打臆坷鸿喇兼钓掘辐第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式n逻辑运算符通常和比较运算一起构成更为复杂的表达式。逻辑运算符通常和比较运算一起构成更为复杂的表达式。逻辑运算符的操作数都只能是布尔型数据。逻辑运算符的操作数都只能是布尔型数据。例如，在表例如，在表employee中查找中查找1973年以前与年以前与1980年以后出生的年以后出生的男员工的表达式为：男员工的表达式为：(year(birth_date)1980) AND sex=男男9.1.3 运算符与表达式运算符与表达式钳巫揣丫蔑臃糊靡岿蒋魂塞耶溪挤椒嘱箩囱完效疑颖永嘿娜扭伸澎厅诲椎第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式nLIKE运算符运算符n确定给定的字符串是否与指定的模式匹配，通常只限于字确定给定的字符串是否与指定的模式匹配，通常只限于字符数据类型。符数据类型。LIKE的通配符如下表的通配符如下表运算符描 述示 例%包含零个或多个字符的任意字符串。address LIKE %公司% 将查找地址任意位置包含公司的所有职员。_下划线，对应任何单个字符。employee_name LIKE _海燕 将查找以“海燕”结尾的所有6个字符的名字。 指定范围(a-f )或集合(abcdef )中的任何单个字符。employee_name LIKE 张李王海燕 将查找张海燕、李海燕、王海燕等。不属于指定范围(a-f )或集合(abcdef )的任何单个字符。employee_name LIKE 张李海燕 将查找不姓张、李的名为海燕的职员。幂涣碾荔氛径员赤禾唁枉眺贿到详抹驰辗釉刑份裹饲用胎添雍闹章拆尸橙第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式例如例如,查找所有姓查找所有姓“钱钱”的员工及住址的员工及住址SELECT employee_name, addressFROM employeeWHERE employee_name LIKE 钱钱%焙导柜香擅浓随贵翔晴册缝疵加谬笑祈郧鹤凋骸芬腿映床蝉趴韭忱否鸥罐第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n5连接运算符与表达式连接运算符与表达式n连接运算符连接运算符(+)用于两个字符串数据的连接，通常也称为用于两个字符串数据的连接，通常也称为字符串运算符。字符串运算符。n在在SQL Server中，对字符串的其他操作通过字符串函数进中，对字符串的其他操作通过字符串函数进行。字符串连接运算符的操作数类型有行。字符串连接运算符的操作数类型有char、varchar和和text等。等。n例如，例如，Dr.+Computer的运算结果的运算结果:Dr. Computer莆捏蓄却蠕莽霓测毛澜增姆轮槛阑饥我指寓考岂喉鸦沂何可养瓦只栏拟狭第9章TransactSQL程序设计第9章TransactSQL程序设计9.1.3 9.1.3 运算符与表达式运算符与表达式9.1.3 运算符与表达式运算符与表达式n6运算符的优先级别运算符的优先级别nSQL Server中各种运算符的优先顺序如下：中各种运算符的优先顺序如下：() | *、/、% +、- NOT AND OR排在前面的运算符的优先级高于其后的运算符。在一个表排在前面的运算符的优先级高于其后的运算符。在一个表达式中，先计算优先级较高的运算，后计算优先级低的运达式中，先计算优先级较高的运算，后计算优先级低的运算，相同优先级的运算按自左向右的顺序依次进行。算，相同优先级的运算按自左向右的顺序依次进行。 牢积祟贤同艇摄辣舔树泽佛霞慧痊象缠缆最褪缩蹄高备叶泡斌烈凰菲虐亿第9章TransactSQL程序设计第9章TransactSQL程序设计9.2 函函 数数9.2.1 常用函数常用函数9.2.2 用户定义函数用户定义函数喘两奸爬讥猾祖欣箔怔件拾貉量馋孪叶势嚎返佑标驱灼践膊尾含禄彪降浩第9章TransactSQL程序设计第9章TransactSQL程序设计9.2 9.2 函函 数数9.2.1 常用函数常用函数n函数是函数是组编译好的组编译好的Transact-SQL语句，它们可以带一个语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。值集合，或执行一些操作。n函数能够重复执行一些操作，从而避免不断重写代码。函数能够重复执行一些操作，从而避免不断重写代码。nSQL Server 2005支持两种函数类型：支持两种函数类型：(1) 内置函数内置函数:是一组预定义的函数，是是一组预定义的函数，是Transact-SQL语语言的一部分，按言的一部分，按Transact-SQL参考中定义的方式运行参考中定义的方式运行且不能修改。且不能修改。(2) 用户定义函数用户定义函数:由用户定义的由用户定义的Transact-SQL函数。它函数。它将频繁执行的功能语句块封装到一个命名实体中，该将频繁执行的功能语句块封装到一个命名实体中，该实体可以由实体可以由Transact-SQL语句调用。语句调用。喉属炭耘龄魂甸极独谱姻巳灿沙福臃欢赡状诗堑析挎埋垛肺犁锹捣斜算度第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n1字符串函数字符串函数n字符串函数用来实现对字符型数据的转换、查找、分析等字符串函数用来实现对字符型数据的转换、查找、分析等操作，通常用做字符串表达式的一部分。表操作，通常用做字符串表达式的一部分。表9-7中列出了中列出了SQL Server的常用字符串函数。的常用字符串函数。金般鸡佛档浮吠涩氦湍黔驯皿苍世杂暴玖煽疹扁捻噬筋效迪老暗梆溉踪峨第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n(1) 使用使用datalength和和Len函数函数ndatalength函数主要用于判断可变长字符串的长度，对于函数主要用于判断可变长字符串的长度，对于定长字符串将返回该列的长度。要得到字符串的真实长度，定长字符串将返回该列的长度。要得到字符串的真实长度，通常需要使用通常需要使用rtrim函数截去字符串尾部的空格。函数截去字符串尾部的空格。nLen函数可以获取字符串的字符个数，而不是字节数，也函数可以获取字符串的字符个数，而不是字节数，也不包含尾随空格。不包含尾随空格。坚蜘醇蛮厨锦趣摩欧唾溉从伯届匆星腻命鹿萄遣课冬赛蒋予又酋粕胞锗阎第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-10 从表从表department中读取中读取manger列的各记录的实际长度。列的各记录的实际长度。SELECT Datalength(rtrim(manger) AS DATALENGTH, Len(rtrim(manger) AS LENFROM departmentn运行结果如下：运行结果如下：DATALENGTH LEN4 26 36 36 3戴眶庞呛哦埃佰菌拍蔓搔您蒂坤规器撅糠豌岭期惯普御拨乐蛤预郎网嫡彪第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n(2) 使用使用Soundex函数函数nsoundex函数将函数将char_expr转换为转换为4个字符的声音码，其中个字符的声音码，其中第一个码为原字符串的第一个字符，第第一个码为原字符串的第一个字符，第24个字符为数字，个字符为数字，是该字符串的声音字母所对应的数字，但忽略了除首字母是该字符串的声音字母所对应的数字，但忽略了除首字母外的串中的所有元音。外的串中的所有元音。nSoundex函数可用来查找声音相似的字符串，但它对数字函数可用来查找声音相似的字符串，但它对数字和汉字均只返回和汉字均只返回0值。值。n例如例如SELECT soundex(1),soundex(a), soundex(计算机计算机), soundex(abc), soundex (abcd), soundex(a12c), soundex(a数字数字)返回值为：返回值为：0000 A000 0000 A120 A120 A000 A000 狮什弓革者坤佯孔退妆件涛粤丫中省鞘滋俞跳炎捎卖钢卑羔秋锣静货霍邮第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n(3)使用使用Difference函数函数 ndifference函数返回两个字符表达式的函数返回两个字符表达式的soundex值的差。值的差异用值的差。值的差异用04之间的数字来表示，其含义如下：之间的数字来表示，其含义如下： 0：两个：两个soundex函数返回值的第一个字符不同。函数返回值的第一个字符不同。 1：两个：两个soundex函数返回值的第一个字符相同。函数返回值的第一个字符相同。 2：两个：两个soundex函数返回值的第一、二个字符相同。函数返回值的第一、二个字符相同。 3：两个：两个soundex函数返回值的第一、二、三个字符相同。函数返回值的第一、二、三个字符相同。 4：两个：两个soundex函数返回值完全相同。函数返回值完全相同。 例如：例如： SELECT difference(red,read),difference(soundex(ac),soundex(zc),difference(soundex(abc),soundex(abcd) 运行结果如下：运行结果如下： 4 0 4 射桥肪檀绊朱绎单贪罐叹弗闻酞鬃捆务棚乾萌府饰懈喇义盐清衫囊伸融概第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n(4) 使用使用Charindex函数实现串内搜索函数实现串内搜索ncharindex函数主要用于在串内找出与指定串匹配的串，如果找到的话，函数主要用于在串内找出与指定串匹配的串，如果找到的话，charindex函数返回第一个匹配的位置。格式：函数返回第一个匹配的位置。格式：Charindex(expr1, expr2, start_location) expr1是待查找的字符串是待查找的字符串expr2是用来搜索是用来搜索expr1的字符表达式，的字符表达式，start_location是在是在expr2中查找中查找expr1的开始位置，如果此值省略、的开始位置，如果此值省略、为负或为为负或为0，均从起始位置开始查找。，均从起始位置开始查找。 例如：例如：SELECT charindex(, red,white,blue) 该查询确定了字符串该查询确定了字符串red,white,blue中第一个中第一个逗号的位置。逗号的位置。捌屡阳摔违芥颈埃倒弘稽伶挚点漓生滩脓另童掳找缅疾豆韦权丹帚姥锡声第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n(5) 使用使用Patindex函数函数npatindex函数返回在指定表达式中模式第一次出现的起始函数返回在指定表达式中模式第一次出现的起始位置，如果模式没有则返回位置，如果模式没有则返回0。格式：。格式：Patindex(%pattern%, expression) pattern是字符串，是字符串，%字符必须出现在模式的开头和结尾。字符必须出现在模式的开头和结尾。expression通常是搜索指定子串的表达式或列。通常是搜索指定子串的表达式或列。例如：例如：SELECT patindex(%abc%,abc123), patindex(123,abc123) 子串子串“abc”和和“123”在字符串在字符串“abc123”中出现的中出现的起始位置分别为：起始位置分别为：1和和0。因为子串。因为子串“123”不是以不是以%开开头和结尾。头和结尾。琅北甥强努江滨狗谤暖藏杰毕冲看课毗传汰糯噬男微拽卤妙廖宇逼弹荫钥第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 常用函数常用函数卑揪咏撕溜胖卵仔枫蚌翰度韶诣泞标记隧通姻肮钻纲通檀圆宝蜀柞式流颓第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n2数学函数数学函数n数学函数用来实现各种数学运算，如指数数学函数用来实现各种数学运算，如指数运算、对数运算、三角运算等，其操作数运算、对数运算、三角运算等，其操作数为数值型数据，如为数值型数据，如int、float、real、money等等 n表表9-8列出了列出了SQL Server的数学函数。的数学函数。惋钎袭鹏苛泌筹婴蘑旱讨饱臣簿啄巾牟析砾豢哨俩昂痛莫神氢劝显窒车印第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-11 在同一表达式中使用在同一表达式中使用sin、atan、rand、pi、sign函数。函数。 SELECT sin(23.45), atan(1.234), rand(), pi(), sign(-2.34)n运行结果如下：运行结果如下：-0.99374071017265964 0.88976244895918932 0.19756617656167863.1415926535897931 -1.00斑傣阐日铁仟碟差起硝臀鬼期打阻谷倘忱讹翟漏递葱帮铆斩笼阂粱剿谚器第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-12 用用ceiling和和floor函数返回大于或等于指定值的最小整函数返回大于或等于指定值的最小整数值和小于或等于指定值的最大整数值。数值和小于或等于指定值的最大整数值。 SELECT ceiling(123),floor(321), ceiling(12.3), ceiling(-32.1),floor(-32.1)n运行结果如下：运行结果如下：12332113-32-33摊夸愉慑谭赁截甩腔抉尧租对郁免啼拇整到株玫牟瀑否呛艺夸册掇趴需埂第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数例例9-13 round函数的使用。函数的使用。 SELECT round(12.34512,3), round(12.34567,3), round(12.345,-2), round(154.321,-2)n运行结果如下：运行结果如下：12.34500 12.3460 0 .000 200.000nRound(numeric_expr, int_expr) 的的int_expr为负数时，将为负数时，将小数点左边第小数点左边第int_expr位四舍五入。位四舍五入。 9.2.1 常用函数常用函数配淡舒栈阶据庙分茎坏旬茧捣型诬咒紫娠楼格阶鬼饺仿曼珠隆杜苛着仗赔第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n3日期函数日期函数n日期函数用来操作日期函数用来操作datetime和和smalldatetime类型的数据，类型的数据，执行算术运算。与其他函数一样，可以在执行算术运算。与其他函数一样，可以在SELECT语句和语句和WHERE子句以及表达式中使用日期函数。子句以及表达式中使用日期函数。 绒庶独淳咽庸孕落彼瓣什狐焦联搽泽币封味翻逸眨钓卖雍疚络鄙苦弱英亥第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数函数名称及格式描 述Getdate()返回当前系统的日期和时间Datename(datepart, date_expr)以字符串形式返回date_expr中的指定部分，如果合适的话还将其转换为名称(如June)Datepart(datepart, date_expr)以整数形式返回date_expr中的datepart指定部分Datediff(datepart,date_expr1,date_expr2)以datepart指定的方式，返回date_expr2与date_expr1之差Dateadd(datepart, number, date_expr)返回以datepart指定方式表示的date_expr加上number以后的日期Day(date_expr)返回date_expr中的日期值Month(date_expr)返回date_expr中的月份值Year(date_expr)返回date_expr中的年份值表表9-9 SQL Server的日期函数的日期函数摹概艰诸候裕憾咽险戈潦澜描哗燃桂矿默栓飘加关选钒巡蠢形权趋诱买更第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数日期部分写 法取值范围Yearyy17539999Quarterqq14Monthmm112Dayofyeardy1366Daydd131Weekwk154Weekdaydw17(MonSun)Hourhh023Minutemi059Secondss059Millisecondms0999表表9-10 SQL Server的日期部分的日期部分扫赡体弯爪卖革挛黔喝孵滨荷抨贰怂焦昼眷陋僚剧板溢燎拙入襄辩椿符劈第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-14 9-14 使用使用datediffdatediff函数来确定货物是否按时送给客户。函数来确定货物是否按时送给客户。 SELECT SELECT goods_id,datediff(dd,send_date,arrival_date)goods_id,datediff(dd,send_date,arrival_date)FROM purchase_orderFROM purchase_ordern为了从为了从datediffdatediff中得到一个正值，应注意把较早的日期放中得到一个正值，应注意把较早的日期放在前面在前面 n例如：例如：select datediff(day,2004-09-01,2004-09-select datediff(day,2004-09-01,2004-09-18) -18) -返回：返回：1717竣弃愉店睡诅苹佑齐疥甭延路粟吮酝夷峻饿义怀侍玄堕易膛社眨罚幂把承第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-15 使用使用datename函数返回员工的出生日期的月份函数返回员工的出生日期的月份(mm)名称。名称。SELECT employee_name,datename(mm,birth_date)FROM employeen运行结果如下：运行结果如下：钱达理钱达理 December东方牧东方牧 April郭文斌郭文斌 March肖海燕肖海燕 July张明华张明华 August寡毡泽具遮妙棠吞缠瞳乱勋亮挟蓝惕串辜猖引弗垄含检唬签吸便薯沃捕广第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数n4系统函数系统函数n系统函数用于获取有关计算机系统、用户、数据库和数据系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。与其他函数一样，可以在库对象的信息。与其他函数一样，可以在SELECT和和WHERE子句以及表达式中使用系统函数。子句以及表达式中使用系统函数。n表表9-11列出了列出了SQL Server的系统函数。的系统函数。枣淘凳咖茬郧差揖啊哑哨桩匆雀辜正需囱篱蜀尚阿曹绅质痞筐布椭鳞挟殃第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-16 使用使用object_name函数返回已知函数返回已知ID号的对象名。号的对象名。SELECT object_name(469576711)n运行结果如下：运行结果如下：Employee痒染汤愿烬苇屈惩娱混万塞添巷激毕硕樊笨弟沤膛橱碎命挺眩柄驾若僻越第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.1 9.2.1 常用函数常用函数9.2.1 常用函数常用函数例例9-17 利用利用object_id函数，根据表名返回该表的函数，根据表名返回该表的ID号。号。SELECT name FROM sysindexesWHERE id=object_id(customer)n运行结果如下：运行结果如下：namecustomer_WA_Sys_customer_id_75D7831F趾旅矽林蛇荷洞谊础玖键赊贩雅谭蔡科领驴霸虐敛乌溪物握套坐伸酚触遗第9章TransactSQL程序设计第9章TransactSQL程序设计9.2 9.2 函函 数数9.2.2 用户定义函数用户定义函数根据函数返回值形式的不同将用户定义函数分为根据函数返回值形式的不同将用户定义函数分为3种类型。种类型。(1) 标量函数标量函数标量函数返回一个确定类型的标量值，其函数值类型为标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在类型外）。函数体语句定义在BEGINEND语语句内。句内。(2) 内嵌表值函数内嵌表值函数内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用使用BEGINEND语句，其返回的表是语句，其返回的表是RETURN子句中的子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。命令查询的结果集，其功能相当于一个参数化的视图。(3) 多语句表值函数多语句表值函数多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用函数值也是一个表，但函数体也用BEGINEND语句定义，返回语句定义，返回值的表中的数据由函数体中的语句插入。值的表中的数据由函数体中的语句插入。绚明跌殆适踪甸崩畴毖亚延珊硅老馏绷沉褂痰欠悬矽该肝臭兽侨跟候寺挪第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数1. 创建用户定义函数创建用户定义函数(1)使用使用CREATE FUNCTION创建用户定义函数创建用户定义函数n标量函数的语法格式：标量函数的语法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type=default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN scalar_expressionEND庆查围检办携潘又洛若卵挽浪哆堤料讥鳃脾姐狞器勿敛咱撑咙鸳哺究虞视第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数内嵌表值函数的语法格式：内嵌表值函数的语法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type =default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( select_stmt ) 揪研样吧亏拘诸敝褒涡喀怖狄咏省序饥铺佐禁绒孜惹锤胳荐盈毒盒揪测闻第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数多语句表值函数的语法格式：多语句表值函数的语法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURNEND聪幅褐僻孰渝苍弯垒绸缩桩曳肝脾憎淤阔副宗垢邯喝广限尤撒顺膜玻鲸密第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数例例9-18 创建一个用户定义函数创建一个用户定义函数DatetoQuarter，将输入的日，将输入的日期数据转换为该日期对应的季度值。如输入期数据转换为该日期对应的季度值。如输入2006-8-5，返回返回3Q2006，表示，表示2006年年3季度。季度。CREATE FUNCTION DatetoQuarter(dqdate datetime)RETURNS char(6)ASBEGIN RETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate)END欲烟帝姆噬墒审邮孜眺聪键源潜代化哇菇顷漂缔伪桓戚盾榜咀哪审气笨点第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数例例9-19 创建用户定义函数创建用户定义函数goodsq，返回输入商品编号的商品，返回输入商品编号的商品名称和库存量。名称和库存量。CREATE FUNCTION goodsq(goods_id varchar(30)RETURNS TABLEASRETURN(SELECT goods_name,stock_quantity FROM goods WHERE goods_id =goods_id)滴耍勺业赵咸彤溯竖巴丈习答殊杠淀磨膘埔自戊皂啸尚拂花监毋艘造园准第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数例例9-20 根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、类别名称。类别名称。CREATE FUNCTION good_info(in_o_id varchar(10)RETURNS goodinfo TABLE(o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20)ASBEGIN DECLARE g_id varchar(10),g_name varchar(30) DECLARE c_id varchar(10),c_name varchar(30) SELECT g_id=goods_id FROM sell_order WHERE order_id1=in_o_id SELECT g_name=goods_name,c_id=classification_id FROM goods WHERE goods_id=g_id SELECT c_name=classification_name FROM goods_classification WHERE c_id=classification_id INSERT goodinfo VALUES(in_o_id,g_id,g_name,c_id,c_name) RETURNEND利轴呆抄质汰披晶腐慕茎酿豢常炔少匈筐锄考旬打州锋恶乳殊置芦离夕拈第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数n2. 执行用户定义函数执行用户定义函数n使用函数需要指出函数所有者，即为函数加上所使用函数需要指出函数所有者，即为函数加上所有者权限作为前缀。其语法格式如下：有者权限作为前缀。其语法格式如下： database_name.owner_name.function_name (argument_expr , . )溪鉴蒲指蔚么蛰逸得诣求肢散奎刨蔫嚷豺脊遁勇吧哥另归噬歌拂荡辉叠摊第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数例如，调用例例如，调用例9-18创建的用户定义函数创建的用户定义函数DatetoQuarterSELECT dbo.DatetoQuarter (2006-8-5)运行结果为：运行结果为：3Q2006n调用例调用例9-20创建的用户定义函数创建的用户定义函数good_info，使用以下语句：，使用以下语句：SELECT * FROM dbo.good_info(S00002)运行结果为表的记录，如图运行结果为表的记录，如图9-3所示。所示。啼侵啤俭反拥已郁亲烷炸岂懂太众捏黍托扼苟陵哈欲槽计终壶港跺廊淮载第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数n3. 修改和删除用户定义函数修改和删除用户定义函数n用用SQL Server管理平台中修改用户定义函数，选择要修改函数，单管理平台中修改用户定义函数，选择要修改函数，单击右键，从快捷菜单中选择击右键，从快捷菜单中选择“修改修改”选项，打开图选项，打开图9-2所示的所示的“用户用户定义函数属性定义函数属性”编辑框。在该对话框中可以修改用户定义函数的函数编辑框。在该对话框中可以修改用户定义函数的函数体、参数等。从快捷菜单中选择体、参数等。从快捷菜单中选择“删除删除”选项，则可删除用户定义函选项，则可删除用户定义函数。数。 n用用ALTER FUNCTION命令也可以修改用户定义函数。此命令的语法命令也可以修改用户定义函数。此命令的语法与与CREAT FUNCTION相同，使用相同，使用ALTER FUNCTION命令相当于重命令相当于重建一个同名的函数。建一个同名的函数。n使用使用DROP FUNCTION命令删除用户定义函数，其语法如下：命令删除用户定义函数，其语法如下：DROP FUNCTION owner_name . function_name ,.n 其中，其中，function_name是要删除的用户定义的函数名称。是要删除的用户定义的函数名称。塔掐掀蹄叫桨艺执畅现甥儒湿绞马辗逸剪困痒撕哥去钨纬匠懒斟撅解布挂第9章TransactSQL程序设计第9章TransactSQL程序设计9.2.2 9.2.2 用户定义函数用户定义函数9.2.2 用户定义函数用户定义函数例如，删除例例如，删除例9-18创建的用户定义函数创建的用户定义函数DROP FUNCTON DatetoQuartern删除用户定义函数时，可以不加所有者前删除用户定义函数时，可以不加所有者前缀。缀。屁坏酣孝后用腕第芥撕络岩酥闹彤遗扁费驼竭坷找轮淄啼揪塞表洪尼品韩第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 程序控制流语句程序控制流语句9.3.1 语句块和注释语句块和注释9.3.2 选择控制选择控制9.3.3 循环控制循环控制9.3.4 批处理批处理庆洪辞叉咒碴拄羌泞碍一踢钟正慌亦惠戈痛剪铱功滴吨替豪翻偶池凶漏林第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句9.3.1 语句块和注释语句块和注释nTransact-SQL提供了控制流语言的特殊关键字和用于编写提供了控制流语言的特殊关键字和用于编写过程性代码的语法结构，可进行顺序、分支、循环、存储过程性代码的语法结构，可进行顺序、分支、循环、存储过程、触发器等程序设计，编写结构化的模块代码，并放过程、触发器等程序设计，编写结构化的模块代码，并放置到数据库服务器上。置到数据库服务器上。矮入酸秤差召邱香搀气吭概芜导厚诺誊州饺搜空蓄厅衙分许馅咸拥埃齐坏第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释n1. 语句块语句块BEGIN.ENDnBEGIN.END用来设定一个语句块，将在用来设定一个语句块，将在BEGIN.END内内的所有语句视为一个逻辑单元执行。的所有语句视为一个逻辑单元执行。n语句块语句块BEGIN.END的语法格式为：的语法格式为：BEGIN sql_statement | statement_block END9.3.1 语句块和注释语句块和注释靶渐粟议愧赢薪超洽遥卧熄赡糊鞭朝差涅锅些屹载出柞伺痴靖酵谈澜爵顽第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释例例9-21 显示显示Sales数据库中数据库中customer表的编号为表的编号为C0001的联的联系人姓名。系人姓名。USE SalesGODECLARE linkman_name char(8)BEGIN SELECT linkman_name=(SELECT linkman_name FROM customer WHERE customer_id LIKE C0001) SELECT linkman_nameEND 本例中，本例中，BEGINEND将两个将两个SELECT语句组合成一个语句组合成一个语句块，用于给语句块，用于给linkman_name变量赋值和显示。变量赋值和显示。 9.3.1 语句块和注释语句块和注释凿楚檄愚烽侗沙版烤惠朴享盲过叛望爷寺小墩氖举舶岩哼预迁纺蘸溺怎掂第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释9.3.1 语句块和注释语句块和注释在在BEGIN.END中可嵌套另外的中可嵌套另外的BEGIN.END来定义另一程序块。来定义另一程序块。例例9-22 语句块嵌套举例。语句块嵌套举例。DECLARE errorcode int,nowdate dateTIMEBEGIN SET nowdate=getdate() INSERT sell_order(order_date,send_date,arriver_date,custom_id) VALUES(nowdate,nowdate+5,nowdate+10,C0002) SELECT errorcode=error IF errorcode0 BEGIN RAISERROR(当表当表sell_order插入数据时发生错误插入数据时发生错误!,16,1) RETURN END贝嘉妮阅跺但凡途糕万肉娟纸勒们授坚乞伊摆陀玉诈风糯糯豆怖岩牡该隅第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释9.3.1 语句块和注释语句块和注释n2. 注释注释n有两种方法来声明注释：有两种方法来声明注释：单行注释单行注释多行注释。多行注释。秤舷润埋爽揣行羊脆被宜力确袄央磕僧锨汇递吾膜怠睦宿丛节盂虫颐椰滨第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释9.3.1 语句块和注释语句块和注释n（1）单行注释）单行注释n在语句中，使用两个连字符在语句中，使用两个连字符“-”开头，则从此开始的整行开头，则从此开始的整行或者行的一部分就成为了注释，注释在行的末尾结束。或者行的一部分就成为了注释，注释在行的末尾结束。-This is a comment.Whole line will be ignored.SELECT employee_name, address -查询所有姓钱的员工查询所有姓钱的员工FROM employeeWHERE employee_name LIKE 钱钱%n注释的部分不会被注释的部分不会被SQL Server执行。执行。玩类蜕瞧排疟掌在题杀孙堡函荚验宪授计丘裙层苞炽蒜压砂灭铬伺乐搐畦第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.1 9.3.1 语句块和注释语句块和注释9.3.1 语句块和注释语句块和注释n（2）多行注释）多行注释n多行注释方法是多行注释方法是SQL Server自带特性，可以注释大块跨越自带特性，可以注释大块跨越多行的代码，它必须用一对分隔符多行的代码，它必须用一对分隔符“/* */”将余下的其将余下的其他代码分隔开。他代码分隔开。/*This is a commnetAll these lines will be ignored.*/* List all employees.*/SELECT * FROM employeen注释并没有长度限制。注释并没有长度限制。SQL Server文档禁止嵌套多行注释，文档禁止嵌套多行注释，但单行注释可以嵌套在多行注释中。但单行注释可以嵌套在多行注释中。/* -List all employees.SELECT * FROM employee*/弱目逞斌撑袋些终论讨篡淀榔坛沥丹倒颈访蔷归畅头迟榴澄件升毋遭酗隶第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句9.3.2 选择控制选择控制1. 条件执行语句条件执行语句IFELSE必须必须IF.ELSE结构根据条件表达式的值，以决定结构根据条件表达式的值，以决定执行哪些语句。执行哪些语句。nIF.ELSE的语法格式为：的语法格式为：IF Boolean_expression sql_statement | statement_block -条件表达式为真时执行条件表达式为真时执行 ELSE sql_statement | statement_block -条件表达式为假时执行条件表达式为假时执行间喳蝇拒弗硼推孰谚粹蹭俩研维哭重毁毗豺娄繁如产拖哦冯俗蹲混磁靖拐第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制例例9-23 判断表判断表goods中中supplier_id为为“S001”的商品的平均的商品的平均单价是否大于单价是否大于9799。IF (SELECT avg(unit_price) FROM goods WHERE supplier_id=S001)$9799.0 SELECT supplier_id为为S001的商品的平均单价比的商品的平均单价比9799大大ELSE SELECT supplier_id为为S001的商品的平均单价比的商品的平均单价比9799小小n运行结果如下：运行结果如下：supplier_id为为S001的商品的平均单价比的商品的平均单价比9799大大9.3.2 选择控制选择控制恭阎完惊鼻铰纽舶姜巫者汰让劳刘闷迹亦垦逸摸瘤驶菱社句豺粗姐漳觉虱第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制例例9-24 用用EXISTS确定表确定表department中是否存在中是否存在“陈晓兵陈晓兵”。DECLARE lname varchar(40),msg varchar(255)SELECT lname=陈晓兵陈晓兵IF EXISTS(SELECT * FROM department WHERE manager=lname) BEGIN SELECT msg=有人名为有人名为+lname SELECT msg ENDELSE BEGIN SELECT msg=没有人名为没有人名为+lname SELECT msg ENDn运行结果为：运行结果为：有人名为陈晓兵有人名为陈晓兵9.3.2 选择控制选择控制赠毖菲享宫躇疙昧珍砚抨教约结莆回骄揍锅母滓鸣伪揭官凛阐拳侵弱博圭第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制9.3.2 选择控制选择控制例例9-25 嵌套嵌套IF.ELSE语句的使用。语句的使用。IF (SELECT SUM(order_num) FROM sell_order)50 PRINT 他们是最佳的客户他们是最佳的客户ELSE IF (SELECT SUM(order_num) FROM sell_order)30 PRINT 必须与他们保持联络必须与他们保持联络 ELSE PRINT 再想想办法吧再想想办法吧!已弱戌楚肛厅黄随侦蔓避拍舔脆劫肤煎掣剖掏乃镣管殉因趾然痰椎氟扇授第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制9.3.2 选择控制选择控制n2. CASE函数函数nCASE函数计算多个条件并为每个条件返回单个值。函数计算多个条件并为每个条件返回单个值。 (1) 简单简单CASE函数：将某个表达式与一组简单表达式进行比较以确定结函数：将某个表达式与一组简单表达式进行比较以确定结果。果。CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END (2) CASE搜索函数，搜索函数，CASE计算一组逻辑表达式以确定结果。计算一组逻辑表达式以确定结果。CASE WHEN Boolean_expression THEN result_expression . n ELSE else_result_expression END李手际断除伦伐川摈刑肾海膀毫虹雪享踩止由烫奋协傍传糟却淄栖扑讽沽第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制9.3.2 选择控制选择控制例例9-26 使用简单使用简单CASE函数将函数将goods表中的商品分类重命名，以使之更易理解。表中的商品分类重命名，以使之更易理解。SELECT CASE classification_id WHEN P001 THEN 笔记本计算机笔记本计算机 WHEN P002 THEN 激光打印机激光打印机 WHEN P003 THEN 喷墨打印机喷墨打印机 WHEN P004 THEN 交换机交换机 ELSE 没有这种品牌没有这种品牌 END AS Classification, goods_name AS Goods Name, unit_price AS PriceFROM goodsWHERE unit_price IS NOT NULL得吓走搓蹦伪蛙阁牙巩旬米颜铱松刽革盟捷掌篆识领哮泪蕊篇摹拥派鸯弛第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制例例9-27 根据根据goods表中库存货物数量与订货量之差，使用表中库存货物数量与订货量之差，使用CASE搜索函数判断该商品是否进搜索函数判断该商品是否进货。货。SELECT goods_name AS 商品名称商品名称, CASE WHEN stock_quantity-order_quantity3 and stock_quantity-order_quantity10 THEN 货物充足货物充足 END AS 进货判断进货判断FROM goods9.3.2 选择控制选择控制刁拄佃糯匡垛酶担哮英亚棘较偏逞撇拨痴音线灯伶猪能逞肾鸳箱骑戚飞大第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制9.3.2 选择控制选择控制n3. 跳转语句跳转语句GOTOnGOTO语句将允许程序的执行转移到标签处语句将允许程序的执行转移到标签处,GOTO语句的语法格式如下：语句的语法格式如下：GOTO label其中，其中，label为为GOTO语句处理的起点。语句处理的起点。label必必须符合标识符规则。须符合标识符规则。蝴扇蹲鄂捣疑肿堆柱敝肄箕荒祝疆落徽漫藩孪界芜妨牺胜斯坑摆塑店钱掐第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.2 选择控制选择控制9.3.2 选择控制选择控制例例9-28 使用使用GOTO语句改变程序流程。语句改变程序流程。 DECLARE x int SELECT x=1label_1: SELECT x SELECT x=x+1 WHILE x6 GOTO label_1例如：使用IF语句求1到10之间的累加和并输出结果ndeclare sum int,count intnselect sum=0,count=1nlabel:nselect sum=sum+countnselect count=count+ 1nif count (SELECT order_quantity FROM goods WHERE goods_id=maxstockid)BEGIN UPDATE goods SET order_quantity=order_quantity+2 WHERE goods_id=maxstockid SET count=count+1ENDSELECT countn运行结果如下：运行结果如下：8膏夏函陀赞管彝该佛依陇砚削术它秘碟挞抽措邻倘召怜蜒蜂比辈些抢酿样第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.3 循环控制循环控制n输出字符串“School” 中每一个字符的ASCII值和字符。乡亥寻汐血轧妨袱验簿瘩透皖徐勒漏淖彼姐乖漓靳掂瘦洼锣胰鉴阿饥浸袒第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句nBREAK或或CONTINUE语句语句nBREAK语句让程序跳出循环，语句让程序跳出循环，CONTINUE语句让程序跳过语句让程序跳过CONTINUE命令之后的语命令之后的语句，回到句，回到WHILE循环的第一行命令，重新循环的第一行命令，重新开始循环。开始循环。9.3.3 循环控制循环控制遁闽纬蝗艇择缴统孟脖详其砸服烽回翔航匆累归做牟遗脚选限赌抽状凹果第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.3 循环控制循环控制身蜜肩谁判跃碾处侗茶措供华量蓝溉肠祭袱烬站德宁赖愿野争佛悄匠寄徒第9章TransactSQL程序设计第9章TransactSQL程序设计9.3.3 循环控制循环控制嘉柑瞄椰穴须审苹说蜡郡茂宜皖水收丘堡消彤垮金磁搞笑末肿院爪扦挨玲第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句9.3.3 循环控制循环控制例例9-31 对于对于goods表，如果平均库存少于表，如果平均库存少于12，WHILE循环就将各记录库存增加循环就将各记录库存增加5%，再判断最高库存是否少于或等于，再判断最高库存是否少于或等于25，是则，是则WHILE循环重新启动并再次循环重新启动并再次将各记录库存增加将各记录库存增加5%。当循环不断地将库存增加直到最高库存超过。当循环不断地将库存增加直到最高库存超过25时，然时，然后退出后退出WHILE循环。循环。/*执行循环，直到库存平均值超过执行循环，直到库存平均值超过12*/WHILE(SELECT avg(stock_quantity) FROM goods)25 BEGIN PRINT 库存太多了库存太多了 BREAK END ELSE CONTINUEEND暇持妒雏呈铡县稿遂熟占鞋若缚疟腮牛反椿铬霍岳摊窍坪糯缮蔽铭只搁瘤第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句DECLARE s int,n int,t int,c intSET S=0SET n=1WHILE n=10BEGIN SET c=1 SET t=1 WHILE c=n BEGIN SET t=t*c SET c=c+1 END SET s=s+t SET n=n+1ENDSELECT s,n例例9-32 计算计算s=1!+2!+10!。小麻俐膳晴磨课浪兼喂汕吉走坍践搜窿拟隧持莎审札储叛姿氨磺堂吴晃草第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句9.3.4 批处理批处理n一个批处理是一个批处理是条或多条条或多条Transact-SQL语句的集合。语句的集合。nSQL Server服务器对批处理的处理分为四个阶段：服务器对批处理的处理分为四个阶段：分析阶段，服务器检查命令的语法，验证表和列的名字的合法性分析阶段，服务器检查命令的语法，验证表和列的名字的合法性优化阶段，服务器确定完成一个查询的最有效的方法；优化阶段，服务器确定完成一个查询的最有效的方法；编译阶段，生成该批处理的执行计划；编译阶段，生成该批处理的执行计划；运行阶段，运行阶段，条一条地执行该批处理中的语句。条一条地执行该批处理中的语句。n批处理最重要的特征就是它作为一个不可分的实体在服务批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。在一些情况下批处理被隐式地设定。例器上解释和执行。在一些情况下批处理被隐式地设定。例如，用查询分析器来执行一组如，用查询分析器来执行一组Transact-SQL语句，这组语语句，这组语句将被视为一个批处理来对待。句将被视为一个批处理来对待。菊咋磅谋赦说苫犬茅醚仑后咒逞逾预敖貉狡囚淌曼眶荤怪留走完户淳玄基第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句n1. 批处理的指定批处理的指定nSQL Server有以下几种指定批处理的方法。有以下几种指定批处理的方法。(1) 应用程序作为一个执行单元发出的所有应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，语句构成一个批处理，并生成单个执行计划。并生成单个执行计划。(2) 存储过程或触发器内的所有语句构成一个批处理。每个存储过程或存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划。触发器都编译为一个执行计划。(3) 由由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计语句执行的字符串是一个批处理，并编译为一个执行计划。例如，划。例如，EXEC (SELECT * FROM employee)(4) 由由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如，一个执行计划。例如，execute sp_executesql NSELECT * from Sales.dbo.employee 9.3.4 批处理批处理病桥咒舷沙韧先发茹褥执暮酞准迸化除婿裤表臀秧旗然风持庸笋每蓑屯凉第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句n2. 批处理的结束与退出批处理的结束与退出nGO是批处理的结束标志。当编译器执行到是批处理的结束标志。当编译器执行到GO时会把时会把GO前面的所有语句当成一个批处理来执行。前面的所有语句当成一个批处理来执行。nGO命令和命令和Transact-SQL语句不可处在同一行上。但在语句不可处在同一行上。但在GO命令行中可以包含注释。命令行中可以包含注释。n在批处理的第一条语句后执行任何存储过程必须包含在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部关键字。局部(用户定义用户定义)变量的作用域限制在变量的作用域限制在一个批处理中，不可在一个批处理中，不可在GO命令后引用。命令后引用。nRETURN可在任何时候从批处理中退出，而不执行位于可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。之后的语句。9.3.4 批处理批处理运快群甚孰惩库迢勉导讳活见捆北彻敝元理琐党冤烦渺乳竣蒋植吨幂抑椅第9章TransactSQL程序设计第9章TransactSQL程序设计9.3 9.3 程序控制流语句程序控制流语句例例9-33 创建一个视图，使用创建一个视图，使用GO命令将命令将CREATE VIEW语句语句与批处理中的其他语句与批处理中的其他语句(如如USE、SELECT语句等语句等)隔离。隔离。USE SalesGO - 批处理结束标志批处理结束标志CREATE VIEW employee_infoASSELECT * FROM employeeGO- CREATE VIEW语句与其他语句隔离语句与其他语句隔离SELECT * FROM employee_infoGO 9.3.4 批处理批处理隧领腺眠钡丢谐色蜜粮熄泽儒诊剑舱饺磕梧阿讳撰最军张斗篡粟诱伐兽健第9章TransactSQL程序设计第9章TransactSQL程序设计9.4 游标管理与应用游标管理与应用9.4.1 游标概述游标概述9.4.2 声明游标声明游标9.4.3 使用游标使用游标9.4.4 游标的应用游标的应用9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标涯木穗评态河策盼灸辉涤茂出谁厘篱疫歌醉姻拂扫尧骨部俯絮两恿搁瓷渣第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.1 游标概述游标概述9.4 9.4 游标管理与应用游标管理与应用1. 游标种类游标种类(1)Transact-SQL游标：是由游标：是由DECLARE CURSOR语句定义，主要用在语句定义，主要用在服务器上，由从客户端发送给服务器的服务器上，由从客户端发送给服务器的Transact-SQL语句或批处理、语句或批处理、存储过程、触发器中的存储过程、触发器中的Transact-SQL语句进行管理。语句进行管理。Transact-SQL游游标不支持提取数据块或多行数据。标不支持提取数据块或多行数据。(2)API游标：支持在游标：支持在OLE DB、ODBC以及以及DB_library中使用游标函数，中使用游标函数，主要用在服务器上。每一次客户端应用程序调用主要用在服务器上。每一次客户端应用程序调用API游标函数，游标函数，SQL Server的的OLE DB提供者、提供者、ODBC驱动器或驱动器或DB_library的动态链接库的动态链接库(DLL)都会将这些客户请求送给服务器以对都会将这些客户请求送给服务器以对API游标进行处理。游标进行处理。(3)客户游标：当客户机缓存结果集时才使用。在客户游标中，有一个默客户游标：当客户机缓存结果集时才使用。在客户游标中，有一个默认的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态认的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标。一般情况下，服务器游标能支持绝大多数的游标操作，但不支游标。一般情况下，服务器游标能支持绝大多数的游标操作，但不支持所有的持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用作服语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。务器游标的辅助。n由于由于API游标和游标和Transact-SQL游标使用在服务器端，所以被称为服务游标使用在服务器端，所以被称为服务器游标或后台游标，而客户端游标被称为前台游标。器游标或后台游标，而客户端游标被称为前台游标。陷妨蔼惜馁希导莽晾嚎孩簿莆够换充堆风所彤文颗糟升匹犹望岁囚嫌已系第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.1 游标概述游标概述9.4.1 游标概述游标概述n2. 服务器游标与默认结果集的比较服务器游标与默认结果集的比较nSQL Server以两种方式为用户返回结果集：默认结果集和以两种方式为用户返回结果集：默认结果集和服务器游标。服务器游标。(1)默认结果集具有的特点：开销小；取数据时提供最大性默认结果集具有的特点：开销小；取数据时提供最大性能；仅支持默认的单进、只读游标功能；返回结果行时一能；仅支持默认的单进、只读游标功能；返回结果行时一次次行；连接时一次只支持一个活动语句；支持所有行；连接时一次只支持一个活动语句；支持所有Transact-SQL语句。语句。(2)服务器游标具有的持点：支持所有游标功能；可以为用服务器游标具有的持点：支持所有游标功能；可以为用户返回数据块；在单个连接上支持多个活动语句；以性能户返回数据块；在单个连接上支持多个活动语句；以性能补偿游标功能；不支持所有返回多于一行结果集的补偿游标功能；不支持所有返回多于一行结果集的Transact-SQL语句。语句。n使用游标不如使用默认结果集的效率高。在默认结果集中，使用游标不如使用默认结果集的效率高。在默认结果集中，客户端只向服务器发送要执行的语句。而使用服务器游标客户端只向服务器发送要执行的语句。而使用服务器游标时，每个时，每个FETCH语句都必须从客户端发往服务器，再在语句都必须从客户端发往服务器，再在服务器中分析此语句并将它编译为执行汁划。服务器中分析此语句并将它编译为执行汁划。顿闻泄届巧吱畴渔菌御翼零烷么命桔军张除夺泵舍哨莫仕虽纹德鼎剩檄节第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.1 游标概述游标概述9.4.1 游标概述游标概述n3. 服务器游标与客户端游标的比较服务器游标与客户端游标的比较n使用服务器游标比使用客户游标有以下几方面的优点：使用服务器游标比使用客户游标有以下几方面的优点：(1)性能：如果要在游标中访问部分数据，使用服务器游标性能：如果要在游标中访问部分数据，使用服务器游标将提供最佳性能，因为只有被取到的数据在网络上发送，将提供最佳性能，因为只有被取到的数据在网络上发送，客户游标在客户端存取所有结果集。客户游标在客户端存取所有结果集。(2)更准确的定位更新：服务器游标直接支持定位操作，如更准确的定位更新：服务器游标直接支持定位操作，如UPDATE和和DELETE语句，客户游标通过产生语句，客户游标通过产生Transact-SQL搜索搜索UPDATE语句模拟定位游标更新，如果多行与语句模拟定位游标更新，如果多行与UPDATE语句的语句的WHERE子句的条件相匹配将导致无意义子句的条件相匹配将导致无意义更新。更新。(3)内存使用：使用服务器游标时，客户端不需要高速存取内存使用：使用服务器游标时，客户端不需要高速存取大量数据或者保持有关游标位置的信息，这些都由服务器大量数据或者保持有关游标位置的信息，这些都由服务器来完成。来完成。(4)多活动语句：使用服务器游标时，结果不会存留在游标多活动语句：使用服务器游标时，结果不会存留在游标操作之间的连接上，这就允许同时拥有多个活动的基于游操作之间的连接上，这就允许同时拥有多个活动的基于游标的语句。标的语句。陈态研赡系韵剁铂耗泞慧尹沿袱疆增保羌悠喊芯赵棉啡仔纹墙酵缅还悔避第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.1 游标概述游标概述n4. 服务器游标类型服务器游标类型nSQL Server支持支持4种类型的服务器游标，它们是单进游标、静态游标、种类型的服务器游标，它们是单进游标、静态游标、动态游标和键集驱动游标。动态游标和键集驱动游标。(1)单进游标只支持游标按从前向后顺序提取数据，游标从数据库中提取单进游标只支持游标按从前向后顺序提取数据，游标从数据库中提取一条记录并进行操作，操作完毕后，再提取下一条记录。一条记录并进行操作，操作完毕后，再提取下一条记录。(2)静态游标也称为快照游标，它总是按照游标打开时的原样显示结果集，静态游标也称为快照游标，它总是按照游标打开时的原样显示结果集，并不反映在数据库中对任何结果集成员所做的修改，因此不能利用静并不反映在数据库中对任何结果集成员所做的修改，因此不能利用静态游标修改基表中的数据。静态游标打开时的结果集存储在数据库态游标修改基表中的数据。静态游标打开时的结果集存储在数据库tempdb中。静态游标始终是只读的。中。静态游标始终是只读的。(3)动态游标也称为敏感游标，与静态游标相对，当游标在结果集中滚动动态游标也称为敏感游标，与静态游标相对，当游标在结果集中滚动时，结果集中的数据记录的数据值、顺序和成员的变化均反映到游标时，结果集中的数据记录的数据值、顺序和成员的变化均反映到游标上，用户所做的各种操作均可通过游标反映。上，用户所做的各种操作均可通过游标反映。(4)键集驱动游标介于静态游标和动态游标之间，兼有两者的特点。打开键集驱动游标介于静态游标和动态游标之间，兼有两者的特点。打开键集驱动游标后，游标中的成员和行顺序是固定的。键集驱动游标由键集驱动游标后，游标中的成员和行顺序是固定的。键集驱动游标由一套惟一标识符控制，这些惟一标识符就是键集。用户对基表中的非一套惟一标识符控制，这些惟一标识符就是键集。用户对基表中的非关键值列插入数据或进行修改造成数据值的变化，在整个游标中都是关键值列插入数据或进行修改造成数据值的变化，在整个游标中都是可见。键集驱动游标的键集在游标打开时建立在数据库可见。键集驱动游标的键集在游标打开时建立在数据库tempdb中。中。9.4.1 游标概述游标概述件廉堕格的更司帽漓彪肯煎爽邪逮映航剐弗旱荐旱陈喜韩彝乒喉虽沥枝苇第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 声明游标声明游标9.4 9.4 游标管理与应用游标管理与应用SQL Server游标具有下面的处理过程：游标具有下面的处理过程：(1)声明游标，定义其特性，如游标中的行是否可以声明游标，定义其特性，如游标中的行是否可以被更新。被更新。(2) 执行执行Transact-SQL语句生成游标。语句生成游标。(3)在游标中检索要查看的行。从游标中检索一行或在游标中检索要查看的行。从游标中检索一行或几行的操作称为取数据。向前或向后执行取数据几行的操作称为取数据。向前或向后执行取数据操作来检索行的行为称为滚动。操作来检索行的行为称为滚动。(4)关闭游标。关闭游标。魔役寸课痊企蛇筐迅谭口妆哑鹅冻桓前茵眩翌势亿漓调螟粟杏氧册襄亲狡第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 9.4.2 声明游标声明游标n1. SQL-92游标定义格式游标定义格式n语法格式如下：语法格式如下：DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 9.4.2 声明游标声明游标芒棠颤致彭锰俯闪桔轧登娶鄙严鹊垢懂桐是春敌甄想亲唱搪佃拽酷捷低涂第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 9.4.2 声明游标声明游标9.4.2 声明游标声明游标SCROLL选项含 义FIRST提取游标中的第一行数据LAST提取游标中的最后一行数据PRIOR提取游标当前位置的上一行数据NEXT提取游标当前位置的下一行数据RELATIVE n提取游标当前位置之前或之后的第n行数据(n为正表示向后，n为负表示向前)ABSULUTE n提取游标中的第n行数据表表9-13 SCROLL的取值的取值贫绩驹锁妮演抿垢迅佬孽磁颠它蔡掂钨氛眯敢狸严月茄篓絮卜朽戴这毅谣第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 9.4.2 声明游标声明游标9.4.2 声明游标声明游标例例9-34 使用使用SQL-92标准的游标声明语句声明一个游标，用标准的游标声明语句声明一个游标，用于访问于访问Sales数据库中的数据库中的goods表的信息。表的信息。USE SalesGODECLARE Goods_cursor CURSORFOR SELECT * FROM GoodsFOR READ ONLY谋芋肃卫斌虫糙码脖宁识刃涎林埔算针足落验纪也锭刃怜舌否考颇嘲钩涡第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 9.4.2 声明游标声明游标n2. Transact-SQL扩展游标定义格式扩展游标定义格式n语法格式如下：语法格式如下：DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,.n 9.4.2 声明游标声明游标改泻慑悸芥瞬救句曾登印魔库珐蛇努嗣祖毫愉鸦剿返债奥航裁却益峪哦锑第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.2 9.4.2 声明游标声明游标9.4.2 声明游标声明游标例例9-35 为为customer表定义一个全局滚动动态游标，表定义一个全局滚动动态游标，用于访问顾客的编号、姓名、地址、电话信息。用于访问顾客的编号、姓名、地址、电话信息。DECLARE cur_customer CURSORGLOBAL SCROLL DYNAMICFORSELECT customer_id,customer_name,address,telphoneFROM customer刚谬磷抡睬欺师颖韦埠蓝崩伍匝蛮汾七管旺耸糟惯淆澎毅断妒湖寞膏束倡第9章TransactSQL程序设计第9章TransactSQL程序设计9.4 9.4 游标管理与应用游标管理与应用9.4.3 使用游标使用游标1. 打开游标打开游标游标声明之后，必须打开才能使用。打开游标的语法格式游标声明之后，必须打开才能使用。打开游标的语法格式如下：如下：OPEN GLOBAL cursor_name | cursor_variable_name n例如，打开例例如，打开例9-35所声明的游标操作：所声明的游标操作：OPEN cur_customer丫煌显绊矿渠闻危狗嫉倪力禁委巩供蔡为泥殆贼郭诛夺澡昧韩颖臃碉像歉第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 9.4.3 使用游标使用游标9.4.3 使用游标使用游标n2. 读取游标读取游标n一旦游标被成功打开，就可以从游标中逐行地读取数据，一旦游标被成功打开，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用以进行相关处理。从游标中读取数据主要使用FETCH命命令。其语法格式为：令。其语法格式为：FETCH NEXT|PRIOR|FIRST|LAST |ABSOLUTE n|nvar |RELATIVE n|nvar FROM GLOBAL cursor_name| cursor_variable_name INTO variable_name ,.n 果病懈领桔锯峙砰躬疏梧卢扛醋廷吸拳令找鬃好兑仓艾仁林焕皆涅茸雕部第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 9.4.3 使用游标使用游标9.4.3 使用游标使用游标返回值描 述0FETCH命令已成功执行-1FETCH命令失败或者行数据己超出了结果集-2所读取的数据已经不存在。表表9-14 fetch_status变量变量移筹咒雁触们砒鳖埂旨谴撕象件云们活肃帽鄂清炼银白与江雨娃添抱筐箱第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 9.4.3 使用游标使用游标例例9-36 打开例打开例9-35中声明的游标，读取游标中的数据。中声明的游标，读取游标中的数据。OPEN cur_customerFETCH NEXT FROM cur_customer /*取第一个数据行取第一个数据行*/WHILE fetch_status = 0 /* 检查检查fetch_status是否还有数据可取是否还有数据可取*/BEGIN FETCH NEXT FROM cur_customerEND9.4.3 使用游标使用游标屁淬鼓溅穴损翁莉义喘擦判常诌旦苗斟赎锌窖录混初痈勘高娃识炮傻类早第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 9.4.3 使用游标使用游标n3. 关闭游标关闭游标nCLOSE的语法格式为：的语法格式为：CLOSE GLOBAL cursor_name | cursor_variable_name n例如，关闭例例如，关闭例9-35中的游标中的游标cur_customer的命令：的命令：CLOSE cur_customern游标游标cur_customer在关闭后，仍可用在关闭后，仍可用OPEN语句打开继续语句打开继续读取数据行。读取数据行。9.4.3 使用游标使用游标蛆裕休庇抠碎蹭辣典胡绎凹采勾双酬驹汞膀凌滇寒罪奉匪斥傈渔遮帜鲤鸦第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 9.4.3 使用游标使用游标n4. 释放游标释放游标nDEALLOCATE命令删除游标与游标名或游标变量之间的命令删除游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。其语法格式为：联系，并且释放游标占用的所有系统资源。其语法格式为：DEALLOCATE GLOBAL cursor_name | cursor_variable_name n一旦某个游标被删除，在重新打开之前，必需再次对其进一旦某个游标被删除，在重新打开之前，必需再次对其进行声明。行声明。n例如，释放例例如，释放例9-35所定义的游标所定义的游标cur_customer。DEALLOCATE cur_customernDEALLOCATE cursor_variable_name语句只删除对游标语句只删除对游标命名变量的引用。直到批处理、存储过程或触发器结束时命名变量的引用。直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。变量离开作用域，才释放变量。9.4.3 使用游标使用游标孽佬霉饭谎胁乞疾骨胺捌酒饰古驭郊腰扫绵进活吏夺鼎昏怠奄邯泞选标降第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.3 使用游标使用游标n定义一个游标，将定义一个游标，将employeesemployees中所有职工姓名、所属部门显示出来。中所有职工姓名、所属部门显示出来。拦鸽幸匿煤屋宿蔽氦败韶恰蚤蹭库绰晦榴非踊齐氢谢瓶豹淆半差食谣码诫第9章TransactSQL程序设计第9章TransactSQL程序设计9.4 9.4 游标管理与应用游标管理与应用9.4.4 游标的应用游标的应用1. 用游标修改和删除表数据用游标修改和删除表数据n定位修改游标数据的语法格式为：定位修改游标数据的语法格式为：UPDATE table_name SET column_name=expression | DEFAULT | NULL , . nWHERE CURRENT OF GLOBAL cursor_name | cursor_variable_namen删除游标数据的语法格式为：删除游标数据的语法格式为：DELETE FROM table_nameWHERE CURRENT OF GLOBAL cursor_name | cursor_variable_name晌占揉淖疵慧料承楔雇属邀曝富偶鼎箭纪则撤乎刽谢团弟扳全晴墅廷路鳖第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用9.4.4 游标的应用游标的应用例例9-37 定义游标定义游标cur_customer，通过，通过cur_customer更新更新customer表中的表中的customer_name和和linkman_name列。列。DECLARE cur_customer CURSORFORSELECT * FROM customerFOR UPDATE OF customer_name,linkman_name /*该两列可更新该两列可更新*/OPEN cur_customer /*打开打开cur_customer游标游标*/FETCH NEXT FROM cur_customer /*将第一行数据放入缓冲区，以便更新操作将第一行数据放入缓冲区，以便更新操作*/UPDATE customerSET customer_name=南方体育用品公司南方体育用品公司,linkman_name=李强李强WHERE CURRENT OF cur_customerCLOSE cur_customer /*关闭关闭cur_customer游标游标*/ 兑矩狗讨厂殖滞过急朝樟路逃磐芽韵么哇阶学舱砧骚单勿笋瞬活搏逾朔顽第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用n若要删除若要删除customer表的一行数据，则使用以表的一行数据，则使用以下命令替换例下命令替换例9-37中的中的UPDATE语句，就可语句，就可以删除通过游标读入的一行数据。以删除通过游标读入的一行数据。DELETE FROM customerWHERE CURRENT OF cur_customer9.4.4 游标的应用游标的应用锻惕污舒剑疵棱容老獭丧霞增诺谁卷驯钳矫籍氟倪橱滥枚怀佩船巳佯采钡第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 游标的应用游标的应用n通过游标将通过游标将employeesemployees中顺序号为中顺序号为3 3的的“所属部门所属部门” ” 由由“市场部市场部”改改为新增加的为新增加的“检验部检验部”。度渐副伙靛仇遵份告撕袁叫勤俘棚需岭排找瘩轩鸭檀谚岸彦敞樱仇纶捉冀第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 游标的应用游标的应用n例如：删除例如：删除employeesemployees中第中第1 1条记录条记录湛砍眷锑娠蔓护信仙准拽拭孕缉蚕游捎灸休砍陌彻洒庭抱言浸拣剩获嫩犬第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用9.4.4 游标的应用游标的应用n2. 使用游标变量使用游标变量nCURSOR关键字还可以作为变量类型来使关键字还可以作为变量类型来使用，此时，必须要将用，此时，必须要将CURSOR进行变量声进行变量声明。其语法格式为：明。其语法格式为：DECLARE cursor_variable_name CURSOR ,.n驶欧刺唬吩嗅怒浮陌吞驯华蔡馒奶茬约办景护展褒陨窃葛锦讨客笆盾琢腻第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用9.4.4 游标的应用游标的应用游标与一个游标变量相关联的方法游标与一个游标变量相关联的方法(1) 分别定义游标变量与游标，再将游标赋给游标变量。分别定义游标变量与游标，再将游标赋给游标变量。DECLARE cur_var CURSOR /*定义游标变量定义游标变量*/DECLARE cur_customer CURSORFOR SELECT * FROM customer /*定义游标定义游标*/SET cur_var=cur_customer /*设置游标与游标变量的关联设置游标与游标变量的关联*/忙味雌衫逾胖芳份吟锑幢绷杂续踪闭魏鸟拭另浮阿表汇河围曙冉叭甲倾礁第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用9.4.4 游标的应用游标的应用 (2) 定义游标变量后，通过定义游标变量后，通过SET命令直接创建游标与游标变命令直接创建游标与游标变量关联量关联DECLARE cur_var CURSOR /*定义游标变量定义游标变量*/SET cur_var=CURSOR SCROLL KEYSET FORSELECT * FROM customer /*创建游标并与游标变量的关联创建游标并与游标变量的关联*/庶婿侠淄悯芦钉吉供侍尹增食逊瘪秆疏晶啄憎坪氰啃迭吹叫濒苦蝉贺面萝第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用例例9-38 通过游标变量来操作例通过游标变量来操作例9-35所声明的游标所声明的游标cur_customer，操作完成后删除游标变量。，操作完成后删除游标变量。DECLARE cur_var cursorSET cur_var =cur_customerOPEN cur_varFETCH NEXT FROM cur_varCLOSE cur_varDEALLOCATE cur_var9.4.4 游标的应用游标的应用所缕朔答舵再慨女彬搭你瞄酷癌道领轩呈茬或聪郊祖击惊跑溶袋箩剥耿田第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用n3. 滚动游标滚动游标n在游标定义语句中使用了关键字在游标定义语句中使用了关键字SCROLL，则可，则可以用以用FETCH语句在游标集合内向前或向后移动，语句在游标集合内向前或向后移动，也可以直接跳到集合的某一条记录。也可以直接跳到集合的某一条记录。例例9-39 定义可以任意移动的游标。定义可以任意移动的游标。DECLARE cur_customer CURSOR SCROLLREAD_ONLYFOR SELECT * FROM customer9.4.4 游标的应用游标的应用吉纤勃辰乒蹋静腺括踏响繁歇置徒嫂闲罪纂液胺裁元然尤前贿毙在塞村乒第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.4 9.4.4 游标的应用游标的应用9.4.4 游标的应用游标的应用用用FETCH语句将记录指针滚动记录语句将记录指针滚动记录OPEN cur_customerFETCH NEXT FROM cur_customerFETCH PRIOR FROM cur_customerFETCH FIRST FROM cur_customerFETCH LAST FROM cur_customerFETCH ABSOLUTE 5 FROM cur_customerFETCH RELATIVE -2 FROM cur_customer邵跪各戎甘闭皖哄免镁烫犀峦置隔祷遂图义滁校振晌惟觉律狗虐熏厘崭纠第9章TransactSQL程序设计第9章TransactSQL程序设计9.4 9.4 游标管理与应用游标管理与应用9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标1. sp_cursor_listnsp_cursor_list显示在当前作用域内的游标及其属性显示在当前作用域内的游标及其属性sp_cursor_list cursor_return=cursor_variable_name OUTPUT, cursor_scope=cursor_scope表9-15 游标作用域范围值cursor_scope值描 述1表示返回所有的LOCAL游标2表示返回所有的GLOBAL游标3表示LOCAL、GLOBAL游标都返回阜妓汾曲剥歌束痘绎掉和脸吭轩郸息仪俗旨芬抱僳宇充兴钝疮奇犀草限喧第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.5 9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标例例9-40 声明一个键值驱动游标，并使用声明一个键值驱动游标，并使用 sp_cursor_list 报告该游标的特性。报告该游标的特性。DECLARE employee_cur CURSOR KEYSET FOR SELECT employee_name FROM Employee WHERE employee_name LIKE 肖肖%OPEN employee_curDECLARE report CURSOR /*声明游标声明游标report存储来自存储来自sp_cursor_list的信息的信息 */*执行执行sp_cursor_list 将信息送游标变量将信息送游标变量report*/EXEC master.dbo.sp_cursor_list cursor_return = report OUTPUT,cursor_scope = 2FETCH NEXT FROM reportWHILE (fetch_status -1)BEGIN FETCH NEXT FROM reportENDCLOSE reportDEALLOCATE reportGOCLOSE employee_curDEALLOCATE employee_curGO荤峪雏诣刃拟涅慢腆熬冷鄙赫骆剂罢授契蕊啄扁墟婉属缮抱攻宰趟室铆凉第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.5 9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标n2. sp_describe_cursornsp_describe_cursor用来显示游标的属性。用来显示游标的属性。sp_describe_cursor cursor_return = output_cursor_variable OUTPUT , cursor_source = Nlocal, cursor_identity = Nlocal_cursor_name | , cursor_source = Nglobal, cursor_identity = Nglobal_cursor_name | , cursor_source = Nvariable, cursor_identity = Ninput_cursor_variable 9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标名宾孔框于讳约糖载初贱价厅矾锻怎涸鸿墟莽舶粉尾饲凸弱痹找汕溉康饲第9章TransactSQL程序设计第9章TransactSQL程序设计9.4.5 9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标9.4.5 使用系统存储过程管理游标使用系统存储过程管理游标例例9-41 定义并打开一个全局游标，使用定义并打开一个全局游标，使用sp_describe_cursor报告游标的特性。报告游标的特性。DECLARE employee_cur CURSOR STATIC FORSELECT employee_name FROM employeeOPEN employee_curDECLARE report CURSOREXEC master.dbo.sp_describe_cursor cursor_return = Report OUTPUT, cursor_source = Nglobal, cursor_identity = Nemployee_curFETCH NEXT from reportWHILE (FETCH_STATUS -1)BEGIN FETCH NEXT from reportENDCLOSE reportDEALLOCATE reportGOCLOSE employee_curDEALLOCATE employee_curGO膛铝冰前常询吴已婚丽摇忆儒慈浓孙麻悦炭倦怂小屉晰裴蹿须柒喷途寻伞第9章TransactSQL程序设计第9章TransactSQL程序设计本章小结本章小结（1）数据类型是学习）数据类型是学习Transact-SQL语言的基础，不同种类的数据类型具有不语言的基础，不同种类的数据类型具有不同的精度和取值范围。除基本数据类型外，同的精度和取值范围。除基本数据类型外，SQL Server也支持用户定义的数据类也支持用户定义的数据类型。型。（2）变量分为局部变量和全局变量两种。局部变量由）变量分为局部变量和全局变量两种。局部变量由DECLARE语句声明，可语句声明，可以由以由SET、SELECT或或UPDATE语句赋值；全局变量不可由用户定义。语句赋值；全局变量不可由用户定义。（3）Transact-SQL的运算符分为算术运算符、位运算符、比较运算符、逻辑的运算符分为算术运算符、位运算符、比较运算符、逻辑运算符、连接运算符，每种运算符都有专门的数据类型或操作数，各运算符间遵循运算符、连接运算符，每种运算符都有专门的数据类型或操作数，各运算符间遵循一定的优先级。一定的优先级。（4）函数是一组编译好的）函数是一组编译好的Transact-SQL语句。语句。SQL Server 2005支持的函数支持的函数分为内置函数和用户定义函数两种类型。用户定义函数可以通过分为内置函数和用户定义函数两种类型。用户定义函数可以通过SQL Server管理管理平台和平台和Transact-SQL语句来管理。语句来管理。CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION分别创建、修改、删除用户定义函数。用户定义函数的调用要分别创建、修改、删除用户定义函数。用户定义函数的调用要在函数名前加所有者作为前缀。在函数名前加所有者作为前缀。（5）程序控制流语句）程序控制流语句BEGIN和和END要一起使用，其功能是将语句块括起来。要一起使用，其功能是将语句块括起来。IFELSE语句根据条件来执行语句块。当程序有多个条件需要判断时，可以使用语句根据条件来执行语句块。当程序有多个条件需要判断时，可以使用CASE函数实现。函数实现。WHILE循环可根据条件多次重复执行语句。循环可根据条件多次重复执行语句。GOTO语句会破坏程语句会破坏程序结构化的特点，尽量不要使用。序结构化的特点，尽量不要使用。（6）游标是应用程序通过行来管理数据的一种方法。有）游标是应用程序通过行来管理数据的一种方法。有3种游标：种游标：Transact-SQL游标、游标、API服务器游标和客户游标。游标声明使用服务器游标和客户游标。游标声明使用DECLARE CURSOR语句，语句，游标的使用包括打开游标、读取数据、关闭游标、删除游标等，分别使用游标的使用包括打开游标、读取数据、关闭游标、删除游标等，分别使用OPEN、FETCH、CLOSE、DEARLLOCATE语句。语句。 健塔舷消磊蛊否绊直荫支贬傅腰铅中徐殊磋鼎盈覆漂泥网睫婚不座险暑柯第9章TransactSQL程序设计第9章TransactSQL程序设计