第8章 T-SQL编程.ppt

第8章Transact-SQL编程,8.1T-SQL程序设计基础8.2函数8.3存储过程8.4触发器8.5游标的定义与使用,T-SQL简介,T-SQL语言是对SQL-92标准的扩展，具有SQL的主要特点，同时：,扩充了数据类型；增加了变量、常量、运算符、函数、流程控制和注释等语言元素；对标准SQL的存储过程、触发器和游标等机制进行了修改。,8.1T-SQL程序设计基础,8.1.1数据类型系统数据类型,8.1T-SQL程序设计基础,8.1.1数据类型系统数据类型,用户自定义数据类型建立在系统数据类型基础上；自定义数据类型时，需指定类型名称、建立在其上的系统数据类型以及是否允许为空；可使用SQLServerManagementStudio和调用系统存储过程sp_addtype创建用户自定义数据类型。,8.1T-SQL程序设计基础,8.1.1数据类型用户自定义数据类型,例：在XSGL数据库中创建自定义类型birth_date。,8.1T-SQL程序设计基础,8.1.1数据类型用户自定义数据类型,USEXSGLEXECsp_addtypebirth_datesmalldatetime,notnull,8.1T-SQL程序设计基础,8.1.2常量与变量常量,字符串常量男maleUnicode字符串常量Nfemale日期时间常量December5,201214:30:24,数字常量10001000.001货币型常量$1000￥1000,8.1T-SQL程序设计基础,8.1.2常量与变量局部变量,定义形式：DECLARElocal_variabledata_type,n局部变量不能定义为text、ntext或image数据类型。,由用户定义和使用，用来存储从表中查询到的数据，或在程序执行过程中暂存数据；仅在声明它的批处理、存储过程或触发器中有效；,定义变量时，其值为NULL，不能使用“变量=变量值”的格式进行初始化；局部变量的赋值：SET=或：SELECTlocal_variable=expression局部变量的输出SELECT,8.1T-SQL程序设计基础,8.1.2常量与变量局部变量,例：局部变量的定义与赋值。,8.1T-SQL程序设计基础,8.1.2常量与变量局部变量,DECLAREmyvar1char(20)，myvar2intSETmyvar1=ThisisatestSETmyvar2=1SELECTmyvar，myvar2,由系统定义和维护，是SQLServer系统内部使用的变量，通常存储一些系统配置设定值和统计数据；用户可以在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值；引用全局变量时，必须以标记符“”开头；局部变量的名称不能与全局变量的名称相同。,8.1T-SQL程序设计基础,8.1.2常量与变量全局变量,例：删除学生管理数据库XSGL中选课表sc中成绩为空的记录，并统计删除的行数。,8.1T-SQL程序设计基础,8.1.2常量与变量全局变量,USEXSGLDELETEFROMscWHEREGRADEISNULLSELECTROWCOUNTAS删除行数,算术运算符（+、*、/、%）赋值运算符（=）位运算符（&、|、）比较运算符（=，=，!=，!，!；）逻辑运算符（NOT、AND、OR）字符串串联运算符（+）,8.1T-SQL程序设计基础,8.1.3运算符,例：declareyvarchar(10),zvarchar(100)sety=需要setz=y+帮助printz,输出结果为：需要帮助,8.1T-SQL程序设计基础,8.1.3运算符,批处理是一组SQL语句的集合，以GO结束。批处理中的所有语句被一次提交给SQLServer2005编译为一个执行单元（执行计划）。在一个批处理中，如果出现编译错误，SQLServer2005将取消整个批处理内所有语句的执行。,8.1T-SQL程序设计基础,8.1.4批处理,例：GO语句的使用。,8.1T-SQL程序设计基础,8.1.4批处理,USEXSGLGODECLAREMyMsgVARCHAR(50)SELECTMyMsg=Hello,World.GO,行注释-注释内容块注释/*注释内容*/,8.1T-SQL程序设计基础,8.1.5注释,8.1T-SQL程序设计基础,8.1.6流程控制语句定义语句块,BEGINsql_statement|statement_blockEND,(1)IFELSE条件语句,8.1T-SQL程序设计基础,8.1.6流程控制语句选择结构,IFBoolean_expressionsql_statement|statement_blockELSEsql_statement|statement_block,例：针对XSGL数据库，在选课表sc中查找课程号为214181这门课是否有人选修。,(1)IFELSE条件语句,8.1T-SQL程序设计基础,8.1.6流程控制语句选择结构,USEXSGLGOIFEXISTS(SELECT*FROMscWHERECNO=214181)PRINT214181号课程已有人选ELSEPRINT214181号课程还没有人选,(2)CASE语句-简单CASE格式,8.1T-SQL程序设计基础,8.1.6流程控制语句选择结构,将某个表达式与一组简单表达式进行比较以确定结果。,CASEinput_expressionWHENwhen_expressionTHENresult_expressionnELSEelse_result_expressionEND,SELECTsno,age=CASEageWHEN19THENage+10WHEN20THENage+100ELSEage-10END,snameFROMstudent,8.1T-SQL程序设计基础,(2)CASE语句-简单CASE格式,8.1.6流程控制语句选择结构,CASEWHENBoolean_expression1THENresult_expression1WHENBoolean_expression2THENresult_expression2nELSEelse_result_expressionEND,(2)CASE语句搜索CASE格式,8.1T-SQL程序设计基础,8.1.6流程控制语句选择结构,计算一组布尔表达式以确定结果。,SELECTSNO,CNO,成绩=CASEWHENGRADE(SELECTAVG(GRADE)FROMsc)THEN高分ELSE平均分ENDFROMsc,8.1T-SQL程序设计基础,(2)CASE语句搜索CASE格式,8.1.6流程控制语句选择结构,WHILEBoolean_expressionBEGINsql_statement|statement_blockBREAKCONTINUEsql_statement|statement_blockEND,8.1T-SQL程序设计基础,8.1.6流程控制语句循环结构,例：计算1+2+3+100的和。,8.1T-SQL程序设计基础,8.1.6流程控制语句循环结构,DECLAREMyvalINT,MyresultINTSELECTMyval=1,Myresult=0WHILEMyval=100SELECTMyresult=Myresult+Myval,Myval=Myval+1PRINTMyresult,GOTOlabellabel:,8.1T-SQL程序设计基础,8.1.6流程控制语句GOTO语句,例：计算1+2+3+100的和。,DECLAREMyvalINT,MyresultINTSELECTMyval=1,Myresult=0MYLOOP:SETMyresult=Myresult+MyvalSETMyval=Myval+1IFMyval=5)BEGINROLLBACKPRINT超过4门课了，不能再选END,DDL触发器一般用于以下目的：,8.4.3DDL触发器的创建和应用,8.4触发器,防止对数据库架构进行某些更改；在数据库中发生某些情况时，响应数据库架构中的修改；要记录数据库架构中的更改或事件。,例：使用DDL触发器来防止数据库中的任一表被修改或删除。,8.4.3DDL触发器的创建和应用,8.4触发器,CREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINTYoumustdisableTriggersafetytodroporaltertables!ROLLBACK,8.5游标的定义与使用,游标提供了一种从包括多条数据记录的结果集中每次提取一条记录的机制，游标可看做一个指针，与一个查询结果集相关联。游标分为T-SQL游标、应用程序编程接口服务器游标和客户端游标。,DECLAREcursor_nameSCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_name,n,8.5.1声明游标,8.5游标的定义与使用,例：定义标准游标,DECLAREcursor1CURSORFORSELECT*FROMstudent,DECLAREcursor2CURSORFORSELECT*FROMstudentFORREADONLY,例：定义只读游标,OPENGLOBALcursor_name|cursor_variable_name,8.5.2打开游标,8.5游标的定义与使用,打开游标后，可以使用全局变量ERROR判断该游标是否打开成功。ERROR为0则打开成功，否则打开失败。,FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,n,8.5.3检索游标,8.5游标的定义与使用,例：,8.5.3检索游标,8.5游标的定义与使用,DECLARECURCURSORSCROLLFORSELECT*FROMSTUDENTOPENCURFETCHNEXTFROMCURFETCHABSOLUTE3FROMCURFETCHRELATIVE-2FROMCURFETCHPRIORFROMCURFETCHLASTFROMCUR,DEALLOCATEGLOBALcursor_name|cursor_variable_name,8.5.5释放游标,8.5游标的定义与使用,利用游标修改数据时，首先在定义游标时要指明修改的字段，然后在update语句中指定通过游标修改。,8.5.6利用游标修改数据,CLOSEGLOBALcursor_name|cursor_variable_name,8.5.4关闭游标,例：在学生管理数据库XSGL中声明游标，给选修了“通信原理”课程并且成绩低于60分的同学加10分，并统计有多少个学生获得了加分。,8.5.7综合实例,8.5游标的定义与使用,DECLAREcountintSETcount=0DECLAREcurCURSORSCROLLFORSELECT*FROMsc,co