资源描述
第第8章章T-SQL语言语言8.1批处理、脚本和注释批处理、脚本和注释8.2常量、变量和表达式常量、变量和表达式8.3流程控制语句流程控制语句8.4系统内置函数系统内置函数8.5用户自定义函数用户自定义函数8.6游标及其使用游标及其使用教学要求：通过本章学习，读者应掌握以教学要求：通过本章学习，读者应掌握以下内容：下内容：批处理的概念批处理的概念数据类型与常量的表示方法数据类型与常量的表示方法全局变量与局部变量的使用全局变量与局部变量的使用运算符与表达式的使用运算符与表达式的使用流程控制语句的使用流程控制语句的使用系统函数与自定义函数系统函数与自定义函数游标的使用游标的使用8.1批处理、脚本和注释批处理、脚本和注释8.1.1批处理批处理建立批处理如同编写建立批处理如同编写SQL语句，区别在于语句，区别在于它是多条语句同时执行的，用它是多条语句同时执行的，用GO语句作为语句作为一个批处理的结束。一个批处理的结束。注意：注意：GO语句行必须单独存在，不能含有语句行必须单独存在，不能含有其他的其他的SQL语句，也不可以有注释。语句，也不可以有注释。一些一些SQL语句不可以放在一个批处理中进行处理，它们需语句不可以放在一个批处理中进行处理，它们需要遵守以下规则。要遵守以下规则。（1）大多数）大多数CREATE命令要在单个批命令中执行，但命令要在单个批命令中执行，但CREATEDATABASE、CREATETABLE和和CREATEINDEX例外。例外。（2）调用存储过程时，如果它不是批处理中的第一个语）调用存储过程时，如果它不是批处理中的第一个语句，则在其前面必须加上句，则在其前面必须加上EXECUTE，或简写，或简写EXEC。（3）不能把规则和默认值绑定到表的字段或用户定义数）不能把规则和默认值绑定到表的字段或用户定义数据类型上之后，在同一个批处理中使用它们。据类型上之后，在同一个批处理中使用它们。（4）不能在给表字段定义了一个）不能在给表字段定义了一个CHECK约束后，在同一约束后，在同一个批处理中使用该约束。个批处理中使用该约束。（5）不能在修改表的字段名后，在同一个批处理中引用）不能在修改表的字段名后，在同一个批处理中引用该新字段名。该新字段名。【例【例8.1】查询学生成绩信息，要求使用查询学生成绩信息，要求使用“学生信息学生信息”表和表和“成绩成绩”表，并且显示课程名称。表，并且显示课程名称。-新建视图新建视图学生成绩学生成绩CREATEVIEW学生成绩学生成绩ASSELECTA.stu_id,A.stu_name,A.dept_id,B.course_id,B.scoreFROM学生信息学生信息AINNERJOIN成绩成绩BONA.stu_id=B.stu_idGO-查询学生成绩信息查询学生成绩信息SELECTA.stu_name,C.dept_name,B.course_name,A.scoreFROM学生成绩学生成绩AINNERJOIN课程课程BONA.course_id=B.course_idINNERJOIN系部系部CONA.dept_id=C.dept_idGO8.1.2脚本脚本脚本是批处理的存在方式，将一个或多个脚本是批处理的存在方式，将一个或多个批处理组织到一起就是一个脚本，例如我批处理组织到一起就是一个脚本，例如我们执行命令的各个实例都可以称为一个脚们执行命令的各个实例都可以称为一个脚本。将脚本保存到磁盘文件上就称为脚本本。将脚本保存到磁盘文件上就称为脚本文件。文件。8.1.3注释注释注释，也称为注解，是写在程序代码中的说明性文字，对注释，也称为注解，是写在程序代码中的说明性文字，对程序的结构及功能进行文字说明。注释内容不被系统编译，程序的结构及功能进行文字说明。注释内容不被系统编译，也不被程序执行。也不被程序执行。注释还可以用于描述复杂的计算或者解释编程的方法。注释还可以用于描述复杂的计算或者解释编程的方法。1.行内注释行内注释行内注释的语法格式为：行内注释的语法格式为：注释文本注释文本从双连字符从双连字符“”开始到行尾均为注释，但前面可以有执开始到行尾均为注释，但前面可以有执行的代码。对于多行注释，必须在每个注释行的开始都是行的代码。对于多行注释，必须在每个注释行的开始都是用双连字符。用双连字符。2.块注释块注释块注释的语法格式为：块注释的语法格式为：/*注释文本注释文本*/或：或：/*注释文本注释文本*/8.2常量、变量和表达式常量、变量和表达式8.2.1常量常量（1）字符串常量）字符串常量（2）二进制常量）二进制常量（3）bit常量常量（4）数值常量）数值常量（5）货币常量）货币常量（6）日期时间常量）日期时间常量8.2.2变量变量变量又分为局部变量和全局变量，局部变量是一个能够保变量又分为局部变量和全局变量，局部变量是一个能够保存特定数据类型实例的对象，是程序中各种类型数据的临存特定数据类型实例的对象，是程序中各种类型数据的临时存储单元，用在批处理内时存储单元，用在批处理内SQL语句之间传递数据。全局语句之间传递数据。全局变量是系统给定的特殊变量。变量是系统给定的特殊变量。1.局部变量局部变量局部变量是用户在程序中定义的变量，一次只能保存一个局部变量是用户在程序中定义的变量，一次只能保存一个值，仅用于声明它的批、存储过程或触发器中。批处理结值，仅用于声明它的批、存储过程或触发器中。批处理结束后，存储在局部变量中的信息将丢失。束后，存储在局部变量中的信息将丢失。局部变量的定义遵守局部变量的定义遵守SQLServer标识符的命名规则，其标识符的命名规则，其开始字母必须使用开始字母必须使用符号，最长为符号，最长为128个字符。个字符。（1）局部变量的定义）局部变量的定义语法形式如下：语法形式如下：DECLARE变量名变量名数据类型数据类型,.N（2）局部变量的赋值方法）局部变量的赋值方法其语法形式为：其语法形式为：SET变量名变量名=表达式表达式或者或者SELECT变量名变量名=表达式表达式,.N【例例8.2】局部变量的定义与赋值。局部变量的定义与赋值。DECLAREMY_VAR1VARCHAR(5),MY_VAR2CHAR(8)SELECTMY_VAR1=你好你好!,MY_VAR2=happyPRINTMY_VAR1+MY_VAR2SELECTMY_VAR1+MY_VAR2【例【例8.5】查询查询“学生信息学生信息”表中女同学的记录表中女同学的记录(在在SELECT语句中使语句中使用由用由SET赋值的局部变量赋值的局部变量)。USEjxgl-打开打开jxgl数据库数据库GODECLARESCHAR(2)-声明局部变量声明局部变量SETS=女女-给局部变量赋值给局部变量赋值-根据局部变量的值进行查询根据局部变量的值进行查询SELECTstu_idAS学号学号,stu_nameAS姓名姓名,stu_sexAS性别性别,stu_birthAS出生时间出生时间FROM学生信息学生信息WHEREstu_sex=SGO2.全局变量全局变量全局变量是全局变量是SQLServer系统提供并赋值的系统提供并赋值的变量。用户不能定义全局变量，也不能用变量。用户不能定义全局变量，也不能用SET语句来修改全局变量。通常是将全局变语句来修改全局变量。通常是将全局变量的值赋给局部变量，以便保存和处理。量的值赋给局部变量，以便保存和处理。事实上，在事实上，在SQLServer中，全局变量是一中，全局变量是一组特定的函数，它们的名称是以组特定的函数，它们的名称是以开头，开头，而且不需要任何参数，在调用时无需在函而且不需要任何参数，在调用时无需在函数名后面加圆括号，这些函数也称为无参数名后面加圆括号，这些函数也称为无参数函数。见表数函数。见表9-1名名称称说说明明CONNECTIONS返回当前服务器的连接的数目返回当前服务器的连接的数目ROWCOUNT返回上一条返回上一条T-SQL语句影响的数据行数语句影响的数据行数ERROR返回上一条返回上一条T-SQL语句执行后的错误号语句执行后的错误号PROCID返回当前存储过程的返回当前存储过程的ID号号SERVICENAME返返回回正正在在运运行行SQLServer服服务务器器所所使使用用的的登登录录表表键名键名SERVERNAME返回运行返回运行SQLServer的本地服务器名称的本地服务器名称VERSION返回当前返回当前SQLServer服务器的版本和处理器类型服务器的版本和处理器类型LANGUAGE返回当前返回当前SQLServer服务器的语言服务器的语言MAX_CONNECTIONS返回返回SQLServer上允许同时连接的最大用户数上允许同时连接的最大用户数8.2.3运算符与表达式运算符与表达式1.算术运算符与算术表达式算术运算符与算术表达式算术运算符包括加（算术运算符包括加（+）、减（）、减（）、乘（）、乘（*）、除）、除（/）、和取模（）、和取模（%）。对于加、减、乘、除这）。对于加、减、乘、除这4种算术运算符，计算的两个表达式可以是数字数种算术运算符，计算的两个表达式可以是数字数据类型分类的任何数据类型；对于取模运算符，据类型分类的任何数据类型；对于取模运算符，要求操作数的数据类型为要求操作数的数据类型为int、smallint和和tinyint。如果在一个表达式中，出现多个算术运算符，运如果在一个表达式中，出现多个算术运算符，运算符优先级顺序如下：乘、除、取模运算为同一算符优先级顺序如下：乘、除、取模运算为同一优先级，加、减运算优先级为次。优先级，加、减运算优先级为次。2.字符串连接运算符与字符串表达式字符串连接运算符与字符串表达式字符串连接运算符使用加号（字符串连接运算符使用加号（+）表示，可）表示，可以实现字符串的连接。其他的字符操作都以实现字符串的连接。其他的字符操作都是通过函数，如是通过函数，如SUBSTRING来操作的。来操作的。字符串连接运算符可以操作的数据类型有字符串连接运算符可以操作的数据类型有char、varchar、text、nchar、nvarchar和和ntext。在。在INSERT语句或者赋值语句中，语句或者赋值语句中，如果字符串为空，那么就作为空的字符串如果字符串为空，那么就作为空的字符串来处理。来处理。3.位运算符与位表达式位运算符与位表达式位运算符可以对整型类型或二进制数据进行按位位运算符可以对整型类型或二进制数据进行按位与（与（&）、或（）、或（|）、异或（）、异或（）、求反（）、求反（）等逻）等逻辑运算。对整型数据进行位运算时，首先把它们辑运算。对整型数据进行位运算时，首先把它们转换为二进制数，然后再进行计算。其中与、或、转换为二进制数，然后再进行计算。其中与、或、异或运算需要两个操作数，它们可以是整型或二异或运算需要两个操作数，它们可以是整型或二进制数据（进制数据（image数据类型除外），但运算符左数据类型除外），但运算符左右两侧的操作数不能同时为二进制数据。求反运右两侧的操作数不能同时为二进制数据。求反运算符是一个单目运算符，它只能对算符是一个单目运算符，它只能对int、smallint、tinyint或或bit类型的数据进行求反运算。类型的数据进行求反运算。4.比较运算符与比较表达式比较运算符与比较表达式比较运算符用来对多个表达式进行比较。比较运比较运算符用来对多个表达式进行比较。比较运算符可以比较除算符可以比较除text、ntext和和image数据类型以数据类型以外的其他所有的数据类型表达式。在外的其他所有的数据类型表达式。在SQLServer中，比较运算符包括：等于（中，比较运算符包括：等于（=）、大于（）、大于（）、）、大于或等于（大于或等于（=）、小于（）、小于（）、小于或等于）、小于或等于（=）、不等于（）、不等于（或或!=）、不小于（）、不小于（!）。）。比较运算符的结果是布尔值比较运算符的结果是布尔值TRUE（表示两个表（表示两个表达式相同）、达式相同）、FALSE（表示两个表达式不同）或（表示两个表达式不同）或UNKNOWN，返回布尔数据类型的表达式称为布，返回布尔数据类型的表达式称为布尔表达式。尔表达式。5.逻辑运算符与逻辑表达式逻辑运算符与逻辑表达式逻辑运算符见表逻辑运算符见表8-36.赋值运算符赋值运算符Transact-SQL中有一个赋值运算符，即等号中有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。题和为列定义值的表达式之间建立关系。7.运算符的优先级运算符的优先级当一个复杂的表达式中包含多种运算符时，运算符的优先当一个复杂的表达式中包含多种运算符时，运算符的优先顺序将决定表达式的计算和比较顺序。在顺序将决定表达式的计算和比较顺序。在SQLServer中，中，运算符的优先等级从高到低如下。运算符的优先等级从高到低如下。+（正）、（正）、（负）、（负）、（位求反）（位求反）*（乘）、（乘）、/（除）、（除）、%（求余）（求余）+（加）、（加）、+（连接）、（连接）、（减）（减）=、=、=、!=、!、!=90THEN优优WHENscore=80THEN良良WHENscore=70THEN中中WHENscore=60THEN及格及格ELSE不及格不及格ENDFROM成绩成绩8.3.4无条件转移语句无条件转移语句GOTOGOTO语句可以使程序直接跳到指定的标识语句可以使程序直接跳到指定的标识符位置处继续执行，而位于符位置处继续执行，而位于GOTO语句和标语句和标识符之间的程序将不会被执行。标识符后识符之间的程序将不会被执行。标识符后面加冒号（：）。面加冒号（：）。GOTO语句可以用在语句语句可以用在语句块、批处理和存储过程中。块、批处理和存储过程中。GOTO语句也可语句也可以嵌套使用。以嵌套使用。语法格式：语法格式：GOTO标号标号【例例8.17】使用使用GOTO语句求语句求5的阶乘。的阶乘。DECLAREIINT,TINTSETI=1SETT=1LABEL:SETT=T*ISETI=I+1IFI0BEGINSETMY_RESULT=MY_RESULT*2SETMY_VAR=MY_VAR-1ENDPRINTMY_RESULT8.3.7RETURN语句语句RETURN语句实现无条件退出执行的批处理命令、存储过程或触发器。语句实现无条件退出执行的批处理命令、存储过程或触发器。RETURN语句可以返回一个整数给调用它的过程或应用程序，返回值语句可以返回一个整数给调用它的过程或应用程序，返回值0表明成功返回，保留表明成功返回，保留1到到99代表不同的出错原因。代表不同的出错原因。【例【例8.22】在在“成绩成绩”表中查询某学生的某科成绩是否及格。表中查询某学生的某科成绩是否及格。-创建存储过程创建存储过程MY_TESTCREATEPROCEDUREMY_TESTXHCHAR(10),KCHCHAR(4)ASIF(SELECTscoreFROM成绩成绩WHEREstu_id=XHANDcourse_id=KCH)=60RETURN1ELSERETURN2-调用存储过程调用存储过程MY_TEST，通过返回值判断是否及格，通过返回值判断是否及格DECLAREIINTEXECI=MY_TEST2013010104,112011IFI=1PRINT及格及格ELSEPRINT不及格不及格8.4系统内置函数系统内置函数8.4.1行集函数行集函数详见教材详见教材P2048.4.2聚合函数聚合函数详见教材详见教材P2058.4.3标量函数标量函数详见教材详见教材P205始始8.5用户自定义函数用户自定义函数8.5.1用户自定义函数的创建与调用用户自定义函数的创建与调用用户定义函数可以有输入参数并返回值，用户定义函数可以有输入参数并返回值，但没有输出参数。当函数的参数有默认值但没有输出参数。当函数的参数有默认值时，调用该函数时必须明确指定时，调用该函数时必须明确指定DEFAULT关键字才能获取默认值。关键字才能获取默认值。SQLServer支持支持3种类型的用户自定义种类型的用户自定义函数：标量函数、内嵌表值函数、多语句函数：标量函数、内嵌表值函数、多语句表值函数。表值函数。1.标量用户定义函数标量用户定义函数语法格式：语法格式：CREATFUNCTIONschema_name.function_name(parameter_namescalar_data_type=default,n)RETURNSscalar_data_type*WITHENCRYPTION|SCHEMABINDING,nASBEGINfunction_bodyRETURNscalar_expressionEND其中各参数说明如下：其中各参数说明如下：(1)schema_name。函数所属架构名称。函数所属架构名称。(2)function_name。用户自定义函数名，命名须符合标。用户自定义函数名，命名须符合标识符规则，对其所有者来说，该名称在数据库中必须唯一。识符规则，对其所有者来说，该名称在数据库中必须唯一。(3)parameter_name。用户自定义函数的参数名，。用户自定义函数的参数名，CREATEFUNCTION语句中可以声明一个或多个参数，语句中可以声明一个或多个参数，用用“”作为第一个字符来指定参数名，每个函数的参数作为第一个字符来指定参数名，每个函数的参数都局部于该函数。都局部于该函数。(4)scalar_data_type。参数的数据可以为。参数的数据可以为SQLServer支支持的基本标量类型，不能为持的基本标量类型，不能为timestamp类型，也不能是非类型，也不能是非标量类型。标量类型。(5)scalar_data_type*。函数的返回值类型，可以是。函数的返回值类型，可以是SQLServer支持的基本标量类型，但支持的基本标量类型，但text、ntext、image、timestamp类型除外。类型除外。(6)scalar_expression。函数所返回的表达式。函数所返回的表达式。(7)function_body。函数体，由。函数体，由T-SQL语句序列构成。语句序列构成。(8)ENCRYPTION。用于指定对。用于指定对CREATEFUNCTION语语句的文本进行加密，这样就可以避免将函数作为句的文本进行加密，这样就可以避免将函数作为SQLServer复制的一部分发布。复制的一部分发布。(9)SCHEMABINDING。用于指定将函数绑定到它所引用。用于指定将函数绑定到它所引用的数据库对象。的数据库对象。【例【例8.33】在在jxgl数据库中，创建一个计算学生年龄的函数。该函数数据库中，创建一个计算学生年龄的函数。该函数接收学生的学号，通过查询接收学生的学号，通过查询“学生信息学生信息”表返回该学生的年龄。表返回该学生的年龄。-如果存在同名函数则删除如果存在同名函数则删除IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME=年龄年龄ANDTYPE=FN)DROPFUNCTIONdbo.年龄年龄GO-建立新的函数建立新的函数CREATEFUNCTIONdbo.年龄年龄(XHASCHAR(10),CURRENTDATEASDATETIME)RETURNSINTASBEGINDECLARECSSJDATETIMESELECTCSSJ=stu_birthFROM学生信息学生信息WHEREstu_id=XHRETURNDATEDIFF(YY,CSSJ,CURRENTDATE)ENDGO当调用自定义标量函数时，必须提供至少由两部当调用自定义标量函数时，必须提供至少由两部分组成的名称分组成的名称(schema_name.function_name）。）。可按以下方式调用函数。可按以下方式调用函数。(1)在在SELECT语句中调用语句中调用调用形式：调用形式：schema_name.function_name（参数（参数1，参数，参数2，参数，参数n）实参为已赋值的局部变量或表达式，实参的顺序实参为已赋值的局部变量或表达式，实参的顺序要与函数创建时的顺序完全一致。要与函数创建时的顺序完全一致。2.内嵌表值函数内嵌表值函数语法格式：语法格式：1）创建内嵌表值函数）创建内嵌表值函数创建内嵌表值函数的语法格式如下。创建内嵌表值函数的语法格式如下。CREATEFUNCTIONschema_name.function_name(parameter_namescalar_data_type=default,n)RETURNSTABLEWITHENCRYPTION|SCHEMABINDING,nASRETURN(SELECT_statment)【例【例8.36】在在jxgl数据库中创建内嵌表值函数。该函数接收学生的学数据库中创建内嵌表值函数。该函数接收学生的学号，给出该学生的考试科目及成绩。号，给出该学生的考试科目及成绩。-如果存在同名函数则删除如果存在同名函数则删除IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHERENAME=kskmcjANDTYPE=IF)DROPFUNCTIONdbo.kskmcjGO-建立新的函数建立新的函数CREATEFUNCTIONdbo.kskmcj(stu_idASvarchar(10)RETURNSTABLEASRETURN(SELECTA.stu_idAS学号学号,A.stu_nameAS姓名姓名,C.course_nameAS课程名课程名,B.scoreAS成绩成绩FROM学生信息学生信息AINNERJOIN成绩成绩BONA.stu_id=B.stu_idINNERJOIN课程课程CONB.course_id=C.course_idWHEREA.stu_id=stu_id)GO2)内嵌表值函数的调用内嵌表值函数的调用内嵌表值函数只能通过内嵌表值函数只能通过SELECT语句进行调用，语句进行调用，调用时，可以仅使用函数名。调用时，可以仅使用函数名。-调用例调用例8.36函数显示课程名和成绩函数显示课程名和成绩SELECT*FROMdbo.kskmcj(2013030301)GO3.多语句表值函数多语句表值函数语法格式：语法格式：1)创建多语句表值函数创建多语句表值函数使用使用T-SQL语句创建多语句表值函数的语法格式如下。语句创建多语句表值函数的语法格式如下。CREATEFUNCTIONschema_name.function_name(parameter_namescalar_data_type=default,n)RETURNSreturn_variableTABLEWITHENCRYPTION|SCHEMABINDING,nASBEGINfunction_bodyRETURNEND【例【例8.37】在】在jxgl数据库中，创建多语句表值函数。该函数接收系别名称，给出该数据库中，创建多语句表值函数。该函数接收系别名称，给出该系所有学生的考试科数。系所有学生的考试科数。CREATEFUNCTIONdbo.dept_score(deptASvarchar(20)RETURNSdept_scoreTABLE(学号学号char(10)PRIMARYKEY,姓名姓名varchar(10),科数科数INT)ASBEGINDECLAREksTABLE(学号学号char(10),科数科数INT)INSERTksSELECTstu_idAS学号学号,科数科数=COUNT(stu_id)FROM成绩成绩GROUPBYstu_idINSERTdept_scoreSELECTA.stu_idAS学号学号,A.stu_nameAS姓名姓名,B.科数科数FROM学生信息学生信息ALEFTJOINksBONA.stu_id=B.学号学号WHEREdept_id=(SELECTdept_idFROM系部系部WHEREdept_name=dept)RETURNENDGO8.5.2查看与修改用户自定义函数查看与修改用户自定义函数1.用户自定义函数的查看用户自定义函数的查看查看用户自定义函数的信息可以在查询分析器中利用系统查看用户自定义函数的信息可以在查询分析器中利用系统存储过程存储过程sp_helptext、sp_depends和和sp_help等对自定等对自定义函数的不同信息进行查看。义函数的不同信息进行查看。1)sp_helptext利用该存储过程，可以查看自定义函数的定义文本信息。利用该存储过程，可以查看自定义函数的定义文本信息。要求该函数在创建时不带要求该函数在创建时不带WITHENCRYPTION子句。子句。语法格式：语法格式：sp_helptextobjname=name参数说明：参数说明：objname=name是要查看的自定义函数的是要查看的自定义函数的名称。要求该函数必须在当前数据库中。名称。要求该函数必须在当前数据库中。2)sp_depends利用该存储过程，可以查看自定义函数的相关性信息。利用该存储过程，可以查看自定义函数的相关性信息。语法格式：语法格式：sp_dependsobjname=name参数说明：参数说明：objname=name是要查看的自定义函数的是要查看的自定义函数的名称。要求该函数必须在当前数据库中。名称。要求该函数必须在当前数据库中。3)sp_help利用该存储过程，可以查看自定义函数的一般性信息。利用该存储过程，可以查看自定义函数的一般性信息。语法格式：语法格式：sp_helpobjname=name参数说明：参数说明：objname=name是要查看的自定义函数的是要查看的自定义函数的名称。要求该函数必须在当前数据库中。名称。要求该函数必须在当前数据库中。2.用户自定义函数的修改用户自定义函数的修改使用使用ALTERFUNCTION语句可以修改用户自定义函数，语句可以修改用户自定义函数，其格式与定义函数相同。修改函数不能更改函数的类型和其格式与定义函数相同。修改函数不能更改函数的类型和名称，因此它不会破坏用户定义函数的依附关系。名称，因此它不会破坏用户定义函数的依附关系。ALTERFUNCTION命令不能与其他的命令不能与其他的T-SQL命令位于同一个批处命令位于同一个批处理中。这里只给出修改函数的理中。这里只给出修改函数的T-SQL命令格式。命令格式。3.用户自定义函数的重命名用户自定义函数的重命名用户定义函数的重命名可以通过对象资源管理器来实现，用户定义函数的重命名可以通过对象资源管理器来实现，也可以由系统存储过程也可以由系统存储过程sp_rename实现。使用实现。使用sp_rename命令重命名自定义函数的格式如下：命令重命名自定义函数的格式如下：EXECsp_renameobjname,new_objname8.5.3删除用户自定义函数删除用户自定义函数当自定义函数不再需要时，就可以将其删当自定义函数不再需要时，就可以将其删除。自定义函数的删除既可以通过除。自定义函数的删除既可以通过SQL语语句，也可以通过对象资源管理器手动删除。句，也可以通过对象资源管理器手动删除。使用使用T-SQL语句删除自定义函数的语法格式语句删除自定义函数的语法格式如下：如下：DROPFUNCTIONschema_name.function_name,n8.6游标及其使用游标及其使用8.6.1游标概述游标概述游标支持以下功能。游标支持以下功能。（1）定位在结果集的特定行。）定位在结果集的特定行。（2）从结果集的当前位置检索一行或多行。）从结果集的当前位置检索一行或多行。（3）支持对结果集中当前位置的行进行数据修改。）支持对结果集中当前位置的行进行数据修改。（4）为由其他用户对显示在结果集中的数据库数据所做的）为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。更改提供不同级别的可见性支持。（5）提供脚本、存储过程和触发器中使用的访问结果集中）提供脚本、存储过程和触发器中使用的访问结果集中的数据的的数据的Transact-SQL语句。语句。9.6.1游标概述游标概述根据游标的用途不同，将游标分成三种类型。根据游标的用途不同，将游标分成三种类型。1.T-SQL游标游标T-SQL游标是由游标是由DECLARECURSOR语法定义的，语法定义的，主要用在服务器上。主要用在服务器上。2.API游标游标API游标支持在游标支持在OLEDB、ODBC以及以及DB_library中使用游标函数，主要用在服务器上。中使用游标函数，主要用在服务器上。3.客户游标客户游标在客户游标中，有一个默认的结果集被用来在客在客户游标中，有一个默认的结果集被用来在客户机上缓存整个结果集。户机上缓存整个结果集。Page 5012 七月 2024根据根据T-SQL服务器游标的处理特性，服务器游标的处理特性，SQLServer将游标将游标分为四种。分为四种。1)静态游标静态游标：静态游标是在打开游标时在静态游标是在打开游标时在tempdb中建立中建立SELECT结果集的快照。结果集的快照。2)动态游标动态游标：动态游标与静态游标相对。当滚动游标时，动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中的所有更改。动态游标反映结果集中的所有更改。3)只进游标只进游标：只进游标不支持滚动，它只支持游标从头到只进游标不支持滚动，它只支持游标从头到尾顺序提取数据。尾顺序提取数据。4)键集游标键集游标：键集游标中各行的成员身份和顺序是固定的。键集游标中各行的成员身份和顺序是固定的。键集驱动游标由一组唯一标识符键集驱动游标由一组唯一标识符(键键)控制，这组键称为键控制，这组键称为键集。集。Page 5112 七月 2024为了使用一个游标，需要以下几个步骤。为了使用一个游标，需要以下几个步骤。（1）声明一个游标。）声明一个游标。（2）打开游标。）打开游标。（3）从游标中读取数据行。）从游标中读取数据行。（4）关闭游标。）关闭游标。（5）删除游标。）删除游标。Page 5212 七月 20248.6.2游标的定义与使用游标的定义与使用1.声明游标声明游标声明游标使用声明游标使用DECLARECURSOR语句，这里简单介绍语句，这里简单介绍Transact-SQL扩展的语法，具体可参考联机帮助。扩展的语法，具体可参考联机帮助。Transact-SQL扩展的语法格式：扩展的语法格式：DECLAREcursor_nameCURSOR/*指定游标名指定游标名*/LOCAL|GLOBAL/*游标作用域游标作用域*/FORWARD_ONLY|SCROLL/*游标移动方向游标移动方向*/STATIC|KEYSET|DYNAMIC|FAST_FORWARD/*游标类型游标类型*/READ_ONLY|SCROLL_LOCKS|OPTIMISTIC/*访问属性访问属性*/TYPE_WARNING/*类型转换警告信息类型转换警告信息*/FORSELECT_statementFORUPDATEOFcolumn_name,n/*可修改的列可修改的列*/各参数说明如下。各参数说明如下。(1)LOCAL|GLOBAL：LOCAL说明游标只适合说明游标只适合用在建立游标的存储过程、触发器或批处理文件用在建立游标的存储过程、触发器或批处理文件内。当建立它的存储过程等结果执行时，即自动内。当建立它的存储过程等结果执行时，即自动解除解除(Deallocate)。GLOBAL适用于适用于session的所的所有存储过程、触发器或批处理文件内。结束连接有存储过程、触发器或批处理文件内。结束连接时，即自动解除。时，即自动解除。(2)FORWARD_ONLY：读取游标中的数据只能：读取游标中的数据只能由第一行数据向前读至最后一行，默认为此选项。由第一行数据向前读至最后一行，默认为此选项。(3)SCROLL参数允许用户查看前后行的数据，参数允许用户查看前后行的数据，具体取值如表具体取值如表9.16所示。所示。Page 5412 七月 2024(4)STATIC：表示游标为静态游标，即游标内的数据不能被修改。：表示游标为静态游标，即游标内的数据不能被修改。(5)KEYSET：指定当游标打开时，系统在：指定当游标打开时，系统在tempdb内部建立一个内部建立一个keyset，keyset的键值可唯一识别游标的数据。当用户更改非键值的键值可唯一识别游标的数据。当用户更改非键值时，能反映出其变动。当新增一行符合游标范围的数据时，无法由时，能反映出其变动。当新增一行符合游标范围的数据时，无法由此游标读到；当删除游标中的一行数据时，由此游标读取该行数据，此游标读到；当删除游标中的一行数据时，由此游标读取该行数据，会得到一个会得到一个FETCH_status值为值为-2的返回值。的返回值。(6)DYNIMIC：当游标在流动时能反映游标内最新的数据。：当游标在流动时能反映游标内最新的数据。(7)FAST_FORWARD：当设定：当设定FORREAD_ONLY或或READ_ONLY时，设置这一选项可启动系统的效能最佳化。时，设置这一选项可启动系统的效能最佳化。(8)READ_ONLY：内容不能更改；：内容不能更改；SCROLL_LOCKS：当数据读：当数据读入游标时，系统将这些数据锁定，可确保成功更新或删除游标内的入游标时，系统将这些数据锁定，可确保成功更新或删除游标内的数据，与选项数据，与选项FAST_FORWARD冲突；冲突；OPTIMISTIC：用：用WHERECURRENTOF方式修改或删除游标内的某行数据时，如果该行数方式修改或删除游标内的某行数据时，如果该行数据已被其他用户变动过，则据已被其他用户变动过，则WHERECURRENTOF的更新方式将的更新方式将不会成功。不会成功。(9)TYPE_WARNING：若游标的类型被内部更改为和用户要求说：若游标的类型被内部更改为和用户要求说明的类型不同时，发送一个警告信息给客户。明的类型不同时，发送一个警告信息给客户。Page 5512 七月 20242.打开游标打开游标使用使用OPEN语句填充该游标。该语句将执行语句填充该游标。该语句将执行DECLARECURSOR语句中的语句中的SELECT语句。语法格式：语句。语法格式：OPENGLOBALcursor_name|cursor_variable_name其中其中GLOBAL参数表示要打开的是全局游标。要判断打开游参数表示要打开的是全局游标。要判断打开游标是否成功，可以通过判断全局变量标是否成功，可以通过判断全局变量ERROR是否为是否为0来来确定。等于确定。等于0表示成功，否则表示失败。游标打开成功之后，表示成功，否则表示失败。游标打开成功之后，可以通过全局变量可以通过全局变量CURSOR_ROWS来获取游标中的记来获取游标中的记录行数。录行数。CURSOR_ROWS变量有以下几种取值。变量有以下几种取值。（1）m：游标采用异步方式填充，：游标采用异步方式填充，m为当前键集中已填充的行为当前键集中已填充的行数。数。（2）1：游标为动态游标，游标中的行数是动态变化的，因此：游标为动态游标，游标中的行数是动态变化的，因此不能确定。不能确定。（3）0：指定的游标没有被打开，或是打开的游标已被关闭或：指定的游标没有被打开，或是打开的游标已被关闭或释放。释放。（4）n：游标已完全填充。返回值为游标中的行数。：游标已完全填充。返回值为游标中的行数。Page 5612 七月 2024【例【例8.40】使用游标的使用游标的CURSOR_ROWS变量，统计学生信变量，统计学生信息表中的人数，假定每个学生有一个唯一的记录。息表中的人数，假定每个学生有一个唯一的记录。要通过要通过CURSOR_ROWS变量得到记录的个数，则要声明不变量得到记录的个数，则要声明不敏感游标或扩展语法格式的静态游标或键集游标，运行下面的语敏感游标或扩展语法格式的静态游标或键集游标，运行下面的语句。句。DECLARErsINSENSITIVECURSORFORSELECT*FROM学学生信息生信息OPENrs-打开游标打开游标IFERROR=0BEGINPRINT游标打开成功。游标打开成功。PRINT学生总数为：学生总数为：+CONVERT(VARCHAR(3),CURSOR_ROWS)ENDCLOSErs-关闭游标关闭游标DEALLOCATErs-释放游标释放游标GO3.获取数据获取数据游标被打开后中，可以用游标被打开后中，可以用FETCH语句，从结果集语句，从结果集中检索单独的行。中检索单独的行。语法格式：语法格式：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,n各参数含义如下。各参数含义如下。（1）NEXT。返回紧跟当前行之后的结果行，并且当前行。返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果递增为结果行。如果FETCHNEXT为对游标的第一次提取为对游标的第一次提取操作，则返回结果集中的第一行。操作，则返回结果集中的第一行。NEXT为默认的游标提为默认的游标提取选项。取选项。（2）PRIOR。返回紧临当前行前面的结果行，并且当前。返回紧临当前行前面的结果行，并且当前行递减为结果行。如果行递减为结果行。如果FETCHPRIOR为对游标的第一次为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。提取操作，则没有行返回并且游标置于第一行之前。（3）FIRST。返回游标中的第一行并将其作为当前行。返回游标中的第一行并将其作为当前行。（4）LAST。返回游标中的最后一行并将其作为当前行。返回游标中的最后一行并将其作为当前行。（5）ABSOLUTEn|nvar。如果。如果n或或nvar为正数，为正数，返回从游标头开始的第返回从游标头开始的第n行并将返回的行变成新的当前行。行并将返回的行变成新的当前行。如果如果n或或nvar为负数，返回游标尾之前的第为负数，返回游标尾之前的第n行并将返行并将返回的行变成新的当前行。如果回的行变成新的当前行。如果n或或nvar为为0，则没有行，则没有行返回。返回。n必须为整型常量且必须为整型常量且nvar必须为必须为smallint、tinyint或或int。【例【例8.41】使用游标成绩】使用游标成绩_CUR逐行读取学生成绩。逐行读取学生成绩。命令如下：命令如下：OPEN成绩成绩_CURFETCHNEXTFROM成绩成绩_CURWHILEFETCH_STATUS=0FETCHNEXTFROM成绩成绩_CURFETCHRELATIVE2FROM成绩成绩_CURCLOSE成绩成绩_CURDEALLOCATE成绩成绩_CUR4关闭游标关闭游标使用使用CLOSE语句可以关闭游标，游标关闭后，数据不可语句可以关闭游标，游标关闭后，数据不可再读。该过程可以结束动态游标的操作并释放资源，在再读。该过程可以结束动态游标的操作并释放资源，在CLOSE语句之后还可以使用语句之后还可以使用OPEN语句重新打开。语句重新打开。语法格式：语法格式：CLOSEcursor_name5释放游标释放游标使用使用DEALLOCATE语句可以从当前的会话中移除游标的语句可以从当前的会话中移除游标的引用，该过程可以完全释放分配给游标的所有资源。游标引用，该过程可以完全释放分配给游标的所有资源。游标释放之后不能用释放之后不能用OPEN语句重新打开，必须使用语句重新打开，必须使用DECLARE语句重建游标。语句重建游标。语法格式：语法格式：DEALLOCATEcursor_namePage 6112 七月 20246.利用游标修改数据利用游标修改数据SQLServer中的中的UPDATE和和DELETE语句也支持游标操语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据作，它们可以通过游标修改或删除游标基表中的当前数据行。行。UPDATE的语句格式如下：的语句格式如下：UPDATEtable_nameSET列名列名=表达式表达式,nWHERECURRENTOFcursor_nameDELETE的语句格式如下：的语句格式如下：DELETEFROMtable_nameWHERECURRENTOFcursor_name说明：说明：CURRENTOFcursor_name表示当前游标指针所表示当前游标指针所指的当前行数据。指的当前行数据。CURRENTOF只能在只能在UPDATE和和DELETE语句中使用。语句中使用。Page 6212 七月 2024【例【例8.42】使用游标将】使用游标将student表中生源地表中生源地(stu_birthplace)为为“哈尔哈尔滨市滨市”和第和第2条记录，生源地修改为条记录，生源地修改为“牡丹江市牡丹江市”。-将学生信息表复制到将学生信息表复制到student中中SELECT*INTOstudentFROM学生信息学生信息-声明和打开游标声明和打开游标DECLARExgdzCURSORFORSELECT*FROMstudentWHEREstu_birthplace=哈尔滨市哈尔滨市OPENxgdz-提取两条记录，目的是为了定位到第提取两条记录，目的是为了定位到第2条记录条记录FETCHNEXTFROMxgdzFETCHNEXTFROMxgdz-修改生源地修改生源地UPDATEstudentSETstu_birthplace=牡丹江市牡丹江市WHERECURRENTOFxgdz-关闭并释放游标关闭并释放游标CLOSExgdzDEALLOCATExgdzSELECT*FROMstudentWHEREstu_birthplace=牡丹江市牡丹江市GO【例【例8.43】使用游标删除使用游标删除student表中第表中第2条总学分小于条总学分小于14的的记录。记录。SELECT*INTOstudentFROM学生信息学生信息SELECT*FROMstudentWHEREcredit14DECLAREscxfCURSORFORSELECT*FROMstudentWHEREcredit14OPENscxfFETCHNEXTFROMscxfFETCHNEXTFROMscxfDELETEFROMstudentWHERECURRENTOFscxfCLOSEscxfDEALLOCATEscxfSELECT*FROMstudentWHEREcredit14
展开阅读全文