SQL Server高级查询与T-SQL编程 第4章 T-SQL编程

4.1T-SQL 常量和变量 

4.1.1 T-SQL 常量 

  • T-SQL 即 Transact-SQL,是 SQL 在 Microsoft SQL Server 上的增强版,为应用程序与 SQL Server 进行沟通的主要语言 
  • T-SQL 提供了标准 SQL 的 DDL 和 DML 功能,其延伸的函数、系统预定义程序以及程序设计结构(例如 if、while 等)让程序设计更有弹性 
  • 数字常量包裹整数常量、小数常量和浮点常量,例如:12,-37,200.35 
  • 浮点常量使用符号 e 来指定,例如:1.5e3,-3.14e1,2.5e-7 
  • 字符串常量包括在单引号内,它包含字母数字字符(a ~ z、A ~ Z 和 0 ~ 9)以及特殊字符,如感叹号(!)、at 符(@)和数字号(#),例如:’Football’ 
  • SQL 规定日期、时间和时间间隔的常量值被指定为日期和时间常量。例如:’2017-01-13′,’03/03/2016‘ 
  • SQL 包含几个特有的符号常量,这些常量代表不同的常用数据值。例如:CURRENT_DATE 表示当前的日期,类似的还有 CURRENT_TIME(当前时间)、CURRENT_TIMESTAMP(当前时间戳)等 

4.1.2 T-SQL 标识符 

  1. 变量用于临时存放数据,其中的数据随着程序的运行而变化,变量包括名称及数据类型两个属性。变量名用于标识该变量,数据类型确定了该变量存放值的格式及其允许的运算 
  2. SQL Server 中的每一项对象均有一个作为标识用的名称,即 T-SQL 标识符 
  3. 命名规则 
  • 常规标识符。以 ASCII 字母、Unicode 字母、下划线(_)、@ 或 # 开头,其后可跟一个或若干个 ASCII 字符、Unicode 字符、下划线(_)、美元符号($)、@ 或 #,但不能全为下划线(_)、@ 或 #。常规标识符不能是 T-SQL保留字 
  • 分隔标识符。包括在双引号(“”)或方括号([ ])内的常规标识符,或不符合常规标识符规则的标识符 

4.1.3 T-SQL 局部变量定义 

局部变量是作用域局限在一定范围内的 T-SQL 对象,局部变量是用户自定义的变量,其名称必须以 @ 开始,用于保存单个数据值。局部变量使用 declare 语句声明,所有局部变量在声明后均初始化为 null 

语法:declare {   @varaible_name  datatype [,…n] } 

@varaible_name 为局部变量名,并以 @ 开头。datatype 为该局部变量指定的数据类型 

示例:declare @e_mail  varchar(50) 

declare @lastname varchar(30),@firstname varchar(20),@tel varchar(30) 

4.1.4 使用 set 语句为局部变量赋值 

一个 set 语句仅能为一个变量赋值 

语法:set @varaible_name=expression 

示例:

在 T-SQL 中,go 语句的作用主要表现在以下两个方面 

  • 等待 go 语句前的代码执行完毕,再执行 go 之后的语句。如果需要连续执行多条 SQL 语句,并希望它们依次按顺序往下执行,则需要使用 go 进行控制 
  • T-SQL 在执行 go 语句时,SQL Server 的命令窗口和查询窗口将 go 之前已定义的局部变量清除,被 go分开的局部变量不能共享 

使用 T-SQL 输出“火锅”类商品的数量 

示例:

4.1.5 使用 select 语句为局部变量赋值 

一个 select 语句仅能为一个变量赋值 

语法:select @varaible_name=expression 

示例:select @areaID = 4   select @categoryID = 7 

使用子查询执行 select 赋值,如果子查询没有返回值,则将变量赋值为 null 

示例:

4.1.6 T-SQL 全局变量 

全局变量是 SQL Server 系统内部事先定义好的变量,不用用户参与定义,对用户而言,其作用范围并不局限于某一程序,而是任何程序均可随时调用

SQL Server 2018常见的全局变量

  • @@ERROR    :返回执行上一条 Transact-SQL 语句所返回的错误号 
  • @@IDENTITY:返回最后插入的标志值 
  • @@MAX_CONNECTIONS  :返回 SQL Server 实例所允许同时连接的最大用户数
  • @@ROWCOUNT :返回上一条语句影响的行数 
  • @@TRANCOUNT :返回当前连接的活动事务数 

4.2流程控制语句 

4.2.1 begin…end 语句 

T-SQL 的流程控制关键字包括:begin…end、if…else、while、case 等 

begin…end 语句可以将多个 SQL 语句限制在其中,作为一个逻辑执行块。begin…end 语句块应至少包含一条SQL 语句,否则将出错 

在流程控制语句中包含不止一条 SQL 语句时,需要使用 begin…end 语句,其类似于 C# 或 Java 语言的大括号“{  }”。begin…end 主要用于 if…else 语句、while 循环和 case 语句的执行体 

4.2.2 if…else 条件判断语句 

如果要对给定的条件进行判定,当条件为真或假时,分别执行不同的 T-SQL 语句,可用 if…else语句实现 

语法:if  logical_expression     expression1 [ else       expression2 ] 

示例:输入某客户的姓名,显示该客户的年龄,如果年龄小于 18,则会显示该客户“还未成年”的信息,否则会显示该客户“已经成年” 

示例:查询商品标题名中含有“KTV”字样的商品,如果有则显示该商品标题和团购价,否则显示“检索商品不存在” 

4.2.3 while 循环语句 

while 语句的功能是在满足条件的情况下,重复执行同样的语句 

语法:while logical_expression begin     expression       [break]       [continue] end 

编写 T-SQL,计算 1+2+3+……+10 的值 

示例:

示例:查询出“食品”类商品的最低团购价,如该价格低于 5 元,则将所有“食品”类商品的团购价上调 10%,再次查询出加价之后的“食品”类商品的最低团购价,如该价格仍低于 5 元,则将所有“食品”类商品的团购价再次上调 10%,依次反复,直至所有“食品”类商品的最低团购价高于 5 元 

4.2.4 case 选择语句 

由于 case 结构提供了比 if…else 结构更多的选择(switch)和判断的机会,所以使用 case 语句可以很方便地实现多重选择,从而避免了编写繁琐的多重 if…else 嵌套循环 

语法:case input_expression when when_expression then result_expression   [ …n]   [else  else_result_expression] end 

示例;显示所有商品类型信息 

示例:通过T-SQL编写分类语句 

case 选择语句选择表达式的语法如下:

语法:case when boolean_expression then result_expression     [ …n]       [else else_result_expression] end 

示例:输出每件商品的优惠程度。商品的优惠程度可以由商品的团购价与原价之间的比值反映,比值 =currentPrice/originalPrice,如果该比值小于 0.4(含),则显示“巨优惠”;如果在 0.4 ~ 0.6(含)之间,则显示“很优惠”;如果在 0.6 ~ 0.8(含)之间,则显示“一般优惠”;其他情况则显示“普通优惠”  

4.3系统存储过程和无参存储过程 

4.3.1 系统存储过程 

SQL Server 提供系统存储过程,它们是一组预编译的 T-SQL 语句 

所有系统存储过程均以“sp_”开始,系统存储过程均存放于系统数据库 master 中 

常见的系统存储过程 

  • sp_databases  :列出服务器上所有的数据库 
  • sp_helpdb :报告有关指定数据库或所有数据库的信息
  • sp_renamedb :更改数据库的名称
  • sp_tables  :返回当前环境下可查询的对象列表 
  • sp_columns :查看某个表的所有列信息
  • sp_help :查看某个表的所有信息 
  • sp_helpconstraint :查看某个表的约束
  • sp_helpindex:查看某个表的索引
  • sp_stored_procedures :列出当前环境中所有的存储过程 
  • sp_password :修改登录账号密码 

示例:使用系统存储过程 sp_database 列出服务器上所有的数据库 

4.3.2 存储过程特点 

SQL Server 中的 T-SQL 语言为了实现特定的任务,而将一些需要多次调用的固定操作编写为子程序,并集中以一个存储单元的形式存储在服务器上,由 SQL Server 数据库服务器通过子程序名来调用,这些子程序即存储过程 

存储过程是一种数据库对象,存储于数据库内,可由应用程序通过一个调用执行,具有较强的编程功能 

在 SQL Server 中使用存储过程,有下列几个方面的好处 

  • 加快系统运行速度
  • 封装复杂操作 
  • 实现模块化程序设计和代码重用
  • 增强安全性
  • 减少网络流量 

4.3.3 用户自定义存储过程 

用户可以调用系统存储过程,还可以创建自定义的存储过程。用户自定义存储过程是指用户根据自身需要,为完成某一特定功能,在用户数据库中创建的存储过程 

语法:create  proc[edure]  proc_name [{@parameter_name data_type}=[ 默认值 ]]   [output] , … , n] as procedure_body 

4.3.4 创建不带参数的存储过程 

创建一个名为 proc_Product_Info 的存储过程,其将获取所有商品的标题、类型名、团购价、地区名和商店名,按照类型和团购价升序显示 

示例:use lingju

go   

 if exists(select * from sysobjects where name=’proc_Product_Info’)   drop procedure proc_Product_Info go create procedure proc_Product_Info as select title, categoryName, currentPrice, areaName, shopName from product p, category c, area a, shop s where p.categoryID=c.categoryID and p.areaID=a.areaID and p.shopID=s.shopID order by categoryName, currentPrice 

为防止新创建的存储过程的名称出现重复, 需在创建之前查询系统表 sysobjects 中新建 存储过程名的存在性,如存在,则将其删除。 

示例:调用存储过程,可在 SQL Server Management Studio 的对象资源管理器的数据库“lingju” “可编程性” “存储过程”下看到新创建的存储过程 

exec proc_Product_Info 

示例:创建一个名为 proc_ProductStatistics 的存储过程,其将获取不同类型的商品个数和平均团购价 

create procedure proc_ProductStatistics as select categoryName 商品类型名 , count(p.productID) 商品数量 , avg(currentPrice) 平均团购价 from product p, category c where p.categoryID=c.categoryID group by categoryName order by 平均团购价 

exec proc_ProductStatistics 

4.4有参存储过程 

4.4.1 带输入参数存储过程 

SQL Server 的存储过程也可以带有参数。存储过程的参数个数可以是多个,且与参数的顺序无关。参数定义为:“参数名 数据类型” 

SQL Server存储过程中的参数分为输入类型和输出类型,参数类型默认为输入类型 

示例:创建一个名为 proc_ProStatisGivenCategory 的存储过程,其将获取指定类型的商品个数和平均团购价 

use lingju go if exists(select * from sysobjects where name=’proc_ProStatisGivenCategory’)   drop procedure proc_ProStatisGivenCategory go create procedure proc_ProdStatisGivenCategory (   @categoryName nvarchar(20)  ) as select categoryName 商品类型名 , count(p.productID) 商品数量 , avg(currentPrice) 平均团购价 from product p, category c where p.categoryID=c.categoryID and categoryName=@categoryName group by categoryName order by 平均团购价 

示例:创建一个名为 proc_OrdersGivenCustomer 的存储过程,其将获取指定客户在指定日期之后的订购信息,要求输出客户姓名、订单编号、下单日期、商品标题和团购价,按照订单编号和团购价升序显示。 

use lingju go if exists(select * from sysobjects where name=’proc_OrdersGivenCustomer’)   drop procedure proc_OrdersGivenCustomer go create procedure proc_OrdersGivenCustomer (   @customerName nvarchar(20),   @ordersDate date ) as select customerName 客户姓名 , o.ordersID 订单号 , ordersDate 下单日期 , title 商品标题 , currentPrice 团购价 From customer c, orders o, ordersDetail od, product p where c.customerID=o.customerID and o.ordersID=od.ordersID and od.productID=p.productID and c.customerName=@customerName and ordersDate>@ordersDate order by o.ordersID, currentPrice 

4.4.2 带输出参数存储过程 

如果需要存储过程返回一个或多个值,可通过使用输出参数来实现。输出参数必须在创建存储过程时,使用output 关键字进行声明。 

示例:use lingju go if exists(select * from sysobjects where name=’proc_MaxPriceGivenCategory’)   drop procedure proc_MaxPriceGivenCategory go create procedure proc_MaxPriceGivenCategory (   @categoryName nvarchar(20),   @maxPrice money output,   @productName nvarchar(20) output ) as select @maxprice=max(currentPrice) from product p, category c where p.categoryID=c.categoryID and categoryName=@categoryName select @productName=title from product where currentPrice=@maxPrice 

go declare @categoryName nvarchar(20) declare @maxPrice money declare @productName nvarchar(20) set @categoryName=’ 火锅 ‘              exec proc_MaxPriceGivenCategory @categoryName, @maxPrice output, @productName output print ‘ 团购价最贵的 ‘+@categoryName+’ 类商品是:’+@productName+’,价格是:’+convert(nvarchar(20),@maxPrice)+’ 元。’ 

创建一个名为 proc_NumsGivenCategory 的存储过程,其将获取指定类型商品的总订购数 

示例:use lingju go if exists(select * from sysobjects where name=’proc_NumsGivenCategory’)   drop procedure proc_NumsGivenCategory go create procedure proc_NumsGivenCategory (   @categoryName nvarchar(20),   @nums int output   ) as select @nums=sum(od.quantity) from ordersDetail od, product p, category c where od.productID=p.productID and p.categoryID=c.categoryID and categoryName=@categoryName 

总结 :

  1. set 赋给局部变量的值通常是给定的,而 select 赋给局部变量的值则由查询获得
  2. case 语句两种形式的语法是:简单表达式和选择表达式 
  3. 存储过程的优点如下 
  • 加快系统运行速度
  • 封装复杂操作
  • 实现模块化程序设计和代码重用
  • 增强安全性
  • 减少网络流量 

给TA打赏
共{{data.count}}人
人已打赏
SQL Server

SQL Server高级查询与T-SQL编程 第3章 子查询

2022-8-24 15:11:48

SQL Server

SQL Server高级查询与T-SQL编程 第5章 常见数据库对象

2022-8-24 17:55:35

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索