《数据库应用-数据库原理与SQL Server》课件第5章

第5章程序设计

5.1基本语句5.2流程控制语句5.3游标小结习题

5.1基本语句

5.1.1定义批处理语句

批处理就是一个或多个SQL语句的集合，从程序一次性发送到SQLServer2005并由SQLServer2005编译为一个可执行单元，一次性执行。如果一个批处理中任何一条语句有语法错误，则整个批处理将不能编译和执行。有关批处理的相关内容参见9.1节。5.1.2变量

1.局部变量

1)局部变量的声明

SQL中用DECLARE语句声明局部变量，并在声明后将局部变量的值初始化为NULL。

DECLARE语句的基本语法格式为：

DECLATE@<局部变量名><数据类型>[,…n]

实例5-1

声明一个datetime类型的局部变量。

DECLARE@date_vardatetime

实例5-2

声明两个局部变量。

DECLARE@var1int,@var2money

2)局部变量的赋值

局部变量声明后，局部变量的值被初始化为NULL。可以用SET语句或SELECT语句为局部变量赋值。

局部变量赋值语句的基本语法格式为：

SET | SELECT@<局部变量名>=<表达式>

表达式可以是任何有效的SQL表达式。系统将计算表达式的值，并赋给指定的局部变量。实例5-3

用SET语句和SELECT语句给局部变量赋值。

DECLARE@var1datetime,@var2char(10)

SET@var1=getdate()

SELECT@var2=convert(char(10),@var1,102)

实例5-4

用SET语句将查询的结果赋给局部变量，并用SELECT语句显示局部变量的值。

在查询编辑器中输入SQL语句并执行，如图5-1所示。图5-1用SET语句将查询的结果赋给局部变量

2.全局变量

全局变量是SQLServer系统内部使用的变量，以@@开头。在SQLServer2005中，将全局变量改称为函数，并保持这些函数和前期版本中的全局变量具有相同的名称。由于这些函数不需要任何参数，因此也称为无参函数，调用时无需在函数名后加一对小括号。SQLServer2005无参函数见表5-1。实例5-5

用全局变量查看SQLServer的版本、当前所使用的SQLServer服务器的名称以及所使用的服务名称等信息。

在查询编辑器中输入SQL语句并执行，如图5-2所示。图5-2全局变量实例5.1.3注释语句

注释语句是对程序代码的说明或暂时禁用，是程序代码中不编译执行的语句。

注释语句的基本语法格式为：

--<注释文本>

或

/*

<注释文本>

*/5.1.4输出语句

当需要查看程序结果时，可以使用输出语句。

输出语句的基本语法格式为：

PRINT<表达式>

实例5-6

输出变量的值。

在查询编辑器中输入SQL语句并执行，如图5-3所示。图5-3输出变量的值5.1.5定义语句块语句

SQLServer2005是以SQL语句为基本逻辑单位的。如果在控制流程中，需要执行两条或两条以上的语句，应该将这些语句定义为一个语句块(称为复合语句)，控制流程将视为一个单元执行。

定义语句块语句的基本语法格式为：

BEGIN

<SQL语句>|<语句块>

END

5.2流程控制语句

5.2.1选择结构

选择结构可以使用条件语句来实现。

条件语句的基本语法格式为：

 IF<逻辑表达式>

<SQL语句>|<语句块>

[ELSE

<SQL语句>|<语句块>]

条件语句的执行流程是：当条件满足，也就是逻辑表达式的值为真时，执行IF后面的语句或语句块。ELSE语句为可选项，它引入另一个语句或语句块，当逻辑表达式的值为假时，执行该语句或语句块。实例5-7

查找学号为1001的学生。

在查询编辑器中输入SQL语句并执行，如图5-4所示。图5-4查找学号为1001的学生实例5-8

条件语句的嵌套。

在查询编辑器中输入SQL语句并执行，如图5-5所示。图5-5条件语句的嵌套5.2.2循环结构

循环结构可以使用循环语句来实现。循环语句根据所指定的条件重复执行一个语句或语句块，只要条件成立，循环体将被重复执行下去。

循环语句的基本语法格式为：

WHILE<逻辑表达式>

<SQL语句>|<语句块>

循环语句还可以与BREAK语句和CONTINUE语句一起使用。BREAK语句导致程序从循环体中跳出，而CONTINUE语句则使程序跳过循环体内CONTINUE语句后面的语句，而立即进行下次条件测试。中断语句的基本语法格式为：

BREAK

短路语句的基本语法格式为：

CONTINUE

实例5-9

求1～10之间的奇数和。

在查询编辑器中输入SQL语句并执行，如图5-6所示。图5-6求1～10之间的奇数和实例5-10

求100～200之间的全部素数。

设计：所谓素数，就是除1和自身外不能被其他任何整数整除的整数。按数论来讲，若一个整数m不能被2～sqrt(m)整除，则m为素数。

本程序由双重循环构成，外循环m从101取到199，内循环判断m是否为素数。由于偶数不可能是素数，所以m每次加2。内循环i从2取到sqrt(m)，判断如果m能被i整除即m不是素数，结束循环，否则i加1后继续判断。内循环结束后，判断i值如果大于sqrt(m)，则循环为正常结束，意味着m不能被所有的2到sqrt(m)整除，即m为素数。

在查询编辑器中输入SQL程序并执行，如图5-7所示。图5-7求100～200之间的全部素数5.2.3转移语句

转移语句的基本语法格式为：

GOTO<标号>

转移语句将程序的执行流程无条件转移到指定的标号处。GOTO语句和标号可用在存储过程、批处理或语句块中。定义标号时，应在标号名后面加上冒号。GOTO语句常用在循环语句和条件语句内，使程序跳出循环，或有条件转移。

实例5-11

利用转移语句和条件语句求10的阶乘。

在查询编辑器中输入SQL语句并执行，如图5-8所示。图5-8求10的阶乘5.2.4等待语句

等待语句挂起一个程序中语句的执行，直到指定的某一时间点到来或在一定的时间间断之后才继续执行。

等待语句的基本语法格式为：

WAITFORDELAY‘<时间间隔>’ | TIME‘<时间>’

其中，时间间隔以及时间均为datetime类型，但不包括日期，其格式为“hh:mm:ss”，分别说明等待的时间长度和时间点。

实例5-12

设置等待一小时后执行查询。

在查询编辑器中输入SQL语句并执行，如图5-9所示。图5-9设置等待一小时后执行查询实例5-13

设置到十点整执行查询。

在查询编辑器中输入SQL语句并执行，如图5-10所示。图5-10设置到十点整执行查询5.2.5返回语句

返回语句结束程序的执行，使程序无条件返回到上一个调用点，其后面的语句不再执行。

返回语句的基本语法格式为：

RETURN[<整数表达式>]

可以使用RETURN语句向调用者返回一整数值。如果没有指定返回值，返回值为0时表示程序成功执行，返回值为负数时表示不同的出错原因。

5.3游标

5.3.1游标概述

1.游标的概念

在数据库中，游标是个十分重要的概念，游标提供了一种对检索得到的数据集进行操作的更加灵活的手段。就本质而言，游标实际上是一种能从包括多条记录的结果集中每次提取一条记录的机制。游标总是与一条SQL查询语句相关联，因为游标由结果集(可以是零条、一条或由相关的查询语句检索出的多条记录)和结果集中指向特定记录的游标位置组成，所以当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

2.游标的种类

SQLServer2005支持三种类型的游标：Transact-SQL游标、API游标和客户游标。

由于API游标和T-SQL游标使用在服务器端，因此被称为服务器游标，也被称为后台游标，而客户端游标也被称为前台游标。5.3.2使用游标

应用程序对游标的操作过程可分为以下几个步骤：

(1)声明游标(变量)；

(2)打开游标；

(3)从一个游标中提取信息；

(4)关闭(释放)游标。

1.声明游标

通常使用DECLARE语句来声明一个游标，声明一个游标主要包括以下主要内容：

(1)游标名字；

(2)数据来源(表和列)；

(3)选取条件；

(4)属性(仅读或可修改)。

声明游标语句的基本语法格式为：

DECLARE<游标名>[INSENSITIVE][SCROLL]CURSOR

FOR<SELECT语句>

[FOR{READONLY|UPDATE[OF<列名>[,...n]]}]实例5-14

声明标准游标。

在查询编辑器中输入SQL语句并执行，如图5-11所示。图5-11声明标准游标实例5-15

声明只读游标。

在查询编辑器中输入SQL语句并执行，如图5-12所示。图5-12声明只读游标实例5-16

声明更新游标。

在查询编辑器中输入SQL语句并执行，如图5-13所示。图5-13声明更新游标

2.声明游标变量

声明游标后，除了可以使用游标名引用游标外，还可以使用游标变量来引用游标。

声明游标变量语句的基本语法格式为：

DECLARE@<变量名>CURSOR

声明游标变量后，必须与某个游标相关联才能实现游标操作。有两种方法能够建立游标变量与游标之间的关联。

3.打开游标

声明游标后，如果要从游标中读取数据，必须打开游标。

打开游标语句的基本语法格式为：

OPEN[GLOBAL]<游标名>|<游标变量名>

在打开游标时，如果游标声明语句中使用了INSENSITIVE保留字，则OPEN产生一个临时表来存放结果集。

在游标被成功打开之后，全局变量@@CURSOR_ROWS将用来记录游标内数据行数，该变量存储的是在调用@@CURSOR_ROWS时，游标从基础表读入的数据行。@@CURSOR_ROWS返回值含义如表5-2所示。

4.读取游标中的数据

当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。

读取游标语句的基本语法格式为：

FETCH

[[NEXT|PRIOR|FIRST|LAST

|ABSOLUTE{n|@nvar}

|RELATIVE{n|@nvar}]

FROM]

{{[GLOBAL]<游标名>}|<@游标变量>}

[INTO@<变量名>[,...n]]全局变量@@FETCH_STATUS返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，来决定如何进行下一步处理。@@FETCH_STATUS变量的返回值含义如表5-3所示。

5.关闭游标

(1)使用CLOSE命令关闭游标。在处理完游标中的数据之后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。CLOSE语句可以关闭游标，但不释放游标的数据结构。如果准备在随后的使用中再次使用游标结果集，可以使用OPEN命令重新打开游标。

关闭游标语句的基本语法格式为：

CLOSE[GLOBAL]<游标名>|@<游标变量>

(2)自动关闭游标。游标可应用在存储过程、触发器和脚本中，如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。

6.释放游标

当CLOSE命令关闭游标时，并没有释放游标占用的数据结构。如果要释放游标占用的数据结构，应使用DEALLOCATE命令。DEALLOCATE命令删除游标与游标名或游标变量之间的关联。如果一个游标名称或游标变量是最后一次引用游标的，则将释放游标，游标使用的任何资源也随之释放。对于加在基础表上的锁来说，用于保护提取隔离的锁被释放，而用于保护更新(包括通过游标进行的定位更新)的锁一直到事务结束才释放。

释放游标语句的基本语法格式为：

DEALLOCATE[GLOBAL]<游标名>|@<游标变量>实例5-17DEALLOCATE的作用。

在查询编辑器中输入下列SQL语句并执行，结果如图

5-14所示。

USEstudent

GO

--创建一个全局游标，游标名称为abc，在该批处理之外也可用

DECLAREabcCURSORGLOBALSCROLLFOR

SELECT*FROMs

OPENabc

GO

--声明游标变量@MyCrsrRef1，与游标关联

DECLARE@MyCrsrRef1CURSOR

SET@MyCrsrRef1=abc--用DEALLOCATE释放游标变量@MyCrsrRef1和游标的关联

DEALLOCATE@MyCrsrRef1

--此时游标还存在，还可以从中提取数据

FETCHNEXTFROMabc

GO

--重新声明一个游标变量@MyCrsrRef2，与游标关联

DECLARE@MyCrsrRef2CURSOR

SET@MyCrsrRef2=abc

--用DEALLOCATE释放游标名称abc和游标的关联

DEALLOCATEabc

--此时游标还存在，还可以从中提取数据

--注意此时对该游标的引用，只能通过游标变量@MyCrsrRef2

FETCHNEXTFROM@MyCrsrRef2

--当该批处理结束，游标变量@MyCrsrRef2被释放

--对游标的最后一个引用被释放，游标即被释放

GO图5-14DEALLOCATE实例5.3.3游标应用实例

在SQLServer2005中，UPDATE语句和DELETE语句也支持游标操作，可以通过游标修改或删除游标基表中的当前数据。当然，只读游标是不支持这样的操作的。

用于游标时，UPDATE语句的基本语法格式为：

UPDATE<表名>

SET<列名>={<表达式>|NULL|<select子句>}[,…n]

WHERECURRENTOF<游标名>

DELETE语句的基本语法格式为：

DELETEFROM<表名>

WHERECURRENTOF<游标名>实例5-18

游标的定位修改。

在查询编辑器中输入下列SQL语句并执行，结果如图

5-15所示。

--声明游标

DECLAREcur_scSCROLLCURSOR

FOR

SELECT*FROMsc

FORUPDATEOFscore

--打开游标

OPENcur_sc

--提取数据

FETCHFROMcur_sc

--修改当前行score列

UPDATEsc

SETscore=100

WHERECURRENTOFcur_sc

--关闭释放游标

CLOSEcur_sc

DEALLOCATECUR_SC

GO图5-15游标定位修改实例5-19

游标的定位删除。

在查询编辑器中输入下列SQL语句并执行，结果如图

5-16所示。

--声明游标

DECLAREcur_scSCROLLCURSOR

FOR

SELECT*FROMsc

FORUPDATEOFscore

--打开游标

OPENcur_sc

--提取数据

FETCHFROMcur_sc

--删除当前行数据

DELETEFROMsc

WHERECURRENTOFcur_sc

--关闭释放游标

CLOSEcur_sc

DEALLOCATECUR_SC

GO图5-16游标定位删除实例5-20

将课程号为“c001”的课程成绩上浮5%。

在查询编辑器中输入下列SQL语句并执行，结果如图5-17所示。

USEstudent

--声明变量

DECLARE@cnoCHAR(4),@scoreSMALLINT

--声明游标

DECL