第8章 使用Transact-SQL语言编程-2ppt课件.ppt

在此输入您的封面副标题第8章 使用Transact-SQL语言编程-2中国铁道出版社中国铁道出版社3【能力目标】【能力目标】 能使用T-SQL的表达式和基本流程控制语句 能使用各种常用的系统内置函数 能定义与调用用户定义函数 能使用游标第第8章章 使用使用Transact-SQL语言编程语言编程4【项目描述】【项目描述】 使用Transact-SQL 编写批处理与程序流程控制语句程序。练习Transact-SQL语言的函数并创建使用游标。第第8章章 使用使用Transact-SQL语言编程语言编程5【项目分析】【项目分析】 SQL（Structure Query Language）语言是用于数据库查询的结构化语言。1982年美国国家标准化组织ANSI确认SQL为数据库系统的工业标准。目前，许多关系型数据库管理系统都支持SQL语言，如：Access、Orcal、Sybase、DB2等。T-SQL（Transact-SQL）在支持标准SQL的同时，还对其进行了扩充，引入了变量定义、流程控制和自定义存储过程等语句，极大地扩展了SQL Server 2008的功能。使用数据库的客户或应用程序都是通过T-SQL语言来操作数据库的，本项目主要介绍T-SQL程序设计基础知识。第第8章章 使用使用Transact-SQL语言编程语言编程6【任务设置】【任务设置】 任务1认知T-SQL语言编程基础任务2编写批处理与程序流程控制语句任务3使用系统内置函数任务4编写用户定义函数任务5使用游标实训九程序设计第第8章章 使用使用Transact-SQL语言编程语言编程7【项目定位】【项目定位】数据库系统开发第第8章章 使用使用Transact-SQL语言编程语言编程8【任务目标】【任务目标】 了解Transact-SQL语言编程知识 掌握Transact-SQL语法规则 学会在Transact-SQL中使用常量、变量、标示符、运算符表达式-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程9【任务描述】【任务描述】 局部变量和全局变量的使用1利用ServerName查看本地服务器名称，并利用Connections显示截止到当前时刻试图登录SQL Server的次数。2查看Student表的所有记录并利用Rowcount统计记录数。3. 声明一个整型的局部变量Nnm的语句为： ，对该变量赋值500的语句为： 。4. X是一个全局变量吗？5将字符串“china”赋给一个局部变量chr，并输出chr的值。6将XSDA表中第一个男生的姓名赋给局部变量name。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程10【任务分析】【任务分析】在SQL Server数据库中，T-SQL语言由以下几部分组成。（1）数据定义语言（DDL）。（2）数据操纵语言（DML）。（3）数据控制语言（DCL）。（4）T-SQL增加的语言元素。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程111 1标识符标识符 数据库对象的名称被看成是该对象的标识符。在SQL Server中标识符可以分成两类：常规标识符与分隔标识符。由于分隔标识符不常用，我们只介绍常规标识符的使用。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程12常规标识符应符合如下规则：（1）第一个字符必须是下列字符之一：ASCII字符、Unicode字符、下划线 _ 、或#。在 SQL Server 中，某些处于标识符开始位置的符号具有特殊意义，以开始的标识符表示局部变量或参数，以一个数字符号开始的标识符表示临时表或过程，以#开始的标识符表示全局临时对象。（2）后续字符可以是：ASCII字符、Unicode字符、下划线 _ 、美元符号 $ 或数字符号。（3）标识符不能是 T-SQL 的保留字。（4）不允许嵌入空格或其它特殊字符。 -任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程132 2注释注释 注释是为SQL语句加上解释和说明，以说明该代码的含义，增加代码的可读性。T-SQL支持以下两种类型的注释：（1）多行注释使用“/*”和“*/”将注释括起来可以连续书写多行的注释语句，例如：/*使用GROUP BY子句和聚合函数对数据进行分组后，再使用HAVING子句对分组数据进一步筛选*/（2）单行注释使用“-”表示书写单行注释语句，例如：-声明局部变量 -任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程14 常量是指在程序运行过程中其值不变的量。常量又称为标量值。常量的使用格式取决于它所表示的值的数据类型。 根据常量值的不同类型，常量分为字符串常量、整型常量、实型常量、日期时间常量、货币常量、惟一标识常量。常量的格式取决于它所表示的值的数据类型，如表8-1所示。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程15-任务1 认知Transact-SQL语言编程基础表8-1 SQL常量类型表第第8章章 使用使用Transact-SQL语言编程语言编程16 变量是指在程序运行过程中其值可以改变的量。变量有名字和数据类型两个属性，变量名用于标识该变量，必须是合法的标识符。变量的数据类型确定了该变量存放值的格式及允许的运算。变量可分为局部变量和全局变量。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程171 1局部变量局部变量 局部变量是用户定义的变量，用于保存单个数据值。局部变量常用保存程序运行的中间结果或作为循环变量使用。（1）局部变量的定义 局部变量必须用DECLARE语句声明后才可以使用，所有局部变量在声明后均初始化为NULL。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程18语法格式：DECLARE local_variable data_type ,n说明：local_variable：局部变量名，应为常规标识符。局部变量名必须以开头。data_type：数据类型，用于定义局部变量的类型，可为系统类型或自定义类型。n：表示可定义多个变量，各变量间用“，”隔开。局部变量的作用范围从声明该局部变量的地方开始，到声明的批处理或存储过程的结尾。批处理或存储过程结束后，存储在局部变量中的信息将丢失。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程19（2）局部变量的赋值 当局部变量声明之后，可用SET或SELECT语句给其赋值。 用SET语句赋值语法格式：SET local_variable=expression说明：local_variable：是除cursor、text、ntext、image外的任何类型变量。expression：是任何有效的SQL Server表达式。一条SET语句一次只能给一个局部变量赋值。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程20【例8-1】创建局部变量var1、var2，并赋值，然后输出变量的值。【例8-2】创建一个名为xm的局部变量，并在SELECT语句中使用该局部变量查找表XSDA中所有管理系学生的姓名、总学分。【例8-3】使用查询给变量赋值。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程21 用SELECT语句赋值语法格式：SELECT local_variable=expression ,n说明：local_variable：是除cursor、text、ntext、image外的任何类型变量。expression：是任何有效的SQL Server表达式。n：表示可给多个变量赋值。SELECT通常用于将单个值返回到变量中，当expression为表的列名时，可使用子查询功能从表中一次返回多个值，此时将返回的最后一个值赋给变量。如果子查询没有返回值，则将变量设为NULL。如果省略了赋值号及后面的表达式，则可以将局部变量的值显示出来，起到输出显示局部变量值的作用。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程22【例8-4】创建局部变量var1、var2，并赋值，然后输出变量的值。【例8-5】使用查询给变量赋值。 注意：本例的子查询结果返回XSDA表中所有学生的姓名，name中保存的是最后一个学生的姓名。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程232 2全局变量全局变量 全局变量是由系统提供并赋值，而且预先声明的用来保存SQL Server系统运行状态数据值的变量。用户不能定义全局变量，也不能用SET语句和SELECT语句修改全局变量的值。通常可以将全局变量的值赋给局部变量，以便保存和处理。全局变量名以“”开头。事实上，在SQL Server中，全局变量是一组特定的函数，不需要任何参数，在调用时无需在函数名后面加括号，这些函数也称为无参数函数。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程24 全局变量分为两类：一是与SQL Server连接有关的全局变量，如：rowcount表示受最近一个语句影响的行数；二是与系统内部信息有关的全局变量，如：version表示SQL Server的版本号。SQL Server提供了30多个全局变量，表8-2介绍几个常用的全局变量。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程25-任务1 认知Transact-SQL语言编程基础表8-2 SQL常用的全局变量表【例8-6】利用全局变量查看SQL Server版本和当前使用的语言。第第8章章 使用使用Transact-SQL语言编程语言编程26 SQL Server2008提供的常用运算符有：算术运算符、字符串连接运算符、比较运算符、逻辑运算符四种，本节介绍这些常用的运算符。有关一元运算符、位运算符、赋值运算符的使用请查阅联机帮助。 通过运算符连接运算量构成表达式。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程271 1算术运算符算术运算符算术运算符包括：+（加）、-（减）、*（乘）、/（除)和%（求模)，参与运算的数据是数值类型数据，其运算结果也是数值类型数据。加、减运算符也可用于对日期型数据进行运算，还可进行数字字符数据与数值类型数据进行运算。【例8-7】算术运算符的使用。-任务1 认知Transact-SQL语言编程基础2 2字符串连接运算符字符串连接运算符字符串连接运算符用”+”表示，可以实现字符串之间的连接。参与字符串连接运算的数据只能是字符数据类型：char、varchar、nchar、nvarchar、text、ntext，其运算结果也是字符数据类型。【例8-8】字符串连接运算符的使用。第第8章章 使用使用Transact-SQL语言编程语言编程283 3比较运算符比较运算符比较运算符（又称为关系运算符）如表8-3所示，用来对两个相同类型表达式的顺序、大小、相同与否进行比较。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式，即用于数值大小的比较、字符串排列顺序的前后比较、日期数据前后比较。比较运算结果有三种值： TRUE（正确)、FALSE（错误)、UNKNOWN（未知)。比较表达式用于IF语句和WHILE语句的条件、WHERE子句和HAVING子句的条件。【例8-9】比较运算符的使用。-任务1 认知Transact-SQL语言编程基础表8-3 比较运算符表第第8章章 使用使用Transact-SQL语言编程语言编程29-任务1 认知Transact-SQL语言编程基础表8-3 比较运算符表第第8章章 使用使用Transact-SQL语言编程语言编程304 4逻辑运算符逻辑运算符 逻辑运算符用于对某个条件进行测试，如表8-4所示。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。逻辑表达式用于IF语句和WHILE语句的条件、WHERE子句和HAVING子句的条件。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程31-任务1 认知Transact-SQL语言编程基础表8-4 逻辑运算符表 ANY、SOME、ALL一般用于比较子查询，这种查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算。语法格式：expression | | = | | != | ! ALL | SOME | ANY (subquery)第第8章章 使用使用Transact-SQL语言编程语言编程32说明：expression是要进行比较的表达式。 = | | != | | = | ! | | = | !ALL：表示大于每一个值，即大于最大值。例如，ALL (1, 2, 3) 表示大于3，因此，使用ALL的子查询也可用MAX函数实现。ANY：表示至少大于一个值，即大于最小值。例如，ANY (1, 2, 3) 表示大于 1，因此，使用ANY的子查询也可用MIN函数实现。=ANY：与IN等效。ALL：与NOT IN 等效。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程33【例8-10】查询比信息系所有学生的年龄都小的学生的学号、姓名及出生日期。【例8-11】查询成绩高于方平最低成绩的学生姓名、课程名称及成绩。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程345 5运算符的优先级运算符的优先级 当一个复杂的表达式中包含多个运算符时，运算符优先级将决定执行运算的先后次序。执行的顺序会影响所得到的运算结果。运算符优先级如表8-5所示。在一个表达式中按先高（优先级数字小）后低（优先级数大）的顺序进行运算。-任务1 认知Transact-SQL语言编程基础表8-5 运算符优先级表第第8章章 使用使用Transact-SQL语言编程语言编程35 当一个表达式中的两个运算符有相同的优先级时，根据它们在表达式中的位置，一般而言，一元运算符按从右向左的顺序运算，二元运算符对其从左到右进行运算。 表达式中可用括号改变运算符的优先级，先对括号内的表达式求值，然后对括号外的运算符进行运算。 若表达式中有嵌套的括号，则首先对嵌套最深的表达式求值。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程361利用ServerName查看本地服务器名称，并利用Connections显示截止到当前时刻试图登录SQL Server的次数。2查看Student表的所有记录并利用Rowcount统计记录数。3. 声明一个整型的局部变量Nnm的语句为： ，对该变量赋值500的语句为： 。4. X是一个全局变量吗？5将字符串“china”赋给一个局部变量chr，并输出chr的值。6将XSDA表中第一个男生的姓名赋给局部变量name。-任务1 认知Transact-SQL语言编程基础第第8章章 使用使用Transact-SQL语言编程语言编程37【任务目标】【任务目标】 学会书写批处理语句 学会流控语句编程【任务描述】【任务描述】流程控制语句使用1用循环语句编写s=2+4+6+8+10程序。 -任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程38【任务分析】【任务分析】 SQL Server服务器端的程序通常使用SQL语句来编写。当任务不能由单独的T-SQL语句来完成时，SQL Server通常使用批处理来组织多条T-SQL语句完成任务。一般而言，一个服务器端的程序是由以下一些成分组成：批、注释、变量、流程控制语句、消息处理等。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程39 批处理是由一条或多条T-SQL语句组成的，应用程序将这些语句作为一个单元一次性地发送到SQL SERVER服务器执行。批处理结束的标志是“GO”。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程40使用批处理时应遵守以下规则：CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其它语句组合使用。批处理必须以 CREATE 语句开始，所有跟在CREATE后的其它语句将被解释为第一个 CREATE 语句定义的一部分。不能把规则和默认值绑定到表字段或用户定义数据类型之后，在同一个批处理中使用它们。 不能在给表字段定义了一个CHECK约束后，在同一个批处理中使用该约束。在同一个批处理中不能删除一个数据库对象又重建它。不能在修改表的字段名后，在同一个批处理中引用该新字段名。调用存储过程时，若它不是批处理中的第一条语句，那么在它前面必须加上EXECUTE（或EXEC）。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程41 设计程序时，常常需要利用各种流程控制语句，改变计算机的执行流程以满足程序设计的需要。SQL Server中提供的主要流程控制语句如下：1 1BEGINENDBEGINEND语句语句 BEGINEND 语句用于将多条T-SQL 语句组合成一个语句块，并将它们视为一个单一语句使用。多用于条件语句和循环语句中。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程42语法格式：BEGIN sql_statement /*是任何有效的 T-SQL 语句 nEND说明：BEGINEND 语句块允许嵌套使用，BEGIN和END语句必须成对使用。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程432 2PRINTPRINT语句语句 PRINT语句将用户定义的消息返回客户端。语法格式：PRINT any ASCII text | local_variable | FUNCTION | string_expression说明：any ASCII text：一个文本字符串。local_variable：任意有效的字符数据类型变量。FUNCTION：是返回字符串结果的函数。string_expression：是返回字符串的表达式。PRINT语句向客户端返回一个字符类型表达式的值，最长为255个字符。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程443 3IFELSEIFELSE语句语句 在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的T-SQL语句，可用IFELSE语句实现。语法格式：IF boolean_expression /*条件表达式 sql_statement|statement_block /*条件表达式为真时执行T-SQL语句或语句块ELSE sql_statement|statement_block /*条件表达式为假时执行T-SQL语句或语句块 说明：boolean_expression是条件表达式，运算结果为TRUE（真）或FALSE（假）。如果条件表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来。 【例8-12】如果王红的平均成绩高于90分，则显示“平均成绩优秀”；否则显示“平均成绩非优秀”。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程454 4循环语句（循环语句（WHILEWHILE）、）、BREAKBREAK语句和语句和CONTINUECONTINUE语句语句（1）WHILE循环语句 如果需要重复执行程序中的一部分语句，可使用WHILE循环语句实现。 语法格式：WHILE boolean_expression sql_statement|statement_block /*T-SQL语句或语句块构成的循环体 说明：boolean_expression是条件表达式，运算结果为TRUE（真）或FALSE（假）。如果条件表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来。 【例8-13】求10！。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程46（2）BREAK语句 一般用在循环语句中，用于退出本层循环。当程序中有多层循环嵌套时，使用BREAK语句只能退出其所在的这一层循环。语法格式：BREAK（3）CONTINUE语句 一般用在循环语句中，结束本次循环，重新转到下一次循环条件的判断。语法格式：CONTINUE-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程475 5RETURNRETURN语句语句 用于从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。语法格式：RETURN integer_expression 说明： integer_expression是将整型表达式的值返回。存储过程可以给调用过程或应用程序返回整型值。 除非特别指明，所有系统存储过程返回0值表示成功，返回非0值则表示失败。 当用于存储过程时，RETURN不能返回空值。 -任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程486 6WAITFORWAITFOR语句语句 WAITFOR语句指定触发语句块、存储过程或事务执行的时刻、或需等待的时间间隔。语法格式：WAITFOR DELAY time|TIMEtime说明： DELAY time：用于指定SQL Server必须等待的时间，最长可达24h。time可以datetime数据格式指定，用单引号括起来，但在值中不允许有日期部分，也可以用局部变量指定参数。 TIMEtime：指定SQL Server等待到某一时刻，time值的指定同上。 执行WAITFOR语句后，在到达指定时间之前将无法启用与SQL Server的连接。若要查看活动的进程和正在等待的进程，使用sp_who。【例8-14】如下语句设定在早上8时执行存储过程Manager。-任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程491 1用循环语句编写用循环语句编写s=2+4+6+8+10s=2+4+6+8+10程序。程序。DECLARE i int, s intSET t=0SET i=2WHILE i任务2 批处理与程序流程控制语句第第8章章 使用使用Transact-SQL语言编程语言编程50【任务目标】【任务目标】 学会在查询分析器中测试各内置函数功能 能在实际编程中运用内置函数-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程51【任务描述】【任务描述】流程控制语句和系统内置函数使用1设学位代码与学位名称如下表，用CASE函数编写学位代码转换为名称的程序。2declare ch intset ch=92select char(ch+5)上述语句的返回值是什么？-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程523 3写出下列函数的返回值。写出下列函数的返回值。（1）ABS(-5) （2）REPLACE(ABCDEF,CD,UI) （3）SUBSTRING(中国人民,3,2) （4）ASCII(SQL) -任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程53【任务分析】【任务分析】 函数是T-SQL语言提供的用以完成某种特定功能的程序。在T-SQL编程语言中函数可分为系统内置函数和用户定义函数。本节主要介绍系统内置函数中常用的数学函数、字符串函数、日期和时间函数、聚合函数、系统函数。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程54 数学函数用于对数字表达式进行数学运算并返回运算结果。组成数字表达式的数据类型有decimal，integer，float，real，money，smallmoney，smallint和tinyint。在此介绍几个常用的数学函数。1 1ABSABS函数函数语法格式：ABS ( numeric_expression ) 说明：返回给定数字表达式的绝对值。参数numeric_expression为数字型表达式（bit 数据类型除外），返回值类型与numeric_expression 相同。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程552 2ROUNDROUND函数函数语法格式：ROUND ( numeric_expression , length ) 说明：返回数字表达式并四舍五入为指定的长度或精度。参数numeric_expression为数字型表达式（bit 数据类型除外）；length是 numeric_expression 将要四舍五入的精度，length 必须是 tinyint、smallint 或int，当 length 为正数时，numeric_expression 四舍五入为 length 所指定的小数位数，当 length 为负数时，numeric_expression 则按 length 所指定的在小数点的左边四舍五入。返回值类型与 numeric_expression 相同。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程563 3RANDRAND函数函数语法格式：RAND ( seed ) 说明：返回 0 到1 之间的随机float 值。参数seed是给出种子值或起始值的整型表达式（tinyint、smallint 或 int）。返回值类型为float。【例8-15】使用SELECT语句查询数学函数。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程57 字符串函数用于对字符串进行操作，并返回一个字符串或数字值。在此介绍几个常用的字符串函数。1ASCII函数； 2CHAR函数； 3LEFT函数；4RIGHT函数； 5SUBSTRING函数； 6.REPLACE函数；7STR函数； 8LEN函数-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程581GETDATE函数语法格式：GETDATE ( )-任务3 系统内置函数2DAY函数语法格式：DAY ( date ) 3MONTH函数语法格式：MONTH ( date )4YEAR函数语法格式：YEAR ( date )第第8章章 使用使用Transact-SQL语言编程语言编程59 聚合函数用于计算表中的数据，返回单个计算结果。常用的聚合函数包括：MAX、MIN、SUM、AVG、COUNT，在项目5数据库查询已做介绍，此处不再赘述。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程60 系统函数用于对SQL Server中的值、对象和设置进行操作并返回有关信息。1 1CASECASE函数函数CASE函数有两种使用形式，一种是简单的CASE函数，另一种是搜索型的CASE函数。（1）简单的CASE函数语法格式：CASE input_expression WHEN when_expression THEN result_expression nELSE else_result_expressionEND-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程61 说明：计算input_expression表达式的值，并与每一个when_expression表达式的值比较，若相等，则返回对应的result_expression表达式的值；否则返回else_result_expression表达式的值。参数input_expression和when_expression的数据类型必须相同（或可以隐性转换）。n表示可以使用多个WHEN when_expression THEN result_expression子句。【例8-20】查询XSDA表中所有学生的学号和性别，要求使用简单CASE函数将性别列的值由“男”，“女”替换成“男生”，“女生”进行显示，性别列的标题为sex。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程62（2）搜索型CASE函数语法格式：CASE WHEN Boolean_expression THEN result_expression nELSE else_result_expressionEND-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程63 说明：按指定顺序为每个WHEN子句的Boolean_expression表达式求值。返回第一个取值为TRUE的Boolean_expression表达式对应的result_expression表达式的值；如果没有取值为TRUE的Boolean_expression表达式，则当指定ELSE子句时，返回else_result_expression的值；若没有指定ELSE子句，则返回NULL。【例8-21】使用搜索型CASE函数求解上题，以此对比两种CASE函数的用法。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程642 2CASTCAST函数函数语法格式：CAST(expression AS data_type)说明：将expression表达式的类型转换为data_type所指定的类型。参数expression可为任何有效的表达式，data_type：可为系统提供的基本类型，不能是用户自定义类型，如果data_type为nchar，nvarchar，char，varchar，binary，varbinary等数据类型时，通过length参数指定长度。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程653 3CONVERTCONVERT函数函数语法格式：CONVERT (data_type(length),expression,style)说明：将expression表达式的类型转换为data_type所指定的类型。各参数的说明同CAST函数。参数style一般取默认值，详细用法请参阅联机帮助。【例8-22】使用SELECT语句查询系统函数。-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程661设学位代码与学位名称如下表，用CASE函数编写学位代码转换为名称的程序。2declare ch intset ch=92select char(ch+5)上述语句的返回值是什么？ -任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程673写出下列函数的返回值。（1）ABS(-5) 5（2）REPLACE(ABCDEF,CD,UI) ABUIEF（3）SUBSTRING(中国人民,3,2) 人民（4）ASCII(SQL) 83-任务3 系统内置函数第第8章章 使用使用Transact-SQL语言编程语言编程68【任务目标】【任务目标】 学会创建、删除用户自定义函数 在设计数据库时能灵活运用用户自定义函数【任务描述】【任务描述】 创建一个自定义函数average(num char(20)，能利用该函数计算某门课程的平均分，并试着这个函数计算出“202”号课程平均分。-任务4 用户定义函数第第8章章 使用使用Transact-SQL语言编程语言编程69【任务分析】【任务分析】 在SQL Server中，除了系统提供的内置函数外，用户还可以根据需要在数据库中自己定义函数。用户定义函数是由一个或多个T-SQL语句组成的子程序，可以反复调用。SQL Server 2008根据用户定义函数返回值的类型，将用户定义函数分为标量函数、内嵌表值函数和多语句表值函数三种，本书只介绍最常用的前两种，且语法格式只给出常用的，完整语法格式请参阅SQL Server联机帮助。-任务4 用户定义函数第第8章章 使用使用Transact-SQL语言编程语言编程701 1标量函数标量函数若用户定义函数的返回值为标量值，则该函数称为标量函数。（1）定义标量函数语法格式：CREATE FUNCTION owner_name.function_name /*函数名部分(parameter_name AS scalar_parameter_data_type =default,n) /*形参定义部分RETURNS scalar_return_data_type /*返回值类型ASBEGIN function_body /*函数体部分 RETURN scalar_expression /*返回语句END-任务4 用户定义函数第第8章章 使用使用Transact-SQL语言编程语言编程71说明：owner_name：数据库所有者名。function_name：用户定义函数名。函数名必须符合标识符的规则，对其所有者来说，该名在数据库中必须是惟一的。parameter_name：用户定义函数的形参名。可以声明一个或多个参数，用符号作为第一个字符来指定形参名，每个函数的参数局部于该函数。scalar_parameter_data_type：参数的数据类型。可为系统支持的基本标量类型，不能为timestamp类型、用户定义数据类型、非标量类型（如：cursor和table）。scalar_return_data_type：返回值类型。可以是SQL Server支持的基本标量类型，但text、ntext、image和timestamp除外；函数返回scalar_expression表达式的值。function_body：由T-SQL语句序列构成的函数体。【例8-23】求某学生选修的所有课程的平均成绩。-任务4 用户定义函数第第8章章 使用使用Transact-SQL语言编程语言编程72（2）调用标量函数当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（所有者名.函数名）。可有以下两种方式调用标量函数： 利用SELECT语句调用语法格式：所有者名.函数名（实参1，实参n）说明：实参可为已赋值的局部变量或表达式。【例8-24】用SELECT语句调用例8-23定义的函数，求学号为200801的学生的平均成绩。-任务4 用户定义函数第第8章章 使用使用Transact-SQL语言编程语言编程73 利用EXECUTE语句调用语法格式：所有者名.函数名 实参1，实参n或所有者名.函数名 形参名1=实参1，形参名n=实参n说明：前者实参顺序应与函数定义的形参顺序一致，后者参数顺序可以与函数定义的形参顺序不一致。【例8-25】用EXECUTE语句调用例8-23定义的函数，求学号为200801的学生的平均成绩。-任务4 用户定