资源简介

(共34张PPT)
SQL Server 2019高级应用
第9章
目录
01
T-SQL编程基础
02
游标
03
存储过程
04
触发器
本章主要内容
本章主要阐述T-SQL的标识符、运算符、表达式、函数、变量与常量、流程控制语句等基础知识，并结合实例探讨T-SQL最常见的应用，分析游标的基本原理和使用方法，重点介绍使用T-SQL创建、管理存储过程和触发器。
T-SQL编程基础
第9章
01
9.1.1 T-SQL语法元素
1．标识符：用来标识服务器、数据库和数据库对象。保留字不能作为标识符。
数据类型：用来定义数据对象。
运算符：表达式的组成部分。
表达式：将标识符、值和运算符按一定的规则连接起来的有意义的式子。
函数：可以有n个参数，并返回一个值或几个值的集合。
注释：作为程序说明性文字或调试程序时的辅助手段。
保留关键字：定义、操作或访问数据库。
9.1.2 常量
在程序运行过程中其值不会发生改变的量就是常量，又称文字值或标量值。
在T-SQL程序设计中，常量的格式取决于它所表示的数据值的数据类型，主要包含字符串常量、数值常量、日期常量等。
1．字符串常量
字符串常量需要以单引号（'）括起来。
【例9.1】查询教师表teacher中职称为副教授的教师信息。
SELECT * FROM teacher
WHERE Prof='副教授';
9.1.2 常量
2．数值常量
数值常量包含的内容较多，所占用的存储空间的大小不同，表示的数据范围也各有不同。
3．日期常量
和字符串常量一样，日期常量使用特定格式的字符日期值表示，用单引号（'）括起来。
【例9.2】查询教师表teacher中入职时间在1996年以后的教师信息。
SELECT * FROM teacher
WHERE Hiredate >'1996-12-31';
9.1.3 变量
变量用于临时存储数据，是在语句之间传递数据的方式之一，是可以对其赋值并参与运算的一个实体。变量分为局部变量和全局变量两类。
1．全局变量
全局变量是SQL Server 2019系统内部使用的变量，作用于任何程序。全局变量由系统定义和维护，用户只能使用预先说明及定义的全局变量，因此，全局变量对用户而言是只读的，用户无法对它们进行修改或管理。使用全局变量必须以两个“@@”开头。
9.1.3 变量
2．局部变量
T-SQL中的局部变量是由用户自定义和使用的变量，命名时不区分大小写，但不能与全局变量的名称相同。局部变量一般用于临时存储数据，方便在语句之间传递数值。局部变量的作用范围有限，仅局限在一个批处理、存储过程或触发器中。
局部变量以一个@为标记，如@变量名。T-SQL中的局部变量遵从先声明后使用的原则。用DECLARE语句声明定义局部变量，其语法格式如下：
DECLARE @变量名 数据类型[(长度)] [,@变量名 数据类型[(长度)],…]
9.1.3 变量
2．局部变量
在对局部变量赋值时，可以选用SET命令或SELECT命令。其语法格式如下：
SET @变量名=变量值
SELECT @变量名=变量值[,@变量名=变量值…]
SET命令一次只能给一个局部变量赋值，SELECT命令可以一次性给多个局部变量赋值。
【例9.3】声明一个长度为6个字符的局部变量sno，并赋初值为'202001'。
DECLARE @sno nchar(6)
SET @sno=‘202001’ 或 SELECT @sno='202001'
SELECT命令也可以直接将查询的单值结果赋值给局部变量。
9.1.4 运算符
运算符是一种符号，用来指定在一个或多个表达式中执行的操作。SQL Server 2019为用户提供了丰富的运算符，主要包括算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串串联运算符、一元运算符等。
1．算术运算符：双目运算符，对两个表达式执行算术运算。
赋值运算符：等号（=），其为单目运算符。
位运算符：用来在整型数据或二进制数据（image类型除外）之间执行位操作，
是双目运算符。
4. 比较运算符：又称关系运算符，用来比较两个表达式之间的大小关系，也是双目运算符，通常用于构造表达式。
9.1.4 运算符
5．逻辑运算符：用来将多个逻辑表达式连接起来表达复杂的逻辑关系，运算结果同样返回布尔值（TRUE或FALSE）。
6. 字符串串联运算符：是双目运算符，用于将左右两侧的字符串串联起来形成新的字符串。
一元运算符：+、-、～，都为单目运算符，只对一个表达式进行运算。
需注意两点，一是运算对象的数据类型要与选用的运算符相匹配。例如进行算术运算时，参与数值运算的两个表达式必须是数值类型的数据或者能够进行算术运算的其他数据类型。二是当多个运算符参与运算时，要按照优先级先后执行，优先级相同时从左到右依次求值，还可以使用括号改变优先级。
9.1.5 流程控制语句
流程控制语句是指那些用来控制程序执行和流程分支的命令，流程控制语句主要用来控制SQL语句、语句块或存储过程的执行流程，例如条件控制语句、循环语句等。流程控制语句可以实现程序的结构性和逻辑性，从而帮助用户实现现实世界中较为复杂的逻辑关系。
SQL Server 2019提供以下6种流程控制语句：语句块、选择语句、循环语句、等待语句、转移语句和返回语句。
9.1.5 流程控制语句
1．语句块
语句块由BEGIN…END语句构成，能将多个T-SQL语句组合成一个程序块，并将它们视为一个单元来处理。BEGIN…END语句的语法格式如下所示，在使用时可将其理解为其他程序设计语言中的复合语句。
BEGIN

END
9.1.5 流程控制语句
2．选择语句
选择语句可以有3种表现形式，第一种形式是IF…ELSE语句，它的语法格式如下所示：
IF 条件表达式
语句块1
[ELSE
语句块2]
9.1.5 流程控制语句
2．选择语句
选择语句的第二种形式是IF…EXISTS语句，其语法格式如下所示：
IF [NOT] EXISTS (SELECT 子查询)
语句块1
[ELSE
语句块2]
9.1.5 流程控制语句
2．选择语句
选择语句的第三种形式是CASE语句，CASE语句不是独立语句，只用于允许使用表达式的位置。它又包括两种格式，语法格式如下所示。
格式一： 格式二：
CASE <表达式> CASE
WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式>
… …
WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式>
[ELSE <表达式>] [ELSE <表达式>]
END END
9.1.5 流程控制语句
3．循环语句
当程序中需要重复执行某项操作时，就需要使用循环语句WHILE…CONTINUE…BREAK。WHILE语句通过循环条件表达式来设定一个循环条件，当逻辑值为真时，重复执行一个语句块，否则退出循环，继续执行后续操作。其语法格式如下所示：
9.1.5 流程控制语句
3．循环语句
WHILE <循环条件表达式>
BEGIN
<语句块>
[BREAK]
…
[CONTINUE]
…
END
9.1.5 流程控制语句
4．等待语句
WAITFOR语句用于暂停执行SQL语句、程序块或存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。其语法格式如下所示：
WAITFOR { DELAY 'time' | TIME 'time'}
9.1.5 流程控制语句
5．转移语句
GOTO语句可以使程序直接跳转到指定的位置开始执行，GOTO语句和指定位置之间的程序段将直接跳过，不再执行。GOTO语句的语法形式相对简单，如下所示：
GOTO label
…
label:
9.1.5 流程控制语句
5．返回语句
返回语句是指RETURN语句，后跟整型值。其语法格式如下：
RETURN [integer_expression]
参数integer_expression为返回的整型值。RETURN语句用于无条件地终止一个查询、存储过程或者批处理，其后的语句不再执行。
9.1.6 常用函数
函数是用来执行一些特定功能并有返回值的一组T-SQL语句，每个函数都有一个名称，在名称之后有一对括号()，通常将这组T-SQL语句称为函数体。SQL Server 2019提供了两类函数：系统内置函数和用户自定义函数。
1．系统内置函数
常用的系统内置函数包括转换函数、字符串函数、日期函数、系统函数、数学函数等。由于这些函数是系统内置的，在使用时用户直接调用即可。
9.1.6 常用函数
2．用户自定义函数
SQL Server 2019不但提供了系统内置函数，还允许用户自定义函数，并将其纳入数据库对象中管理。用户自定义函数往往实现一个独立功能，方便用户重复调用。用户可以利用T-SQL命令来创建（CREATE FUNCTION）、修改（ALTER FUNCTION）和删除（DROP FUNCTION）用户自定义函数。根据函数返回值类型的不同，用户自定义函数可分为标量值函数和表值函数两大类。其中，标量值函数的返回值是单个数据值；表值函数又分为内联表值函数和多语句表值函数，它们均返回table类型的数据。
9.1.6 常用函数
2．用户自定义函数——标量值函数
使用T-SQL创建标量值函数的语法为：
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_expression
END
9.1.6 常用函数
2．用户自定义函数——标量值函数
相关参数的说明如下：
function_name：函数名，必须符合标识符规则，应见名知义，函数名后要加括号。
@parameter_name：用户自定义函数中的参数。可声明一个或多个参数，以@开头，必须符合标识符规则，多个参数间用逗点隔开。
parameter_data_type：参数的数据类型。
default：参数的默认值。如果定义了default值，则无须指定此参数的值即可执行函数。
READONLY：不能在函数定义中更新或修改参数。如果参数类型为用户定义的表类型，则应指定READONLY。
9.1.6 常用函数
2．用户自定义函数——标量值函数
return_data_type：用户自定义函数的返回值类型。对于T-SQL函数，可以使用除timestamp数据类型之外的所有数据类型。
WITH ENCRYPTION：数据库引擎会将CREATE FUNCTION语句的原始文本转换为模糊格式。模糊代码的输出在任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。
BEGIN和END之间定义函数体，该函数体中必须包括一条return语句，用于返回一个值。
function_body：指定一系列定义函数值的T-SQL语句。
scalar_expression：指定标量值函数返回的标量值。
【例9.11】自定义一个标量值函数，判断一个任意的三位数是否为水仙花数。如是，函数返回1，否则返回0，数值由用户通过参数传递给函数。
CREATE FUNCTION sxhs(@n,int)
RETURNS INT
AS
BEGIN
DECLARE @x INT,@y INT,@z INT,@m INT,@sign INT
SET @x=@n/100
SET @y= (@n-@x*100)/10
SET @z= @n%10
程序接下一页
【例9.11】自定义一个标量值函数，判断一个任意的三位数是否为水仙花数。如是，函数返回1，否则返回0，数值由用户通过参数传递给函数。
SET @m=@x*@x*@x+@y*@y*@y+@z*@z*@z
IF (@n=@m)
SET @sign=1
ELSE
SET @sign=0
RETURN @sign
END
9.1.6 常用函数
2．用户自定义函数——内联表值函数
使用T-SQL创建内联表值函数的语法为：
CREATE FUNCTION function_name
([{@parameter_name [as] parameter_data_type [=default] [READONLY]}[,…n] ] )
RETURNS TABLE
[WITH ENCRYPTION]
[AS]
RETURN (select statement)
9.1.6 常用函数
2．用户自定义函数——内联表值函数
内联表值函数中参数的用法与标量值函数相同。需要明确的是：内联表值函数没有函数体；与标量值函数不同的是，内联表值函数返回值是table类型数据，即是一个表，且表中的内容由RETURN子句中的select statement决定。
【例9.12】设计内联表值函数，查询teacher表中每位教师的姓名和职称。
CREATE FUNCTION t1()
RETURNS TABLE
AS
RETURN SELECT TNO,Prof FROM teacher
9.1.6 常用函数
2．用户自定义函数——多语句表值函数
多语句表值函数也称为多声明表值型函数，是标量值函数和内联表值函数的结合体。同内联表值函数一样，它的返回值也是一个表，但它和标量值函数一样有一个用BEGIN…END 语句括起来的函数体，返回值的表中数据是由函数体中的语句插入的，可进行多次查询。创建多语句表值函数的语法结构如下所示：
9.1.6 常用函数
2．用户自定义函数——多语句表值函数
CREATE FUNCTION function_name
([{@parameter_name [as] parameter_data_type [=default] [READONLY]}[,…n] ] )
RETURNS @return_variable TABLE < table_type_definition >
[WITH ENCRYPTION]
[AS]
BEGIN
function_body
RETURN
END
9.1.6 常用函数
2．用户自定义函数——多语句表值函数
多语句表值函数的语法格式中参数的使用与标量值函数和内联表值函数相同。需要特别说明的是：RETURNS @return_variable TABLE 指明函数返回的是table类型的局部变量，且紧随其后要对其进行表结构的定义。
在多语句表值函数的函数体中必须包括一条不带参数的RETURN语句用于返回表。

展开更多......

收起↑