使用TransactSQL编程.ppt

《使用TransactSQL编程.ppt》由会员分享，可在线阅读，更多相关《使用TransactSQL编程.ppt（44页珍藏版）》请在三一办公上搜索。

1、第5章 使用Transact-SQL编程,本章学习目标：掌握变量的使用方法掌握运算符和表达式的使用掌握函数的定义和使用方法掌握流程控制语句的使用,Transact-SQL语言包括以下四个部分：数据定义语言(DDL)：定义和管理数据库及其对象，例如：Create、Alter和Drop等语句。数据操作语言(DML)：操作数据库中各对象，例如：Insert、Update、Delete和Select语句。数据控制语言(DCL)：进行安全管理和权限管理等，例如：Grant、Revoke、Deny等语句。附加的语言元素：Transact-SQL语言的附加语言元素，包括变量、运算符、函数、注释和流程控制语句

2、等。,5.2 常量、变量及运算符,表达式是指将常量、变量、函数等，用运算符按一定的规则连接起来的有意义的式子。,5.2.1 常量,常量，也称为文字值或标量值，是表示一个特定数据值的符号，在程序运行过程中其值保持不变。常量的格式取决于它所表示的值的数据类型,1.字符串常量 字符串常量括在单引号内并包含字母、数字字符（a-z、A-Z 和 0-9）以及特殊字符，如!、和#。2数值常量 数值常量以没有用引号括起来的数字字符串来表示，包括Integer常量、Decimal常量、Float和Real常量等，其中Integer常量没有小数点，例如100，54等；Decimal常量包含小数点，例如123.45

3、，5.6等；Float 和 Real 常量使用科学记数法来表示，例如123E2，0.3E-3等。如果要表示一个数是正数还是负数，可以对数值常量应用+或-运算符。,3日期时间常量 日期时间常量使用特定格式的字符日期时间值来表示，并被单引号括起来。4.二进制常量 二进制常量用不加引号的数字0和1表示，如果使用大于1的数字表示，则转换为1。5.十六进制常量 十六进制常量用加前缀0 x的十六进制形式表示，例如0 x24EB5、0 xCF21A等。6.空值 空值是表示值未知，不同于空白或零值，用Null来表示。比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。在往表中添加记录时，

4、如果不对某一列赋值则系统自动让该列取空值，或者也可以在Insert语句或Update语句中显式地对某列赋空值。,5.2.2 变量,变量是可以对其赋值并参与运算的一个实体，其值在运行过程中可以发生改变。变量可以分为全局变量和局部变量两类，其中全局变量由系统定义并维护，局部变量由用户定义并赋值。局部变量的用法非常广泛，除了可以参加运算构成表达式之外，还可以在程序中保存中间结果、控制循环执行次数、保存存储过程的输出结果和函数的返回值等,1.全局变量全局变量由系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值，但是我们可以通过访问全局变量来了解系统目前的一些状态信息,全局

5、变量以两个符号开头,SQL Server中较常用的全局变量,2.声明局部变量局部变量一般出现在批处理、存储过程和触发器中，必须在使用前用Declare 语句声明：指定局部变量名称。名称的第一个字符必须是。指定变量的数据类型，可以是系统提供的数据类型或用户自定义数据类型。对于字符型变量，还可以指定长度；数值型变量，指定精度和小数位数。赋初值NullDeclare语句的语法如下：Declare local_variable AS data_type|=value,使用Set语句或SELECT语句 给变量赋值格式如下：SET local_variable=expression SELECT loca

6、l_variable=expression,n 其中，local_variable是变量的名称，expression是任何有效的SQL Server表达式，可以是常量、变量、函数和表达式，还可以是子查询。SELECT与SET不同的是：SELECT可以一次为多个变量赋值。,5.2.3 运算符,Microsoft SQL Server 2008提供了7中类型的运算符，分别是算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串运算符和一元运算符。,1.算术运算符算术运算符对两个表达式执行数学运算，参与运算的表达式必须是数值数据类型或能够进行算术运算的其它数据类型。SQL Server 2

7、008提供的算术运算符如下表,2.赋值运算符等号(=)是唯一的 Transact-SQL 赋值运算符。3.字符串连接运算符加号(+)是字符串串联运算符，可以用它将字符串串联起来。其他所有字符串操作都使用字符串函数进行处理。,4.比较运算符比较运算符用来比较两个表达式值之间的大小关系，可以用于除了 text、ntext 或 image 数据类型之外的所有数据类型。运算的结果为True、False，通常用来构造条件表达式。右表列出了 Transact-SQL 的比较运算符。,5.逻辑运算符逻辑运算符用来对多个条件进行运算，运算的结果为True或False，通常用来表示复杂的条件表达式。下表列出了

8、Transact-SQL 的逻辑运算符。,逻辑运算符,6.按位运算符按位运算符对两个二进制数据或整数数据进行位操作，但是两个操作数不能同时为二进制数据，必须有一个为整数数据。SQL Server 2008提供的按位运算符如下表所示。,7.一元运算符一元运算符只对一个表达式进行运算，SQL Server 2008提供的一元运算符如下表所示。,5.2.4 运算符的优先级,（按位取反）*（乘）、/（除）、%（取余）+（正）、-（负）、+（加）、（+字符串串联）、-（减）、&（按位与）、（按位异或）、|（按位或）=、=、!=,!、!（比较运算符）NotAndAll、Any、Between、In、Lik

9、e、Or、Some=（赋值）,5.3 常用函数,函数是能够完成特定功能并返回处理结果的一组Transact-SQL语句，处理结果称为“返回值”，处理过程称为“函数体”。函数可以用来构造表达式，可以出现在Select语句的选择列表中，也可以出现在Where子句的条件中。SQL Server提供了许多系统内置函数，同时也允许用户根据需要自己定义函数。SQL Serve提供的常用的内置函数主要有以下几类：数学函数、字符串函数、日期函数、转换函数、聚合函数等。,数学函数,常用的数学函数有：Abs(numeric_expression)：返回指定数值表达式的绝对值Round(numeric_expres

10、sion,length,function)：返回一个舍入到指定的长度或精度的数值Floor(numeric_expression)：返回小于或等于指定数值表达式的最大整数Ceiling(numeric_expression)：返回大于或等于指定数值表达式的最小整数Power(float_expression,y)：返回指定表达式的指定幂的值Sqrt(float_expression)：返回指定表达式的平方根Square(float_expression)：返回指定表达式的平方Exp(float_expression)：返回指定的表达式的指数值Log(float_expression)：返回指定

11、表达式的自然对数Log10(float_expression)：返回指定表达式的以10为底的对数Sin(float_expression)：返回指定角度（以弧度为单位）的三角正弦值Cos(float_expression)：返回指定角度（以弧度为单位）的三角余弦值,5.3.2 字符串函数,常用的字符串函数有：Ascii(character_expression)：返回字符表达式中最左侧的字符的 ASCII 代码值Char(integer_expression)：将 int型的 ASCII 代码转换为字符Str(float_expression,length,decimal)：返回由数字数据转换

12、来的字符数据Len(string_expression)：返回指定字符串表达式的字符数，其中不包含尾随空格Substring(value_expression,start_expression,length_expression)：返回字符表达式的从start_expression位置开始的长度为length_expression的子串Left(character_expression,integer_expression)：返回字符串中从左边开始指定个数的字符Right(character_expression,integer_expression)：返回字符串中从右边开始指定个数的字符Lt

13、rim(character_expression)：返回删除了前导空格之后的字符表达式Rtrim(character_expression)：截断所有尾随空格后返回一个字符串,5.3.3 日期和时间函数,Transact-SQL语言中提供下列日期时间函数：Getdate()：返回系统当前的日期和时间Year(date)：返回表示指定参数 date 的“年”部分的整数Month(date)：返回表示指定参数 date 的“月”部分的整数Day(date)：返回表示指定参数date 的“日”部分的整数Datename(datepart,date)：返回表示指定参数date的指定 datepart参

14、数 的字符串Datepart(datepart,date)：返回表示指定参数 date 的指定 datepart参数 的整数Datediff(datepart,startdate,enddate)：根据指定datepart参数返回两个指定日期之间的差值Dateadd(datepart,number,date)：根据datepart参数将一个时间间隔与指定 date 参数相加，返回一个新的 datetime 值,5.3.4 Convert函数,Convert函数可以将一种数据类型的表达式强制转换为另一种数据类型的表达式。Convert函数的语法格式为：Convert(data_type(leng

15、th),expression,style)参数说明：expression：任何有效的表达式。data_type：目标数据类型。length：指定目标数据类型长度的可选整数。style：用于日期时间型数据类型和字符数据类型的转换。,5.4 用户自定义函数,通过用户自定义函数可以接受参数，执行复杂的操作并将操作的结果以值的形式返回。根据函数返回值的类型，可以把SQL Server用户自定义函数分为标量值函数（数值函数）和表值函数（内联表值函数和多语句表值函数）。数值函数返回结果为单个数据值；表值函数返回结果集（table数据类型）。,5.4.1 使用Create Function创建,1.创建标量

16、值函数标量值函数的函数体由一条或多条Transact-SQL语句组成，这些语句以Begin开始，以End结束。创建标量值函数的语法为：Create Function function_name(parameter_name As parameter_data_type=default Readonly,.n)Returns return_data_type With Encryption As Begin function_body Return scalar_expressionEnd,2.创建内联表值函数创建内联表值函数的语法如下：Create Function function_name

17、(parameter_name As parameter_data_type=default Readonly,.n)Returns Table With Encryption As Return（select_statement）说明：内联表值函数没有函数体Return Table子句指明该用户自定义函数的返回值是一个表。Return子句中的Select语句决定了返回表中的数据。,3.多语句表值函数与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的Transact-SQL语句，具体的语法如下：Create Function function_name(parameter_name

18、 As parameter_data_type=default Readonly,.n)Returns return_variable Table With Encryption As Begin function_body ReturnEnd,5.4.2 使用“SQL Server Management Studio”创建,具体的步骤为：(1)展开选定的数据库下的“可编程性”节点。(2展开“函数”节点，可以看到“标量值函数”和“表值函数”节点。(3)在相应的节点上单击鼠标右键，在弹出的快捷菜单中选择“新建函数”。(4)在新建的查询窗口中可以看到关于自定义函数的语句模板，在其中添上相应的内容，

19、单击工具栏上的“执行”按钮即可。,5.4.3 修改自定义函数,跟创建自定义函数一样，函数的修改既可以通过Alter Function语句进行，也可以通过对象资源管理器进行。,5.4.4.删除自定义函数,1.利用对象资源管理器删除函数在“SQL Server Management Studio”中，找到需要删除的函数节点，在其上单击鼠标右键，在弹出的快捷菜单中选择“删除”选项，弹出确认删除窗口，选择“确定”即可删除。,2.使用Drop Function语句删除函数使用Drop Function语句可以从当前数据库中删除一个或多个用户自定义函数，语法形式如下：Drop Function funct

20、ion_name,n,5.5 批处理和流程控制语句,5.5.1 批处理和注释1.批处理在程序中，我们可以使用GO语句将多条SQL语句进行分隔，两个GO之间的SQL语句可作为一个批处理。在一个批处理中可以包含一条或多条T-SQL语句，成为一个语句组。批处理中的语句是同时从应用程序发送到 SQL Server服务器执行，加快了执行速度。在书写批处理语句时，需要使用GO语句作为批处理命令的结束标志。2.注释 注释是对程序的说明解释。一般使用注释对程序进行说明，例如变量的含义、程序的功能描述、基本思想等，增加程序的可读性。SQL Server 2008提供了两类注释符。-：单行注释，注释语句写在注释符

21、-的后面，以最近的回车符作为注释的结束。/*/：多行注释，“/*”在开头，“*/”在结尾。,1.BeginEnd语句BeginEnd语句用于将多条Transact-SQL语句组成一个语句块，作为一个整体来执行。BeginEnd语句的语法格式为：Begin sql_statement|statement_blockEnd说明：BeginEnd语句块中至少要包含一条SQL语句。BeginEnd语句块常用在If条件语句和While循环语句中BeginEnd语句允许嵌套。,5.5.2 流程控制语句,2.IFELSE条件语句,语法格式为：IF Boolean_expressionsql_statemen

22、t|statement_blockELSE sql_statement|statement_block,3.Case分支语句(1)简单Case表达式简单Case表达式将一个测试表达式与一组简单表达式进行比较，如果某个简单表达式与测试表达式的值相等，则返回相应结果表达式的值。简单Case表达式的语法格式如下：Case input_expression When when_expression Then result_expression.n Else else_result_expression End,(2)搜索Case表达式搜索Case表达式中，各个When子句后都是布尔表达式。搜索Case

23、表达式的语法格式如下：Case When Boolean_expression Then result_expression.n Else else_result_expression End,4.While循环语句While语句用来实现重复执行语句或语句块，当指定的条件为真时，重复执行循环语句。具体的语法格式如下：While Boolean_expression sql_statement|statement_block Breaksql_statement|statement_block Continuesql_statement|statement_block,5.Goto语句Goto语

24、句用于实现程序跳转，作用是跳过Goto语句后面的SQL语句，并从标号所定义的位置处继续执行。语法格式如下：GOTO label说明：label为标号，标号的定义部分由标识符和冒号“：”组成，而GOTO后面的标号只写标示符。,6.Return语句无条件终止批处理、存储过程或查询的执行，Return之后的语句是不执行的。具体的语法格式为：Return integer_expression 其中，integer_expression表示返回的整数值。返回值是可以省略的，这时系统将根据程序的执行情况返回一个整数，其中0表示执行成功，非0值则表示失败。,7.Waitfor语句Waitfor语句用于暂停程

25、序的执行，直到指定的时间间隔已过或到达指定的时间点。具体的语法格式为：Waitfor Delay time_to_pass|Time time_to_execute其中，Delay关键字后的time_to_pass，指定延迟的时间，也就是等待的时间间隔，等待的最长时间为24小时。Time关键字后的time_to_execute，指定运行批处理、存储过程或事务的时间。time_to_pass和time_to_execute的格式为 hh:mm:ss，不能指定日期。,8.屏幕输出语句Print在程序运行过程中经常需要向屏幕输出一些中间结果或最后的结果值，Print语句用于实现这一功能，向屏幕输出信息，其语法格式为：Print msg_str|local_variable|string_expr参数说明如下。msg_str：要输出的字符串，用单引号括起来。local_variable：任何有效的字符数据类型的局部变量。string_expr：输出的字符串的表达式。可由字符串连接运算符连接的常量、函数和变量等组成。,