数据库系统管理ppt课件第六章.ppt

数据库基础,第六章 T-SQL语言程序设计,数据库基础,【本章要点】,本章将介绍T-SQL(Transact-SQL)程序设计的基本知识。T-SQL作为嵌入在SQL Server中的结构化查询语言，对标准SQL进行了扩展，功能强大、简单易学。同其他程序设计语言一样，T-SQL也有自己的数据类型、运算符、表达式以及流程控制语句等相关语法要求,本章将对这些内容进行详细的介绍。,数据库基础,第六章 T-SQL语言程序设计,6.1 数据类型、常量和变量6.2 运算符和表达式6.3 流程控制语句6.4 函数,数据库基础,6.1 数据类型、常量和变量,6.1.1 数据类型6.1.2 常量6.1.3 变量,数据库基础,6.1.1 数据类型,从大的方面来说SQL Server 2005中包括系统数据类型和用户自定义数据类型。在前面的章节中对系统数据类型已经进行了详细的介绍，这部分将只介绍用户自定义数据类型。用户自定义数据类型基于系统提供的基本数据类型，并不是一种真正意义上的数据类型，只是提供了一种提高数据库内部元素和基本数据类型之间一致性的机制。通过使用用户自定义数据类型能够简化对常用规则和默认值的管理。,数据库基础,1.创建用户自定义数据类型,在SQL Server中可利用系统存储过程和使用图形化界面两种方式来创建用户自定义数据类型。（1）使用系统存储过程来创建用户自定义数据类型命令格式如下：sp_addtype typename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name,数据库基础,1.创建用户自定义数据类型,各参数的含义如下：typename=type：用户自定义数据类型的名称。phystype=system_data_type：用户定义的数据类型所基于的系统数据类型或SQL Server提供的数据类型。nulltype=null_type：用户自定义数据类型处理空值的方式。null_type的取值为NULL、NOT NULL或NONULL,默认值为NULL。owner=owner_name：指定新数据类型的创建者或所有者，若没有指定，则为当前用户。,数据库基础,1.创建用户自定义数据类型,例6.1 为图书管理数据库创建一个不允许为空值的book_code用户自定义数据类型。USE 图书管理GOEXEC sp_addtype book_code,char(8),NOT NULLGO需要说明的是，在char(8)上加了单引号，是因为它包含了标点符号（括号）。,数据库基础,1.创建用户自定义数据类型,(2)采用图形化界面来创建用户自定义数据类型仍以为图书管理数据库创建一个不允许为空值的book_code用户自定义数据类型为例进行讲解。步骤如下：1、打开Microsoft SQL Server Management Studio窗口对象资源管理器窗口数据库图书管理可编程性类型右击类型节点新建用户定义数据类型。2、如图6.1所示，打开新建用户定义数据类型窗口，在名称字段输入book_code，在数据类型下拉列表中选择char数据类型，在长度框中输入8，保留允许空值为取消复选状态。,数据库基础,1.创建用户自定义数据类型,图6.1 新建用户自定义数据类型窗口,数据库基础,1.创建用户自定义数据类型,3、设置完毕后，单击确定按钮，则创建了用户定义数据类型book_code。,数据库基础,2.删除用户自定义数据类型,对于不再使用的用户定义数据类型，可以将其删除。同创建时一样，删除用户自定义数据类型也可以采用系统存储过程和图形化界面两种方式。,数据库基础,2.删除用户自定义数据类型,（1）使用系统存储过程删除用户定义数据类型命令格式如下：sp_droptype typename=typetype用户自定义数据类型的名称。,数据库基础,2.删除用户自定义数据类型,例6.2 删除book_code用户定义数据类型。USE 图书管理GOEXEC sp_droptype book_codeGO,数据库基础,2.删除用户自定义数据类型,（2）使用图形化界面删除用户定义数据类型,步骤如下：1、在Microsoft SQL Server Management Studio中找到需要删除的用户定义数据类型，在这里的具体操作为打开Microsoft SQL Server Management Studio窗口对象资源管理器窗口数据库图书管理可编程性类型用户定义数据类型book_code右击选择删除。2、出现如图6.2所示的删除对象窗口，单击确定按钮，则将其删除。,数据库基础,2.删除用户自定义数据类型,图6.2 删除对象窗口,数据库基础,6.1 数据类型、常量和变量,6.1.1 数据类型6.1.2 常量6.1.3 变量,数据库基础,6.1.2 常量,在程序运行中值保持不变的数据称为常量。常量实际上是表示特定数据值的符号，格式取决于具体的数据类型，通常分为：字符串常量、整型常量、日期时间常量、实型常量、货币常量和全局唯一标识符。,数据库基础,1.字符串常量,字符串常量分为ASCII字符串常量和Unicode 字符串常量。ASCII字符串常量括在单引号内并包含字母数字字符（a-z、A-Z 和 0-9）以及特殊字符，如感叹号(!)、at 符()和数字号(#)等。,数据库基础,1.字符串常量,如果已为某个连接将 QUOTED_IDENTIFIER 选项设置成 OFF，则字符串也可以使用双引号括起来，但 Microsoft SQL 本机客户端提供程序和 ODBC 驱动程序将自动使用 SET QUOTED_IDENTIFIER ON。因此建议使用单引号。如果单引号中的字符串包含一个嵌入的引号，可以使用两个单引号表示嵌入的单引号。对于嵌入在双引号中的字符串则不需要这样做。空字符串用中间没有任何字符的两个单引号表示。,数据库基础,1.字符串常量,引号表示。ASCII字符串常量示例：helloyours bookX is 50%,数据库基础,1.字符串常量,Unicode 字符串的格式与ASCII字符串相似，但它前面有一个 N 标识符（N 代表 SQL-92 标准中的区域语言），并且N 前缀必须是大写字母。将上面的ASCII字符串常量改为Unicode 字符串常量，则如下所示：NhelloNyours bookNX is 50%,数据库基础,2.整型常量,按照整型常量的不同表示方式，又可分为二进制整型常量和十进制整型常量。（1）二进制整型常量二进制常量具有前辍0 x,通常采用十六进制数字字符串表示。这些常量不使用引号括起。例如：0 xEA0 x23EF0 x(表示空的常量),数据库基础,2.整型常量,（2）十进制整型常量十进制整型常量即不带小数点的十进制数，且不需要使用引号括起来。例如：2376-1200+3775,数据库基础,3.日期时间常量,日期时间常量用单引号将表示日期时间的字符串括起来构成。SQL Server中提供了专门的日期和时间数据类型，可以识别多种格式的日期和时间。字母日期格式，例：July 20,2009、20-July-2009数字日期格式，例：5/12/2008、2008-08-25、未分隔的字符串格式，例：20021207、常见的时间格式如下：10:11:06、08:25 AM,数据库基础,4.实型常量,实型常量有定点表示和浮点表示两种方式,不需要用单引号括起来。（1）定点表示的实型常量由包含小数点的数字字符串组成。例如：1123.6204+23464545.2234-1132456432.10,数据库基础,4.实型常量,（2）浮点表示的实型常量使用科学记数法来表示。例如：305.5E5+223E-3-42E5,数据库基础,5.货币常量,货币常量即为money 常量，是以”$”作为前缀的一个整型或实型常量数据，不需扩在单引号中。例如：$642923-$370.59+$66578.733,数据库基础,6.全局唯一标识符,全局唯一标识符是SQL Server根据计算机网络适配器地址和主机CPU时钟产生的唯一号码生成的，可以使用字符或二进制字符串格式指定。例如：6F9619FF-8B86-D011-B42D-00C04FC964FF0 xff19966f868b11d0b42d00c04fc964ff,数据库基础,6.1 数据类型、常量和变量,6.1.1 数据类型6.1.2 常量6.1.3 变量,数据库基础,6.1.3 变量,变量就是在程序执行过程中其值可以改变的量。可以利用变量存储程序执行过程中涉及到的数据，如接收用户输入的数值、计算的结果等。声明变量时需要指定变量名及数据类型，变量名用于标识该变量，数据类型确定了该变量存放值的格式及允许的运算。,数据库基础,6.1.3 变量,为变量命名应使用常规标识符，即以字母、下划线(_)、at符号（）或数字符号（#）开头，后续接字母、数字、at符号、美元符号（$）、下划线的字符序列。不允许嵌入空格或其他特殊字符。,数据库基础,6.1.3 变量,Transact-SQL语言中的变量有两种：一种是全局变量，它们是由系统预先定义好的；另外一种是局部变量，是由用户根据需要自己定义的。,数据库基础,1.全局变量,全局变量以两个字符开头，由系统定义和维护。用户只能使用全局变量，不能对它们进行修改。实际上他们不是变量，不具备变量的行为，而是系统函数，语法遵循函数的规则。为了和以前的版本兼容，这里仍称为全局变量。表6.1中列出了几个在SQL Server中常用的全局变量。,数据库基础,1.全局变量,表6.1 SQL Server中常用的全局变量,数据库基础,1.全局变量,例6.3使用VERSION查看SQL Server版本信息。结果如图6.3所示。Select VERSION,数据库基础,1.全局变量,图6.3 查看版本信息,数据库基础,1.全局变量,例6.4使用ROWCOUNT查看前一条命令影响的行数。结果如图6.4所示。Select ROWCOUNT,数据库基础,1.全局变量,图6.4使用ROWCOUNT查看前一条命令影响的行数,数据库基础,2.局部变量,局部变量是用户在使用T-SQL语言的过程中根据需要定义的，它的作用范围仅限于定义此变量的过程内部。使用DECLARE语句定义局部变量，并在其名称前加上“”标志。定义局部变量的语法形式如下：DECLARE local_variable data_type n,数据库基础,2.局部变量,其中local_variable：用于指定局部变量的名称；data_type：用于设置局部变量的数据类型及其大小，可以是由系统提供数据类型，也可以为用户定义的数据类型，但不能是text、ntext或image数据类型。,数据库基础,2.局部变量,创建局部变量之后，初始值为NULL，如果想要设定局部变量的值，必须使用SET命令或者SELECT命令。其语法形式为：SET local_variable=expression 或者SELECT local_variable=expression,.n 其中，参数local_variable是给其赋值并声明的局部变量，参数expression是任何有效的SQL Server 2005表达式。,数据库基础,2.局部变量,例6.5创建局部变量int_s并赋值，然后输出其值。结果如图6.5所示。declare int_s intset int_s=100select int_s,数据库基础,2.局部变量,图6.5使用set为局部变量赋值,数据库基础,2.局部变量,例6.6查询读者表，将返回的记录数赋给局部变量M_count,并显示结果。结果如图6.6所示。declare M_count intselect M_count=count(*)from 读者select M_count,数据库基础,2.局部变量,图6.6使用select为局部变量赋值,数据库基础,第六章 T-SQL语言程序设计,6.1 数据类型、常量和变量6.2 运算符和表达式6.3 流程控制语句6.4 函数,数据库基础,6.2 运算符和表达式,运算符是一种符号，实现了运算功能，能将数据按照运算符的功能定义进行计算，产生新的结果。在SOL Server 2005中运算符可以分为如下几种：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串连接运算符和一元运算符。在SOL Server 2005中表达式可以分为简单表达式和复杂表达式两种。简单表达式可以只是一个常量、变量、列名或函数；复杂表达式是由运算符连接起来的两个或多个简答表达式构成的。,数据库基础,6.2 运算符和表达式,6.2.1 算数运算符6.2.2 赋值运算符6.2.3 位运算符6.2.4 比较运算符6.2.5 逻辑运算符6.2.6字符串连接运算符6.2.7一元运算符 6.2.8运算符的优先级,数据库基础,6.2.1 算数运算符,算术运算符对两个表达式执行数学运算，这两个表达式可以是任何的数值数据类型。算术运算符包括+（加）、-（减）、*（乘）、/（除）和%（取模）五种运算。其中+(加)和(减)运算符也可用于对 datetime 及 smalldatetime 值进行算术运算。,数据库基础,6.2.1 算数运算符,例6.7将图书表中单价大于20的书籍按九折重新计算单价。SELECT 书名，单价，单价*0.9 实际价格 FROM 图书WHERE 单价20,数据库基础,6.2.1 算数运算符,运行结果如图6.7所示。本例通过算术运算计算出了单价大于20的书籍的实际价格。如果表达式中有多个算术运算符，则先计算乘、除，然后是取模运算，最后是加、减运算。如果表达式中的算术运算符都有相同的优先顺序，则按从左到右进行计算，括号中的表达式比其他所有运算都要优先进行计算。,数据库基础,6.2.1 算数运算符,图6.7使用乘运算计算实际价格,数据库基础,6.2.2 赋值运算符,等号(=)是T-SQL中唯一的赋值运算符。在下面的示例中，将创建一个 intNum变量，然后赋值运算符将 intNum设置为表达式返回的值。,数据库基础,6.2.2 赋值运算符,例6.8使用赋值运算符为变量赋值。DECLARE intNum intSET intNum=5*(32+7)SELECT intNum执行后intNum中的值为195。,数据库基础,6.2.3 位运算符,位运算符在两个表达式之间执行位操作，这两个表达式的类型可以是整型数据或二进制数据（image数据类型除外），但要求在位运算符左右两侧的操作数不能同时是二进制数据。位运算符包括&（按位与）、|（按位或）、（按位异或），如表6.2所示。,数据库基础,6.2.3 位运算符,表6.2 位运算符,数据库基础,6.2.3 位运算符,例6.9对两个整型变量intNum1和intNum2进行按位运算。DECLARE intNum1 int，intNum2 intSET intNum1=8,SET intNum2=-2SELECT intNum1&intNum2 与运算,intNum1|intNum2 或运算,intNum1intNum2 异或运算在T-SQL中先把整形数据转换乘二进制数据，然后再进行按位运算，结果如图6.8。,数据库基础,6.2.3 位运算符,图6.8位运算,数据库基础,6.2.4 比较运算符,比较运算符(又称关系运算符)，用于测试两个表达式的值之间的关系，其运算结果为布尔值，可以为三种之一：TRUE、FALSE 及 UNKNOWN。当 SET ANSI_NULLS 为 ON 时，带有一个或两个 NULL 表达式的运算符返回 UNKNOWN。当 SET ANSI_NULLS 为 OFF 时，上述规则同样适用，但当两个表达式均为 NULL时，则等号(=)运算符返回 TRUE。如表6.3所示，位SQL Server 2005中可以使用的比较运算符。,数据库基础,6.2.4 比较运算符,表6.3 比较运算符,数据库基础,6.2.4 比较运算符,例6.10查询单价大于15小于30的图书的详细信息。SELECT*FROM 图书WHERE 单价15 and 单价30,数据库基础,6.2.5 逻辑运算符,逻辑运算符用于对某些条件进行测试，运算结果返回带有TRUE 或 FALSE的布尔型值。如表6.4所示，为SQL Server 2005中提供的逻辑运算符。,数据库基础,6.2.5 逻辑运算符,表6.4 逻辑运算符,数据库基础,6.2.5 逻辑运算符,例6.11挑选出由清华大学出版社或者南开大学出版社出版的图书。SELECT*FROM 图书WHERE 出版社 IN(清华大学出版社,南开大学出版社),数据库基础,6.2.6 字符串连接运算符,在SQL Server 2005中，允许使用字符串连接运算符加号（+）对两个或多个字符串进行串联。例6.11字符串连接。DECLARE vch1 varchar(20),vch2 varchar(20)SET vch1=helloSET vch2=worldSELECT vch1+vch2运行结果显示为helloworld。,数据库基础,6.2.7 一元运算符,一元运算符只对一个表达式执行操作，该表达式可以是任何数字数据类型。包括+（正）、-（负）和（按位取反）三个运算符。其中需要说明的是按位取反运算符返回一个数的补数，正能用于整数数据。,数据库基础,6.2.7 一元运算符,例6.12对变量num进行一元运算。DECLARE num intSET num=48SELECT+num 取正,-num 取负,num 取反运行结果如图6.9所示。,数据库基础,6.2.7 一元运算符,图6.9一元运算,数据库基础,6.2.8 运算符的优先级,当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序会影响到运算的结果。表6.5中给出了运算符的优先顺序，在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算，相同优先级的运算按自左向右的顺序进行，括号可以改变运算符的优先性，表达式中如果有括号，应先对括号内的部分进行计算。,数据库基础,6.2.8 运算符的优先级,表6.5 运算符的优先级,数据库基础,第六章 T-SQL语言程序设计,6.1 数据类型、常量和变量6.2 运算符和表达式6.3 流程控制语句6.4 函数,数据库基础,6.3 流程控制语句,与其他的程序设计语言一样，T-SQL提供了流程控制语句，可以进行顺序、分支、循环结构的程序设计。,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.1 语句块和注释,1.BEGINEND语句BEGINEND语句能够将多个T-SQL语句组合成一个语句块，并将它们视为一个单元来处理。在控制流语句必须执行包含两条或多条 T-SQL 语句时，就要使用 BEGINEND语句来构成语句块。BEGINEND语句的语法形式如下：BEGIN sql_statement|statement_block END,数据库基础,6.3.1 语句块和注释,其中 sql_statement|statement_block 是任和有效的T-SQL语句或语句块。BEGIN 和 END 语句必须成对使用，任何一个均不能单独使用。BEGIN 语句单独出现在一行中，后跟 T-SQL 语句块，最后END 语句单独出现在一行中，指示语句块的结束。,数据库基础,6.3.1 语句块和注释,例6.12显示图书管理数据库中读者表的借书证号为1344005的读者姓名。DECLARE name char(8)BEGINSELECT name=(SELECT 姓名 FROM 读者 WHERE 借书证号=1344005)SELECT nameEND,数据库基础,6.3.1 语句块和注释,本例中BEGINEND语句将两个SELECT语句组合成一个语句块，实现了给变量name赋值并显示。虽然BEGINEND语句几乎可以用在程序的任何地方，但最常用的地方是在IF 或 ELSE 子句需要包含语句块、CASE 函数的元素需要包含语句块以及WHILE 循环需要包含语句块时使用。,数据库基础,6.3.1 语句块和注释,2注释在程序设计中，注释是必不可少的，加入注释一方面可以有助于对代码的理解，另外也可以方便开发人员调试。在T-SQL中使用两个连字符（-）加入单行注释，使用一对分隔符/*/加入多行注释。,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.2 选择控制,1IFELSE语句2.CASE语句,数据库基础,1IFELSE语句,IFELSE语句是条件判断语句，当条件表达式成立时执行某段程序，条件不成立时执行另一段程序。其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。SQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。IFELSE语句的语法形式为：IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_block,数据库基础,1IFELSE语句,例6.13查询图书管理数据库中图书的平均单价是否大于25。IF(SELECT AVG(单价)FROM 图书)25SELECT 平均单价大于25元ELSE SELECT 平均单价不大于25元,数据库基础,2.CASE语句,CASE语句用于多重选择的情况。可以根据条件表达式的值进行判断，并将其中一个满足条件的结果表达式返回。CASE语句按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句。简单CASE语句的语法形式如下：CASE input_expressionWHEN when_expression THEN result_expression.n ELSE else_result_expressionEND,数据库基础,2.CASE语句,执行过程为：先计算CASE后面的表达式的值，然后将其与WHEN后面的表达式逐个进行比较，若相等则返回THEN后面的表达式，否则返回ELSE后面的表达式。,数据库基础,2.CASE语句,例6.14使用简单CASE将图书管理数据库中部分读者的单位信息重新命名。SELECT CASE 单位 WHEN 计算机系 THEN 计算机科学与技术系 WHEN 电子系 THEN 电子信息系 WHEN 数学系 THEN 应用数学系END AS 单位,姓名 FROM 读者,数据库基础,2.CASE语句,搜索CASE语句的语法形式如下：CASEWHEN Boolean_expression THEN result_expression.n ELSE else_result_expressionEND执行过程为：如果WHEN后面的逻辑表达式为真，则返回THEN后面的表达式，然后判断下一个逻辑表达式，如果所有的逻辑表达式都为假，则返回ELSE后面的表达式。,数据库基础,2.CASE语句,例6.15使用搜索CASE根据图书的单价范围，显示相应的信息。SELECT 书名,CASE WHEN 单价 IS NULL THEN 价钱不确定 WHEN 单价=10 and 单价 20 THEN 可以接收 ELSE 太贵了 END AS 售价满意度 FROM 图书运行结果如图6.10所示。,数据库基础,2.CASE语句,图6.10使用搜索CASE,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.3 GOTO语句,GOTO语句可以使程序直接跳到指定的标识符的位置继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“:”结尾。如：label:。在GOTO语句行，标识符后面不用跟“:”。GOTO语句的语法形式为：GOTO labellabel:,数据库基础,6.3.3 GOTO语句,例6.16利用GOTO语句求出从1到10的总和。DECLARE sum int,count intSELECT sum=0,count=1label:SELECT sum=sum+countSELECT count=count+1IF count=10GOTO labelSELECT count,sum,数据库基础,6.3.3 GOTO语句,使用GOTO语句可以增加程序的灵活性，该语句也破坏了程序结构化的特点，使程序结构变得复杂而且难以测试，因此尽量少使用GOTO语句。,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.4 RETURN语句,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为：RETURN integer_expression 其中，参数integer_expression为返回的整型值。,数据库基础,6.3.4 RETURN语句,例6.17创建一个存储过程，用来判断读者现在借阅的图书是否达到规定的最大数量，不到10本的还可以再借阅。CREATE PROCEDURE check_count param char(8)ASIF(SELECT COUNT(*)FROM 借阅 WHERE 借书证号=param GROUP BY 借书证号)10RETURN 1ELSE RETURN 0,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.5 WAITFOR语句,WAITFOR语句可以指定批处理、存储过程或事务执行的时刻、或需等待的时间间隔。语法形式为：WAITFOR DELAY time|TIME time 其中，DELAY用于指定时间间隔，要求时间间隔在24小时之内，TIME用于指定某一时刻，其数据类型为datetime，格式为hh:mm:ss。,数据库基础,6.3.5 WAITFOR语句,例16.18 在22：00时备份图书管理数据库。WAITFOR TIME 22：00BACKUP DATABASE 图书管理 TO book_bkp,数据库基础,6.3 流程控制语句,6.3.1 语句块和注释6.3.2 选择控制6.3.3 GOTO语句6.3.4 RETURN语句6.3.5 WAITFOR语句6.3.6 循环控制语句,数据库基础,6.3.6 循环控制语句,在T-SQL中可以使用WHILE语句来进行循环控制，当WHILE后面的条件为真时，就重复执行语句。WHILE可以和CONTINUE以及BREAK配合使用来完成对循环的控制。具体的语法格式如下：WHILE Boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE,数据库基础,6.3.6 循环控制语句,其中Boolean_expression 表达式，返回 TRUE 或 FALSE。如果布尔表达式中含有 SELECT 语句，则必须用括号将 SELECT 语句括起来。sql_statement|statement_block为 T-SQL 语句或用BEGIN END定义的语句块。BREAK 导致从最内层的 WHILE 循环中退出，将执行出现在 END 关键字（循环结束的标记）后面的任何语句。CONTINUE 使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。,数据库基础,6.3.6 循环控制语句,例6.19在图书管理数据库中，判断图书单价的平均价格如果少于15，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 40，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 40，然后退出 WHILE 循环并打印一条消息。,数据库基础,6.3.6 循环控制语句,WHILE(SELECT AVG(单价)FROM 图书)40 BREAK ELSE CONTINUEENDPRINT 可以接受,数据库基础,第六章 T-SQL语言程序设计,6.1 数据类型、常量和变量6.2 运算符和表达式6.3 流程控制语句6.4 函数,数据库基础,6.4 函数,在程序设计中经常会用到函数，SQL Server 2005中支持两种函数类型：内置函数和用户定义函数。,数据库基础,6.4 函数,6.4.1 内置函数6.4.2 用户自定义函数,数据库基础,6.4.1 内置函数,内置函数是一组预定义的函数，用户只能调用，不能修改。在程序设计中经常会调用一些内置函数，如进行数学计算，实现数据类型的转换，获得系统信息等。下面将介绍一些常用的内置函数。,数据库基础,1.数学函数,数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server 2005中提供的数字数据（包括decimal、integer、float、real、money、smallmoney、smallint 和 tinyint等类型）进行处理。在SQL Server 2005中，常用的数学函数如表6.6所示。,数据库基础,1.数学函数,数据库基础,1.数学函数,数据库基础,1.数学函数,例6.20用数学函数返回指定数值的绝对值以及大于或等于指定表达式的最小整数和小于或等于指定表达式的最大整数。SELECT ABS(-13.67),CEILING(-13.67),FLOOR(-13.67)执行结果如图6.11所示。,数据库基础,1.数学函数,图6.11使用数学函数,数据库基础,2.字符串函数,字符串函数用来实现对字符型数据的查找、分析、转换等操作。在SQL Server 2005中字符串函数可分为四大类：基本字符串函数、字符串查找函数、长度和分析函数以及转换函数。具体的函数参照表6.7。,数据库基础,2.字符串函数,数据库基础,2.字符串函数,数据库基础,2.字符串函数,数据库基础,2.字符串函数,例6.21在第一个字符串（hello world!）中删除从第七个位置（w字符）开始的五个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。SELECT STUFF(hello world!,7,5,china)运行结果为hello china！。,数据库基础,2.字符串函数,例6.22查询图书管理数据库中，读者姓名的第一个字。SELECT SUBSTRING(姓名,1,1)FROM 读者运行结果如图6.12所示。,数据库基础,2.字符串函数,图6.12使用SUBSTRING函数,数据库基础,3.日期和时间函数,日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。在SQL Server 2005中提供的日期和时间函数如表6.8所示。,数据库基础,3.日期和时间函数,数据库基础,3.日期和时间函数,例6.23显示图书管理数据库中读者借书日期到当前日期的天数。SELECT 借书证号,图书编号,DATEDIFF(day,借阅日期,getdate()AS 借出天数FROM 借阅运行结果如图6.13所示。,数据库基础,3.日期和时间函数,图6.13使用日期时间函数,数据库基础,4.系统函数,系统函数用于返回有关SQL Server 2005系统、用户、数据库和数据库对象的信息。用户可以根据系统函数返回的信息进行不同的操作。表6.9给出了常用的系统函数。,数据库基础,4.系统函数,数据库基础,4.系统函数,例6.24使用cast函数将图书管理数据库中图书的单价转换为int型显示。SELECT 书名,CAST(单价 AS int)as 单价FROM 图书执行结果如图6.14所示。,数据库基础,4.系统函数,图6.14使用cast函数,数据库基础,4.系统函数,例6.25从图书管理数据库中返回图书表的首列名称。SELECT COL_NAME(OBJECT_ID(图书),1)执行结果如图6.15所示。,数据库基础,4.系统函数,图6.15使用系统函数返回列名称,数据库基础,6.4 函数,6.4.1 内置函数6.4.2 用户自定义函数,数据库基础,6.4.2 用户自定义函数,虽然SQL Server 2005中提供了多种可供用户调用的内置函数，但在实际编程中仍不能完全满足用户的多样化需求，因此还需要使用到用户自定义函数。在用户自定义函数中可以包含0个或多个参数，函数的返回值可以是数值，还可以是一个表。根据用户定义函数返回值的类型，可将用户定义函数分为如下三个类别：,数据库基础,6.4.2 用户自定义函数,标量函数 用户自定义函数返回值为一个确定类型的标量值，返回类型可以是除 text、ntext、image、cursor 和 timestamp 外的任何系统数据类型。内嵌表值函数用户定义函数返回值为一个表，且函数体中只包含单个 SELECT 语句，这样的函数称为内嵌表值函数，其功能相当于一个参数化的视图。,数据库基础,6.4.2 用户自定义函数,多语句表值函数用户定义函数的返回值仍是一个表，但在 BEGIN.END 语句块中定义的函数体包含一系列 T-SQL 语句，这些语句可生成行并将其插入到返回的表中，这样的函数称为多语句表值函数。,数据库基础,1.用户自定义函数的创建与调用,创建用户自定义函数可以在Microsoft SQL Server Management Studio中采用有框架提示的方式来实现，也可以使用CREATE FUNCTION语句实现。第一种方法的具体步骤如下：Microsoft SQL Server Management Studio“展开要创建自定义函数的数据库”“可编程性”“函数”“右击新建相应类别的函数”。如图6.16所示。,数据库基础,1.用户自定义函数的创建与调用,图6.16创建用户自定义函数,数据库基础,1.用户自定义函数的创建与调用,完成上面的操作后，会出现相应的提示框架，用户需要在对应的部分写上代码。下面重点介绍采用CREATE FUNCTION语句的方式。在SQL Server 2005中，根据用户自定义函数类型的不同，创建函数的格式也不同。,数据库基础,1.用户自定义函数的创建与调用,创建标量函数的语法格式如下：CREATE FUNCTION 所有者名.函数名(参数1 AS 类型1=默认值),.参数n AS 类型n=默认值)RETURNS 返回值类型 WITH ENCRYPTION|SCHEMABINDING,.n AS BEGIN 函数体 RETURN 标量表达式END,数据库基础,1.用户自定义函数的创建与调用,其中参数可以声明一个或多个，最多可以有1024个参数。函数在执行时，要求指定每个参数的值，对于有默认值的，可以不指定。ENCRYPTION指出系统将加密包含CREATE FUNCTION语句文本的系统表，SCHEMABINDING指明用该选项创建的函数不能更改或删除该函数引用的数据库对象。,数据库基础,1.用户自定义函数的创建与调用,例6.26创建标量函数以计算图书管理数据库中某个出版社出版的图书的平均价格