资源简介

(共29张PPT)
第7章 Transact-SQL编程
目录
◎本章目标
■掌握变量、函数的使用方法
■掌握流程控制语句
■掌握用户自定义函数的定义、修改和调用
◎本章内容
■7.1 批处理
■7.2 注释
■7.3 常量和变量
■7.4 运算符和表达式
■7.5 函数
■7.6 流程控制语句
■7.7 用户自定义函数
7.1 批处理
批处理是从客户机传递给服务器的一组完整的数据和SQL指令集合。但如果一个批处理中存在一个语法错误，那么所有的语句都将无法通过编译,都不会被执行。
【例7-1】在“学生选课”数据库中创建一个视图“View_教师”，从“教师”表中查询职称为教授的教师信息。
USE 学生选课
GO
CREATE VIEW View_教师
AS
SELECT * FROM 教师 /*查询语句*/
WHERE 职称='教授'
GO
SELECT * FROM View_教师
以上命令共3个批处理，用两个GO分开
7.2 注释
注释是程序代码中不被执行的文本字符串，用于对代码进行说明或暂时用来进行诊断的部分语句。
在SQL Server 2008系统中，支持两种注释方式
双连字符（--）注释方式
正斜杠星号字符（/*…*/）注释方式。
USE 学生选课
GO
--查看教师基本信息
SELECT * FROM 教师
/*
按照职称查询
*/
SELECT * FROM 教师 WHERE 职称='讲师'
7.3 常量和变量
7.3.1 常量
常量，也称为文字值或标量值，是指程序运行中值不变的量，用于表示特定数据值的符号，根据代表的数据类型不同，值也就不同，可以是日期型、数值型、字符串型等。
常量类型 常量表示说明
字符串常量 包括在单引号内，由字母（a-z、A-Z）、数字字符（0-9）、汉字以及特殊字符（如!、@和#)等组成
二进制常量 只有0或者1构成的数字串，并且不使用单引号。
十进制整型常量 使用不带小数点的十进制数据表示
十六进制整型常量 使用前缀0X后跟十六进制数字串表示
日期常量 使用单引号将日期时间型量括起来组成
实型常量 用定点和浮点两种方式表示的数字，如：13.5，1.3E+5
货币常量 以前缀为货币符号的数字串来表示
7.3 常量和变量
7.3.2 局部变量
局部变量是作用域局限在一定范围内的Transact-SQL对象。在SQL Server中，局部变量是用户自定义的，可以保存单个特定类型数据值对象。
【例7-3】将局部变量hello声明为char类型，长度为20，并为其赋值为“hello,world!”
DECLARE @hello char(20)
SET @hello='hello,world!'
7.3 常量和变量
7.3.3 全局变量
全局变量是SQL Server系统提供并赋值的变量。用户不能建立全局变量，也不能用SET语句来修改全局变量的值。通常将全局变量的值赋给局部变量，以便保存和处理。全局变量以两个@符号开头。
常用的全局变量
7.3 常量和变量
7.3.3 全局变量
【例7-4】检查UPDATE语句中的错误并给出错误提示，且给出UPDATE语句影响的行数。
使用全局变量@@ERROR返回上一条语句发生错误的错误号，使用@@ROWCOUNT返回上一条语句影响的行数。
USE 学生选课
GO
UPDATE 选课 SET 课程号=5,成绩=88
WHERE 学号='10102004'
IF @@ERROR=547
PRINT '错误：违反CHECK约束！'
IF @@ROWCOUNT=0
PRINT '警告：没有数据更新！'
GO
7.4 运算符和表达式
7.4.1 运算符
运算符是一种符号，用来指定表达式中执行的操作。SQL Server 2008中提供了七种运算符。
1．算术运算符
2．赋值运算符
3．字符串连接运算符
4．位运算符
5．一元运算符
6．比较运算符
7．逻辑运算符
7.4 运算符和表达式
7.4.2 表达式
表达式是指常量、变量、函数、字段等对象，应用运算符组合起来而形成的式子。
运算符的优先级
7.5 函数
函数对于任何程序设计语言都是非常关键的组成部分。函数有返回值，返回值的类型取决于所使用的函数。一般来说，允许使用变量、字段或表达式的地方都可以使用函数。
7.5.1 字符串函数
【例7-5】在“学生选课”数据库中，使用字符串函数输出姓“王”的教师的相关信息。
SELECT RIGHT(工号,2) '工号',姓名,ASCII(性别) '性别',LEN(姓名) '长度',
'聘为'+SPACE(1)+LTRIM(职称) '职称' FROM 教师
WHERE SUBSTRING(姓名,1,1)='王'
7.5 函数
7.5.2 日期函数
【例7-6】根据服务器当前的系统日期与时间，给出系统当前的月份和月份的名字
对应的SQL语句如下：
SELECT GETDATE() 当前日期和时间,DATEPART(YEAR,GETDATE()) 年,
DATENAME(YEAR,GETDATE())年名, DATEPART(MONTH,GETDATE()) 月,
DATENAME(MONTH,GETDATE())月份名, DATEPART(DAY,GETDATE()) 日
7.5 函数
7.5.3 数学函数
【例7-7】使用CEILING()、FLOOR()、 ROUND()、RAND()、SIGN()、PI()函数，显示返回值。
SELECT CEILING(12.345) 最小整数,
FLOOR(12.345) 最大整数,
ROUND(12.345,2) 近似数,RAND() 随机数,
SIGN(12.345) 符号,PI() 圆周率
GO
7.5 函数
7.5.4 系统函数
在对两种不同类型的数据进行运算时，需要将它们转换成同一类型。SQL Server会自动处理某些数据类型的转换，这种转换被称为隐式转换。但是，无法由SQL Server自动转换或由SQL Server自动转换的结果不符合预期时，就需要使用转换函数进行强制转换。转换函数有两个：CAST和CONVERT。
1．CAST 函数
语法格式为：
CAST(expression AS data_type[(length)])
2．CONVERT函数
语法格式为：
CONVERT (data_type[(length)],expression[,style])
7.6 流程控制语句
在程序设计语言中，需要使用一些语句组织形式来控制程序的运行。这些程序设计语言的基本结构分为顺序结构、条件分支结构和循环结构。
7.6.1 BEGIN…END语句块
BEGIN…END可以定义Transact-SQL语句块，这些语句块作为一组语句执行，且允许语句块嵌套；关键字BEGIN定义Transact-SQL语句块的起始位置，END标识同一块Transact-SQL语句的结尾。BEGIN…END语法格式为：
BEGIN
{
sql_statement︱statement_block
}
END
7.6 流程控制语句
7.6.2 IF-ELSE语句
条件分支语句，对IF后给定的条件进行判断，如果条件真，则执行条件表达式后面的Transact-SQL语句。当条件为假时，则执行ELSE关键字后面的Transact-SQL语句。
IF-ELSE语法格式为：
IF Boolean_expression
{sql_statement|statement_block}
ELSE
{sql_statement|statement_block}
7.6 流程控制语句
7.6.2 IF-ELSE语句
【例7-9】查询“学生”表，若其中存在学号为“11101004”的学生，就显示“已经存在学号为11101004的学生”，并输出该学生的所有信息，否则插入此学生信息。
IF EXISTS (SELECT * FROM 学生 WHERE 学号='11101004')
BEGIN
PRINT '已经存在学号为11101004的学生'
SELECT * FROM 学生 WHERE 学号='11101004'
END
ELSE
INSERT 学生(学号,姓名,性别,出生日期,班级)
VALUES('11101004','张三',0, '1993-8-23','电子商务')
7.6 流程控制语句
7.6.3 CASE表达式
CASE表达式可根据其中布尔表达式的真假来确定返回某个值,可在允许使用表达式的任何位置使用这一表达式。使用CASE语句可以进行多个分支的选择。
CASE具有两种格式：
1．简单CASE表达式
简单CASE表达式的语法格式如下：
CASE 测试表达式
WHEN 测试值1 THEN 结果表达式1
WHEN 测试值2 THEN 结果表达式2
…
WHEN 测试值n THEN 结果表达式n
[ELSE 结果表达式m]
END
【例7-10】判断“选课”表中课程号的值，如果为“1”返回“文学欣赏”，如果为“2”返回“中国历史文化”，如果为“3”返回“视频编辑”，如果为“4”返回“音乐欣赏”，否则返回未知课程。
SELECT 学号,课程名=CASE 课程号
WHEN 1 THEN '文学欣赏'
WHEN 2 THEN '中国历史文化'
WHEN 3 THEN '视频编辑'
WHEN 4 THEN '音乐欣赏'
ELSE '未知课程'
END,成绩
FROM 选课
GO
7.6 流程控制语句
7.6.3 CASE表达式
2．搜索CASE表达式
搜索CASE表达式的语法如下：
CASE
WHEN 条件表达式1 THEN 结果表达式1
WHEN 条件表达式2 THEN 结果表达式2
…
WHEN 条件表达式n THEN 结果表达式n
[ELSE 结果表达式m]
END
【例7-12】根据选课表中的成绩输出对应的分数等级。
USE 学生选课
GO
SELECT 成绩=CASE
WHEN 成绩>=90 and 成绩<=100 THEN '优秀'
WHEN 成绩>=80 and 成绩<90 THEN '良好'
WHEN 成绩>=70 and 成绩<80 THEN '中等'
WHEN 成绩>=60 and 成绩<70 THEN '及格'
ELSE '不及格'
END
FROM 选课
GO
7.6 流程控制语句
7.6.4 WAITFOR 语句
WAITFOR语句可以将它之后的语句在一个指定的间隔之后执行，或在将来的某一指定时间执行。
WAITFOR {DELAY time | TIME time}
【例7-13】等待10s，再查询输出学生的信息。
WAITFOR DELAY '00:00:10'
SELECT * FROM 学生
【例7-14】在下午8:00，查询输出学生的信息。
WAITFOR TIME '20:00:00'
SELECT * FROM 学生
7.6 流程控制语句
7.6.5 WHILE语句
循环结构控制语句，由循环控制条件和循环体组成。当循环控制条件为真时，重复执行循环体中的语句。当循环控制条件为假时，跳出循环，执行循环后面的语句。可以在循环体内设置BREAK和CONTINUE关键字，以便控制循环语句的执行过程。
语法格式如下：
WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK]
{sql_statement |statement_block}
[CONTINUE]
{sql_statement | statement_block}
【例7-15】利用BREAK 和CONTINUE语句求1~100之间小于50的奇数之和。
DECLARE @sum int
DECLARE @i int
SET @i=0
SET @sum=0
WHILE @i<=100
BEGIN
SET @i=@i+1
/*判断是否为偶数，如果是， 使用CONTINUE结束本次循环*/
IF((@i%2)=0)
CONTINUE
SET @sum=@sum+@i
/*判断@i是否已经超过50，如果是，使用BREAK结束本循环*/
IF(@i>50)
BREAK
END
PRINT '1-50中奇数和为：'+CONVERT(CHAR(6),@sum)
7.7 用户自定义函数
7.7.1用户自定义函数的创建
SQL SERVER 2008可以创建用户自定义函数。创建用户自定义函数的语法如下：
CREATE FUNCTION 函数名称
（@形式参数名称 AS 数据类型）
RETURNS 返回数据类型
[AS]
BEGIN
函数内容
RETURN 表达式
END
调用用户自定义函数的基本语法为：
SET @变量 = 用户名.函数名称(实际参数列表)
7.7 用户自定义函数
1. 用户自定义标量函数
2. 自定义函数的执行方法
用户定义函数的执行方法有两种：
第一种：通过SET执行函数，并获取返回值；
SET @变量 = dbo.用户自定义函数(输入参数)
第二种：通过Select语句执行函数，并获取返回值；
SELECT @变量 = dbo.用户自定义函数(输入参数)
3. 用户自定义内嵌表值函数
4. 用户自定义多语句表值型函数
【例7-16】建立自定义标量函数，输入课程号，返回该课程的平均成绩。
CREATE FUNCTION averc(@cno varchar(12))
RETURNS float
AS
BEGIN
DECLARE @aver float
SELECT @aver =(SELECT AVG(成绩) FROM 选课 WHERE 课程号=@cno
GROUP BY 课程号)
RETURN @aver
END
GO
DECLARE @jg float
SELECT @jg = dbo.averc(1)
PRINT @jg
7.7 用户自定义函数
7.7.2 用户自定义函数的修改和删除
1．修改用户自定义函数
ALTER FUNCTION 函数名称
（形式参数名称 AS 数据类型）
RETURNS 返回数据类型
BEGIN
函数内容
RETURN 表达式
END
2．删除用户自定义函数
DROP FUNCTION { [ owner_name ] function_name } [，n ]
本章小结
T-SQL中的数据有常量和变量两种基本形式。根据作用范围不同，变量分为用户定义的局部变量和系统提供的全局变量。局部变量在使用之前要先定义。
SQL Server 2008提供了七类运算符。通过运算符将常量、变量、函数连接起来，构成表达式。
SQL Server 2008提供了分支（IF-ELSE）、循环（WHILE）等流程控制语句以及CASE-END表达式。可以通过这些语句，编写复杂的T-SQL程序。
SQL Server 2008提供了大量的系统函数，可供用户直接调用。用户也可以自定义函数。根据返回值的不同，用户自定义函数可以分为标量函数和表值函数。

展开更多......

收起↑