T-SQL程序设计基础.ppt

1,第四章 T-SQL程序设计基础,SQL Server数据类型 SQL Server 全局变量和局部变量 SQL Server 函数 T-SQL 控制流程 游标,2,系统数据类型,数据类型指定列、存储过程参数及局部变量的数据特性 数据按照数据类型存储在列中 系统数据类型：系统数据类型是 SQL Server 支持的内置数据类型，系统数据类型有25种。 用户定义的数据类型：用户根据系统数据类型自己定义的数据类型,3,系统数据类型,4,系统数据类型,5,系统数据类型,6,系统数据类型,7,系统数据类型,8,系统数据类型,9,系统数据类型,10,系统数据类型,11,系统数据类型,12,系统数据类型,只有字符与二进制数据类型需要指定长度，其他如整型、日期时间、浮点数据类型，定义时不用指定长度，也就是长度是默认值。 自定义数据类型 建立在SQL server系统数据类型基础上的。需要指定该类型的名称、建立在其上的系统数据类型及是否充许为空。 可以用如下方法来创建自定义数据类型。,13,自定义数据类型,利用系统存储过程：sp_addtype。其语法为： sp_addtype type, system_data_type , nulltype 其中：type是用户定义数据类型的名称。数据类型名称必须遵循标识符规则，并且在每个数据库中必须是唯一的。 system_data_type是 SQL Server 提供的数据类型，用户定义的数据类型即基于该类型。 null_type ：指定必须如何处理 null 值。null_type 是 varchar(8)，设置值为 NULL（默认）、NOT NULL 或 NONULL 例：exec SP_addtype tele ,smallint , not null,14,删除用户定义的数据类型,使用sp_droptype 系统存储过程来撤销用户定义数据类型。 例删除名为 tele的用户定义数据类型: EXEC sp_droptype telephone,15,其它语言元素,批处理 注释 变量 运算符 函数 流程控制语句,16,批处理,批处理：指包含一条或多条T-SQL语句的语句组，这组语句从应用程序一次性地发送到SQL Server服务器执行。 执行单元：SQL Server服务器将批处理语句编译成一个可执行单元，这种单元称为执行单元。 若批处理中的某条语句编译出错，则无法执行。若运行出错，则视情况而定。,17,书写批处理时，go语句作为批处理命令的结束标志。当编译器读取到go语句时，会把go语句前面所有的语句当作一个批处理，并将这些语句打包发送给服务器。 注意：go语句本身不是T-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。,18,建立批处理时注意,Create default、Create Rule、Create Trigger 和Create view等语句在同一个批处理中只能提交一个。 不能在删除一个对象之后，再同一批处理中再次引用这个对象 不能把规则和默认值绑定到表字段或者自定义字段上之后，立即在同一个批处理中使用它们。 不能定义一个check约束之后，立即在同一个批处理中使用。,19,不能修改表中一个字段名之后，立即在同一个批处理中引用这个新字段。 使用set 语句设置的某些set 选项不能应用于同一个批处理中的查询 若批处理中第一个语句是执行某个存储过程的execute语句，则execute关键字可以省略。若该语句不是第一个语句，则必须写上。,20,例如：,Use pubs Go Select * from 员工数据表 Go Create view pm_view as Select * from 员工数据库 where 所属部门=项目部 Go Select * from pm_view go,21,注释,注释是程序代码中不执行的文本字符串（也称为注解）。在SQL Server中，可以使用两种类型的注释字符：一种是ANSI标准的注释符“-”，它用于单行注释；另一种是与C语言相同的程序注释符号，即“/* */”。例如： -检索部门的员工 /* 检索录入部 的员工*/,22,变量,变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。,23,全局变量,全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。,24,使用全局变量时注意,全局变量不是由用户的程序定义的，它们是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符“”开头。 局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,25, error全局变量将返回最后执行的T-SQL语句的错误代码，数据类型为整型。在SQL Server执行一个T-SQL语句之后，若成功，则返回值为0；否则返回相应的 错误代码。,26,局部变量,局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用。,27,声明局部变量,其声明形式如下： DECLARE 变量名 变量类型 ，变量名 变量类型 其中变量类型可以是SQL Server 2000 支持的所有数据类型，也可以是用户自定义的数据类型,28,局部变量赋值,在Transact-SQL 中,不能像在一般的程序语言中一样使用变量=变量值来给变量赋值,必须使用SELECT 或SET 命令来设定变量的值.其语法如下 SELECT 局部变量= 变量值 或SET 局部变量= 变量值,29,例： 声明一个长度为10 个字符的变量id 并赋值 declare id char 10 select id = 10010001 例：declare temp_counter int Set temp_counter = 0 例：declare max_salary int Select max_salary= max(工资) From 员工数据表 注意：局部变量的作用范围是从声明该局部变量的地方开始，到声明局部变量的批处理或存储过程的结尾。在局部变量的作用范围以外引用该局部变量将产生语法错误。,30,运算符,运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQL Server 2000中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串串联运算符。,31,1算术运算符,算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）。,32,2赋值运算符,Transact-SQL 中只有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。,33,3位运算符,位运算符使我们能够在整型数据或者二进制数据（image 数据类型除外）之间执行位操作。,表1 位运算符,34,4比较运算符,比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及UNKNOWN。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。,35,5逻辑运算符,逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。逻辑运算符和比较运算符一样，返回带有 TRUE 或 FALSE 值的布尔数据类型。,36,6字符串串联运算符,字符串串联运算符允许通过加号 (+) 进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT abc+def，其结果为abcdef。,37,运算符的优先级从高到低排列,括号：（）； 乘、除、求模运算符：*、/、%； 加减运算符：+、- ； 比较运算符：=、=、!=、!、!； 位运算符：、&、|； 逻辑运算符：NOT； 逻辑运算符：AND； 逻辑运算符：OR。,38,函数,在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQL Server的标准命令。Transact-SQL 编程语言提供了三种函数： 行集函数：行集函数可以在Transact-SQL语句中当作表引用。 聚合函数：聚合函数用于对一组值执行计算并返回一个单一的值。 标量函数：标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。,39,标量函数的分类,配置函数：返回当前的配置信息 游标函数：返回有关游标的信息 日期和时间函数：用于对日期和时间类型的输入值进行操作，返回一个字符串、数字或日期和时间值 数学函数：用于对作为函数参数提供的输入值执行操作，返回一个数字值 元数据函数：返回有关数据库和数据库对象的信息 安全函数：返回有关用户和角色的信息 字符串函数：对字符串输入值执行操作，并返回一个字符串或数字值 系统函数：执行系统操作 系统统计函数：返回系统的统计信息 文本和图像函数：对于文本或图像输入值或列执行操作，返回有关这些值的信息。,40,系统函数,系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。,41,例：返回Northwind数据库的 Employees 表中的首列的名称。 USE Northwind SELECT COL_NAME(OBJECT_ID(Employees), 1) 运行结果为： EmployeeID 其中：object_id:返回对象的id,42,日期和时间函数,日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。表1列出了日期类型的名称、缩写形式以及可接受的值。,43,表1 日期和时间函数的类型,44,例：从getdate函数返回的日期中提取月份数 SELECT DATEPART(month, GETDATE() AS Month Number 运行结果为： Month Number - 9,45,例：从日期03/12/1998中返回月份、天数和年份数 SELECT MONTH(03/12/1998), DAY(03/12/1998),YEAR(03/12/1998) 运行结果为： - - - 3 12 1998,46,字符串函数,字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。此外，某些字符串函数还能够处理text、ntext、image数据类型的数据。,47,字符串函数的分类：,基本字符串函数：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。 字符串查找函数：CHARINDEX、PATINDEX。 长度和分析函数：DATALENGTH、SUBSTRING、RIGHT。 转换函数：ASCH、CHAR、STR、SOUNDEX、DIFFERENCE。,48,数学函数,数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。,49,例：在同一表达式中使用CEILING（）、FLOOR（）、ROUND（）函数。 select ceiling(13.4), floor(13.4), round(13.4567,3) 运行结果为： - - - 14 13 13.4570,50,转换函数,一般情况下，SQL Server会自动处理某些数据类型的转换。例如，如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式，SQL Server 可以将它们自动转换，这种转换被称为隐性转换。但是，无法由SQL Server自动转换的或者是SQL Server自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数有两个：CONVERT和CAST。,51,CONVERT和CAST函数,CAST ( expression AS data_type ) data_ type 为SQL Server 系统定义的数据类型，用户自定义的数据类型不能在此使用,52,CONVERT和CAST函数,CONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式。其语法形式为： CONVERT (data_type(length),expression ,style) 其中：length用于指定数据的长度，默认值是30。style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的。由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式,53,例：USE pubs SELECT title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20) LIKE 15% AND type = trad_cook 运行结果为： Title ytd_sales - Fifty Years in Buckingham Palace Kitchens 15096,54,用户自定义函数,除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数是SQL Server 2000 新增的数据库对象，是SQL Server 的一大改进。 用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。用户自定义函数中存储了一个Transact-SQL 例程可以返回一定的值。 在SQL Server 2000 中根据函数返回值形式的不同，将用户自定义函数分为三种类型：,55,标量型函数：.标量型函数返回一个确定类型的标量值。其返回值类型为除了TEXT、NTEXT、 IMAGE、CURSOR、 TIMESTAMP 和TABLE 类型外的其它数据类型。函数体语句定义在BEGIN-END语句内，其中包含了可以返回值的Transact-SQL 命令。 内嵌表值函数：内嵌表值函数以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由BEGIN-END 语句括起来的函数体，其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。内嵌表值型函数功能相当于一个参数化的视图。 多语句表值型函数：多语句表值型函数可以看作标量型和内嵌表值型函数的结合体，它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体。返回值的表中的数据是由函数体中的语句插入的。,56,创建标量型用户自定义函数,其语法如下： CREATE FUNCTION owner_name. function_name ( parameter_name as scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expression END,57, := ENCRYPTION | SCHEMABINDING 各参数说明如下： owner_name：指定用户自定义函数的所有者 function_name：指定用户自定义函数的名称。database_name.owner_name.function_name 应是唯一的。 parameter_name：定义一个或多个参数的名称，一个函数最多可以定义1024 个参数，每个参数前用符号标明。参数的作用范围是整个函数，参数只能替代常量，不能替代表名、列名或其它 数据库对象的名称，用户自定义函数不支持输出参数。,58,scalar_parameter_data_type：指定标量型参数的数据类型，可以为除TEXT、NTEXT、IMAGE、 CURSOR、TIMESTAMP 和TABLE 类型外的其它数据类型。 scalar_return_data_type：指定标量型返回值的数据类型，可以为除TEXT、 NTEXT 、IMAGE、 CURSOR、TIMESTAMP 和TABLE 类型外的其它数据类型。 scalar_expression：指定标量型用户自定义函数返回的标量值表达式。 function_body：指定一系列的Transact-SQL 语句，它们决定了函数的返回值,59,ENCRYPTION：加密选项。让SQL Server 对系统表中有关CREATE FUNCTION 的声明加密，以防止用户自定义函数作为SQL Server 复制的一部分被发布。 SCHEMABINDING：计划绑定选项。将用户自定义函数绑定到它所引用的数据库对象。如果指定了此选项，则函数所涉及的数据库对象从此将不能被删除或修改，除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。,60,例13-19 创建工龄工资计算函数 use pangu go create function WorkYearWage(hiredate datetime, -hiredate 表示雇佣日期 today datetime, per_wage money) -today 表示当前的日期per_wage 表示每一年工龄应得的工资额 returns money as begin declare WorkYearWage money set WorkYearWage = (year(today)-year(hiredate)*per_wage return(WorkYearWage) end -结束函数定义 -创建函数 go,61,-调用函数 select pangu.dbo.workyearwage(1991-7-1,getdate(),15) as work_year_wage 运行结果如下 work_year_wage 135.0000 (1 row(s) affected) 上例的创建语句也可以写成如下形式 create function WorkYearWage(hiredate datetime, today datetime, per_wage money) returns money as begin return(year(today)-year(hiredate)*per_wage) end,62,创建内嵌表值用户自定义函数,其语法如下： CREATE FUNCTION owner_name. function_name ( parameter_name as scalar_parameter_data_type = default ,.n ) RETURNS TABLE WITH ,.n AS RETURN ( select-stmt ) 各参数说明如下 TABLE：指定返回值为一个表 select-stmt：单个SELECT 语句，确定返回的表的数据，其余参数与标量型用户自定义函数相同。,63,例: 创建返回所有订购某类产品的公司信息函数 use sample go create function orderfirms(productid varchar(30) returns table as return (select * from products p where p.p_id = productid) Go,64,创建多语句表值用户自定义函数,其语法如下： CREATE FUNCTION owner_name. function_name ( parameter_name scalar as_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURN END : = ( column_definition | table_constraint ,.n ) 各参数说明如下： return_variable：一个TABLE 类型的变量，用于存储和累积返回的表中的数据行。其余参数与标量型用户自定义函数相同。,65,用企业管理器创建用户自定义函数,用Enterprise Manager 创建用户自定义函数的方法是：在Enterprise Manager 中选择要创建用户自定义函数的数据库，在数据库对象User Defined Functions 上单击右键，从开始菜单中选择New User Defined Function 选项，就会出现定义用户自定义函数属性对话框，可以在其中指定要定义的函数的名称，并编辑函数的脚本，单击OK 按钮则添加用户自定义函数对象到数据库中。,66,修改和删除用户自定义函数,在Enterprise Manager 中选择要进行改动的用户自定义函数，单击右键，从快捷菜单中选择属性选项，则会出现修改用户自定义函数结构对话框。可以修改用户自定义函数的函数体、参数等，从快捷菜单中选择删除选项，则可删除用户自定义函数。 用ALTER FUNCTION 命令也可以修改用户自定义函数，此命令的语法与CREATE FUNCTION 相同,因此使用ALTER FUNCTION 命令其实相当于重建了一个同名的函数,用起来不大方便。另外可以用DROP FUNCTION 命令删除用户自定义函数其语法如下： DROP FUNCTION owner_name. function_name ,.n 例： 删除用户自定义函数chiefinfo drop function chiefinfo,67,流程控制语句,流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。,68,BEGINEND语句,BEGINEND语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句，其语法形式为： BEGIN sql_statement | statement_block END,69,例如：,Use sample Go Declare message varchar(200) If exist(select * from 员工数据表 where 所属部门办公室) Begin Set message=“下列人员在办公室工作：” Print message Select 员工姓名 from 员工数据库 Where 所属部门 “办公室” End Else begin set message=“没有人在办公室工作” Print message End go,70,IFELSE语句,IFELSE语句是条件判断语句，其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IFELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。SQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。,71,IFELSE语句的语法形式,IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block ,72,IF EXISTS语句,If后面的布尔表达式可含有select 语句，如果select语句返回一个值，它可用来与另一值进行比较，以得到布尔表达式。 如果Select语句返回不止一个值，可使用If Exists。语法如下： If exists (select statement) sql_statement | statement_block ELSE Bolean_expression sql_statement | statement_block ,73,例如：查询标识号为9933的出版商出版的任何书的信息 If exists (select * from titles where pub_id = 9933 begin print “包含如下图书：” select *from titles where pub_id = 9933 end else print “无”,74,注意：一定不要把if exists和聚合函数一起使用，因为聚合函数总是返回数据，即使数据是0。 例如：if exists(select count(*) from titles where pub_id = 9933),75,WHILE 语句,WHILE语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。,76,WHILE语句,Break语句将在某些情况发生时，立即无条件地退出最内层while循环。语法为： WHILE 逻辑表达式 begin . Break end,77,While语句,Continue语句在某些情况发生时，控制程序跳出本次循环，重新开始下一次while循环。其语法为： While 逻辑表达式 Begin Continue . End 注意：如果select语句用作while语句的条件，那么，select语句必须包含在英文括号中。,78,例：declare x int， y int ，c int select x = 1， y=1 while x 3 begin print x -打印变量x 的值 while y 3 begin select c = 100*x + y print c -打印变量c 的值 select y = y + 1 end select x = x + 1 select y = 1 end,79,CASE表达式,CASE表达式可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE表达式的语法为： CASE 字段名或变量名 WHEN 逻辑表达式1 THEN 结果表达式1 WHEN 逻辑表达式1 THEN 结果表达式1 WHEN 逻辑表达式1 THEN 结果表达式1 ELSE 结果表达式 END,80,例： 调整员工工资，工作级别为1 的上调8%，工作级别为2 的上调7%，工作级别为3 的上调6%，其它上调5%。 use 工资库 update employee set e_wage = case when job_level = 1 then e_wage*1.08 when job_level = 2 then e_wage*1.07 when job_level = 3 then e_wage*1.06 else e_wage*1.05 end,81,RETURN语句,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为： RETURN 整数表达式 通常，存储过程使用返回代码表示存储过程执行的成功或失败。无错误，则返回0，否则，返回非零值。,82,WAITFOR语句,WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。WAITFOR语句的语法形式为： WAITFOR DELAY 时间间隔 其中，时间间隔指定执行waitfor语句之前需要等待的事件，最多为24小时。 或者 waitfor time 时间值 其中，时间值指定waitfor语句将要执行的时间,83,Use sample Go -指定在执行select语句之前等待二秒 Waitfor delay 00:00:02 -执行查询 Select 姓名，性别 from 员工数据表 Where 所属部门项目部,84,GOTO语句,GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。 GOTO label label:,85,GOTO语句,例：利用GOTO语句求出从1加到5的总和。 declare sum int, count int select sum=0, count=1 label_1: select sum=sum+count select count=count+1 if count=5 goto label_1 select count,sum,86,PRINT语句,语法格式为： Print any ASCII text |local_varible | global_variable 注意：可输出的仅仅是ASCII字符串（字符串常数）或字符类型的变量 若要输出更复杂的内容，须将字符串存入字符变量，然后输出该变量。,87,例：Use pubs Declare msg varchar(50), numWA tinyint Select numWA=count(*) from stores where state = “WA” Select msg=There are + Convert(varchar(3),numWA)+ stores in washington. Print msg,88,游标的引入,游标的优点和种类 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT、INSERT 语句，但是我们常常会遇到这样情况：即从某一结果集中逐一地读取一条记录，那么如何解决这种问题呢？游标为我们提供了一种较好的解决方案,89,声明游标,每一个游标必须有四个组成部分。这四个关键部分必须符合下面的顺序 1.DECLARE 游标 2.OPEN 游标 3.从一个游标中FETCH 信息 4.CLOSE 或DEALLOCATE 游标 通常我们使用DECLARE 来声明一个游标。,90,声明游标,声明一个游标主要包括以下主要内容 游标名字 数据来源表和列 选取条件 属性仅读或可修改,91,其语法格式如下 DECLARE 游标名称 INSENSITIVE CURSOR local|global forward_only|scroll read_only FOR 选择语句 FOR UPDATE OF 字段名称1，字段名称2， 其中：local|global指定该游标的作用域是局部的还是全局的。 如果把forward_only选择为forward_only，则游标只能从第一行滚动到最后一行。,92,. SCROLL：表明所有的提取操作，如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE都可用。如果不使用该保留字那么只能进行NEXT 提取操作。 选择语句：是定义结果集的SELECT 语句，应该注意的是在游标中不能使用COMPUTE、 COMPUTE BY FOR BROWSE INTO 语句 READ ONLY：表明不允许游标内的数据被更新。 UPDATE OF 字段名1,n：定义在游标中可被修改的列。,93,下面给出声明游标的三个例子 例1： 标准游标 declare cur_authors cursor for select au_id, au_lname, au_fname, phone, address, city, state, contract from authors 例2：只读游标 declare cur_authors cursor for select au_lname, au_fname, phone, address, city, state from authors for read only,94,例：更新游标 declare cur_authors cursor for select au_lname, au_fname from authors for update,95,打开游标,游标在声明以后，如果要从游标中读取数据，必须打开游标。打开一个游标使用OPEN 命令，其语法规则为 OPEN 游标名称 注意： 在打开游标时，如果游标声明语句中使用了INSENSITIVE 保留字，则OPEN产生一个临时表来存放结果集。如果在结果集中任何一行数据的大小超过 SQL SERVER定义的最大行尺寸时，OPEN 命令将失败 INSENSITIVE：表明SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内，（建立在tempdb 数据库下）对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。,96,读取游标中的数据fetch,当游标被成功打开以后就可以从游标中逐行地读取数据以进行相关处理。从游标中读取数据主要使用FETCH 命令，其语法规则为： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM cursor_name INTO variable_name1,variable_name2. ,97,各参数含义说明如下: NEXT:返回结果集中当前行的下一行,并增加当前行数为返回行行数如果FETCH NEXT 是第一次读取游标中数据则返回结果集中的是第一行而不是第二行 PRIOR:返回结果集中当前行的前一行并减少当前行数为返回行行数。如果FETCH PRIOR是第一次读取游标中数据则无数据记录返回并把游标位置设为第一行 FIRST:返回游标中第一行 LAST:返回游标中的最后一行,98,ABSOLUTE n | nvar：如果n 或nvar 为正数，则表示从游标中返回的数据行数。如果n 或nvar 为负数，则返回游标内从最后一行数据算起的第n 或nvar 行数据。 若n 或nvar 超过游标的数据子集范畴，则FETCH_STATUS 返回-1 。在该情况下，如果n 或nvar 为负数，则执行FETCH NEXT 命令会得到第一行数据；如果n 或nvar为正值，执行FETCH PRIOR 命令则会得到最后一行数据。n 或nvar 可以是一固定值，也可以是一smallint, tinyint 或int 类型的变量。,99,RELATIVE n | nvar：若n 或nvar 为正数，则读取游标当前位置起向后的第n 或nvar 行数据。如果n 或nvar 为负数，则读取游标当前位置起向前的第n 或nvar 行数据。若n 或nvar 超过游标的数据子集范畴，则FETCH_STARS 返回-1。 在该情况下，如果n 或nvar 为负数，则执行FETCH NEXT 命令则会得到第一行数据；如果n 或nvar 为正值，执行FETCHPRIOR 命令则会得到最后一行数据。n 或nvar 可以是一固定值也可以是一smallint, tinyint或int 类型的变量,100,INTO variable_name,.n：允许将使用FETCH 命令读取的数据存放在多个变量中；在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中数据列的数据类型相匹配。,101,检查游标状态,FETCH_STATUS ：全局变量，返回上次执行FETCH 命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量以确定上次FETCH 操作是否成功，来决定如何进行下一步处理。FETCH_STATUS 变量有三个不同的返回值。 0：表示成功取出了一行。 1：表示未取到数据，因为所要求 游标位置超出了结果集 2：表示返回的行已经不再是结果集的一个成员。这种情况只有在游标不是insensitive的情况下出现，即其它进程已删除了行或改变了游标打开的关键值，从而使该行不再使游标集的结果成员之一了。,102,编辑当前游标行,通常情况下，我们用游标来从基础表中检索数据，以实现对数据的行处理。但在某些情况下，我们也常要修改游标中的数据，即进行定位更新或删除游标所包含的数据。所以必须执行另外的更新或删除命令，并在WHERE 子句中重新给定条件才能修改到该行数据。但是如果在声明游标时使用了FOR UPDATE 语句，那么就可以在UPDATE 或DELETE命令中以WHERE CURRENT OF 关键字直接修改或删除当前游标中所存储的数据，而不必使用WHERE 子句重新给出指定条件。当改变游标中数据时，这种变化会自动地影响到游标的基础表。但是如果在声明游标时选择了INSENSITIVE 选项时，该游标中的数据不能被修改。,103,进行定位修改或删除游标中数据的语法规则为: UPDATE table_name SET column_name1 = expression1 | NULL (select_statement) , column_name2 = expression2 | NULL (select_statement) WHERE CURRENT OF cursor_name DELETE FROM table_name WHERE CURRENT OF cursor_name 其中：table_name是UPDATE 或DELETE 的表名 column_name： UPDATE 的列名 cursor_name： 游标名,104,下例说明如何对游标进行定位更新或删除。 首先声明