TransactSQL编程.ppt

TransactSQL编程,TransactSQL简介,ANSISQL的不足缺乏逻辑控制结构不支持对SQLServer属性的控制TransactSQL（简称T-SQL）是对ANSISQL的扩充，增加了一些Microsoft专用扩展，是一种SQL方言提供了编写结构程序的能力,T-SQL批处理,由一个或多个T-SQL命令构成SQLServer将这些命令作为一个整体执行，如果发生了错误，将会取消批处理中所有T-SQL命令的操作,T-SQL批处理的构成,声明变量可选DECLARE变量名变量数据类型程序体SQL命令T-SQL扩展命令，如流程控制命令程序结束GO,T-SQL变量,用DECLARE命令声明数据类型，包括CreateTable语句中可以使用的所有数据类型，以及table和SQLVariant数据类型。Numbers，Dates，Characters，Binary，UniqueIdentifiers(GUID)，SQLVariants，Image和Text，Table，Cursor，User-defined,变量的作用域和默认值,作用域：当前批处理默认值：空值变量的例子：DECLARETestINT,Test2VARCHAR(20),变量的赋值与显示,使用SET命令SET变量名使用SELECT命令SELECT变量名目标表达式，FROM表名显示变量数据SELECT变量名或常量，PRINT变量名或常量,批处理的例子,DECLARETestINT,声明变量TestTwoNVARCHAR(25)SELECTTest,TestTwoSETTest=1给变量赋值SETTestTwo=avalueSELECTTest,TestTwo显示变量的数据Go批处理结束SELECTTestasBatchTwo,TestTwo,批处理的例子（续）,DECLAREREGIONIDINT,REGIONDESCRIPTIONNCHAR(20),CONDITIONINTUSENorthwindSETCONDITION=1SELECTREGIONID=REGIONID,REGIONDESCRIPTION=REGIONDESCRIPTIONFROMREGIONWHEREREGIONID=CONDITIONSELECTREGIONID,REGIONDESCRIPTIONGO,执行批处理,可以在查询分析器中打开一个.sql脚本文件，并通过按下F5键或者选择“查询”“执行”菜单项来执行一个SQL脚本程序中的全部批处理。可以修改Windows的文件设置，以便通过双击一个.sql文件来打开查询分析器。在查询分析器中可以通过突出显示来选择一些命令，并通过按下F5键或者选择“查询”“执行”菜单项来执行这些选定的T-SQL命令。在应用程序中，可以通过使用ADO或者ODBC来提交并执行T-SQL批处理。,选择数据库,在查询分析器的工具条显示了当前的数据库，也可以使用它来改变当前的数据库。在代码中，可以使用use命令来选定当前的数据库。在批处理中使用use命令可以为它以后的语句指定数据库。,DDL命令,DDL命令不能与其他T-SQL命令放到一个批处理中。或者说，DDL命令必须单独放到自己的批处理中包括：CreateAlterdelete,执行存储过程,在SQL批处理中可以使用EXEC命令来调用存储过程语法：EXEC例子：EXECsp_help,格式化T-SQL代码,续行无需特殊的续行符号，T-SQL可自动识别语句的开始和结束注释ANSI标准注释由两个短横线打头，到行尾结束的，适合于单行注释C语言风格的注释由/*开始，由*/结束的。适合于多行注释,注释的例子,/*本程序由ABC编写用于查询地区表的数据*/SELECTREGIONDESCRIPTION-结果列FROMREGION-要查询的表WHEREREGIONID=1-条件GO批处理结束,流程控制（一）,IFIFCondition（条件表达式）Statement只能对其后的一条语句的执行与否进行控制即一个if只能控制一条命令。同时，它也没有then，以及用来结束if块的endif命令。例子IF1=0PRINTLineOnePRINTLineTwo,流程控制（二）,BEGINEND它可将多条命令作为一个整体构成if命令的下一条命令语法：IFConditionBeginMultiplelinesEnd,流程控制（三）,IFELSE与IF类似，ELSE只能对其后的一条语句或者BEGINEND块的执行与否进行控制语法：IFConditionSinglelineorbegin/endblockofcodeELSESinglelineorbegin/endblockofcode,流程控制（四）,WHILE在条件为真的情况下，WHILE命令可以循环地执行代码。与IF命令类似，WHILE命令只能循环执行其后的一条T-SQL命令。如果想循环执行一组命令，则需要使用BEGINEND块。可以使用CONTINUE命令，可以从循环体中跳回到WHILE命令，并按照正常的方式测试循环条件。可以使用break命令立即终止循环，并继续执行循环体以后的命令,流程控制（五）,DeclareTempIntSetTemp=0WhileTemp0BEGINSETGeneration=Generation+1INSERT#FamilyTree(PersonID,Generation,FamilyLine)SELECTPerson.PersonID,Generation,#FamilyTree.FamilyLine+Str(Person.PersonID,5)FROMPersonJOIN#FamilyTreeON#FamilyTree.Generation=Generation-1AND(Person.MotherID=#FamilyTree.PersonIDORPerson.FatherID=#FamilyTree.PersonID)END,显示家族谱系树,SELECTSPACE(Generation*2)+Cast(#FamilyTree.PersonIDasVARCHAR(4)+FirstName+LastName+IsNull(SrJr,)ASFamilyTreeFROM#FamilyTreeJOINPersonON#FamilyTree.PersonID=Person.PersonIDORDERBYFamilyLine,性能比较,对于具有500万行数据和12个层次的树，基于集合的方法只需要循环执行12次，并执行12个可以优化的查询。,错误处理,Error全局变量包含了代码中执行的最近一条T-SQL命令的错误状态。其中，0表示命令执行成功每一个命令都会对Error进行更新，即使测试它的值的命令都会更新它的值。为了能够得到上一次发生错误时的状态，可以将发生错误时的状态保持在一个局部变量之中，以便对其进行适当的检查和处理。,错误处理（一）,USEFamilyDECLAREerrINTUPDATEPersonSETPersonID=1WherePersonID=2SETerr=ErrorIFerr0Begin-errorhandlingcodePrinterrEnd,错误处理（二）,RowCount记录了SQL语句所操作的行数。通过RowCount可以判断SQL语句是否功地操作了数据。与Error的情况类似，每一个T-SQL命令都会改变RowCount的状态。,错误处理（三）,USEFAMILYUPDATEPersonSETLastName=JohnsonWHEREPersonID=100IFRowCount=0Begin-errorhandlingcodePrintnorowsaffectedEnd,错误处理（四）,如果批处理使用了逻辑事务（begintran/committran），错误处理程序的第一个操作就应当是回滚这个事务，这样可