数据库——SQL Server过程化SQL

一、引入

过程化SQL是对基本SQL的扩展，在基本SQL上增加了一些过程控制语句，过程化SQL程序基本结构是块，由定义部分和执行部分组成如下图，块与块之间可以相互嵌套。

注明：内容参考王珊老师《数据库系统概论》第六版

二、定义部分

表达式：常指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子

1、变量

T-SQL的变量分为局部变量和全局变量。

1.1局部变量

        由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。 局部变量必须先声明，后使用，变量名必须以@开头。

①局部变量声明

以下是两种声明变量的方式：

DECLARE 变量名 数据类型 [[NOT NULL]:=初值表达式]
/*表达式常指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子。*/

DECLARE 变量名 数据类型 [[NOT NULL] 初值表达式]

 示例：定义一个长度为50的字符串变量，其中as可省略

/*定义一个长度为50的字符串变量 */         
DECLARE @sname nvarchar(50）

②局部变量赋值

T-SQL还为局部变量提供了赋值语句，可以通过select、set语句为变量赋值。    

/*select赋值*/
select @sname='黎明'

/*set、select赋值*/
set @id = 1
select @name = name from student where id = @id;

1.2全局变量

由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。 可以通过访问全局变量来了解系统目前的一些状态信息。 全局变量名以@@开头。例如：

WHILE @@FETCH_STATUS=0  
/*表示：游标读取下一条数据是否成功*/

常用全局变量及解释如下表：

2、常量

常量声明如下

DECLARE 数据类型 CONSTANT:=常量表达式

注意：常量必须赋一个值，且在定义期间或作用域内不得更改

3、函数

        函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句，处理结果成为“返回值”，处理过程成为“函数体”。函数又分为系统内置函数和用户自定义函数。

4、游标

在过程化SQL中，如果SELECT语句只返回一条记录，可以将结果存放到变量中，如果返回多条记录，需要使用游标对结果集处理，一个游标与一个SQL语句相关联。

4.1游标使用过程

声明游标-打开游标-使用游标-关闭游标-删除游标

/*声明游标，不执行里面的SELECT语句
DECLARE 游标名称[(参数1 数据类型,参数2 数据类型,...)] CURSOR FOR SELECT语句;*/
--示例
declare sc_cursor  CURSOR FOR SELECT sno,scgrade FROM sc where cid=@cid; 


/*打开游标，执行上述SELECT语句，查询结果放入缓冲区，游标处于活动状态，指针指向查询结果集第一条记录
OPEN 游标名称[(参数1 数据类型,参数2 数据类型,...)];*/
--示例
open sc_cursor

/*使用游标
FETCH 
[ [NEXT | PRIOR | FIRST | LAST  | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar }] FROM]
{ { [GLOBAL ] 游标名称 } | 游标变量名}
[INTO @变量1 [,变量2...n ]]
*/
--示例，NEXT FROM可省略
FETCH NEXT FROM sc_cursor INTO @sno, @scgrade 


--循环检索查询结果集中的数据,@@fetch_status 对应不同值的意思：0 FETCH 语句成功，-1 FETCH 语句失败或此行不在结果集中，-2 被提取的行不存在

WHILE @@fetch_status = 0 

BEGIN   --执行的逻辑代码开始标志

    --------

    --使用游标，缺省默认值NEXT，检索下一行数据

    FETCH sc_cursor INTO @sno, @scgrade

END    --执行代码结束标志

--关闭游标：CLOSE 游标名称  
CLOSE sc_cursor

--删除游标：DEALLOCATE 游标名称
DEALLOCATE sc_cursor

4.2游标使用参数说明

参考 SQL Server基础之游标_三天不学习的博客-CSDN博客

游标使用参数详解
参数	参数说明
NEXT（游标指针指向）	紧跟当前行返回结果行，并且当前行递增为返回行，如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。
PRIOR（游标指针指向）	返回紧邻当前行前面的结果行，并且当前行递减为返回行，如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
FIRST（游标指针指向）	返回游标中的第一行并将其作为当前行。
LAST（游标指针指向）	返回游标中的最后一行并将其作为当前行。
ABSOLUTE { n | @nvar } （游标指针指向）	如果n或@nvar为正，则返回从游标头开始向后n行的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾开始向前的n行的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.
RELATIVE { n | @nvar } （游标指针指向）	如果n或@nvar为正，则返回从当前行开始向后的第n行。如果n或@nvar为负，则返回从当前行开始向前的第n行。如果n或@nvar为0，则返回当前行,对游标第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE,则不返回行，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.
GLOBAL	指定cursor_name是全局游标。
cursor_name （游标名称）	如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。
@cursor_variable_name	游标变量名，引用要从中进行提取操作的打开的游标。
INTO @variable_name [ ,...n ]	允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所支持的隐士转换。变量的数目必须与游标选择列表中的列数一致。

5、异常

如果过程化SQL在执行时出现异常，则应根据异常类型执行异常处理语句，可参考如下博客，详解了异常的声明及类型和相关处理方法。

sql server 报异常语句 sql数据库异常处理_mob6454cc6c1f4a的技术博客_51CTO博客

三、执行部分

执行部分包括异常处理和流程控制语句，异常处理参考上述博客。在SQL SERVER程序中可以使用批处理和流程控制语句来实现相应的功能。

3.1批处理

• 包含一个或多个 Transact-SQL 语句的组，从应用程序一次性地发送到 SQL Server 执行，可以减少系统开销，能够提高程序的执行效率。
• 批处理是使用GO语句将多条SQL语句进行分隔，其中每两个GO之间的SQL语句就是一个批处理单元。    
•  批处理是作为一个逻辑单元的T-SQL语句。如果一条语句不能通过语法分析，那么不会运行任何语句。如果一条语句在运行时失败，那么产生错误的语句之前的语句都已经运行了。

3.2流程控制语句

用来控制SQL语句、语句块或者存储过程的执行流程 。

①BEGIN…END

--语句 语法为格式：      
--在BEGIN…END之间的所有语句看作一个执行单位，可以嵌套使用        
BEGIN               
 { SQL语句或语句组 }                
END  

②条件控制语句 IF…ELSE

/*条件控制语句允许嵌套使用。语法为：
IF 条件表达式  { SQL语句或语句组}                          
[ELSE  { SQL语句或语句组 } ]             
*/   
                     
--例：
USE scoreDB
GO
IF (SELECT AVG(Grade) FROM SC )>80
    PRINT '考试成绩优秀'
ELSE IF (SELECT AVG(Grade) FROM SC )>60
	PRINT '考试成绩合格'
ELSE 
	PRINT ‘考试成绩不合格'

③循环控制语句WHILE

条件表达式为真时进行循环； BREAK跳出当前循环执行END后面的语句； COTINUE可以使程序跳过CONTINUE后面的语句，重新回到WHILE循环的第一行命令。

--语法为：
WHILE  { 条件表达式 }
BEGIN
    {SQL语句或语句块}
    BREAK              --可以没有
    {SQL语句或语句块}
    CONTINUE           --可以没有
    {SQL语句或语句块}
END

示例：用WHILE循环语句计算100以内的所有整数的和

--用WHILE循环语句计算100以内的所有整数的和。
DECLARE @sum_num int,@i int
SELECT @sum_num=0,@i=1
WHILE @i<100
   BEGIN
     SET @sum_num=@sum_num+@i
     SET @i=@i+1
   END
PRINT '100以内的整数求和为：'
PRINT @sum_num 

另外，在第二节第四部分的游标中用到了FOR循环控制语句，可查看用法 

④CASE语句

--CASE语句语法为：
CASE  { 输入表达式 }
WHEN {值表达式1} THEN {结果表达式1}
……
WHEN {值表达式n} THEN {结果表达式n}
ELSE {结果表达式}     --除上述情况外其他情况的处理办法
END

示例

SELECT Sno, Cno,
CASE 
	WHEN Grade >= 90 THEN '优秀'
	WHEN Grade >= 80 THEN '良好'
	WHEN Grade >= 70 THEN '中等'
	WHEN Grade >= 60 THEN '及格'
	ELSE '不及格'  
END AS 等级
FROM SC

运行结果如图，AS给新列取别名"等级"

⑤RETURN语句 

格式：          

RETURN [ 整数值 ]

该语句无条件终止刚进行的查询，终止存储过程、批处理。默认情况下，没有错误返回0值，有错误返回非0值。用户也可以指定返回值。