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 标识符
- 变量用于临时存放数据，其中的数据随着程序的运行而变化，变量包括名称及数据类型两个属性。变量名用于标识该变量，数据类型确定了该变量存放值的格式及其允许的运算
- SQL Server 中的每一项对象均有一个作为标识用的名称，即 T-SQL 标识符
- 命名规则
- 常规标识符。以 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
总结 ：
- set 赋给局部变量的值通常是给定的，而 select 赋给局部变量的值则由查询获得
- case 语句两种形式的语法是:简单表达式和选择表达式
- 存储过程的优点如下
- 加快系统运行速度
- 封装复杂操作
- 实现模块化程序设计和代码重用
- 增强安全性
- 减少网络流量
