Transact-SQL程序设计.ppt

第6章Transact-SQL程序设计,本章学习目标,了解批处理的概念掌握Transact-SQL语言中的变量的定义及使用方法掌握Transact-SQL语言中的常用系统函数及使用方法掌握Transact-SQL语言中的常用运算符及其优先级掌握Transact-SQL语言中的流程控制语句的种类及用法,6.1概述,由于标准SQL语言形式简单，不能满足实际应用中的编程需要，因此，Transact-SQL语言另外增加了一些语言要素，这些附加的语言要素不是标准SQL中的内容。它们包括注释、变量、运算符、函数和流程控制语句等。,6.1概述,为了管理SQLServer2005的一些新增功能和提供程序开发的能力，Transact-SQL将原有的语法加以翻新，提供了更丰富的内容，同时也使得Transact-SQL的语法更一致化。SQLServer2005新增了原生的XML数据类型，能大幅增强XML数据访问的能力。,6.1概述,为了提高程序的执行效率，在Transact-SQL语言编写的程序中，还可以使用GO语句将多条SQL语句进行分隔，两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQLServer服务器进行执行。SQLServer服务器将批处理编译成一个可执行单元，称为执行计划。,6.1概述,例6-1-1：使用GO语句的例子。注意：GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端命令。,6.2注释,注释，也称为注解，是写在程序代码中的说明性文字，它们对程序的结构及功能进行文字说明。注释内容不被系统编译，也不被程序执行。在SQLServer中，有两种类型的注释字符：单行注释：使用两个连在一起的减号“-”作为注释符。多行注释：使用“/*/”作为注释符。例6-2-1：在程序中使用注释的例子。,6.3变量,变量是一种程序设计语言中必不可少的组成部分，可以用它保存程序运行过程中的中间值，也可以在语句之间传递数据。Transact-SQL语言中的变量是可以保存单个特定类型的数据值的对象，也称为局部变量，只在定义它们的批处理或过程中可见。,6.3.1变量的定义,Transact-SQL语言中的变量在定义和引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用。DECLAERlocal_variabledata_typen,6.3.1变量的定义,各个参数的含义如下：local_variable：用于指定变量的名称，变量名必须以符号开头，并且变量名必须符合SQLServer的命名规则。data_type：用于设置变量的数据类型及其大小。data_type可以是任何由系统提供的或用户定义的数据类型。但是，变量不能是text，ntext或image数据类型。,6.3.2变量的赋值方法,使用DECLARE命令声明并创建变量之后，系统会将其初始值设为NULL，如果想要设定变量的值，必须使用SET命令或者SELECT命令。SETlocal_variable=expression或者SELECTlocal_variable=expression,.n参数local_variable是给其赋值并声明的变量，expression是有效的SQLServer表达式。,6.3.3变量使用举例,例6-3-1：创建了一个变量CurrentDateTime，然后将GETDATE()函数的值放在变量中，最后输出CurrentDateTime变量的值。注意：变量只在定义它的批处理中有效，因此，在上例中的程序中间不能写入GO语句。例6-3-2：查询T_STUDENT表，将返回的记录数赋给变量RowsReturn。,6.3.3变量使用举例,例6-3-3：在SELECT语句中使用由SET赋值的变量。例6-3-4：在T_SCORE表中，求05541班学生高等数学课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。,6.4运算符,运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。在SQLServer2005中，运算符主要有以下六大类：算术运算符、赋值运算符、按位运算符、比较运算符、逻辑运算符、字符串串联运算符。,6.4.1算术运算符,算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）。对于加、减、乘、除这四种算术运算符，计算的两个表达式可以是数字数据类型分类的任何数据类型；对于取模运算符，要求进行计算的数据的数据类型为int、smallint和tinyint，完成的功能是返回一个除法运算的整数余数。例6-4-1：计算表达式的值，并将结果赋给变量ExpResult。,6.4.2赋值运算符,Transact-SQL中只有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。例6-4-2：查询05541班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌。,6.4.3按位运算符,按位运算符包括按位与（&）、按位或（|）和按位异或（）。位运算符在两个表达式之间执行位操作，这两个表达式可以为整数数据类型类别中的任何数据类型。要求在位运算符左右两侧的操作数不能同时是二进制数据。例6-4-3：使用位运算符计算表达式的值。,6.4.4比较运算符,在SQLServer2005中，比较运算符包括：等于（=）、大于（）、大于或等于（=）、小于（或!=）、不小于（!）。比较运算符用于比较两个表达式的大小，其比较的结果是布尔值。例6-4-4：使用比较运算符计算表达式的值。,6.4.5逻辑运算符,逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。逻辑运算符用来把多个逻辑表达式连接起来。逻辑运算返回布尔值，值为TRUE或FALSE。例6-4-5：使用比较运算符计算表达式的值。,6.4.6字符串串联运算符,加号(+)是字符串串联运算符，可以用它将字符串串联起来。在SQLServer2005中，允许使用加号对两个或多个字符串进行串联。例6-4-6：使用字符串连接运算符计算表达式的值。,6.4.7运算符的优先级,当一个复杂的表达式中包含多种运算符时，运算符的优先顺序将决定表达式的计算和比较顺序。当一个表达式中的两个运算符有相同的运算符优先级别时，将按照它们在表达式中的位置对其从左到右进行求值。例6-4-7：使用括号改变表达式的计算顺序。,6.5函数,SQLServer2005提供了许多内置函数，可以在Transact-SQL程序中使用这些内置函数，方便地完成一些特殊的运算和操作。函数用函数名来标识，在函数名称之后有一对小括号，如：GETDATE（）。,6.5.1系统函数,系统函数用于返回有关SQLServer系统、用户、数据库和数据库对象的信息。用户可以根据系统函数返回的信息进行不同的操作。在SQLServer2005中，系统函数的表现形式有2种，一种系统函数是由英文字母开头，另一种系统函数是以开头。,6.5.1系统函数,1使用英文字母开头的系统函数常用系统函数及其含义如下：COL_NAME：返回表中指定字段的名称，即列名。COL_LENGTH：返回指定字段的长度值。DB_ID：返回数据库的编号。DB_NAME：返回数据库的名称。DATALENGTH：返回任何数据表达式的实际长度。HOST_ID：返回服务器端计算机的ID号。,6.5.1系统函数,HOST_NAME：返回服务器端计算机的名称。ISNULL：用指定值替换表达式中的指定空值。NULLIF：如果两个表达式相等，则返回NULL值。OBJECT_ID：返回数据库对象的编号。OBJECT_NAME：返回数据库对象的名称。USER_ID：返回用户的数据库ID号。USER_NAME：返回用户的数据库用户名。,6.5.1系统函数,例6-5-1：从STUDENT数据库中返回T_STUDENT表的第4列的名称。例6-5-2：从STUDENT数据库中返回T_STUDENT表的S_NUMBER列的长度。,6.5.1系统函数,例6-5-3：检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据库名称。例6-5-4：在T_STUDENT表中先插入三条新记录，其中的POLITY字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示“群众”。,6.5.1系统函数,2使用开头的系统函数SQLServer使用这类系统函数来记录SQLServer服务器的活动状态。在SQLServer2000及以前的版本中，function被称为全局变量。从SQLServer2005开始，将function作为系统函数。,6.5.1系统函数,使用开头的常用系统函数的名称及其含义如下：CONNECTIONS：返回自最近一次启动SQLServer以来连接或试图连接的次数。ERROR：返回最后执行的Transact-SQL语句的错误代码。IDENTITY：返回最后插入的标识列的列值。LANGUAGE：返回当前使用的语言名。MAX_CONNECTIONS：返回SQLServer上允许的用户同时连接的最大数。,6.5.1系统函数,ROWCOUNT：返回上一次语句影响的数据行的行数。SERVERNAME：返回运行SQLServer的本地服务器的名称。SERVICENAME：返回SQLServer当前运行的服务名称。VERSION：返回SQLServer当前安装的日期、版本和处理器类型。,6.5.1系统函数,例6-5-5：利用ERROR返回程序的错误代码。例6-5-6：显示到当前日期和时间为止试图登录SQLServer的次数。例6-5-7：返回在程序执行过程中上一条SQL语句影响的记录数。例6-5-8：返回SQLServer安装的日期、版本和处理器类型。,6.5.2字符串函数,字符串函数可以对二进制数据、字符串和表达式执行不同的运算，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字符串函数。常用的字符串函数如下所示：ASC（char_expr）：返回第一个字符的ASC值。CHAR（integer_expr）：返回相同ASC代码值的字符。CHARINDEX（expr1，expr2，startposition）：返回字符串中指定表达式的起始位置。DIFFERENCE（char_expr1,char_expr2）：比较两个字符串。,6.5.2字符串函数,LTRIM（char_expr）：删除数据前面的空格。LOWER（char_expr）：转换成小写字母。REPLICATE（char_expr,integer_expr）：按照给定的次数，重复表达式的值。RIGHT（char_expr,integer_expr）：返回字符串中从右开始到指定位置的部分字符。REVERSE（char_expr）：求反向表达式。RTRIM（char_expr）：去掉字符串后面的空格。SPACE（integer_expr）：返回长度为指定数据的空格。,6.5.2字符串函数,STUFF（char_expr1,star,length,char_expr2）：在char_expr1中，把从位置star开始，长度为length的字符串用char_expr2代替。SUBSTRING（expr,start,length）：返回指定表达式的一部分。STR（float_expr,length,decimal）：把数值变成字符串返回，length是总长度，decimal是小数点右边的位数。UPPER（char_expr）：把给定的字符串变成大写字母。,6.5.2字符串函数,例6-5-9：使用RTRIM函数删除字符型字段中的尾部空格。例6-5-10：使用SUBSTRING函数、LEFT函数和RIGHT函数求T_STUDENT中的班级名称和学号。例6-5-11：在第一个字符串（GoodMorning）中删除从第6个位置（字符M）开始的7个字符，然后在删除的起始位置插入第二个字符串（Afternoon），创建并返回一个新的字符串。,6.5.3日期和时间函数,日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期和时间函数。,6.5.3日期和时间函数,SQLServer2005中，常用的日期和时间函数：DATEADD（datepart,number,date）：以datepart指定的方式，返回date加上number之和。DATEDIFF（datepart,date1,date2）：以datepart指定的方式，返回date2与date1之差。DATENAME（datepart,date）：返回日期date中datepart指定部分所对应的字符串。DATEPART（datepart,date）：返回日期date中datepart指定部分所对应的整数值。,6.5.3日期和时间函数,DAY（date）：返回指定日期的天数。GETDATE（）：返回当前的日期和时间。MONTH（date）：返回指定日期的月份数。YEAR（date）：返回指定日期的年份数。,6.5.3日期和时间函数,例6-5-12：查询T_STUDENT表的学生信息，要求显示的字段为：学号、姓名、性别和学生的年龄。例6-5-13：在T_STUDENT表中查询每个学生的出生月份。例6-5-14：从系统当前日期中返回系统当前的年份数、月份数和天数。,6.5.4数学函数,数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQLServer提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint和tinyint）进行处理。在SQLServer中，常用的数学函数如下：ABS（numeric_expr）：返回绝对值。,6.5.4数学函数,EXP（float_expr）：返回给定数据的指数值。LOG（float_expr）：返回给定值的自然对数。LOG10（float_expr）：返回底为10的自然对数值。SQRT（float_expr）：返回给定值的平方根。CEILING（numeric_expr）：返回大于或者等于给定值的最小整数。FLOOR（numeric_expr）：返回小于或者等于给定值的最大整数。,6.5.4数学函数,ROUND（numeric_expr,length）：将给定的数值四舍五入到指定的长度。PI（）：常量，3.141592653589793。RAND（seed）：返回0和1之间的一个随机数。例6-5-15：在同一表达式中使用CEILING（），FLOOR（），ROUND（）函数。,6.5.5转换函数,一般情况下，SQLServer会自动处理某些数据类型的转换。这种转换被称为隐式转换。但是，无法由SQLServer自动转换的或者是SQLServer自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。转换函数有两个：CONVERT和CAST。,6.5.4数学函数,CAST函数允许把一个数据类型强制转换为另一种数据类型：CAST（expressionASdata_type）CONVERT函数允许把表达式从一种数据类型转换成另一种数据类型：CONVERT（data_type（length）,expression,style）例6-5-16：查询T_STUDENT表中出生年份为1985年的学生信息。,6.5.6用户自定义函数,在实际编程过程中，除了可以直接使用系统提供的内置函数以外，SQLServer2005还允许用户使用自定义的函数。在用户自定义函数中可以包含0个或多个参数，函数的返回值可以是数值，还可以是一个表。,6.5.6用户自定义函数,创建返回数值的用户自定义函数的基本语法为：CREATEFUNCTION函数名称（形式参数名称AS数据类型）RETURNS返回数据类型BEGIN函数内容RETURN表达式END,6.5.6用户自定义函数,调用用户自定义函数的基本语法为：变量=用户名.函数名称（实际参数列表）注意：在调用返回数值的用户自定义函数时，一定要在函数名称的前面加上用户名，否则会出现“函数名称不是可以识别的函数名”的错误提示信息。,6.5.6用户自定义函数,例6-5-17：编写一个用户自定义函数fun_SumScores，要求根据输入的班级号和课程号，求此班此门课程的总分。例6-5-18：使用自定义函数fun_SumScores，求T_SCORE表中的各个班级的各门课程的总分。,6.6流程控制语句,Transact-SQL提供了流程控制语句来控制SQL语句、语句块或者存储过程的执行流程。在SQLServer中，可以使用的流程控制语句有BEGINEND、IFELSE、WHILECONTINUEBREAK、GOTO、WAITFOR、RETURN等。,6.6.1BEGINEND语句,BEGINEND语句用于将多条Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环语句等控制流程语句中，当符合特定条件需要执行两个或者多个语句时，就应该使用BEGINEND语句将这些语句组合在一起。BEGINEND语句的语法形式为：BEGINsql_statement|statement_blockEND,6.6.2IFELSE语句,IFELSE语句是条件判断语句，当条件表达式成立时执行某段程序，条件不成立时执行另一段程序。其中，ELSE子句是可选的。IFBoolean_expressionsql_statement|statement_blockELSEsql_statement|statement_block,6.6.2IFELSE语句,例6-6-1：根据T_SCORE表中的考试成绩，查询05541班学生高等数学的平均成绩，并根据平均成绩输出相应的提示信息。,6.6.3CASE语句,CASE语句用于多重选择的情况。可以根据条件表达式的值进行判断，并将其中一个满足条件的结果表达式返回。CASE语句按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句。,6.6.3CASE语句,简单CASE语句的语法形式为：CASEinput_expressionWHENwhen_expressionTHENresult_expression.nELSEelse_result_expressionEND简单CASE语句的执行过程为：先计算CASE后面的表达式的值，然后将其与WHEN后面的表达式逐个进行比较，若相等则返回THEN后面的表达式，否则返回ELSE后面的表达式。,6.6.3CASE语句,搜索CASE语句的语法形式为：CASEWHENBoolean_expressionTHENresult_expression.nELSEelse_result_expressionEND搜索CASE语句的执行过程为：如果WHEN后面的逻辑表达式为真，则返回THEN后面的表达式，然后判断下一个逻辑表达式，如果所有的逻辑表达式都为假，则返回ELSE后面的表达式。,6.6.3CASE语句,例6-6-2：查询05541班学生的考试情况，并使用CASE语句将课程号替换为课程名进行显示。例6-6-3：查询05541班学生的考试情况，并根据考试分数输出考试等级，当分数大于等于90分，输出“优”，当分数在80至90之间，输出“良”，当分数在70至80之间，输出“中”，当分数在60至70之间，输出“及格”，当分数在60分以下，输出“不及格”。,6.6.4WHILECONTINUEBREAK语句,WHILECONTINUEBREAK语句的功能是可以重复执行SQL语句或语句块。当WHILE后面的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。,6.6.4WHILECONTINUEBREAK语