ch09_Transact-SQL程序设计.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二层,第三层,第四层,第五层,数据库技术及应用,第,9,章,Transact-SQL,程序设计,主要内容,一、常量和与变量,二、运算符,三、函数,四、程序控制流语句,五、游标,如何编制类似,C,语言的基本程序？,一、常量与变量,常量,变量,局部变量的声明、赋值和读取,常量,字符串常量括在单引号内,若字符串中本身又有单引号，则单引号要用两个单引号表示。如：,it,s my book,时间日期常量用单引号括起来,可以使用“,/,”,、“,.,”,、“,-,”,作为日期时间常量的分隔符,逻辑数据常量使用数字,0,或,1,表示,在,Transact-SQL,语言中，变量分为局部变量和全局变量,变量,全局变量,由系统定义和维护，名称以两个,符号开头,主要用于记录,SQL Server,的各种状态信息,全局变量对用户而言，是只读的,作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用,=,0901_GlobalVariant,变量,局部变量,由用户定义和维护，名称以单个,符号开头,变量声明,使用,DECLARE,语句声明局部变量,格式：,DECLARE ,.,局部变量的作用域是在其中声明局部变量的批处理、存储过程或语句块,例如：,DECLARE ASTR VARCHAR(10),DECLARE NUM INT,DATE DATETIME,局部变量的声明、赋值和读取,局部变量赋值,方法一：使用,SET,语句,如：,SET ASTR=TAXI,方法二、使用,SELECT,语句,如：,SELECT ASTR=TAXI,SELECT NUM=COUNT(*)FROM Student,-,注意：该情况下查询的结果只能是一行,读取变量的值,方法一：使用,SELECT,语句,如：,SELECT ASTR,方法二、使用,PRINT,语句,如：,PRINT ASTR,=,0902_LocalVariant.SQL,局部变量的声明、赋值和读取,二、运算符,算术运算符,比较运算符,逻辑运算符,连接运算符,”,+,“,用于连接两个字符串,三、函数,什么是函数？,一组编译好,的,Transact-SQL,语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作,为什么要使用函数？,函数能够重复执行一些操作，从而避免不断重写代码,SQL Server,支持两种函数,内置函数：一组预定义好的函数,用户定义函数,常见系统函数举例,系统函数：,CAST,、,CONVERT,、,ISNULL,日期函数：,GETDATE,、,YEAR,、,MONTH,、,DAY,、,DATEADD,字符串函数：,SUBSTRING,、,RTRIM,、,LTRIM,、,SPACE,数学函数：,ABS,、,ROUND,聚合函数：,AVG,、,COUNT,、,MAX,、,MIN,、,SUM,=,0903_SystemFunction,用户定义函数,可以拥有零个、一个或多个参数，必须返回单一的返回值,返回值可以是单个数值，或是一个表,根据函数返回值的形式不同，用户定义函数分为三类,标量函数,内嵌表值函数,多语句表值函数,标量函数,返回一个确定类型的标量值，函数体语句定义在,BEGINEND,语句内,内嵌表值函数,返回的函数值为一个表,内嵌表值函数的函数体不使用,BEGINEND,语句，其返回的表是,RETURN,子句中的,SELECT,命令查询的结果集，其功能相当于一个参数化的视图,多语句表值函数,可以看作标量函数和内嵌表值函数的结合体,其函数值也是一个表，但函数体也用,BEGINEND,语句定义，返回值的表中的数据由函数体中的语句插入,标量函数的语法格式,CREATE,FUNCTION,function_name,(parameter_name AS scalar_parameter_data_type=default ,.n ),RETURNS,scalar_return_data_type,AS,BEGIN,function_body,RETURN,scalar_expression,END,=,090,4,_,Custom,Function,内嵌表值函数的语法格式,CREATE,FUNCTION,function_name,(parameter_name AS scalar_parameter_data_type=default ,.n ),RETURNS,TABLE,AS,RETURN,(select_stmt ),=,090,4,_,Custom,Function,多语句表值函数的语法格式,CREATE,FUNCTION,function_name,(parameter_name AS scalar_parameter_data_type =default ,.n ),RETURNS,return_variable,TABLE,AS,BEGIN,function_body,RETURN,END,四、程序控制流语句,在,SQL Server 2000,中，流程控制语句主要用来控制,SQL,语句、语句块或者存储过程的执行顺序,BEGINEND,IFELSE,WHILECONTINUEBREAK,CASE,RETURN,WAITFOR,注释,批处理,BEGINEND,将多个,Transact-SQL,语句组合成一个语句块，并将它们视为一个整体来执行,在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用,BEGINEND,语句,IF.ELSE,条件判断语句，其中，,ELSE,子句是可选的，最简单的,IF,语句没有,ELSE,子句部分,SQL Server,允许嵌套使用,IFELSE,语句,WHILECONTINUEBREAK,用于设置重复执行,SQL,语句或语句块的条件。只要指定的条件为真，就重复执行语句,CONTINUE,语句结束本次循环,跳过,CONTINUE,语句后面的语句，重新转到下一次循环条件的判断,BREAK,语句则使程序完全跳出循环，结束,WHILE,语句的执行,注释,单行注释：使用两个连字符”,-“,开头,多行注释：使用一对分隔符”,/*/“,注释文本,选择要注释的文本，按,CTRL+SHIFT+C,删除注释标记,选择要从中删除注释标记的文本，按,CTRL+SHIFT+R,CASE,简单,CASE,函数,将某个表达式与一组简单表达式进行比较以确定结果,CASE,搜索函数,CASE,计算一组逻辑表达式以确定结果,区别,CASE,搜索函数允许执行各种比较操作和多条件测试,(,通过,AND,、,OR,实现,),而简单,CASE,函数只能进行相等比较,简单,CASE,函数的语法,CASE,input_expression,WHEN,when_expression,THEN,result_expression.n,ELSE,else_result_expression,END,=,090,5,_,CASE,计算过程,计算,input_expression,，然后按指定顺序对每个,WHEN,子句的,input_expression=when_expression,进行计算,返回第一个取值为,TRUE,的,(input_expression=when_expression),的,result_expression,如果没有取值为,TRUE,的,input_expression=when_expression,，则当指定,ELSE,子句时,SQL Server,将返回,else_result_expression,若没有指定,ELSE,子句，则返回,NULL,值,CASE,搜索函数的语法,CASE,WHEN,Boolean_expression,THEN,result_expression.n,ELSE,else_result_expression,END,=,090,5,_,CASE,计算过程,计算,input_expression,，然后按指定顺序对每个,WHEN,子句的,input_expression=when_expression,进行计算,返回第一个取值为,TRUE,的,(input_expression=when_expression),的,result_expression,如果没有取值为,TRUE,的,input_expression=when_expression,，则当指定,ELSE,子句时,SQL Server,将返回,else_result_expression,若没有指定,ELSE,子句，则返回,NULL,值,WAITFOR,允许开发者定义一个时间，或者一个时间间隔，在定义的时间内或经过定义的时间间隔时，其后的,Transact-SQL,语句会被执行,=0906_WAITFOR,批处理,一个批处理是一条或多条,SQL,语句的集合,SQL Server,服务器把批处理作为一个整体进行分析、再优化、编译和执行,SQL Server,服务器将批处理语句编译成一个可执行单元，称为执行计划,SQL Server,服务器对批处理的处理分为四个阶段,分析阶段：服务器检查命令的语法，验证表和列的名字的合法性,优化阶段：服务器确定完成一个查询的最有效的方法,编译阶段：生成该批处理的执行计划,运行阶段：,条一条地执行该批处理中的语句,批处理的指定,应用程序作为一个执行单元发出的所有,SQL,语句构成一个批处理，并生成单个执行计划,存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划,由,EXECUTE,语句执行的字符串是一个批处理，并编译为一个执行计划。例如，,EXEC(SELECT*FROM employee),注意：,在,SQL Server,中，并不是所有的,Transact-SQL,语句都可以放在一个批中,CREATE VIEW,、,CREATE PROCEDURE,、,CREATE TRIGGER,语句不能在批处理中与其他语句组合使用,如果,EXECUTE,语句是批处理中的第一条语句，则不需要,EXECUTE,关键字，否则需要,EXECUTE,关键字,批处理的结束与退出,GO,是批处理的结束标志,局部,(,用户定义,),变量的作用域限制在一个批处理中，不可在,GO,命令后引用,=0907_Go.SQL,事务处理,一、什么是事务,二、事务的特点,三、事务的模式,四、事务处理语句,事务是用户定义的一个操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，是数据库的逻辑工作单位,例如，在关系数据库中，一个事务可以是一条,SQL,语句、一组,SQL,语句或整个程序,什么是事务,事务具有四个特性，简称,ACID,特性,原子性,(Atomicity),事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做,一致性,(Consistency),事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。,隔离性,(Isolation),一个事务的执行不能被其他事务干扰,持续性,(Durability),一个事务一旦提交，它对数据库中数据的改变就应该是永久性的,事务的特点,事务模式可分为显式事务、隐式事务和自动事务,显式事务,指由用户通过,Transact-SQL,事务语句而定义的事务。这类事务又称为用户定义事务,隐式事务,指在当前事务提交或回滚后，,SQL Server,自动开始的事务,自动事务,是一种能够自动执行并能自动回滚的事务,在自动事务模式下，当一个语句成功执行后，它被自动提交，而当它执行过程中产生错误时则被自动回滚,是,SQL Server,的默认事务管理模式,事务的模式,BEGIN TRANSACTION,标记一个本地显式事务的起始点,COMMIT TRANSACTION,标志一个成功的隐式事务或显式事务的结束,ROLLBACK TRANSACTION,将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点,SAVE TRANSACTION,在事务内设置保存点。当事务执行到该保存点时，,SQL Server,存储所有被修改的数据到数据库中,=0908_Transaction.SQL,事务处理语句,五、游标,为什么要使用游标？,在数据库开发过程中，当我们需要从某一结果集中逐一地读取每条记录时，如何解决这种问题呢？,游标为我们提供了一种极为优秀的解决方案,游标是什么？,游标总是与一条,Transact-SQL,选择语句相关联,游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成,游标允许应用程序对查询语句,select,返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作,如何使用游标？,SQL Server,游标具有如下的处理过程,DECLARE,游标,OPEN,游标,从一个游标中,FETCH,信息,CLOSE,或,DEALLOCATE,游标,声明游标,通常使用,DECLARE CUSROR,来声明一个游标，声明一个游标主要包括以下内容,游标名字,数据来源,(,表和列,),选取条件,属性,(,仅读或可修改,),SQL-92,语法格式,DECLARE,cursor_name INSENSITIVE SCROLL,CURSOR,FOR,select_statement,FOR READ ONLY|UPDATE OF column_name ,.n ,说明,SCROLL,：表明所有的提取操作（如,FIRST,、,LAST,、,PRIOR,、,NEXT,、,RELATIVE,、,ABSOLUTE,）都可用,如果不使用该保留字，那么只能进行,NEXT,提取操作,由此可见，,SCROLL,极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标,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 column_name ,.n ,说明,FORWARD_ONLY,：指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用,FETCH NEXT,操作,注意：不可以将,SQL_92,的游标语法规则与,SQL SERVER,的游标扩展用法混合在一起使用,打开游标,游标在声明以后，如果要从游标中读取数据必须打开游标,打开一个,Transact-SQL,服务器游标使用,OPEN,命令,OPEN GLOBAL cursor_name|cursor_variable_name,读取游标中的数据,当游标被成功执行后，就可以从游标中逐行地读取数据，以进行相关处理,从游标中读取数据主要使用,FETCH,命令,FETCH,NEXT,|PRIOR|FIRST|LAST,|ABSOLUTE n|nvar,|RELATIVE n|nvar,FROM,GLOBAL cursor_name|cursor_variable_name,INTO,variable_name ,.n ,说明,NEXT,：返回结果集中当前行的下一行。如果,FETCH NEXT,是第一次读取游标中数据，则返回结果集中的是第一行，而不是第二行,PRIOR,：返回结果集中当前行的前一行。如果,FETCH PRIOR,是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行,FIRST:,返回游标中的第一行，并将其作为当前行,LAST,：返回游标中的最后一行，并将其作为当前行,ABSOLUTE n|nvar,：如果,n,或,nvar,为正数，返回从游标头开始的第,n,行，并将返回的行变成新的当前行,RELATIVE n|nvar,：如果,n,或,nvar,为正数，返回当前行之后的第,n,行，并将返回的行变成新的当前行,INTO variable_name ,.n,：允许将使用,FETCH,命令读取的数据存放在多个变量中,各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换,变量的数目必须与游标选择列表中的列的数目一致,全局变量,FETCH_STATUS,返回上次执行,FETCH,命令的状态,在每次用,FETCH,从游标中读取数据时，都应检查该变量，以确定上次,FETCH,操作是否成功，来决定如何进行下一步处理,若,FETCH,命令执行成功，则返回值为,0,关闭游标,使用,CLOSE,命令可关闭游标，但不释放游标占用的数据结构，游标关闭之后，不能再执行,FETCH,操作,如果还需要使用,FETCH,语句，则要重新打开游标,CLOSE GLOBAL cursor_name|cursor_variable_name,释放游标,当,CLOSE,命令关闭游标时，并没有释放游标占用的数据结构,常使用,DEALLOCATE,命令来删除掉游标与游标名变量之间的联系，并且释放游标占用的所有系统资源,DEALLOCATE GLOBAL cursor_name|cursor_variable_name,=0909_Cursor.SQL,