第10章Transact-SQL程序设计课件

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第10章 Transact-SQL程序设计,本章内容,10.1,Transact-SQL语言基础,10.2 表达式,10.3 函数,10.4,流程控制语句,10.5 游标,10.1,Transact-SQL语言基础,10.1.1,Transact-SQL语言的编程功能,10.1.2,标识符,10.1.3,注释,10.1.4,语句块,10.1.1,Transact-SQL语言的编程功能,Transact-SQL语言是在微软公司的SQL Server中使用的编程语言，它是一个数据定义、操作和控制的语言。,(1)基本功能,支持ANSI SQL-92标准：DDL数据定义，DML数据操纵，DCL数据控制，DD数据字典。,(2)扩展功能,加入程序流程控制结构。,加入局部变量和系统变量等,10.1.2,标识符,(2)后续字符必须是符合Unicode 2.0(统一码)标准的字母，或者是十进制数字，或是特殊字符、#、_、$。,(3)标识符不能与任何SQL Server保留字匹配。标识符不能包含空格或别的特殊字符。,2. 对象命名规则,所有数据库对象的引用由下面四部分构成 ：,server_name.database_name.schema_name.object_name,| database_name.schema_name.object_name,| schema_name.object_name,| object_name,其中：,server_name：指定链接的服务器名称或远程服务器名称。,database_name：如果对象驻留在SQL Server的本地实例中，则指定SQL Server数据库的名称。如果对象在链接服务器中，则database_name将指定OLE DB目录 。,schema_name：如果对象在SQL Server数据库中，则指定包含对象的架构的名称。如果对象在链接服务器中，则schema_name将指定OLE DB架构名称 。,object_name：对象的名称 。,数据库架构是一个独立于数据库用户的非重复命名空间可以将架构视为对象的容器。可以在数据库中创建和更改架构，并且可以授予用户访问架构的权限。任何用户都可以拥有架构，并且架构所有权可以转移 。,在SQL Server 2005中，每个对象都属于一个数据库架构 。,10.1.3 注释,注释是程序代码中不执行的文本字符串，也称为备注 。注释通常用于记录程序名、作者姓名和主要代码更改的日期，注释可用于描述复杂的计算或解释编程方法。,SQL Server支持两种类型的注释字符：,-（双连字符） ：这些注释字符可与要执行的代码处在同一行，也可另起一行。对于多行注释，必须在每个注释行的前面使用双连字符。,/* . */（正斜杠-星号字符对）：对于多行注释，必须使用开始注释字符“/*”来开始注释，并使用结束注释字符“*/”来结束注释。,10.1.4 语句块,语句块是由BEGIN 和 END括起来的一系列的Transact-SQL语句，作为一个逻辑单元执行。 语法格式如下：,BEGIN,sql_statement | statement_block,END,其中： sql_statement | statement_block 是使用语句块定义的任何有效的Transact-SQL语句或语句组 。,BEGIN.END语句块允许嵌套。,10.2 表达式,10.2.1 常量,10.2.2 变量,10.2.3 运算符,9.1 数据与表达式,10.2.1 常量,在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。,根据常量的类型不同分为字符串常量、整型常量、日期时间型常量、实型常量、货币常量、全局唯一标识符。,1. 字符串常量,字符串常量分为ASCII字符串常量、UNICODE字符串常量。,ASCII字符串常量：用单引号括起来，由ASCII构成的字符串。如：abcde。,UNICODE字符串常量：以字符N开头，如Nabcde。,9.1 数据与表达式,字符串常量必须放在单引号或双引号中。由字母、数字、下划线、特殊字符(!，#)组成。,当单引号括住的字符串常量中包含单引号时，用两个单引号表示字符串中的单引号。如：Im ZYT写作Im ZYT。,UNICODE(统一码、万国码、单一码)是一种在,计算机,上使用的,字符,编码。它为每种,语言,中的每个字符设定了统一并且唯一的,二进制,编码，以满足跨语言、跨平台进行文本转换、处理的要求 。,9.1 数据与表达式,3. 日期时间型常量,datetime常量使用特定格式的字符日期值表示，用单引号括起来。,输入时，可以使用“/”、“.”、“-”作日期/时间常量的分隔符。,默认情况下，按照mm/dd/yy(月/日/年)的格式来处理。,输入格式,datetime值,Smalldatetime值,Sep 3, 2008 1:34:34.122,2008-09-03 01:34:34.123,2008-09-03 01:35:00,9/3/2008 1PM,2008-09-03 13:00:00.000,2008-09-03 13:00:00,9.3.2008 13:00,2008-09-03 13:00:00.000,2008-09-03 13:00:00,13:25:19,1900-01-01 13:25:19.000,1900-01-01 13:25:00,9/3/2008,2008-09-03 00:00:00.000,2008-09-03 00:00:00,9.1.2 常量与变量,4. 实型常量,实型常量有纯小数和指数形式两种。如165.234，10E23。,5. 货币常量,用货币符号开头。如$12.5，$54230.25。SQL Server不强制分组，如每隔三个数字插一个逗号等。,6. 全局唯一标识符,全局唯一标识符(Globally Unique Identification Numbers，GUID)是16字节长的二进制数据类型，是SQL Server根据计算机网络适配器地址和主机时钟产生的唯一号码生成的全局唯一标识符。,9.1.2 常量与变量,9.1.2 常量与变量,变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。,变量的命名使用常规标识符，即以字母、下划线(_)、at符号()、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。,10.2.2 变量,9.1.2 常量与变量,全局变量由系统定义并维护，通过在名称前面加“”符号,局部变量的首字母为单个“”。,全局变量和局部变量,9.1.2 常量与变量,1. 局部变量,局部变量是作用域局限在一定范围内的Transact-SQL对象。,作用域：若局部变量在一个批处理、存储过程、触发器中被声明或定义，则其作用域就在批处理、存储过程或触发器内。,(1) 局部变量的声明或定义,DECLARE, local_variable AS data_type ,.n,其中：,local_variable：变量的名称。变量名必须以开头。,data_type：数据类型,变量先声明或定义，然后就可以在Transact-SQL命令中使用。默认初值NULL。,(2)局部变量的赋值,用SET或SELECT为局部变量赋值,SET local_variable = expression,或 SELECT local_variable = expression,或 SELECT local_variable =output_value FROM table_name WHERE .,各选项含义如下：,local_variable：是除cursor，text，ntext，image外的任何类型变量名 。,expression：表达式是任何有效的SQL Server表达式。,output_value：用于将单个值返回到变量中 。,注意：如果output_value为列名，则返回多个。若SELECT语句返回多个值，则将返回的最后一个值赋给变量。若SELECT语句没有返回值，变量保留当前值；若output_value是不返回值的子查询，则变量为NULL。,9.1.2 常量与变量,9.1.2 常量与变量,9.1.2 常量与变量,2. 全局变量,系统全局变量是SQL Server系统提供并赋值的变量。,用户不能建立全局变量，也不能用SET语句改变全局变量的值。,全局变量记录SQL Server服务器活动状态的一组数据。例如：ERROR 表示最后一个Transact-SQL命令错误的错误号；SERVERNAME 表示本地服务器的名称。具体见表10.2常用的SQL Server全局变量,全局变量由开始，由系统定义和维护，用户只能显示和读取，不能修改 。,例如：,显示SQL Server的版本 。,SELECT version AS 版本号,执行结果如图10.4所示。,9.1.2 常量与变量,例如：使用spid返回当前用户进程的ID。,SELECT spid as ID,SYSTEM_USER AS Login Name,USER AS User Name,运行结果：,ID Login Name User Name,53 sa dbo,9.1.2 常量与变量,10.2.3 运算符,运算符,用于指定要在一个或多个表达式中执行的操作 。,将变量、常量和函数连接起来，构成表达式。,下表列出了SQL Server 2005的运算符,优先级,运算符类别,所包含运算符,1,一元运算符,+(正)、-(负)、(取反),2,算术运算符,*(乖)、/(除)、%(取模),3,算术字符串运算符,+(加)、-(减)、+(连接),4,比较运算符,=(等于)、(大于)、=(大于等于)、( 或!=不等于)、!(不大于),5,按位运算符,&(位与)、|(位或)、(位异或),6,逻辑运算符,NOT(非),7,逻辑运算符,AND(与),8,逻辑运算符,ALL(所有)、ANY(任意一个)、BETWEEN(两者之间)、EXISTS(存在)、IN(在范围内)、LIKE(匹配)、OR(或)、SOME(任意一个),9,赋值运算符,=(赋值),SQL Server 2005运算符,10.3 函 数,10.3.1 内置函数,10.3.2 用户定义函数,函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。,函数能够重复执行一些操作，从而避免不断重写代码。,SQL Server 2005支持两种函数类型：,(1) 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。,(2) 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名函数中，该函数可以由Transact-SQL语句调用。,9.2.1 常用函数,10.3.1 内置函数,1字符串函数,字符串函数用来实现对字符型数据的转换、查找、分析等操作，,对字符串输入值执行操作，返回字符串或数字值 。,(1) ASCII()函数,返回字符表达式最左端字符的ASCII码值。语法格式如下。,ASCII ( character_expression ),其中：character_expression为char或varchar类型的,表达式,。,9.2.1 常用函数,(2),CHAR()函数,将int型的ASCII码转换为字符的字符串函数。语法格式如下。,CHAR ( integer_expression ),其中：integer_expression是介于0和255之间的整数。如果该整数表达式不在此范围内，将返回NULL值。,例如：以下示例为CHAR()函数的使用。,DECLARE StringTest CHAR (10),SET StringTest=ASCII(Robin ),SELECT CHAR(StringTest),执行结果为：,R,9.2.1 常用函数,(3),LEFT()函数,返回从字符串左边开始指定个数的字符。语法格式如下。,LEFT ( character_expression , integer_expression ),其中：,character_expression：字符或二进制数据,表达式,。可以是常量、变量或列。可以是任何能够隐式转换为varchar或nvarchar的数据类型，但text或ntext除外。,integer_expression：正整数，指定character_expression将返回的字符数。如果为负，则会返回错误。integer_expression可以是bigint类型。,9.2.1 常用函数,例如：以下示例为LEFT()函数的使用。,DECLARE StringTest CHAR (10),SET StringTest=Robin ,SELECT LEFT(StringTest,3),执行结果为：,Rob,9.2.1 常用函数,(4),LOWER()函数,将大写字符数据转换为小写字符数据后返回字符表达式。语法格式如下。,LOWER ( character_expression ),例如：以下示例为LOWER()函数的使用,DECLARE StringTest CHAR (10),SET StringTest=Robin ,SELECT LOWER(LEFT(StringTest,3),执行结果为：,rob,9.2.1 常用函数,(5),LTRIM()函数,删除起始空格后返回字符表达式。语法格式如下。,LTRIM ( character_expression ),例如：以下示例为LTRIM()函数的使用,DECLARE StringTest CHAR (10),SET StringTest= Robin,SELECT Start-+LTRIM(StringTest),Start-+StringTest,执行结果为：,Start-Robin Start- Robin,9.2.1 常用函数,(6),RIGHT()函数,返回字符串中从右边开始指定个数的字符。语法格式如下。,RIGHT ( character_expression , integer_expression ),例如：以下示例为RIGHT()函数的使用。,DECLARE StringTest CHAR (10),SET StringTest= Robin,SELECT RIGHT(StringTest,3),执行结果为：,in,9.2.1 常用函数,(7),RTRIM()函数,截断所有尾随空格后返回一个字符串。语法格式如下。,RTRIM ( character_expression ),例如：以下示例为RTRIM()函数的使用。,DECLARE StringTest CHAR (10),SET StringTest=Robin ,SELECT StringTest+-End, RTRIM(StringTest)+-End,执行结果为：,Robin -End Robin-End,9.2.1 常用函数,9.2.1 常用函数,9.2.1 常用函数,(9),SUBSTRING()函数,求子串函数。语法格式如下。,SUBSTRING ( expression ,start , length ),其中：,expression：是字符串、二进制字符串、文本、图像、列或包含列的,表达式,。不能使用包含聚合函数的表达式。,start：指定子字符串开始位置的整数，start可以为bigint类型。,length：正整数，指定要返回的expression的字符数或字节数。如果length为负，则会返回错误。length可以是bigint类型。,9.2.1 常用函数,例如：,以下示例为SUBSTRING()函数的使用。,DECLARE StringTest char(10),SET StringTest=Robin,SELECT SUBSTRING(StringTest,3,LEN(StringTest),执行结果为：,bin,9.2.1 常用函数,(10) UPPER()函数,返回将小写字符数据转换为大写字符的表达式。语法格式如下。,UPPER ( character_expression ),例如：以下示例为UPPER ()函数的使用,DECLARE StringTest CHAR(10),SET StringTest=Robin,SELECT UPPER(StringTest),执行结果为：,ROBIN,9.2.1 常用函数,2.日期时间函数,日期时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。,(1) DATEADD()函数,在指定日期加上一段时间的基础上，返回新的DATETIME类型值。语法格式如下。,DATEADD ( datepart , number, date ),其中：,datepart：指定要返回新值的日期的组成部分。,number：用来增加datepart的值。如果指定一个不是整数的值，则将废弃此值的小数部分。,date：是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的表达式。,9.2.1 常用函数,表 10.4 SQL Server的日期部分,日期部分,写 法,取值范围,Year,yy,17539999,Quarter,qq,14,Month,mm,112,Dayofyear,dy,1366,Day,dd,131,Week,wk,154,Weekday,dw,17(MonSun),Hour,hh,023,Minute,mi,059,Second,ss,059,Millisecond,ms,0999,9.2.1 常用函数,例如：,以下示例为DATEADD()函数的使用。,DECLARE OLDTime DATETIME,SET OLDTime=12-02-2004 06:30pm,SELECT DATEADD(hh,4,OldTime),执行结果为：,2004-12-02 22:30:00.000,9.2.1 常用函数,(2),DATEDIFF()函数,两时间之差，返回跨两个指定日期的日期边界数和时间边界数。语法格式如下。,DATEDIFF ( datepart , startdate , enddate ),其中：,datepart：指定应在日期的哪一部分计算差额的参数。,startdate：计算的开始日期。startdate是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的,表达式,。,enddate：计算的结束日期。enddate是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的表达式。,9.2.1 常用函数,9.2.1 常用函数,(3),DATENAME(),返回表示指定日期的指定日期部分的字符串。语法格式如下。,DATENAME ( datepart , date ),其中：,datepart：是指定要返回的日期部分的参数。,date：,表达式,，用于返回DATETIME或SMALLDATETIME类型值，或日期格式的字符串。,例如：DECLARE StatementDate DATETIME,SET StatementDate=2006-3-14 3:00 PM,SELECT DATENAME(dw,StatementDate),执行结果为：,星期二,9.2 函 数,10.3.2 用户定义函数,用户定义函数(User-Defined Function，UDF)是执行计算并返回一个值(标量值或表)的一段程序。,根据函数返回值形式的不同将用户定义函数分为3种类型。,(1) 标量函数,标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在BEGINEND语句内。,(2) 内嵌表值函数,内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGINEND语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。,(3) 多语句表值函数,多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用BEGINEND语句定义，返回值的表中的数据由函数体中的语句插入。,9.2 函 数,用户定义函数需要注意以下几点 ：,UDF可以嵌入到查询、约束和计算列中。定义UDF的代码不能影响函数范围之外的数据库状态，也就是说，UDF代码不能修改表中的数据或调用会产生副作用的函数(例如，RAND) 。,UDF的代码只能创建表变量，不能创建或访问临时表，也不允许使用动态执行。,在Microsoft SQL Server 2005系统中，可以分别使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION语句来实现用户定义函数的创建、修改和删除。在创建用户定义函数时，每个完全限定用户函数名称必须唯一。,用户定义函数不能用于执行一系列可以改变数据库状态的操作 。,9.2.2 用户定义函数,1. 创建用户定义函数,(1) 使用CREATE FUNCTION创建用户定义函数,标量函数的语法格式：,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_expression,END,:=, ENCRYPTION | SCHEMABINDING ,其中：,owner_name：指定用户定义函数的所有者。,function_name：用户定义函数的名称。函数名称必须符合有关,标识符,的规则，并且在数据库中以及对其架构来说是唯一的。,parameter_name：用户定义函数中的参数。一个函数最多可以有1 024个参数。执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值。通过将符号用作第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。参数是对应于函数的局部参数；其他函数中可使用相同的参数名称。参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。,scalar_parameter_data_type：指定标量参数的数据类型，除了text、ntext、image、cursor、timestamp和table类型外的其他数据类型。, = default ：参数的默认值。如果定义了default值，则无需指定此参数的值即可执行函数 。,scalar_return_data_type：指定标量返回值的数据类型，除了text、ntext、image、cursor、timestamp和table类型外的其它数据类型 。,scalar_expression：指定标量型用户自定义函数返回的标量值表达式 。,function_body：指定一系列的Transact_SQL语句，它们决定了函数的返回值。,encryption：加密选项，让SQL Server对系统表中有关CREATE FUNCTION的声明加密，以防止用户定义函数作为SQL Server复制的一部分被发布 。,schemabinding：计划绑定选项。将用户定义函数绑定到它所引用的数据库对象，则函数所涉及的数据库对象从此将不能被删除或修改，除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。,9.2.2 用户定义函数,例如：,根据输入的某人的年龄，返回其出生年份。,USE teaching,GO,CREATE FUNCTION birth_year(age int),RETURNS int,AS,BEGIN,RETURN(YEAR(GETDATE()-age),END,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 ),其中：,TABLE：指定表值函数的返回值为表。只有常量和local_variables可以传递到表值函数。在内联表值函数中，TABLE返回值是通过单个SELECT语句定义的。,select_stmt：定义内联表值函数返回值的单个SELECT语句，确定返回的表的数据 。,9.2.2 用户定义函数,例如：在teaching数据库中创建内联表值函数，根据输入的学生学号，返回student表中对应的姓名和年龄。,USE teaching,GO,CREATE FUNCTION student_info(no char(4),RETURNS TABLE,AS,RETURN(SELECT SNAME, AGE,FROM student,WHERE SNO=no),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,RETURN,END,: =,( | ,.n ),9.2.2 用户定义函数,其中：,return_variable：一个table类型的变量，用于存储和累积返回的表中的数据行。, ：定义Transact-SQL函数的表数据类型。表声明包含列定义和列约束(或表约束)。表始终放在主文件组中。,9.2.2 用户定义函数,9.2.2 用户定义函数,具体步骤详见教材,（2）使用图形工具创建用户定义函数,9.2.2 用户定义函数,3. 修改用户定义函数,可以使用Transact-SQL命令ALTER FUNCTION命令修改用户定义函数，语法格式与CREATE FUNCTION相同，相当于重建。,也可以使用图形工具修改用户定义函数，具体步骤详见教材,9.2.2 用户定义函数,3. 删除用户定义函数,(1),使用Transact-SQL命令删除,使用DROP FUNCTION命令删除用户定义函数，其语法如下：,DROP FUNCTION owner_name . function_name ,.n ,其中，function_name是要删除的用户定义的函数名称。,例如：,删除teaching数据库中的用户定义函数birth_year。,USE teaching,GO,DROP FUNCTION birth_year,GO,9.2.2 用户定义函数,(2)使用图形工具删除用户定义函数,具体步骤详见教材,10.4 流程控制语句,10.4.1 批处理,10.4.2 选择语句,10.4.3 循环语句,9.3 程序控制流语句,Transact-SQL语言提供了称为控制流语言的特殊关键字，这些关键字用于控制Transact-SQL语句、语句块、用户定义函数以及存储过程的执行流。,控制流语言支持基本的流控制逻辑，它允许按照给定的某种条件执行程序流和分支。,控制流语句不能跨多个批处理、用户定义函数或存储过程。,Transact-SQL提供的控制流有：IFELSE分支、CASE多重分支、WHILE循环结构、GOTO语句、WAITFOR语句和RETURN语句。,9.3 程序控制流语句,10.4.1 批处理,批处理是包含条或多条Transact-SQL语句的集合，被一次性执行。,SQL Server将批处理编译成一个可执行单元，称为执行计划。批中如果某处发生编译错误，整个执行计划都无法执行。,写批处理时，GO语句作为批处理命令的结束标志，当编译器读取到GO语句时，会把GO语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身不是Transact-SQL语句的组成部分，只是一个表示批处理结束的前端指令。,1. 批处理使用规则,(1) CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW语句不能在批处理中与其他语句组合使用，在同一个批处理中只能提交一个 。,(2)不能在删除一个对象之后，在同一批处理中再次引用这个对象。,(3)不能把规则和默认值绑定到表字段或者自定义字段上后，立即在同一批处理中使用它们。,(4)不能定义一个CHECK约束后，立即在同一个批处理中使用这个约束。,(5)不能修改表中一个字段名后，立即在同一个批处理中引用这个新字段。,(6)使用SET语句设置的某些SET选项不能应用于同一个批处理中的查询。,(7)若批处理中第一个语句是执行某个存储过程的EXECUTE语句，则EXECUTE关键字可以省略。若该语句不是第一个语句，则必须写上。,9.3 程序控制流语句,SQL Server有以下几种指定批处理的方法。,(1) 应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。,(2) 存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划。,(3) 由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。例如，,EXEC (SELECT * FROM employee),(4) 由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如，,execute sp_executesql NSELECT * from Sales.dbo.employee,注意：,应用程序发出的批处理过程中含有EXECUTE语句，已执行字符串或存储过程的执行计划，将和包含EXECUTE语句的执行计划分开执行。,若sp_executesql存储过程所执行的字符串生成的执行计划也与包含sp_executesql调用的批处理执行计划分开执行。,若批处理中的语句激发了触发器，则触发器执行将和原始的批处理执行分开进行 。,2. 指定批处理的方法,9.3 程序控制流语句,(1),批处理结束语句：GO,作为批处理的结束标志，也就是说当编译器执行到GO时会把GO之前的所有语句当作一个批处理来执行。,注意：,GO命令和Transact-SQL语句不可在同一行，在批处理中的第一条语句后执行任何存储过程必须包含EXECUTE关键字。,局部变量的作用域限制在一个批处理中，不可在GO命令后引用。,EXECUTE命令执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持Transact-SQL批处理内的字符串的执行。,3. 批处理的结束与退出,9.3 程序控制流语句,(2),批处理退出语句：RETURN 整型表达式,无条件中止查询、存储过程或批处理的执行。,注意：,存储过程或批处理不执行位于RETURN之后的语句。,当存储过程使用该语句，则可用该语句指定返回给调用应用程序、批处理或过程的整数值。,若RETURN语句未指定值，则存储过程的返回值是0。,当用于存储过程时，RETURN不能返回空值 。,4. 脚本,脚本是存储在文件中的一系列Transact-SQL语句。可包含一个或多个批处理，GO作为批处理结束语句，如果脚本中无GO语句，则作为单个批处理。脚本文件扩展名为.sql。,9.3 程序控制流语句,10.4.2 选择语句,1. 条件执行语句IFELSE,必须IF.ELSE结构根据条件表达式的值，以决定执行哪些语句。,IF.ELSE的语法格式为：,IF Boolean_expression, sql_statement | statement_block -条件表达式为真时执行, ELSE, sql_statement | statement_block -条件表达式为假时执行,其中：,Boolean_expression：返回TRUE或FALSE的表达式。如果布尔表达式中含有SELECT语句，则必须用括号将SELECT语句括起来。, sql_statement | statement_block ：任何Transact-SQL语句或用语句块定义的语句分组。,注意：,IF.ELSE构造可用于批处理、存储过程和即席查询。当此构造用于存储过程时，通常用于测试某个参数是否存在。,可以在其他IF之后或在ELSE下面，嵌套另一个IF测试，嵌套级数的限制取决于可用内存 。,例如：,查询课程中是否有OS课程，如果有，统计选课人数。,USE teaching,GO,DECLARE num smallint,IF EXIST(SELECT * FROM course WHERE CNAME LIKE OS%),BEGIN,SELECT num=COUNT(*),FROM s_c,WHERE CNO IN,(SELECT CNO FROM course WHERE CNAME LIKE OS%),PRINT 选此课程人数为：+str(num),END,ELSE,PRINT 数据库中没有此课程,执行结果为：,选此课程人数为： 3,9.3.2 选择控制,2. CASE函数,CASE表达式用来计算条件列表并返回多个可能结果表达式之一。,(1) 简单CASE函数：将某个表达式与一组简单表达式进行比较以确定结果。,CASE input_expression,WHEN when_expression THEN result_expression, .n ,ELSE else_result_expression ,END,其中：,input_expression：计算的表达式，可以是任意有效的,表达式,。,WHEN when_expression：要与input_expression进行比较的简单表达式，是任意有效的表达式。input_expression及每个when_expression 的数据类型必须相同或必须是隐式转换的数据类型。,THEN result_expression：当input_expression = when_expression计算结果为TRUE时返回的表达式。,ELSE else_result_expression：比较运算计算结果不为TRUE时返回的表达式。,9.3.2 选择控制,例如：显示每个学生选课的数量。,USE teaching,GO,SELECT SNO, 课程数量=,CASE COUNT(*),WHEN 1 THEN 选修了一门课,WHEN 2 THEN 选修了两门课,WHEN 3 THEN 选修了三门课,ELSE 选修了三门课以上,END,FROM s_c,GROUP BY SNO,执行结果如下图：,9.3.2 选择控制,(2) CASE搜索函数，CASE计算一组逻辑表达式以确定结果。,CASE,WHEN Boolean_expression THEN result_expression, . n , ELSE else_result_expression ,END,其中：,WHEN Boolean_expression是计算的布尔表达式，是任意有效的布尔表达式。,例如：使用CASE搜索表达式显示每个学生选课的数量,USE teaching,GO,SELECT SNO, 课程数量=,CASE,WHEN COUNT (*)=1 THEN 选修了一门课,WHEN COUNT (*)=2 THEN 选修了两门课,WHEN COUNT (*)=3 THEN 选修了三门课,END,FROM s_c,GROUP BY SNO,执行结果如下图：,9.3.2 选择控制,3.,WAITFOR语句,WAITFOR语句，称为延迟语句。就是暂停批处理、存储过程或事务的执行，转去执行一个指定的时间间隔或者到一个指定的时间。在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。,语法格式如下：,WAITFOR, DELAY time_to_pass /* 设定等待时间 */,| TIME time_to_execute /* 设定等待到某一时刻 */,9.3.2 选择控制,其中：,DELAY：可以继续执行批处理、存储过程或事务之前必须经过的指定时段，最长可为24小时。,time_to_pass：等待的时段。可以使用datetime数据可接受的格式之一指定time_to_pass，也可以将其指定为局部变量。不能指定日期，因此，不允许指定datetime值的日期部分，只能指定时间。,TIME：指定的运行批处理、存储过程或事务的时间。,time_to_execute：WAITFOR语句完成的时间。可以使用datetime数据可接受的格式之一指定time_to_execute，也可以将其指定为局部变量，不能指定日期，因此，不允许指定datetime值的日期部分。,注意：,执行WAITFOR语句时，事务正在运行，并且其他请求不能在同一事务下运行。,WAITFOR不更改查询的语义。,如果查询不能返回任何行，WAITFOR将一直等待，或等到满足TIMEOUT条件(如果已指定)。,9.3.2 选择控制,例如：延迟30秒执行查询,USE teaching,GO,WAITFOR DELAY 00:00:30,SELECT * FROM student,例如：在时刻21:20:00执行查询。,USE teaching,GO,WAITFOR TIME 21:20:00,SELECT * FROM student,9.3.2 选择控制,4. 跳转语句GOTO,GOTO语句将执行语句无条件跳转到标签处，并从标签位置继续处理。GOTO语句和标签可在过程、批处理或语句块中的任何位置使用。语法格式如下 ：,GOTO label,其中，label为GOTO语句处理的起点。label必须符合标识符规则。,9.3.2 选择控制,5. RETURN语句,从查询或过程中无条件退出。RETURN的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。RETURN之后的语句是不执行的。语法格式如下 ：,RETURN integer_expression ,其中：integer_expression是返回的整数值。存储过程可向执行调用的过程或应用程序返回一个整数值。,注意：除非另外说明，否则所有系统存储过程都将返回一个0值。此值表示成功，非0值表示失败。如果用于存储过程，RETURN不能返回NULL值。,9.3 程序控制流语句,10.4.3 循环语句,WHILE语句根据条件表达式设置Transact-SQL语句或语句块重复执行的次数。如果所设置的条件为真（TRUE）时，在WHILE循环体内的Transact-SQL语句会一直重复执行，直到条件为假（FALSE）为止。,可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。,WHILE循环语句的语法格式如下：,WHILE boolean_expression, sql_statement | statement_block , BREAK , sql_statement | statement_block , CONTINUE , sql_statement | statement_block ,9.3 程序控制流语句,其中：,Boolean_expression：返回TRUE或FALSE的,表达式,。如果布尔表达式中含有SELECT语句，则必须用括号将SELECT语句括起来。,sql_statement | statement_block：Transact-SQL语句或用语句块定义的语句分组。若要定义语句块，需使用BEGIN和END括起来。,BREAK：导致从最内层的WHILE循环中退出。将执行出现在END关键字（循环结束的标记）后面的任何语句。,CONTINUE：使WHILE循环重新开始执行，忽略CONTINUE关键字后面的任何语句。,注意：如果嵌套了两个或多个WHILE循环，则内层的BREAK将退出到下一个外层循环。将首先运行内层循环结束之后的所有语句，然后重新开始下一个外层循环。,9.3 程序控制流语句,例如：,DECLARE x int,SET x=0,WHILE x3,BEGIN,SET x=x+1,PRINT x=+STR(x,1),END,执行结果为：,x=1,x=2,x=3,10.5 游标,10.5.1 游标概述,10.5.2 操作游标,10.5.1 游标概述,9.4 游标管理与应用,游标(cursor)是一种数据访问机制，它允许用户单独地访问数据行，而不是对整个行集进行操作。Transact-SQL游标类似于C语言指针。,在SQL Server 2005中，游标主要包括以下两个部分 ：,(1)游标结果集：由定义游标的SELECT语句返回的行的集合。,(2)游标位置：指向这个结果集中的某一行的指针。,9.4.1 游标概述,在SQL Server 2005中游标具有以下特点：,游标返回一个完整的结果集，但允许程序设计语言只调用集合中的一行。,允许定位在结果集中的特定行。,从结果集的当前位置检索一行或多行,支持对结果集中在当前位置的行进行数据修改。,可以为其他用户对显示在结果集中的数据库数据所作的更改提供不同级别的可见性支持。,提供脚本、存储过程和触发器中使用的访问结果集中数据的Transact-SQL语句。,10.5.2 操作游标,9.4 游标管理与应用,SQL Server游标具有下面的处理过程：,(1),声明Transact-SQL变量包含游标返回的数据。为每个结果集中的列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。,(2),使用DECLARE CURSOR语句将Transact-SQL游标与SELECT语句相关联。另外，DECLARE CURSOR语句还定义游标的特性，例如游标名称以及游标是只读还是只进。,(3),使用OPEN语句执行SELECT语句并填充游标。,(4),使用FETCH INTO语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他Transact-SQL语句可以引用那些变量来访问提取的数据值。Transact-SQL游标不支持提取行块。,(5)使用CLOSE语句结束游标的使用,关闭游标可以释放某些资源 。,1. 声明游标,9.4 游标管理与应用,声明游标的主要内容包括游标名字、数据来源表和列、选取条件，以及属性仅读或可修改。,SQL 92 标准语法格式,DECLARE cursor_name INSENSITIVE SCROLL CURSOR,FOR select_statement, FOR READ ONLY | UPDATE OF column_name ,.n ,;,其中：,cursor_name：所定义的Transact-SQL服务器游标的名称。,9.4 游标管理与应用,INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的这一临时表中得到应答。因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，则已提交的(任何用户)对基础表的删除和更新都反映在后面的提取中。,SCROLL：指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE，如表10.5所示)均可用。如果未在SQL-92 DECLARE CURSOR中指定SCROLL，则NEXT是唯一支持的提取选项。如果也指定了FAST_FORWARD，则不能指定SCROLL。,9.4.2 声明游标,表10.5 SCROLL的取值,SCROLL选项,含 义,FIRST,提取游标中的第一行数据,LAST,提取游标中的最后一行数据,PRIOR,提取游标当前位置的上一行数据,NEXT,提取游标当前位置的下一行数