Transact-SQL程序设计.ppt

第9章Transact-SQL程序设计,Transact-SQL语言,Transact-SQL作为嵌入在SQLServer中的结构化查询语言，具有非常强大的数据库查询功能。不仅如此，Transact-SQL扩展了SQL的功能，引入了程序流程控制语句，具有丰富的语言元素和强大的程序设计能力。Transact-SQL语句执行的操作类似于其他程序设计语言，可以声明变量、使用流程控制语句、向用户显示信息以及执行其他许多功能。可以写出更为复杂的查询语句，或建立驻留在服务器上的基于代码的对象，如存储过程和触发器。,Transact-SQL语言的特点,1.Transact-SQL是在标准SQL基础上添加了流程控制等要素后的、由SQLServer2000支持的语言。2.Transact-SQL是一门解释性的语言，所编写的程序由SQLServer2000服务器解释执行。3.Transact-SQL具备高级编程语言的基本要素，包括变量、常量、数据类型、流程控制和函数等。4.Transact-SQL程序是DBA执行复杂管理任务和开发SQLServer2000数据库应用系统的基础。,本章内容,9.1数据与表达式9.2函数9.3程序控制流语句9.4游标管理与应用,9.1数据与表达式,9.1数据与表达式,SQLServer支持4种基本数据类型：字符和二进制数据类型、日期/时间数据类型、逻辑数据类型、数值数据类型，用于各类数据值的存储、检索和解释。SQLServer也支持用户定义的数据类型，但这只是使用户能够限定已有的数据类型，方便用户对数据的操作，而不是定义具有新的存储和检索特点的新类型。SQLServer允许在系统数据类型的基础上建立用户定义的数据类型。用户定义数据类型可以在CREATETABLE和ALTERTABLE语句中定义数据表列，并且可以将默认和规则关联于用户定义数据类型，为用户定义数据类型的列提供默认值和完整性约束。,9.1数据与表达式,9.1.1用户定义数据类型,创建用户定义数据类型时，必须提供3个参数：名称、作为新数据类型基础的系统数据类型和NULL值属性(数据类型是否允许NULL值)。,当在几个表中必须存储同一种数据类型，并且为保证列有相同的数据类型、长度和可控性时，可以使用用户定义的数据类型。,9.1数据与表达式,9.1.1用户定义数据类型,命令格式如下：sp_addtypetypename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name执行系统存储过程sp_addtype后，如果执行成功，则会返回数值0；否则返回数值1。,1使用系统存储过程来创建用户定义数据类型,9.1.1用户定义数据类型,例：为Sales数据库创建一个不允许为NULL值的test_add用户定义数据类型。,USESalesGOEXECsp_addtypetest_add,Varchar(10),NOTNULLGO此后，test_add可用为数据列或变量的数据类型。,9.1.1用户定义数据类型,2使用企业管理器创建用户定义数据类型,在企业管理器中，为Sales数据库创建一个不允许NULL值的test_add用户定义数据类型，操作步骤如下。(1)选择Sales数据库。(2)在右窗格中选择“用户定义的数据类型”项，单击鼠标右键，在出现的快捷菜单中选择“新建用户定义数据类型”命令。(3)在“用户定义的数据类型属性”对话框中的文本框内输入test_add。(4)在“数据类型”下拉列表框中，选择char。(5)在“长度”文本框中输入10。(6)选中“允许NULL值”复选框。(7)单击“确定”按钮完成创建用户自定义数据类型。,9.1数据与表达式,9.1.2常量与变量,在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。相应地，在程序运行过程中可以改变其值的数据，称为变量。,1常量,常量是表示特定数据值的符号，其格式取决于其数据类型，具有以下几种类型：(1)字符串和二进制常量字符串常量括在单引号内并包含字母、数字、字符(a-z、A-Z和0-9)以及特殊字符，如感叹号(!)、at符()和数字号(#)。例如：Cincinnati、OBrien、ProcessXis50%complete.为字符串常量。二进制常量具有前辍0 x并且是十六进制数字字符串，它们不使用引号。例如0 xAE、0 x12Ef、0 x69048AEFDD010E、0 x(空串)为二进制常量。,(2)日期/时间常量,datetime常量使用特定格式的字符日期值表示，用单引号括起来。输入时，可以使用/、.、-作日期/时间常量的分隔符。SQLServer中可使用SETDATEFORMAT命令来设定日期格式。,SQLServer日期时间格式,(3)数值常量,整型常量由没有用引号括起来且不含小数点的一串数字表示。例如，1894、2为整型常量。浮点常量主要采用科学记数法表示。例如，101.5E5、0.5E-2为浮点常量。精确数值常量由没有用引号括起来且包含小数点的一串数字表示。例如，1894.1204、2.0为精确数值常量。货币常量是以“$”为前缀的一个整型或实型常量数据，不使用引号。例如，$12.5、$542023.14为货币常量。uniqueidentifier常量是表示全局惟一标识符GUID值的字符串。可以使用字符或二进制字符串格式指定。,(4)逻辑数据常量逻辑数据常量使用数字0或1表示，并且不使用引号。非0的数字当作1处理。(5)空值在数据列定义之后，还需确定该列是否允许空值(NULL)。允许空值意味着用户在向表中插入数据时可以忽略该列值。空值可以表示整型、实型、字符型数据。,9.1.2常量与变量,变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。变量的命名使用常规标识符，即以字母、下划线(_)、at符号()、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。SQLServer将变量分为全局变量和局部变量两类全局变量由系统定义并维护，在名称前面加“”符号局部变量的首字母为单个“”,2变量,9.1.2常量与变量,(1)局部变量,局部变量使用DECLARE语句定义，仅存在于声明它的批处理、存储过程或触发器中，处理结束后，存储在局部变量中的信息将丢失。DECLARE语句的语法格式为：DECLARElocal_variabledata_type,.n用DECLARE定义的变量不能是text、ntext或image数据类型。变量名最大长度为30个字符。一条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。例如DECLAREnamevarchar(30),typeint局部变量的值使用SELECT或者PRINT语句显示。,9.1.2常量与变量,局部变量的赋值,用SELECT为局部变量赋值SELECTvariable_name=expression,nFROMWHERE例如DECLAREint_varintSELECTint_var=12/*给int_var赋值*/SELECTint_var/*将int_var的值输出到屏幕上*/局部变量没有被赋值前，其值是NULL，若要在程序中引用它，必须先赋值。,9.1.2常量与变量,在一条语句中可以同时对几个变量进行赋值,例如DECLARELastNamechar(8),Firstnamechar(8),BirthDatedatetimeSELECTLastName=Smith,Firstname=David,BirthDate=1985-2-20SELECTLastName,Firstname,BirthDate,9.1.2常量与变量,例9-1使用SELECT语句从customer表中检索出顾客编号为C0002的行，再将顾客的名字赋给变量customer。,DECLAREcustomervarchar(40),curdatedatetimeSELECTcustomer=customer_name,curdate=getdate()FROMcustomerWHEREcustomer_id=C0002,9.1.2常量与变量,SELECT赋值语句通过FROM子句可以从一个表中检索出数据并赋值给局部变量。,利用UPDATE为局部变量赋值,例9-2将sell_order表中的transporter_id列值为“T001”、goods_id列值为“G00003”的order_num列的值的2倍赋给局部变量order_num。,9.1.2常量与变量,DECLAREorder_numfloatUPDATEsell_orderSETorder_num=order_num*2WHEREtransporter_id=T001ANDgoods_id=G00003,用SET给局部变量赋值,建议使用SET语句为变量赋值，其语法格式为：SETlocal_variable=expression使用SET初始化变量的方法与SELECT语句相同，但一个SET语句只能为一个变量赋值。SET也可以使用查询给变量赋值。,9.1.2常量与变量,例9-3计算employee表的记录数并赋值给局部变量rows。DECLARErowsintSETrows=(SELECTCOUNT(*)FROMemployee)SELECTrows,(2)全局变量,全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。DBA和用户可以使用全局变量，但不能自己定义全局变量。,9.1.2常量与变量,rowcount,例如DECLARErowsintSELECTrows=rowcount在触发器中，这项技术特别有用，因为用户可能经常访问rowcount，以确保表中的所有行都是有效的。,9.1.2常量与变量,常用全局变量的介绍,rowcount存储前一条命令影响到的记录总数，除了DECLARE语句之外，其他任何语句都可以影响rowcount的值。,error,一般情况下，为了保证自己编写的代码运行起来更为流畅，以及对数据的操作更为稳妥，用户应当在执行完每条SQL语句后都检查一遍error，尤其是在存储过程和触发器中。如果error为非0值，则表明执行过程中产生了错误，此时应当在程序中采取相应的措施加以处理。error的值与rowcount一样，会随着每一条SQLServer语句的变化而改变。,9.1.2常量与变量,例9-4使服务器产生服务，并显示错误号。raiserror(miscellaneouserrormessage,16,1)/*产生一个错误*/iferror0SELECTerroraslasterror运行结果：服务器:消息50000，级别16，状态1，行1miscellaneouserrormessagelasterror0,9.1.2常量与变量,例9-5捕捉例9-4中服务器产生的错误号，并显示出来。,DECLAREmy_errorintRAISERROR(miscellaneouserrormessage,16,1)SELECTmy_error=errorIFmy_error0SELECTmy_erroraslasterror运行结果：服务器:消息50000，级别16，状态1，行2miscellaneouserrormessagelasterror50000,9.1.2常量与变量,trancounttrancount记录当前的事务数量，当某个事务当前并没有结束会话过程时，trancount的值大于0。versionversion的值代表服务器的当前版本和当前操作系统版本，是SQLServer中一项较实用的技术支持，通常对识别网络中某个未命名的服务器时非常有用。spidspid返回当前用户进程的服务器进程ID，可以用来识别sp_who输出中的当前用户进程。,9.1.2常量与变量,例9-6使用spid返回当前用户进程的ID。,SELECTspidasID,SYSTEM_USERASLoginName,USERASUserName,9.1.2常量与变量,identity当INSERT、SELECTINTO或批复制语句完成后，identity等于由语句产生的最后一个identity值。如果语句没有影响任何表的identity列，identity返回NULL；如果插入了多行，产生了多个identity值，identity返回最后产生的identity值；如果语句激发了一个或多个触发器，而这些触发器执行了产生identity值的插入操作，那么在语句执行结束后就会立即调用identity来返回由触发器产生的最后一个identity值。如果INSERT、SELECTINTO或批复制失效，或者事务撤消了，identity值并不恢复到以前的设置。,9.1.2常量与变量,nestlevelnestlevel返回当前存储过程执行的嵌套(初始值为0)。一个存储过程每次调用另一个存储过程时，嵌套层就增加，当超过最大值32时，事务就被终止。,9.1.2常量与变量,fetch_statusfetch_status返回最后一个游标语句FETCH的状态，返回值有0、-1和-2。因为fetch_status对于连接的所有游标是一个全局变量，所以使用它时要格外谨慎。当一个FETCH语句执行后，对fetch_status的检测必须在另一个游标执行任何其他FETCH语句之前进行。在任何取数据操作发生之前，fetch_status的值是不确定的。,9.1.2常量与变量,9.1数据与表达式,9.1.3运算符与表达式,运算符用来执行数据列之间的数学运算或比较操作。Transact-SQL运算符有算术运算符、位运算符、逻辑运算符、比较运算符和连接运算符5类。表达式是符号与运算符的组合。简单的表达式可以是一个常量、变量、列或函数，复杂表达式是由运算符连接一个或多个简单表达式。,9.1.3运算符与表达式,1.算术运算符与表达式,算术运算符用于数值型列或变量间的算术运算。算术运算符包括加(+)、减(-)、乘(*)、除(/)和取模(%)运算等。,例9-9使用“+”将goods表中高于9000的商品价格增加15元。SELECTgoods_name,unit_price,(unit_price+15)ASnowpriceFROMgoodsWHEREunit_price9000,9.1.3运算符与表达式,2.位运算符与表达式,位运算符用以对数据进行按位与()、或(|)、异或()、求反()等运算。优化阶段，服务器确定完成一个查询的最有效的方法；编译阶段，生成该批处理的执行计划；运行阶段，条一条地执行该批处理中的语句。批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。在一些情况下批处理被隐式地设定。例如，用查询分析器来执行一组Transact-SQL语句，这组语句将被视为一个批处理来对待。,9.3程序控制流语句,SQLServer有以下几种指定批处理的方法。(1)应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。(2)存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划。(3)由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。例如，EXEC(SELECT*FROMemployee)(4)由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如，executesp_executesqlNSELECT*fromSales.dbo.employee,1.批处理的指定,(1)CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句不能在批处理中与其他语句组合使用。批处理必须以CREATE语句开始。所有跟在该批处理后的其他语句将被解释为第一个CREATE语句定义的一部分。(2)不能在同一个批处理中更改表，然后引用新列。(3)如果EXECUTE语句是批处理中的第一句，则不需要EXECUTE关键字，否则需要EXECUTE关键字。,批处理使用时的注意事项,9.3程序控制流语句,GO是批处理的结束标志。当编译器执行到GO时会把GO前面的所有语句当成一个批处理来执行。GO命令不是Transact-SQL语句，而是可被SQLServer查询分析器识别的命令。GO命令和Transact-SQL语句不可处在同一行上。但在GO命令行中可以包含注释。在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部(用户定义)变量的作用域限制在一个批处理中，不可在GO命令后引用。RETURN可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。,2.批处理的结束与退出,9.3程序控制流语句,USESalesGO-批处理结束标志CREATEVIEWemployee_infoASSELECT*FROMemployeeGO-CREATEVIEW语句与其他语句隔离SELECT*FROMemployee_infoGO,例9-33创建一个视图，使用GO命令将CREATEVIEW语句与批处理中的其他语句(如USE、SELECT语句等)隔离。,判断下面的语句是否有效,DECLAREstud_varintGOSELECTstud_var=25GOPRINTstud_var,9.4游标管理与应用,关系数据库中的操作作用于表中的所有数据行，SELECT语句返回的结果集由满足WHERE子句条件的所有行组成。应用程序并非总是有效地把整个结果集作为一个单元来处理，他们需要一种机制来每次处理一行或几行，游标(Cursor)正是为结果集提供这种机制的工具。游标是一种处理数据的方法，它可以对结果集进行逐行处理，也可以指向结果集中的任意位置，并对该位置的数据进行处理。,9.4.1游标概述,1.游标种类,9.4游标管理与应用,(1)Transact-SQL游标：是由DECLARECURSOR语句定义，主要用在服务器上，由从客户端发送给服务器的Transact-SQL语句或批处理、存储过程、触发器中的Transact-SQL语句进行管理。Transact-SQL游标不支持提取数据块或多行数据。(2)API游标：支持在OLEDB、ODBC以及DB_library中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API游标函数，SQLServer的OLEDB提供者、ODBC驱动器或DB_library的动态链接库(DLL)都会将这些客户请求送给服务器以对API游标进行处理。(3)客户游标：当客户机缓存结果集时才使用。在客户游标中，有一个默认的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标。一般情况下，服务器游标能支持绝大多数的游标操作，但不支持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。由于API游标和Transact-SQL游标使用在服务器端，所以被称为服务器游标或后台游标，而客户端游标被称为前台游标。,2.服务器游标与默认结果集的比较,9.4.1游标概述,SQLServer以两种方式为用户返回结果集：默认结果集和服务器游标。(1)默认结果集具有的特点：开销小；取数据时提供最大性能；仅支持默认的单进、只读游标功能；返回结果行时一次行；连接时一次只支持一个活动语句；支持所有Transact-SQL语句。(2)服务器游标具有的持点：支持所有游标功能；可以为用户返回数据块；在单个连接上支持多个活动语句；以性能补偿游标功能；不支持所有返回多于一行结果集的Transact-SQL语句。,3.服务器游标与客户端游标的比较,9.4.1游标概述,使用服务器游标比使用客户游标有以下几方面的优点：(1)性能：如果要在游标中访问部分数据，使用服务器游标将提供最佳性能，因为只有被取到的数据在网络上发送，客户游标在客户端存取所有结果集。(2)更准确的定位更新：服务器游标直接支持定位操作，如UPDATE和DELETE语句，客户游标通过产生Transact-SQL搜索UPDATE语句模拟定位游标更新，如果多行与UPDATE语句的WHERE子句的条件相匹配将导致无意义更新。(3)内存使用：使用服务器游标时，客户端不需要高速存取大量数据或者保持有关游标位置的信息，这些都由服务器来完成。(4)多活动语句：使用服务器游标时，结果不会存留在游标操作之间的连接上，这就允许同时拥有多个活动的基于游标的语句。,9.4.1游标概述,SQLServer支持4种类型的服务器游标，它们是单进游标、静态游标、动态游标和键集驱动游标。(1)单进游标只支持游标按从前向后顺序提取数据，游标从数据库中提取一条记录并进行操作，操作完毕后，再提取下一条记录。(2)静态游标也称为快照游标，它总是按照游标打开时的原样显示结果集，并不反映在数据库中对任何结果集成员所做的修改，因此不能利用静态游标修改基表中的数据。静态游标打开时的结果集存储在数据库tempdb中。静态游标始终是只读的。(3)动态游标也称为敏感游标，与静态游标相对，当游标在结果集中滚动时，结果集中的数据记录的数据值、顺序和成员的变化均反映到游标上，用户所做的各种操作均可通过游标反映。(4)键集驱动游标介于静态游标和动态游标之间，兼有两者的特点。打开键集驱动游标后，游标中的成员和行顺序是固定的。键集驱动游标由一套惟一标识符控制，这些惟一标识符就是键集。用户对基表中的非关键值列插入数据或进行修改造成数据值的变化，在整个游标中都是可见。键集驱动游标的键集在游标打开时建立在数据库tempdb中。,4.服务器游标类型,使用游标的工作流程,（1）声明或创建游标（2）打开游标（3）推进游标指针，从游标的结果集中检索一行或多行数据（提取数据）（4）逐行处理游标指针所指向的数据行（5）关闭和释放游标,9.4.2声明游标,9.4游标管理与应用,SQLServer游标具有下面的处理过程：(1)声明游标，定义其特性，如游标中的行是否可以被更新。(2)执行Transact-SQL语句生成游标。(3)在游标中检索要查看的行。从游标中检索一行或几行的操作称为取数据。向前或向后执行取数据操作来检索行的行为称为滚动。(4)关闭游标。,9.4.2声明游标,语法格式如下：DECLAREcursor_nameINSENSITIVESCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_name,.n,1.SQL-92游标定义格式,9.4.2声明游标,SCROLL的取值,例9-34使用SQL-92标准的游标声明语句声明一个游标，用于访问Sales数据库中的goods表的信息。,USESalesGODECLAREGoods_cursorCURSORFORSELECT*FROMGoodsFORREADONLY,9.4.2声明游标,语法格式如下：DECLAREcursor_nameCURSORLOCAL|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FORWARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICTYPE_WARNINGFORselect_statementFORUPDATEOFcolumn_name,.n,2.Transact-SQL扩展游标定义格式,9.4.2声明游标,例9-35为customer表定义一个全局滚动动态游标，用于访问顾客的编号、姓名、地址、电话信息。,DECLAREcur_customerCURSORGLOBALSCROLLDYNAMICFORSELECTcustomer_id,customer_name,address,telephoneFROMcustomer,9.4游标管理与应用,9.4.3使用游标,游标声明之后，必须打开才能使用。打开游标的语法格式如下：OPENGLOBALcursor_name|cursor_variable_name,例如，打开例9-35所声明的游标操作：OPENcur_customer,1.打开游标,提示：游标打开后，使用全局变量cursor_rows可以得到最后打开的游标中符合条件的行数。,9.4.3使用游标,2.读取游标,一旦游标被成功打开，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH命令。其语法格式为：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,.n,9.4.3使用游标,fetch_status变量,rowcount变量,保存自游标打开后的第一个FETCH语句，直到最近一次FETCH语句为止，已从游标结果集中提取的行数。,9.4.3使用游标,OPENcur_customerFETCHNEXTFROMcur_customer/*取第一个数据行*/WHILEfetch_status=0/*检查fetch_status是否还有数据可取*/BEGINFETCHNEXTFROMcur_customerEND,例9-36打开例9-35中声明的游标，读取游标中的数据。,9.4.3使用游标,CLOSE的语法格式为：CLOSEGLOBALcursor_name|cursor_variable_name,3.关闭游标,例如，关闭例9-35中的游标cur_customer的命令：CLOSEcur_customer游标cur_customer在关闭后，仍可用OPEN语句打开继续读取数据行。,9.4.3使用游标,DEALLOCATE命令删除游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。其语法格式为：DEALLOCATEGLOBALcursor_name|cursor_variable_name一旦某个游标被删除，在重新打开之前，必需再次对其进行声明。DEALLOCATEcursor_variable_name语句只删除对游标命名变量的引用。直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。,4.释放游标,例如，释放例9-35所定义的游标cur_customer。DEALLOCATEcur_customer,9.4游标管理与应用,9.4.4游标的应用,1.用游标修改和删除表数据定位修改游标数据的语法格式为：UPDATEtable_nameSETcolumn_name=expression|DEFAULT|NULL,.nWHERECURRENTOFGLOBALcursor_name|cursor_variable_name删除游标数据的语法格式为：DELETEFROMtable_nameWHERECURRENTOFGLOBALcursor_name|cursor_variable_name,9.4.4游标的应用,例9-37定义游标cur_customer，通过cur_customer更新customer表中的customer_name和linkman_name列。,DECLAREcur_customerCURSORFORSELECT*FROMcustomerFORUPDATEOFcustomer_name,linkman_name/*该两列可更新*/OPENcur_customer/*打开cur_customer游标*/FETCHNEXTFROMcur_customer/*将第一行数据放入缓冲区，以便更新操作*/UPDATEcustomerSETcustomer_name=南方体育用品公司,linkman_name=李强WHERECURRENTOFcur_customerCLOSEcur_customer/*关闭cur_customer游标*/,9.4.4游标的应用,若要删除customer表的一行数据，则使用以下命令替换例9-37中的UPDATE语句，就可以删除通过游标读入的一行数据。DELETEFROMcustomerWHERECURRENTOFcur_customer,9.4.4游标的应用,2.使用游标变量,CURSOR关键字还可以作为变量类型来使用，此时，必须要将CURSOR进行变量声明。其语法格式为：DECLAREcursor_variable_nameCURSOR,.n,9.4.4游标的应用,游标与一个游标变量相关联的方法,方法一：分别定义游标变量与游标，再将游标赋给游标变量。,DECLAREcur_varCURSOR/*定义游标变量*/DECLAREcur_customerCURSORFORSELECT*FROMcustomer/*定义游标*/SETcur_var=cur_customer/*设置游标与游标变量的关联*/,9.4.4游标的应用,DECLAREcur_varCURSOR/*定义游标变量*/SETcur_var=CURSORSCROLLKEYSETFORSELECT*FROMcustomer/*创建游标并与游标变量的关联*/,方法二：定义游标变量后，通过SET命令直接创建游标与游标变量关联。,9.4.4游标的应用,DECLAREcur_varcursorSETcur_var=cur_customerOPENcur_varFETCHNEXTFROMcur_varCLOSEcur_varDEALLOCATEcur_var,例9-38通过游标变量来操作例9-35所声明的游标cur_customer，操作完成后删除游标变量。,9.4.4游标的应用,在游标定义语句中使用了关键字SCROLL，则可以用FETCH语句在游标集合内向前或向后移动，也可以直接跳到集合的某一条记录。,3.滚动游标,例9-39定义可以任意移动的游标。DECLAREcur_customerCURSORSCROLLREAD_ONLYFORSELECT*FROMcustomer,9.4.4游标的应用,用FETCH语句将记录指针滚动记录,OPENcur_customerFETCHNEXTFROMcur_customerFETCHPRIORFROMcur_customerFETCHFIRSTFROMcur_customerFETCHLASTFROMcur_customerFETCHABSOLUTE5FROMcur_customerFETCHRELATIVE-2FROMcur_customer,9.4游标管理与应用,9.4.5使用系统存储过程管理游标,在建立一个游标后，便可利用系统存储过程对游标进行管理，管理游标的系统过程主要有以下几个：sp_cursor_listsp_describe_cursorsp_describe_cursor_tablessp_describe_cursor_columns,9.4游标管理与应用,1.sp_cursor_list,sp_cursor_list显示在当前作用域内的游标及其属性其命令格式为：sp_cursor_listcursor_return=cursor_variable_nameOUTPUT,cursor_scope=cursor_scope其中，cursor_variable_name为游标变量，cursor_scope指出游标的作用域。,例9-40声明一个键值驱动游标，并使用sp_cursor_list报告该游标的特性。,DECLAREemployee_curCURSORKEYSETFORSELECTemployee_nameFROMEmployeeWHEREemployee_nameLIKE肖%OPENemployee_cur/*打开游标employee_cur*/*声明游标变量report以存储来自sp_cursor_list的游标信息*/DECLAREreportCURSOR/*执行sp_cursor_list将信息送游标变量report*/EXECmaster.dbo.sp_cursor_listcursor_return=reportOUTPUT,cursor_scope=2FETCHNEXTFROMreportWHILE(fetch_status-1)BEGINFETCHNEXTFROMreportENDCLOSEreportDEALLOCATEreportGOCLOSEemployee_curDEALLOCATEemployee_curGO,sp_describe_cursor用来显示游标的属性其语法格式如下：sp_describe_cursorcursor_return=output_cursor_variableOUTPUT,cursor_source=Nlocal,cursor_identity=Nlocal_cursor_name|,cursor_source=Nglobal,cursor_identity=Nglobal_cursor_name|,cursor_source=Nvariable,cursor_identity=Ninput_cursor_variable其中，output_cursor_variable为游标变量，接收游标的输出；cursor_source指定进行报告的游标是LOCAL、GLOBAL、或游标变量；cursor_identity指定具有LOCAL类型、GLOBAL类型、或关联的游标变量的名称。,2.sp_describe_cursor,9.4.5使用系统存储过程管理游标,例9-41定义并打开一个全局游标，使用sp_describe_cursor报告游标的特性。,DECLAREemployee_curCURSORSTATICFORSELECTemployee_nameFROMemployeeOPENemployee_curDECLAREreportCURSOREXECmaster.dbo.sp_describe_cursorcursor_return=ReportOUTPUT,cursor_source=Nglobal,cursor_identity=Nemployee_curFETCHNEXTfromreportWHILE(FETCH_STATUS-1)BEGINFETCHNEXTfromreportENDCLOSEreportDEALLOCATEreportGOCLOSEemployee_curDEALLOCATEemployee_curGO,(1)数据类型是学习Transact-SQL语言的基础，不同种类的数据类型具有不同的精度和取值范围。(2)变量分为局部变量和全局变量两种。局部变量由DECLARE语句声明，可以由SET、SELECT或UPDATE语句赋值；全局变量不可由用户定义。(3)Transact-SQL的运算符分为算术运算符、位运算符、比较运算符、逻辑运算符、连接运算符，每种运算符都有专门的数据类型或操作数，各运算符间遵循一定的优先级。(4)函数是一组编译好的Transact-SQL语句。SQLServer2000支持的函数分为内置函数和用户定义函数两种类型。用户定义函数可以通过企业管理器和Transact-SQL语句来管理。CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION分别创建、修改、删除用户定义函数。用户定义函数的调用要在函数名前加所有者作为前缀。,本章小结,(5)程序控制流语句BEGIN和END要一起使用，其功能是将语句块括起来。IFELSE语句根据条件来执行语句块。当程序有多个条件需要判断时，可以使用CASE函数实现。WHILE循环可根据条件多次重复执行语句。GOTO语句会破坏程序结构化的特点，尽量不要使用。(6)游标是应用程序通过行来管理数据的一种方法。有3种游标：Transact-SQL游标、API服务器游标和客户游标。游标声明使用DECLARECURSOR语句，游标的使用包括打开游标、读取数据、关闭游标、删除游标等，分别使用OPEN、FETCH、CLOSE、DEARLLOCATE语句。,本章小结,