《使用TransactSQL编程.ppt》由会员分享，可在线阅读，更多相关《使用TransactSQL编程.ppt（44页珍藏版）》请在金锄头文库上搜索。
1、第5章 使用Transact-SQL编程本章学本章学习目目标：v掌握掌握变量的使用方法量的使用方法v掌握运算符和表达式的使用掌握运算符和表达式的使用v掌握函数的定掌握函数的定义和使用方法和使用方法v掌握流程控制掌握流程控制语句的使用句的使用Transact-SQL语言包括以下四个部分：言包括以下四个部分：v数据定数据定义语言言(DDL)：定：定义和管理数据和管理数据库及其及其对象，例如：象，例如：Create、Alter和和Drop等等语句。句。 v数据操作数据操作语言言(DML)：操作数据：操作数据库中各中各对象，例如：象，例如：Insert、Update、Delete和和Select语句。
2、句。v数据控制数据控制语言言(DCL)：进行安全管理和行安全管理和权限管理等，例如：限管理等，例如：Grant、Revoke、Deny等等语句。句。v附加的附加的语言元素：言元素：Transact-SQL语言的附加言的附加语言元素，包括言元素，包括变量、量、运算符、函数、注运算符、函数、注释和流程控制和流程控制语句等。句等。5.2 常量、常量、变量及运算符量及运算符 v表达式是指将常量、表达式是指将常量、变量、函数等，用运算符按量、函数等，用运算符按一定的一定的规则连接起来的有意接起来的有意义的式子。的式子。5.2.1 常量v常量，也称常量，也称为文字文字值或或标量量值，是表示一个特定，是表示
3、一个特定数据数据值的符号，在程序运行的符号，在程序运行过程中其程中其值保持不保持不变。 v常量的格式取决于它所表示的常量的格式取决于它所表示的值的数据的数据类型型1.字符串常量字符串常量 v字符串常量括在字符串常量括在单引号内并包含字母、数引号内并包含字母、数字字符（字字符（a-z、A-Z 和和 0-9）以及特殊）以及特殊字符，如字符，如!、 和和#。2数数值常量常量 v数数值常量以没有用引号括起来的数字字符常量以没有用引号括起来的数字字符串来表示，包括串来表示，包括Integer常量、常量、Decimal常量、常量、Float和和Real常量等，常量等，其中其中Integer常量没有小数点，
4、例如常量没有小数点，例如100，54等；等；Decimal常量包含小数点，常量包含小数点，例如例如123.45，5.6等；等；Float 和和 Real 常量使用科学常量使用科学记数法来表示，例如数法来表示，例如123E2，0.3E-3等。等。v如果要表示一个数是正数如果要表示一个数是正数还是是负数，可以数，可以对数数值常量常量应用用 + 或或 -运算符。运算符。3日期日期时间常量常量 日期日期时间常量使用特定格式的字符日期常量使用特定格式的字符日期时间值来表示，并被来表示，并被单引号引号括起来。括起来。4. 二进制常量 二二进制常量用不加引号的数字制常量用不加引号的数字0和和1表示，如果使用
5、大于表示，如果使用大于1的数字的数字表示，表示，则转换为1。5. 十六进制常量 十六十六进制常量用加前制常量用加前缀0x的十六的十六进制形式表示，例如制形式表示，例如0x24EB5、0xCF21A等。等。6.空值 空空值是表示是表示值未知，不同于空白或零未知，不同于空白或零值，用，用Null来表示。比来表示。比较两个两个空空值或将空或将空值与任何其他与任何其他值相比均返回未知，相比均返回未知，这是因是因为每个空每个空值均均为未知。在往表中添加未知。在往表中添加记录时，如果不，如果不对某一列某一列赋值则系系统自自动让该列取空列取空值，或者也可以在，或者也可以在Insert语句或句或Update语
6、句中句中显式地式地对某列某列赋空空值。 5.2.2 变量v变量是可以量是可以对其其赋值并参与运算的一个并参与运算的一个实体，其体，其值在运行在运行过程中可以程中可以发生改生改变。变量可以分量可以分为全全局局变量和局部量和局部变量两量两类，其中全局，其中全局变量由系量由系统定定义并并维护，局部，局部变量由用量由用户定定义并并赋值。局部。局部变量的用法非常广泛，除了可以参加运算构成表达量的用法非常广泛，除了可以参加运算构成表达式之外，式之外，还可以在程序中保存中可以在程序中保存中间结果、控制循果、控制循环执行次数、保存存行次数、保存存储过程的程的输出出结果和函数的果和函数的返回返回值等等 1. 全
7、局全局变量量v全局全局变量由系量由系统定定义，通常用来跟踪服，通常用来跟踪服务器范器范围和特定会和特定会话期期间的信息，不能被用的信息，不能被用户显式地定式地定义和和赋值，但是我，但是我们可以通可以通过访问全局全局变量来了解量来了解系系统目前的一些状目前的一些状态信息信息 ,全局全局变量以两个量以两个符号符号开开头 SQL Server中较常用的全局变量变量变量说明说明error上一条上一条SQL语句句报告的告的错误号号rowcount上一条上一条SQL语句句处理的行数理的行数identity最后插入的最后插入的标识值fetch_status上一条游上一条游标 Fetch 语句的状句的状态ne
8、stlevel当前存当前存储过程或触程或触发器的嵌套器的嵌套级别servername本地服本地服务器的名称器的名称spid当前用当前用户进程的会程的会话idcpu_busySQL Server 自上次启自上次启动后的工作后的工作时间2. 声明局部声明局部变量量v局部局部变量一般出量一般出现在批在批处理、存理、存储过程和触程和触发器中，必器中，必须在使用前用在使用前用Declare 语句声明：句声明：v指定局部指定局部变量名称。名称的第一个字符必量名称。名称的第一个字符必须是是。v指定指定变量的数据量的数据类型，可以是系型，可以是系统提供的数提供的数据据类型或用型或用户自定自定义数据数据类型。型
9、。对于字符型于字符型变量，量，还可以指定可以指定长度；数度；数值型型变量，指定量，指定精度和小数位数。精度和小数位数。赋初初值NullvDeclare语句的句的语法如下：法如下：vDeclare local_variable AS data_type | = value , 使用使用Set语句或句或SELECT语句句 给变量量赋值格式如下：格式如下： SET local_variable = expression SELECT local_variable = expression,n 其中，其中，local_variable是是变量的名称，量的名称，expression是任何有效的是任何有效
10、的SQL Server表达表达式，可以是常量、式，可以是常量、变量、函数和表达式，量、函数和表达式，还可以可以是子是子查询。 SELECT与与SET不同的是：不同的是：SELECT可以一次可以一次为多个多个变量量赋值。5.2.3 运算符vMicrosoft SQL Server 2008提供了提供了7中中类型型的运算符，分的运算符，分别是算是算术运算符、运算符、赋值运算符、位运算符、位运算符、比运算符、比较运算符、运算符、逻辑运算符、字符串运算运算符、字符串运算符和一元运算符。符和一元运算符。1. 算算术运算符运算符v算算术运算符运算符对两个表达式两个表达式执行数学运算，参与运算行数学运算，参
11、与运算的表达式必的表达式必须是数是数值数据数据类型或能型或能够进行算行算术运算运算的其它数据的其它数据类型。型。SQL Server 2008提供的算提供的算术运算符如下表运算符如下表运算符运算符名称名称语法语法+加加Expression1 + Expression2-减减Expression1 - Expression2*乘乘Expression1 * Expression2/除除Expression1 / Expression2%取余取余Expression1 % Expression22. 赋值运算符运算符v等号等号 (=) 是唯一的是唯一的 Transact-SQL 赋值运算运算符。符
12、。3. 字符串字符串连接运算符运算符v加号加号 (+) 是字符串串是字符串串联运算符，可以用它将字运算符，可以用它将字符串串符串串联起来。其他所有字符串操作都使用字符起来。其他所有字符串操作都使用字符串函数串函数进行行处理。理。4. 比比较运算符运算符v比比较运算符用来比运算符用来比较两个两个表达式表达式值之之间的大小关系，的大小关系，可以用于除了可以用于除了 text、ntext 或或 image 数据数据类型之外的所有数据型之外的所有数据类型。型。运算的运算的结果果为True、False，通常用来构造条，通常用来构造条件表达式。右表列出了件表达式。右表列出了 Transact-SQL 的比
13、的比较运算符。运算符。运算运算符符名称名称语法语法=等于等于Expression1 = Expression2大于大于Expression1 Expression2=大于等于大于等于Expression1 = Expression2小于小于Expression1 Expression2=小于等于小于等于Expression1 = Expression2或或!=不等于不等于Expression1 Expression2!不大于不大于Expression1 ! Expression2!不小于不小于Expression1 !any(值表或子表或子查询)All如果操作数与一列如果操作数与一列值中所有中
14、所有值的比的比较结果果为True，结果果为TrueExpression 、= 、= 、 、!=, ! 、!（比（比较运算符）运算符）vNotvAndvAll、Any、Between、In、Like、Or、Somev=（赋值）5.3 常用函数v函数是能函数是能够完成特定功能并返回完成特定功能并返回处理理结果的一果的一组Transact-SQL语句，句，处理理结果称果称为“返回返回值”，处理理过程称程称为“函数体函数体”。函数可以用来构造表达。函数可以用来构造表达式，可以出式，可以出现在在Select语句的句的选择列表中，也可列表中，也可以出以出现在在Where子句的条件中。子句的条件中。SQL
15、Server提供了提供了许多系多系统内置函数，同内置函数，同时也允也允许用用户根据根据需要自己定需要自己定义函数。函数。vSQL Serve提供的常用的内置函数主要有以下提供的常用的内置函数主要有以下几几类：数学函数、字符串函数、日期函数、：数学函数、字符串函数、日期函数、转换函数、聚合函数等函数、聚合函数等 。5.3.1数学函数常用的数学函数有：常用的数学函数有：vAbs ( numeric_expression )：返回指定数：返回指定数值表达式的表达式的绝对值vRound ( numeric_expression , length ,function )：返回一：返回一个舍入到指定的个舍
16、入到指定的长度或精度的数度或精度的数值vFloor ( numeric_expression )：返回小于或等于指定数：返回小于或等于指定数值表达式的最表达式的最大整数大整数vCeiling ( numeric_expression )：返回大于或等于指定数：返回大于或等于指定数值表达式的表达式的最小整数最小整数vPower ( float_expression , y )：返回指定表达式的指定：返回指定表达式的指定幂的的值vSqrt ( float_expression )：返回指定表达式的平方根：返回指定表达式的平方根vSquare ( float_expression )：返回指定表达式
17、的平方：返回指定表达式的平方vExp ( float_expression )：返回指定的表达式的指数：返回指定的表达式的指数值vLog ( float_expression )：返回指定表达式的自然：返回指定表达式的自然对数数vLog10 ( float_expression )：返回指定表达式的以：返回指定表达式的以10为底的底的对数数vSin ( float_expression )：返回指定角度（以弧度：返回指定角度（以弧度为单位）的三角正弦位）的三角正弦值vCos ( float_expression )：返回指定角度（以弧度：返回指定角度（以弧度为单位）的三角余弦位）的三角余弦值5
18、.3.2 字符串函数常用的字符串函数有：常用的字符串函数有：vAscii ( character_expression )：返回字符表达式中最左：返回字符表达式中最左侧的字符的的字符的 ASCII 代代码值vChar ( integer_expression )：将：将 int型的型的 ASCII 代代码转换为字符字符vStr ( float_expression , length , decimal )：返回由数字数据：返回由数字数据转换来的字符数据来的字符数据vLen ( string_expression )：返回指定字符串表达式的字符数，其中不包含尾：返回指定字符串表达式的字符数，其中
19、不包含尾随空格随空格vSubstring ( value_expression ,start_expression , length_expression )：返回字符表达式的从：返回字符表达式的从start_expression位置开始的位置开始的长度度为length_expression的子串的子串vLeft ( character_expression , integer_expression )：返回字符串中从：返回字符串中从左左边开始指定个数的字符开始指定个数的字符vRight ( character_expression , integer_expression )：返回字符串中：
20、返回字符串中从右从右边开始指定个数的字符开始指定个数的字符vLtrim( character_expression )：返回：返回删除了前除了前导空格之后的字符表达式空格之后的字符表达式vRtrim ( character_expression )：截断所有尾随空格后返回一个字符串：截断所有尾随空格后返回一个字符串5.3.3 日期和时间函数Transact-SQL语言中提供下列日期言中提供下列日期时间函数：函数：vGetdate ( )：返回系：返回系统当前的日期和当前的日期和时间vYear ( date )：返回表示指定参数：返回表示指定参数 date 的的“年年”部分的整数部分的整数vMo
21、nth ( date )：返回表示指定参数：返回表示指定参数 date 的的“月月”部分的整数部分的整数vDay ( date )：返回表示指定参数：返回表示指定参数date 的的“日日”部分的整数部分的整数vDatename ( datepart , date )：返回表示指定参数：返回表示指定参数date的指定的指定 datepart参数参数 的字符串的字符串vDatepart ( datepart , date )：返回表示指定参数：返回表示指定参数 date 的指定的指定 datepart参数参数 的整数的整数vDatediff ( datepart , startdate , end
22、date )：根据指定：根据指定datepart参数返回两个指定日期之参数返回两个指定日期之间的差的差值vDateadd (datepart , number , date )：根据：根据datepart参数将一个参数将一个时间间隔与指定隔与指定 date 参数相加，返回一个新的参数相加，返回一个新的 datetime 值5.3.4 Convert函数vConvert函数可以将一种数据函数可以将一种数据类型的表达式型的表达式强制制转换为另一种数据另一种数据类型的表达式。型的表达式。Convert函数函数的的语法格式法格式为：vConvert ( data_type ( length ) , e
23、xpression , style )参数参数说明：明：vexpression ：任何有效的表达式。：任何有效的表达式。vdata_type ：目：目标数据数据类型。型。vlength ：指定目：指定目标数据数据类型型长度的可度的可选整数。整数。vstyle ：用于日期：用于日期时间型数据型数据类型和字符数据型和字符数据类型的型的转换。5.4 用户自定义函数v通通过用用户自定自定义函数可以接受参数，函数可以接受参数，执行复行复杂的的操作并将操作的操作并将操作的结果以果以值的形式返回。根据函数的形式返回。根据函数返回返回值的的类型，可以把型，可以把SQL Server用用户自定自定义函数分函数分
24、为标量量值函数（数函数（数值函数）和表函数）和表值函数函数（内（内联表表值函数和多函数和多语句表句表值函数）。数函数）。数值函数函数返回返回结果果为单个数据个数据值；表；表值函数返回函数返回结果集果集（table数据数据类型）。型）。5.4.1 使用Create Function创建1.创建建标量量值函数函数v标量量值函数的函数体由一条或多条函数的函数体由一条或多条Transact-SQL语句句组成，成，这些些语句句以以Begin开始，以开始，以End结束。束。创建建标量量值函数的函数的语法法为：vCreate Function function_name v( parameter_name
25、As parameter_data_type = default Readonly v ,.n vv)vReturns return_data_typev With Encryption v As vBegin vfunction_body vReturn scalar_expressionvEnd2. 创建内建内联表表值函数函数v创建内建内联表表值函数的函数的语法如下：法如下：vCreate Function function_name v( parameter_name As parameter_data_type = default Readonly v ,.n vv)vReturns
26、Tablev With Encryption v As vReturn （select_statement）说明：明：v内内联表表值函数没有函数体函数没有函数体vReturn Table子句指明子句指明该用用户自定自定义函数的返回函数的返回值是一个表。是一个表。vReturn子句中的子句中的Select语句决定了返回表中的数据。句决定了返回表中的数据。3. 多多语句表句表值函数函数v与内与内联表表值函数不同的是，多函数不同的是，多语句表句表值函数在返回函数在返回语句之前句之前还有其他有其他的的Transact-SQL语句，具体的句，具体的语法如下：法如下：vCreate Function fu
27、nction_name v( parameter_name As parameter_data_type = default Readonly v ,.n vv)vReturns return_variable Table v With Encryption v As vBegin vfunction_body vReturnvEnd5.4.2 使用“SQL Server Management Studio”创建具体的步具体的步骤为：v(1)展开展开选定的数据定的数据库下的下的“可可编程性程性”节点。点。v(2展开展开“函数函数”节点，可以看到点，可以看到“标量量值函数函数”和和“表表值函数函
28、数”节点。点。v(3)在相在相应的的节点上点上单击鼠鼠标右右键，在，在弹出的快捷菜出的快捷菜单中中选择“新建新建函数函数”。v(4)在新建的在新建的查询窗口中可以看到关于自定窗口中可以看到关于自定义函数的函数的语句模板，在其中添上相句模板，在其中添上相应的内容，的内容，单击工具工具栏上的上的“执行行”按按钮即可。即可。5.4.3 修改自定义函数v跟跟创建自定建自定义函数一函数一样，函数的修改既可以通，函数的修改既可以通过Alter Function语句句进行，也可以通行，也可以通过对象象资源管理器源管理器进行。行。5.4.4. 删除自定义函数1.利用利用对象象资源管理器源管理器删除函数除函数在
29、在“SQL Server Management Studio”中中，找找到到需需要要删除除的的函函数数节点点，在在其其上上单击鼠鼠标右右键，在在弹出出的的快快捷捷菜菜单中中选择“删除除”选项，弹出出确确认删除窗口，除窗口，选择“确定确定”即可即可删除。除。v2.使用使用Drop Function语句句删除函数除函数v使用使用Drop Function语句可以从当前数句可以从当前数据据库中中删除一个或多个用除一个或多个用户自定自定义函数，函数，语法形式如下：法形式如下：vDrop Function function_name ,n5.5 批处理和流程控制语句v5.5.1 批批处理和注理和注释v1
30、.批批处理理在在程程序序中中，我我们可可以以使使用用GO语句句将将多多条条SQL语句句进行行分分隔隔，两两个个GO之之间的的SQL语句句可可作作为一一个个批批处理理。在在一一个个批批处理理中中可可以以包包含含一一条条或或多多条条T-SQL语句句，成成为一一个个语句句组。批批处理理中中的的语句句是是同同时从从应用用程程序序发送送到到 SQL Server服服务器器执行行，加加快快了了执行行速速度度。在在书写写批批处理理语句句时，需需要要使使用用GO语句作句作为批批处理命令的理命令的结束束标志。志。v2. 注释 注注释是是对程序的程序的说明解明解释。一般使用注。一般使用注释对程序程序进行行说明，例
31、如明，例如变量的含量的含义、程序的功能描述、基本思想等，增加程序的可、程序的功能描述、基本思想等，增加程序的可读性。性。 SQL Server 2008提供了两提供了两类注注释符。符。-：单行注行注释，注，注释语句写在注句写在注释符符-的后面，以最近的回的后面，以最近的回车符作符作为注注释的的结束。束。/*/：多行注：多行注释，“/*”在开在开头，“*/”在在结尾。尾。1. BeginEnd语句句vBeginEnd语句用于将多条句用于将多条Transact-SQL语句句组成一个成一个语句句块，作，作为一个整体来一个整体来执行。行。BeginEnd语句的句的语法格式法格式为：vBeginv sq
32、l_statement | statement_blockvEndv说明：明：vBeginEnd语句句块中至少要包含一条中至少要包含一条SQL语句。句。vBeginEnd语句句块常用在常用在If条件条件语句和句和While循循环语句中句中vBeginEnd语句允句允许嵌套。嵌套。5.5.2 流程控制语句2.IFELSE条件语句v语法格式法格式为：vIF Boolean_expressionvsql_statement|statement_blockvELSEv sql_statement|statement_blockv3. Case分支分支语句句v(1)简单Case表达式表达式v简单Case
33、表达式将一个表达式将一个测试表达式与一表达式与一组简单表达表达式式进行比行比较，如果某个，如果某个简单表达式与表达式与测试表达式的表达式的值相等，相等，则返回相返回相应结果表达式的果表达式的值。简单Case表达表达式的式的语法格式如下：法格式如下：vCase input_expression vWhen when_expression Then result_expression v .n v Else else_result_expression vEnd v(2)搜索搜索Case表达式表达式v搜索搜索Case表达式中，各个表达式中，各个When子句后都是布子句后都是布尔表达式。搜索表达式。
34、搜索Case表达式的表达式的语法格式如下：法格式如下：vCase vWhen Boolean_expression Then result_expression v .n v Else else_result_expression vEnd v4. While循环语句vWhile语句用来句用来实现重复重复执行行语句或句或语句句块，当，当指定的条件指定的条件为真真时，重复，重复执行循行循环语句。具体的句。具体的语法格式如下：法格式如下：vWhile Boolean_expression vsql_statement | statement_blockv Breakvsql_statement |
35、 statement_blockv Continuevsql_statement | statement_blockv5. Goto语句vGoto语句用于句用于实现程序跳程序跳转，作用是跳，作用是跳过Goto语句后面的句后面的SQL语句，并从句，并从标号所定号所定义的位置的位置处继续执行。行。v语法格式如下：法格式如下：vGOTO labelv说明：明：vlabel为标号，号，标号的定号的定义部分由部分由标识符和冒号符和冒号“：”组成，而成，而GOTO后面的后面的标号只写号只写标示符。示符。v6. Return语句v无条件无条件终止批止批处理、存理、存储过程或程或查询的的执行，行，Return
36、之后的之后的语句是不句是不执行的。具体的行的。具体的语法格法格式式为：vReturn integer_expression v其中，其中，integer_expression表示返回的整数表示返回的整数值。返回。返回值是可以省略的，是可以省略的，这时系系统将根据程序将根据程序的的执行情况返回一个整数，其中行情况返回一个整数，其中0表示表示执行成功，行成功，非非0值则表示失表示失败。v7. Waitfor语句vWaitfor语句用于句用于暂停程序的停程序的执行，直到指定的行，直到指定的时间间隔已隔已过或到达指定的或到达指定的时间点。具体的点。具体的语法格式法格式为：vWaitfor Delay
37、time_to_pass | Time time_to_executev其中，其中，Delay关关键字后的字后的time_to_pass，指定延，指定延迟的的时间，也就是等待的，也就是等待的时间间隔，等待的最隔，等待的最长时间为24小小时。Time关关键字后的字后的time_to_execute，指，指定运行批定运行批处理、存理、存储过程或事程或事务的的时间。time_to_pass和和time_to_execute的格式的格式为 hh:mm:ss，不能指定日期。，不能指定日期。 v8.屏幕输出语句Printv在程序运行在程序运行过程中程中经常需要向屏幕常需要向屏幕输出一些中出一些中间结果或果或最后的最后的结果果值，Print语句用于句用于实现这一功能，向屏幕一功能，向屏幕输出信息，其出信息，其语法格式法格式为：vPrint msg_str | local_variable | string_exprv参数参数说明如下。明如下。vmsg_str：要：要输出的字符串，用出的字符串，用单引号括起来。引号括起来。vlocal_variable：任何有效的字符数据：任何有效的字符数据类型的局部型的局部变量。量。vstring_expr：输出的字符串的表达式。可由字符串出的字符串的表达式。可由字符串连接运算符接运算符连接的常量、函数和接的常量、函数和变量等量等组成。成。
