情景7Transact–SQL程序设计.ppt

《情景7Transact–SQL程序设计.ppt》由会员分享，可在线阅读，更多相关《情景7Transact–SQL程序设计.ppt（74页珍藏版）》请在三一办公上搜索。

1、情景7 TransactSQL程序设计,SQL Server数据库技术及应用,目录,任务1 掌握Transact-SQL程序设计基础知识 任务2 掌握流程控制语句 任务3 掌握游标的使用,任务1 Transact-SQL程序设计基础知识,在SQL Server数据库应用中，经常会出现一些比较复杂的业务数据处理，比如进行复杂的数据查询和统计工作等，需要编写一些SQL程序来完成这些复杂的工作。SQL程序是面向过程的语言与SQL的结合，可以进行复杂的数据处理。此外，为了减少数据库服务器与数据库应用程序之间传输的数据量，也需要设计开发一些SQL程序。,7.1 批处理、块定义语句及注释、输出语句,7.1

2、.1 批处理定义批处理是同时从应用程序发送到 SQL Server 并得以执行的一组单条或多条 SQL 语句。SQL Server 将批处理的语句编译为单个可执行单元，称为执行计划。执行计划中的语句每次执行一条。批处理的种类较多，例如存储过程、触发器、函数内的所有语句都可构成批处理,7.1.1 批处理定义,1使用批处理的优点 减少数据库服务器有客户端之间的数据传输次数，消除过多的网络流量。减少数据库服务器有客户端之间传输的数据量。缩短完成逻辑任务所需的时间，提高代码的可复用度。,7.1.1 批处理定义,2批处理的执行 批处理以 GO 命令作为结束信号，当前批处理语句由上一 GO 命令后输入的所

3、有语句组成，如果是第一条 GO 命令，则由即席会话或脚本开始后输入的所有语句组成。注意:GO 命令和 SQL 语句不能在同一行中，但在 GO 命令行中可包含注释。,7.1.1 批处理定义,批处理在执行过程中发生运行错误:（1）大多数运行时错误将停止执行批处理中当前语句和它之后的语句。（2）某些运行时错误（如违反约束）仅停止执行当前语句。而继续执行批处理中其他所有语句。,在遇到运行时错误的语句之前执行的语句不受影响。唯一例外的情况是批处理位于事务中并且错误导致事务回滚。在这种情况下，所有在运行时错误之前执行的未提交数据修改都将回滚。,7.1.1 批处理定义,【例7-1】不能建立执行计划的批处理。

4、批处理中存在语法错误，不能建立执行计划，其中 master 是 SQL Server 自带的数据库。use master CREATE TABLE TestBatch(a int);INSERT INTO TestBatch VALUES(1);INSERT INTO TestBatch VALUES(2);INSERT INTO TestBatch VALUUS(3);/*语法错误，VALUES 拼写错误*/SELECT*FROM TestBatch;GO,7.1.1 批处理定义,【例7-2】批处理执行过程中发生的运行时错误。下面的示例没有语法错误，可以建立执行计划。在执行过程中，由于第 3

5、 个 INSERT 语句产生主键重复的错误，因此该语句停止执行，继续执行第 4 个 INSERT 语句。SELECT 语句返回三个行，如图7-1所示。,图7-1,7.1.1 批处理定义,3编写批处理的规则不能在同一个批处理中更改表，然后引用新列。不能在定义一个 CHECK 约束后，立即在同一个批处理中使用该约束。CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句，在一个批处理中只能提交一个。批处理必须以 CREATE 语句开始。所有跟在该批处理后的其他语句将被解释

6、为第一个 CREATE 语句定义的一部分。如果 EXECUTE 语句（执行存储过程的语句）是批处理中的第一句，则不需要 EXECUTE 关键字。如果 EXECUTE 语句不是批处理中的第一条语句，则需要 EXECUTE 关键字。,7.1.2 块定义,在 BEGINEND 关键字之间封装一系列的 SQL 语句，就形成了一个语句块，从而可以执行一组 SQL 语句。BEGINEND 语句块允许嵌套。BEGINEND 的语法结构如下：BEGIN SQL 语句1 SQL 语句2 END,7.1.2 块定义,【例7-3】块语句定义。下面的示例中如果没有包括 BEGINEND 块，IF 语句在条件为“真”时

7、仅执行 ROLLBACK TRANSACTION 语句，而 PRINT 语句不会被执行，不会返回打印信息。运行结果如图7-2所示。,图7-2,7.1.3 注释、输出语句,1注释注释是程序代码中不执行的文本字符串。注释可用于对代码进行解释说明或暂时禁用正在进行诊断的部分 SQL 语句。SQL Server 支持两种类型的注释字符：（1）-（双连字符）。这些注释字符可与要执行的代码处在同一行，也可另起一行。（2）/*.*/（正斜杠-星号字符对）。这些注释字符可与要执行的代码处在同一行，也可另起一行，甚至可以在可执行代码内部。,【例7-4】注释字符的使用。-Choose the AdventureW

8、orks database.USE AdventureWorks;GO-Choose all columns and all rows from the Address table.SELECT*FROM Person.AddressORDER BY PostalCode ASC;/*We do not have to specify ASC because that is the default.*/GO,7.1.3 注释、输出语句,2输出语句PRINT 语句用于将消息返回到应用程序，采用字符或 Unicode 字符串表达式作为参数，并将字符串作为消息返回到应用程序。PRINT 语句接受任何

9、字符串表达式，包括字符或 Unicode 类型的常量和局部变量名，或返回字符或 Unicode 字符串的函数，或由两个或多个常量、局部变量或函数串联生成的复杂字符串。【例7-3】中使用了 PRINT 语句实现打印文本字符串信息。,7.1.3 注释、输出语句,7.2 Transact-SQL语言的变量,变量是指在程序运行过程中，其值可以发生变化的量，通常用来保存程序运行过程中的输入数据、计算获得的中间结果和最终结果。变量有名字和数据类型两个属性。变量名用于标识该变量，以便被引用，变量的数据类型（请参考情景4）确定了该变量存放数据的格式以及允许的运算。,7.2 Transact-SQL语言的变量,

10、1变量变量名必须是一个合法的标识符（有效的字符序列）。（1）标识符。在 SQL Server 中标识符分为以下两类。常规标识符。以 ASCII 字母、Unicode 字母、下划线(_)、at 符号()或数字符号(#)开头，后续可跟一个或若干个 ASCII 字符、Unicode 字符、at 符号、美元符号($)、数字符号或下划线。分隔标识符。包含在双引号()或者方括号()内的常规标识符或不符合常规标识符规则的标识符。,（2）变量的分类。SQL Server 中变量可以分为以下两类。全局变量。全局变量由系统提供且预先声明，通过在名称前加两个at 符号()区别于局部变量。局部变量。局部变量用于保存单

11、个特定类型的数据值。当首字母为“”时，表示该标识符为局部变量名；当首字母为“#”时，表示该为一个临时数据库对象名，若开头含一个“#”，表示局部临时数据库对象名，若开头含两个“#”，表示全局临时数据库对象名。,7.2 Transact-SQL语言的变量,2局部变量的使用（1）局部变量的定义。在批处理或程序中用 DECLARE 语句声明局部变量，声明后其值设置为 NULL。语法格式：DECLARE local_variable AS data_type,.n,7.2 Transact-SQL语言的变量,参数说明：local_variable：局部变量名，应为合法常规标识符。前面的“”表示局部变量。

12、data_type：数据类型，用于定义局部变量的类型，可为系统类型或自定义类型。n：表示可以定义多个变量，各变量之间用“，”号分开。,7.2 Transact-SQL语言的变量,（2）局部变量的赋值。用 SET 语句赋值。将先前使用 DECLARE local_variable 语句创建的指定局部变量设置为指定值。在初始化多个变量时，为每个局部变量使用单独的 SET 语句。语法格式：SET local_variable=expression 参数说明：local_variable：除 cursor、text、ntext、image 或 table 以外的任何类型变量的名称。变量名称必须以 at

13、 符号()开头。变量名称必须符合标识符规则。expression：任何有效的 SQL Server 表达式。,7.2 Transact-SQL语言的变量,【例7-5】创建 myvar 变量，将字符串值放入变量，然后输出 myvar 变量的值。DECLARE myvar char(20);SET myvar=中华人民共和国;SELECT myvar;GO,7.2 Transact-SQL语言的变量,【例7-6】创建一个名为 SEX 的局部变量，并在 SELECT 语句中使用该局部变量查找表 Student_info 中所有男同学的学号和姓名。,用 SELECT 语句赋值。语法格式：SELECT

14、local_variable=expression，,.n参数说明：local_variable：除 cursor、text、ntext、image 或 table 以外的任何类型变量的名称。变量名称必须以 at 符号()开头。expression：任何有效的 SQL Server 表达式，包括标量子查询。n：表示可以给多个变量赋值，各变量之间用“，”号分开。,7.2 Transact-SQL语言的变量,注意：local_variable通常用于将单个值返回到变量中。如果expression为列名，则返回多个值，此时将返回的最后一个赋值给变量；如果 SELECT 语句没有返回行，变量将保留当前

15、值；如果 expression 是不返回值的标量子查询，则将变量设为 NULL；包含变量赋值的 SELECT 语句不能也用于执行通常的结果集检索操作。,【例7-7】在以下示例中，为变量 var1 赋值 刘翔。由于 Student_info 表中不存在为 SID 指定的值，因此对该表的查询不返回任何行。变量的值仍为 刘翔。USE student;GO DECLARE var1 nvarchar(30);SELECT var1=刘翔;SELECT var1=SName FROM Student_info WHERE Sid=08060111;SELECT var1 AS 姓名;,7.2 Trans

16、act-SQL语言的变量,【例7-8】在以下示例中，使用了一个子查询为 var1 赋值。由于为 SID 请求的值不存在，因此子查询不返回值，并将变量设为 NULL。USE student;GO DECLARE var1 nvarchar(30)SELECT var1=Generic Name SELECT var1=(SELECT Sname FROM Student_info WHERE Sid=08060111)SELECT var1 AS 姓名;,7.2 Transact-SQL语言的变量,任务2 掌握流程控制语句,开发设计 SQL 程序时，常常要使用流程控制语句来改变计算机的执行流程以

17、满足程序设计的需要，实现较复杂的功能。SQL Server 提供的流程控制语句如表所示。,7.3 条件语句和循环语句,7.3.1 条件语句 在程序中如果要对给定的条件进行判定时，当条件为 TRUE（真）或 FALSE（假）时分别执行不同的 T-SQL 语句。1IFELSE语句 IFELSE 语句为 Transact-SQL 语句的执行强加条件。只有条件满足，才能执行条件之后的语句或语句块，否则执行 ELSE 关键字之后的语句或语句块。其中 ELSE 关键字与其后面的语句块是可选的。,语法格式：IF Boolean_expression sql_statement|statement_block

18、 ELSE sql_statement|statement_block 参数说明：Boolean_expression：返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句，则必须用括号将 SELECT 语句括起来。sql_statement|statement_block：任何 T-SQL 语句或用语句块定义的语句分组。除非使用语句块，否则 IF 或 ELSE 条件只能执行一条 T-SQL 语句。若要定义语句块，请使用块定义关键字 BEGIN 和 END。,7.3.1 条件语句,从上述语法格式，可看出 IF 语句分带 ELSE 部分和不带 ELSE 部分两种使用

19、形式:,（1）带 ELSE 部分的 IF 语句：IF A/*T-SQL 语句或语句块*/ELSE B/*T-SQL 语句或语句块*/当条件表达式的值为真时执行 A，然后执行 IF 语句的下一语句；当条件表达式的值为假时执行 B，然后执行 IF 语句的下一语句。,（2）不带 ELSE 部分的 IF 语句：IF A/*T-SQL 语句或语句块*/当条件表达式的值为真时执行 A，然后执行 IF 语句的下一语句；当条件表达式的值为假时直接执行 IF 语句的下一语句。,7.3.1 条件语句,【例7-9】如果“计算机基础”的平均成绩高于80分，则显示“计算机基础课程平均成绩高于80分”，否则显示“计算机基

20、础课程平均成绩低于80分”。use student declare val char(30)set val=计算机基础课程平均成绩高于80分.if(select AVG(Grade)from Student_info,Course_info,SC where Student_info.Sid=SC.Sid and Course_info.Cid=SC.Cid and Course_info.Cname=计算机基础)80 select 计算机基础课程平均成绩低于80分.else select val,7.3.1 条件语句,【例7-10】IF.ELSE 语句的嵌套使用。use student de

21、clare val floatselect val=AVG(Grade)from Student_info,Course_info,SC where Student_info.Sid=SC.Sid and Course_info.Cid=SC.Cid and Course_info.Cname=计算机基础if val80 select 计算机基础课程平均成绩高于分.else if val80 select 计算机基础课程平均成绩低于分.else select 计算机基础课程平均成绩等于分.;,2多分支 CASE 表达式CASE 表达式用于多条件分支选择。CASE 具有两种格式：（1）简单 CA

22、SE 函数将某个表达式与一组简单表达式进行比较以确定结果。（2）CASE 搜索函数计算一组布尔表达式以确定结果。两种格式都支持可选的 ELSE 参数。,7.3.1 条件语句,语法格式：Simple CASE function:CASE input_expression WHEN when_expression THEN result_expression.n ELSE else_result_expression Searched CASE function:CASEWHEN Boolean_expression THEN result_expression.n ELSE else_resul

23、t_expression END,7.3.1 条件语句,参数说明：input_expression：使用简单 CASE 格式时所计算的表达式，可以是任意有效的表达式。WHEN Boolean_expression：使用 CASE 搜索格式时所计算的布尔表达式，可以是任意有效的布尔表达式。,7.3.1 条件语句,【例7-11】使用简单 CASE 函数更改课程编号的显示。USE student;GOSELECT Sid,cid=CASE Cid WHEN 101 THEN 计算机基础 WHEN 102 THEN 数据库技术 WHEN 103 THEN 网页设计与制作 END,Grade as 成绩

24、FROM sc,7.3.1 条件语句,【例7-12】使用带有 CASE 搜索函数的查询语句，搜索函数允许根据比较值在结果集内对值进行替换。USE student;GOSELECT sid,cid,Grade=CASE WHEN Grade 60 THEN 不及格 WHEN Grade 70 THEN 及格 WHEN Grade 80 THEN 中等 WHEN Grade=100 THEN 优秀 ENDFROM sc,7.3.1 条件语句,7.3.2 循环语句,如果需要重复执行程序中的一部分语句，可以使用 WHILE 循环语句实现，只要条件成立，语句或者语句块就会一直重复执行下去。可以使用 BR

25、EAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。语法格式：WHILE Boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE sql_statement|statement_block,参数说明：Boolean_expression：返回 TRUE 或 FALSE 的条件表达式。如果布尔表达式中含有 SELECT 语句，则必须用括号将 SELECT 语句括起来。sql_statement|statement_block：T-SQL

26、 语句或用语句块定义的语句分组。若要定义语句块，请使用控制流关键字 BEGIN 和 END。BREAK：使程序从最内层的 WHILE 循环中退出。将执行出现在 END 关键字（循环结束的标记）后面的任何语句。CONTINUE：使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。,7.3.2 循环语句,WHILE 循环语句的执行流程图如图7-6所示。从 WHILE 循环执行流程图可以看出其使用形式如下：WHILE 条件表达式 循环体语句/*T-SQL 语句或者语句块*/当条件表达式值为真时，执行循环体语句，然后，在进行条件的判断，重复上述操作，直到条件表达式的值为假，

27、退出循环体的执行，继续执行 WHILE 语句的下一语句,图7-6 WHILE 循环语句的执行流程,7.3.2 循环语句,【例7-13】编写代码计算并显示 n=1+2+3+.+100。DECLARE counter int,n int SET counter=1set n=0WHILE counter=100 BEGIN SET n=n+counter SET counter=counter+1 ENDSELECT n=1+2+3+.+20=+CONVERT(char(4),n),7.3.2 循环语句,【例7-14】编写代码计算并显示1100之间的所有完全平方数。例如，81=92，则称81为完全

28、平方数。DECLARE n int SET n=0WHILE(n100)BREAK else begin print n*n continue print 这是第+convert(char(2),n)+次循环 end end,7.3.2 循环语句,注意：本例用到了BREAK 语句和 CONTINUE 语句。BREAK 语句一般用在循环语句中，用于退出本层循环。当程序中有多层循环嵌套时，使用 BREAK 语句只能退出其所在的这一层循环。CONTINUE 语句一般用在循环语句中，重新开始 WHILE 循环。在 CONTINUE 关键字之后的任何语句都将被忽略，重新转到下一次循环条件的判断。,7.3

29、.2 循环语句,7.4 转移、等待、返回语句,1无条件转移（GOTO）语句 GOTO语句将程序执行流程无条件转移到 GOTO之后的标签处，跳过后面的 T-SQL 语句，并从标签位置继续处理。GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。GOTO 语句还可以嵌套使用。语法格式：label:T-SQL语句/*定义标签*/GOTO label/*改变执行流程*/参数说明：label：如果 GOTO 语句指向该标签，则其为处理的起点。标签必须符合标识符规则。无论是否使用 GOTO 语句，标签均可作为注释方法使用。,【例7-15】采用 GOTO 语句的方法实现例7-13。DECLARE

30、counter int,n int SET counter=1set n=0 BEGIN LABEL1:SET n=n+counter SET counter=counter+1 IF(counter100)GOTO LABEL2 ELSE GOTO LABEL1 ENDLABEL2:SELECT n=1+2+3+.+20=+CONVERT(char(4),n),7.4 转移、等待、返回语句,2等待（WAITFOR）语句 WAITFOR 语句指定触发器语句块、存储过程或事务执行的时间、时间间隔或事件。即在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或

31、事务。语法格式：WAITFOR DELAY time_to_pass|TIME time_to_execute 参数说明：DELAY time_to_pass：用于指定 SQL Server 必须等待的时间，最长可为 24 小时。可以使用 datetime 数据可接受的格式之一指定 time_to_pass，也可以将其指定为局部变量，不能指定日期。因此，不允许指定 datetime 值的日期部分。TIME time_to_execute：指定 SQL Server 等待到某一时刻。,7.4 转移、等待、返回语句,【例7-16】WAITFOR 语句使用。以下示例在晚上 10:20(22:20)执

32、行存储过程 sp_add_job。USE msdb;BEGIN WAITFOR TIME 22:20;EXECUTE sp_add_job job_name=TestJob;END;GO以下示例在两小时的延迟后执行存储过程。BEGIN WAITFOR DELAY 02:00;EXECUTE sp_helpdb;END;GO,7.4 转移、等待、返回语句,3返回（RETURN）语句用于从过程、批处理或语句块中无条件退出，不执行位于 RETURN 之后的语句。语法格式：RETURN integer_expression 参数说明：integer_expression：将要返回的整型表达式的值。存储

33、过程可以给调用过程或应用程序返回整型值。,7.4 转移、等待、返回语句,【例7-17】以下示例显示如果在执行 findstudent 时没有指定用户名作为参数，则 RETURN 将使过程向用户屏幕发送一条消息后退出。如果指定了用户名，则将从相应的系统表中检索此用户在当前数据库创建的所有对象名。CREATE PROCEDURE findstudent name char(30)AS IF name is NULL BEGIN PRINT You must give a student name RETURN/*无条件返回*/ENDELSE BEGIN SELECT sid,sname,sbirt

34、h,saddr WHERE sname=name END;,7.4 转移、等待、返回语句,任务3 掌握游标的使用,关系数据库中的操作会对整个行集起作用。由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。,7.5 游标的概念和应用,1游标的概念游标（CURSOR）是结果集的逻辑扩展，可以看作是指向结果集的一个指针，通过使用游标，应用程序可以逐行访问并处理结

35、果集。使用游标时，应先声明，然后打开，接着使用。使用完后关闭、释放资源。,2声明游标声明游标可以通过 DECLARE CURSOR 语句完成，该语句包括两种格式:（1）ISO 标准的语法（SQL-92）。语法格式：DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY|UPDATE OF column_name,.n,7.5 游标的概念和应用,参数说明：cursor_name：是所定义的 Transact-SQL 服务器游标的名称。cursor_name 必须符合标识符规则。INSENSI

36、TIVE：系统将创建供所定义的游标使用的数据的临时复本，对游标的所有请求都从 tempdb 中的该临时表中得到应答。因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。如果省略 INSENSITIVE，则已提交的（任何用户）对基础表的删除和更新则会反映在后面的提取操作中。SCROLL：说明所声明的游标可以前滚、后滚。可使用所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。如果省略 SCROLL，则 只能使用 NEXT提取选项。,7.5 游标的概念和应用,select_statement：定义游标结果集的标准 S

37、ELECT 语句。在游标声明的 select_statement 中不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。READ ONLY：说明该游标为只读游标，禁止进行更新。UPDATE OF column_name,.n：定义游标中可更新的列。如果指定了 OF column_name,.n，则只允许修改所列出的列。如果指定了 UPDATE，但未指定列的列表，则可以更新所有列。,7.5 游标的概念和应用,以下一个符合 SQL-92 标准的游标声明如下：DECLARE STU_CUR CURSOR FOR SELECT*FROM Student_info

38、 WHERE Sdepart=计算机系 FOR READ ONLY注意:该语句定义的游标与单个表的查询结果集相关联，是只读的，游标只能从头到尾顺序提取数据。,7.5 游标的概念和应用,（2）Transact-SQL 扩展语法。语法格式：DECLARE cursor_name CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD READ_ONLY|SCROLL_LOCKS|OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF colum

39、n_name,.n,7.5 游标的概念和应用,参数说明：cursor_name：是所定义游标的名称。LOCAL：指定游标的作用域。GLOBAL：指定该游标的作用域对连接是全局的。FORWARD_ONLY|SCROLL：指定游标的移动方向。FORWARD_ONLY：游标只能从第一行滚动到最后一行；SCROLL 同SQL-92语法。STATIC|KEYSET|DYNAMIC|FAST_FORWARD：定义游标类型。静态游标。关键字 STATIC指定游标为静态游标。,7.5 游标的概念和应用,键集驱动游标。关键字KEYSET 定义一个键集驱动游标。动态游标。关键字DYNAMIC 指定游标为动态游标。

40、与静态游标不同，动态游标能够反映对结果集中所做的更改。只进游标。关键字 FAST_FORWARD定义一个快速只进游标，它是优化的只进游标，只支持游标从头到尾顺序提取数据。,7.5 游标的概念和应用,READ_ONLY|SCROLL_LOCKS|OPTIMISTIC：说明游标或基表的访问属性。READ ONLY是只读游标；SCROLL_LOCKS 指定通过游标进行的定位更新或删除一定会成功，当将行读入游标时 SQL Server 将锁定这些行，以确保随后可对它们进行修改。如果还指定了 FAST_FORWARD 或 STATIC，则不能指定 SCROLL_LOCKS；OPTIMISTIC 指明如果

41、行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功，该选项不能与 FAST_FORWARD 同时使用。TYPE_WARNING：指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告消息。,7.5 游标的概念和应用,以下是Transact-SQL 扩展游标声明：DECLARE STU_CUR2 CURSORDYNAMICFOR SELECT Sid,Sname,Sgender,Sbirth,Sdepart FROM Student_info WHERE Sdepart=计算机系FOR UPDATE OF Sbirth,7.5 游标的概念和应用,3打开游标声明游标后，就

42、可以打开游标。打开游标的语法如下：OPEN GLOBAL cursor_name|cursor_variable_name 其中，GLOBAL 指定cursor_name 是全局游标，否则打开局部游标。cursor_variable_name 是游标变量名。,7.5 游标的概念和应用,【例7-18】定义游标STU_CUR，然后打开该游标，输出其行数。declare stu_cur cursor local scroll scroll_locks for select Sid,Sname,Sgender,Sbirth,Sdepart from Student_info for update of

43、 Sbirthopen stu_curselect 游标stu_cur 数据行数=CURSOR_ROWS结果为游标 stu_cur 数据的行数。,7.5 游标的概念和应用,4读取游标游标打开后，可以使用 FETCH 语句来读取数据。读取游标的语法如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvar FROM GLOBAL cursor_name|cursor_variable_name INTO variable_name,.n,7.5 游标的概念和应用,参数说明：NEXT|PRIOR|FIRST|LAST：用于说明读取数

44、据的位置。分别为读取当前行的下一行；当前行的前一行；结果集的第一行；结果集的最后一行。并且使其置为当前行。ABSOLUTE n|nvar：如果 n 或 nvar 为正，则返回从游标头开始向后的第 n 行，并将返回行变成新的当前行。如果 n 或 nvar 为负，则返回从游标末尾开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 nvar 为 0，则不返回行。n 必须是整数常量，并且 nvar 的数据类型必须为 smallint、tinyint 或 int。RELATIVE n|nvar如果 n 或 nvar 为正，则返回从当前行开始向后的第 n 行，并将返回行变成新的当前行。如果 n

45、或 nvar 为负，则返回从当前前行开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 nvar 为 0，则返回当前行。在对游标进行第一次提取时，如果在将 n 或 nvar,7.5 游标的概念和应用,设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。n 必须是整数常量，nvar 的数据类型必须为 smallint、tinyint 或 int。GLOBAL指定 cursor_name 是指全局游标。INTO variable_name,.n：说明将读取的游标数据存放到指定的局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相

46、应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。,7.5 游标的概念和应用,【例7-19】从游标STU_CUR中读取数据。设该游标已经打开。FETCH NEXT FROM STU_ CUR5关闭游标 游标使用完后，要及时关闭。关闭游标使用 CLOSE 语句。语法如下：CLOSE GLOBAL cursor_name|cursor_variable_name 参数同 OPEN 参数说明。例如：CLOSE STU_ CUR将关闭游标 STU_ CUR。,7.5 游标的概念和应用,6释放游标游标关闭后，其定义仍在，若确认游标不再需要使用了，

47、就要释放其定义所占用的系统空间，即删除游标。DEALLOCATE 语句用于删除游标与游标名称或游标变量之间的关联，游标使用的任何资源也随之释放。语法如下：DEALLOCATE GLOBAL cursor_name|cursor_variable_name 参数同 OPEN 参数说明。,7.5 游标的概念和应用,7游标的应用【例7-20】使用游标汇总每位学生的平均成绩和应得学分。use studentdeclare name char(10)declare avg_grade float-定义平均成绩变量declare total float-定义应得学分变量declare mycursor c

48、ursor for-声明游标 select Sname,avg(grade),sum(Ccedit)from Student_info,sc,Course_info where Student_info.sid=sc.sid and Course_info.cid=sc.cid GROUP BY Sname,7.5 游标的概念和应用,open mycursor-打开游标fetch next from mycursor into name,avg_grade,total-读取游标数据print 每个学生的平均成绩和应得学分如下：while(fetch_status=0)-检测游标数据是否读取完，

49、如果还有数据，继续循环 begin print name+str(avg_grade,5,2)+str(total,3,1)fetch next from mycursor into name,avg_grade,total endclose mycursor-关闭游标deallocate mycursor-释放游标GO,7.5 游标的概念和应用,总结与反思,本情境中，要认真学习以上3个工作任务，最终完成“实训项目”的上机实践,达到以下要求:1掌握Transact-SQL的数据类型、常量变量、表达式等概念。2掌握程序中注释的基本概念和使用方法。3掌握程序中的流程控制语句。4掌握游标的概念和声明方法，以及使用游标进行数据的查询、修改、删除操作等。,