【教学课件】第6章Transact-SQL程序设计.ppt

《【教学课件】第6章Transact-SQL程序设计.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第6章Transact-SQL程序设计.ppt（40页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、信息技术系信息技术系第第6章章 Transact-SQL程序设计程序设计 n Transact-SQL语言是对标准SQL语言的扩充，是SQL Server中的编程语言，它提供了类似程序设计语言的基本功能，如变量、函数、流程控制语句等。n Transact-SQL最重要的用途是设计服务器端的能够在后台执行的程序块，如：存储过程、触发器等。信息技术系信息技术系第6章 Transact-SQL程序设计程序设计 n6.1 使用批处理、脚本和注释使用批处理、脚本和注释n6.2 变量变量n6.3 运算符运算符n6.4 函数函数n6.5 流程控制语句流程控制语句信息技术系信息技术系6.1 使用批处理、脚本和

2、注释使用批处理、脚本和注释批处理批处理批处理是一个或多个批处理是一个或多个T-SQL语句的集合。从应用语句的集合。从应用程序一次性地发送到程序一次性地发送到SQL Server服务器执行。服务器执行。SQL Server服务器将批处理编译成一个可执行单元服务器将批处理编译成一个可执行单元执执行计划。行计划。使用使用GO语句作为批处理的结束标记。语句作为批处理的结束标记。建立批处理时，应当注意几点建立批处理时，应当注意几点(P119)。信息技术系信息技术系6.1 使用批处理、脚本和注释使用批处理、脚本和注释 如果在一个批处理中出现任何如果在一个批处理中出现任何语法错误，则整个批处理语法错误，则整

3、个批处理都不能被执行。都不能被执行。例：例：create table table1(f1 int)create table table2(f1 it)goprint 5*7 -这是行注释这是行注释 第一个批处理整个都不能执行，而第二个批处理正常执第一个批处理整个都不能执行，而第二个批处理正常执行。行。信息技术系信息技术系6.1 使用批处理、脚本和注释使用批处理、脚本和注释如果在一个批处理中出现任何如果在一个批处理中出现任何执行错误执行错误,它仅影响该句的执行，它仅影响该句的执行，并不影响批处理中其他语句的执行并不影响批处理中其他语句的执行。例：例：insert 选修课成绩选修课成绩 value

4、s(099,001,99)-违反约束违反约束insert 选修课成绩选修课成绩 values(020310,001,99)go/*第一个批处理到这里结束；第一个批处理到这里结束；下面是第二个批处理；下面是第二个批处理；这是块注释这是块注释*/select*from 学生学生 除第一句外正常执行。除第一句外正常执行。信息技术系信息技术系6.1 使用批处理、脚本和注释使用批处理、脚本和注释脚脚 本本脚本是存储在文件中的一系列脚本是存储在文件中的一系列SQL语句。语句。脚本中包含一个或多个批处理。脚本中包含一个或多个批处理。脚本文件可以反复使用。脚本文件可以反复使用。查询分析器是建立、编辑和使用脚本

5、的一个最好的查询分析器是建立、编辑和使用脚本的一个最好的环境。在查询分析器中，可以新建、保存、打开、修改、环境。在查询分析器中，可以新建、保存、打开、修改、执行脚本文件。执行脚本文件。脚本文件扩展名脚本文件扩展名.SQL。信息技术系信息技术系6.2 变量变量变变量量是是一一种种语语言言中中必必不不可可少少的的组组成成部部分分。Transact-SQL语语言言中中有有两两种种形形式式的的变变量量，一一种种是是用用户户自自己己定定义义的的局局部部变变量量，另另外外一一种种是是系系统提供的统提供的全局变量全局变量。信息技术系信息技术系6.2 变量变量1.局部变量（局部变量（Local Variabl

6、e）用户定义的局部变量以一个用户定义的局部变量以一个开头开头，用于在，用于在T-SQL语句语句之间传递中间结果等。之间传递中间结果等。局部变量局部变量的的作用范围仅限制在声明作用范围仅限制在声明它们的批处理或存储过程或触发器中它们的批处理或存储过程或触发器中使用，一旦这些批处使用，一旦这些批处理或存储过程结束，局部变量将自行清除。理或存储过程结束，局部变量将自行清除。局部变量必须先用局部变量必须先用DECLARE语句声明后才可以使用。语句声明后才可以使用。信息技术系信息技术系6.2 变量变量局部变量的声明与赋值：局部变量的声明与赋值：例：例：DECLARE x varchar(10),y ch

7、ar(10)SET x=okSET y=123PRINT x+y例：例：DECLARE x char(6),y varchar(10)SELECT x=工号,y=工资 FROM 教师/*-注意：取注意：取SELECT语句返回的最后一个值语句返回的最后一个值*/PRINT x+y-注意：可以赋不同类型的数据注意：可以赋不同类型的数据信息技术系信息技术系6.2 变量变量局部变量的作用范围：局部变量的作用范围：仅限制在声明它们的批处理或存储过程或触发器中使用。仅限制在声明它们的批处理或存储过程或触发器中使用。例：例：DECLARE x varchar(10),y char(10)SET x=okSE

8、T y=123 GOPRINT x+y -注意：这里将出错注意：这里将出错信息技术系信息技术系6.2 变量变量 2.全局变量全局变量全局变量是全局变量是SQL Server系统提供并赋值的变量，在整个系统提供并赋值的变量，在整个SQL Server系统内使用，存储的通常是一些系统内使用，存储的通常是一些SQL Server的配置设定值和统计数据。的配置设定值和统计数据。在使用全局变量时应该注意以下几点：在使用全局变量时应该注意以下几点：用户不能建立全局变量，也不能修改全局变量。用户不能建立全局变量，也不能修改全局变量。引用全局变量时，必须以标记符引用全局变量时，必须以标记符“”开头。开头。局部

9、变量的名称不要与全局变量的名称相同局部变量的名称不要与全局变量的名称相同例：例：PRINT VERSION显示版本信息显示版本信息信息技术系信息技术系6.3 运算符运算符 运算符的优先等级从高到低如下所示：运算符的优先等级从高到低如下所示：括号：（）；括号：（）；乘、除、求模运算符：乘、除、求模运算符：*、/、%；加减运算符：加减运算符：+、-；注意：注意：+也是字符串连接运算符也是字符串连接运算符 比较运算符：比较运算符：=、=、=、!=、!、!1984/01/01declare x char(4)set x=123select ok+ltrim(345)+ok但但是是，无无法法由由SQL

10、Server自自动动转转换换的的或或者者是是SQL Server自自动动转转换换的的结结果果不不符符合合预预期期结结果果的的，就就需需要要使使用用转转换换函函数数显显式式地地进进行行转转换换。还还有有一一些些数数据据类类型型之之间间是是不不允允许许转换的。转换的。转换函数有两个：转换函数有两个：CAST 和和CONVERT。信息技术系信息技术系6.4 函数函数例：例：要查教师工资要查教师工资(输出样式如下输出样式如下)select 教师工资教师工资=工号工号+的工资是的工资是+工资工资+元元 from 教师教师信息技术系信息技术系6.4 函数函数格式：格式：CAST(表达式表达式 AS 数据类

11、型数据类型)例：例：select 教师工资教师工资=工号工号+的工资是的工资是+cast(工资工资 as char(4)+元元 from 教师教师格式：格式：CONVERT(数据类型，表达式数据类型，表达式)例：例：select 教师工资教师工资=工号工号+的工资是的工资是+convert(char(4),工资工资)+元元 from 教师教师信息技术系信息技术系6.4 函数函数一组SQL语句6.4.2 用户自定义函数用户自定义函数 （是数据库对象）是数据库对象）1 创建创建CREATE FUNCTION 标量标量函数函数名称名称（形式参数名称（形式参数名称 AS 数据类型）数据类型）RETUR

12、NS 返回数据类型返回数据类型ASBEGIN 函数体函数体 RETURN 表达式表达式END 调用用户自定义函数的基本语法为：调用用户自定义函数的基本语法为：用户名用户名.函数名称（实际参数列表）函数名称（实际参数列表）信息技术系信息技术系6.4 函数函数例例1 1：创建一个函数：创建一个函数,可以查询某个学号选修课的平均成绩可以查询某个学号选修课的平均成绩create function dbo.cj(xh char(6)returns intbegin declare x int select x=avg(成绩成绩)from 选修课成绩选修课成绩 where 学号学号=xh return x

13、endgo -create function 语句必须单独一个批处理语句必须单独一个批处理print dbo.cj(020301)信息技术系信息技术系6.4 函数函数例例2 2：创创建建一一个个用用户户定定义义函函数数，根根据据出出生生日日期期和和当当前前日日期期计计算年龄算年龄CREATE FUNCTION F4(birthday datetime,currentdate datetime)RETURNS INT BEGIN RETURN datediff(yy,birthday,currentdate)ENDGOselect 学号学号,年龄年龄=dbo.f4(出生日期出生日期,getdat

14、e()from 学学生生GO信息技术系信息技术系6.4 函数函数2 修改修改 ALTER FUNCTION 3 删除删除 DROP FUNCTION 所有者名称所有者名称函数名称函数名称在企业管理器中对用户定义函数的管理在企业管理器中对用户定义函数的管理信息技术系信息技术系6.5 流程控制语句流程控制语句 6.5.1 BEGINEND语句块语句块格式：格式：BEGIN 语句语句1 语句语句2 ENDBEGINEND语句块能够将多个语句块能够将多个Transact-SQL语句组合语句组合成一个语句块，并将它们视为一个单元处理。主要用在条成一个语句块，并将它们视为一个单元处理。主要用在条件语句和循

15、环等控制流程语句中。件语句和循环等控制流程语句中。BEGINEND语句块可语句块可以嵌套。必须成对使用。以嵌套。必须成对使用。信息技术系信息技术系6.5 流程控制语句流程控制语句 6.5.2 IFELSE语句语句 格式：格式：IF ELSE IFELSE语句可以嵌套语句可以嵌套信息技术系信息技术系6.5 流程控制语句流程控制语句 例例1：判断数值大小，打印运算结果：判断数值大小，打印运算结果declare x int,y int,z intselect x=1,y=2,z=3 -利用利用select赋值赋值if xy print xyelse if yz print yz else print

16、 z=y信息技术系信息技术系6.5 流程控制语句流程控制语句 例例2：在在教教学学管管理理的的学学生生表表中中查查询询外外语语成成绩绩，若若有有不不及及格格，显显示示其其信信息息，并并输输出出这这些些同同学学外外语语不不及及格格，需需要要补补考考，否则输出，否则输出外语全部通过外语全部通过use 教学管理教学管理if exists(select*from 学生学生 where 外语成绩外语成绩60)beginselect 学号学号,姓名姓名 from 学生学生 where 外语成绩外语成绩60print 这些同学外语不及格，需要补考这些同学外语不及格，需要补考 endelse print 外语

17、全部通过外语全部通过信息技术系信息技术系6.5 流程控制语句流程控制语句 例例3：在在商商品品管管理理库库中中，在在goods表表查查看看pen的的库库存存量量，若若小小于于30，则则该该商商品品库库存存量量并并显显示示需需要要进进货货，否否则则只只显显示示货货品名称、库存量品名称、库存量use 商品管理商品管理if(select 库存量库存量 from goods where 货品名称货品名称=pen)30 begin declare x int set x=(select 库存量库存量 from goods where 货品名称货品名称=pen)-注意注意:没有括号不行没有括号不行 pri

18、nt 该商品库存量为该商品库存量为+convert(varchar(5),x)+,需需要进货要进货 endelse select 货品名称货品名称,库存量库存量 from goods where 货品名称货品名称=pen信息技术系信息技术系另一写法：另一写法：declare x intselect x=库存量库存量 from goods where 货品名称货品名称=penif x30 select 该商品库存量为该商品库存量为+str(x,2)+需要进货需要进货else select 货品名称货品名称,库存量库存量 from goods where 货品名称货品名称=pencast(x as

19、 varchar)信息技术系信息技术系6.5 流程控制语句流程控制语句6.5.3 CASE表达式表达式 CASE表达式用来简化表达式用来简化SQL表达式，可以用在任何允表达式，可以用在任何允许使用表达式的地方并根据条件的不同而返回不同的许使用表达式的地方并根据条件的不同而返回不同的值。值。CASE表达式不能够单独执行。表达式不能够单独执行。CASE表达式有两种：表达式有两种：简单简单CASE表达式表达式搜索搜索CASE表达式表达式信息技术系信息技术系6.5 流程控制语句流程控制语句CASEEND必须成对使用必须成对使用(1)简单简单CASE表达式表达式 格式：格式：CASE WHEN THEN

20、 WHEN THEN ELSE END 注意：注意：1）测试表达式必须与测试值的数据类型相同）测试表达式必须与测试值的数据类型相同 2）测试表达式可以使局部变量、字段变量）测试表达式可以使局部变量、字段变量 3）若同时有多个测试值与表达式值相同，则只执行）若同时有多个测试值与表达式值相同，则只执行第一个第一个匹配的测试值后的表达式。匹配的测试值后的表达式。信息技术系信息技术系6.5 流程控制语句流程控制语句简单简单Case例例1：判断警务管理数据库中，案件记录简表中的判断警务管理数据库中，案件记录简表中的“案件状态案件状态”字段。若为字段。若为1，则显示，则显示“已结案已结案”，0则显示则显示

21、“未结案未结案”use 警务管理警务管理select 案件号案件号,是否结案是否结案=case 案件状态案件状态when 1 then 已结案已结案else 未结案未结案 end from 案件记录简表案件记录简表信息技术系信息技术系6.5 流程控制语句流程控制语句简单简单Case例例2：根据案件记录表中的根据案件记录表中的“案件类别案件类别”字段，显示字段，显示每个案件的案件号和汉字的类别。每个案件的案件号和汉字的类别。1凶杀；凶杀；2抢劫；抢劫；3盗盗窃；窃；4诈骗诈骗select 案件号案件号,案件类别案件类别=case 案件类别案件类别when 1 then 凶杀凶杀 when 2 t

22、hen 抢劫抢劫 when 3 then 盗窃盗窃 when 4 then 诈骗诈骗end from 案件记录简表案件记录简表信息技术系信息技术系6.5 流程控制语句流程控制语句(2)搜索搜索CASE表达式表达式格式：格式：CASE WHEN THEN WHEN THEN ELSE END注意：注意：1）case关键字后不跟任何表达式关键字后不跟任何表达式 2）when后的条件表达式结果为逻辑后的条件表达式结果为逻辑T或或F 3）若有多个比较结果为）若有多个比较结果为T，则只返回第一个为，则只返回第一个为T的结的结果表达式果表达式信息技术系信息技术系6.5 流程控制语句流程控制语句例例1：根据

23、学生表中的外语成绩，显示教学管理中学生：根据学生表中的外语成绩，显示教学管理中学生的学号、姓名、外语及格否和外语成绩的学号、姓名、外语及格否和外语成绩use 教学管理教学管理select 学号学号,姓名姓名,外语及格否外语及格否=casewhen 外语成绩外语成绩=60 then 是是else 否否 end,外语成绩外语成绩 from 学生学生信息技术系信息技术系6.5 流程控制语句流程控制语句要注意先后顺序！要注意先后顺序！例例2：根据外语成绩，显示外语等级（优、良、中、及格、：根据外语成绩，显示外语等级（优、良、中、及格、不及格）不及格）use 教学管理教学管理select 学号学号,姓名

24、姓名,外语等级外语等级=case when 外语成绩外语成绩=90 then 优优when 外语成绩外语成绩=80 then 良良when 外语成绩外语成绩=70 then 中中when 外语成绩外语成绩=60 then 及格及格else 不及格不及格 end,外语成绩外语成绩 from 学生学生信息技术系信息技术系6.5 流程控制语句流程控制语句6.5.4 WAITFOR语句语句 格式：格式：WAITFOR DELAY time|TIME timeDELAY指出一段时间间隔后开始一个动作；指出一段时间间隔后开始一个动作；TIME指定一个时刻到来时开始一个动作；指定一个时刻到来时开始一个动作；

25、time必须是必须是hh:mm:ss的格式。的格式。例：例：如下语句在如下语句在4秒钟之后打印出秒钟之后打印出OK1字样：字样：WAITFOR DELAY 00:00:04 PRINT OK1如下语句在如下语句在17:02:35时打印出时打印出OK2字样：字样：WAITFOR TIME 17:02:35 PRINT OK2信息技术系信息技术系6.5 流程控制语句流程控制语句6.5.5 WHILE语句语句 WHILE语语句句通通过过条条件件表表达达式式来来设设置置一一个个循循环环条条件件，当当满满足足条条件件时时，重重复复执执行行一一个个语语句句或或语语句句块块，直直到到条条件件不不满满足足时时

26、退出循环，继续执行后面的语句。退出循环，继续执行后面的语句。格式：格式：WHILE BEGIN BREAK CONTINUE ENDWhile语句可以语句可以嵌套嵌套信息技术系信息技术系6.5 流程控制语句流程控制语句例：显示例：显示100以内所有能被以内所有能被7整除的整数整除的整数declare x intset x=1while x=60000 break else set i=i+1 endprint 共有共有+cast(e as varchar)+个个declare i int,e intset i=0set e=0while 1=1 begin set i=i+1 if i60000 break if i%7=0 set e=e+1 continue endprint 共有共有+cast(e as varchar)+个个P139例例6.19