第5章_T-SQL程序设计.ppt

第5章T-SQL,第5章T-SQL程序设计,5.1概述5.2数据类型、变量、及函数5.3批处理、流控制语句5.4游标5.5存储过程5.6用户定义数据类型与自定义函数5.7触发器,5.1T-SQL概述,T-SQL(Transact-Sql)：MSSQLServer在标准SQL基础上添加程序设计语言的增强版，用来让应用程式与SQLServer沟通的主要语言。T-SQL提供标准SQL的DDL和DML功能，以及函数、系统存储过程以及程序设计结构(例如IF和WHILE)让程式设计更有弹性。,5.2数据类型与变量,5.2.1数据类型5.2.2变量5.2.3函数,5.2.1数据类型,在SQLServer中，每个列、局部变量、表达式和参数都有其各自的数据类型，指定对象的数据类型相当于定义了该对象的下列特性：对象所含的数据类型，如字符、整数、二进制数；所存储值的长度或大小；数值精度、小数位数（仅用于数值数据类型）系统数据类型：SQLServer提供系统数据类型集，定义可供使用的所有数据类型用户自定义数据类型：根据需要，依据系统基本数据类型定义,系统数据类型,在SQLServer中，存在两类变量全局变量：由系统定义和维护，变量名前加“”，如：VERSION局部变量：由用户定义和使用，仅在声明它的批处理、存储过程或触发器中有效。变量名前加“”，如：mystr,5.2.2变量,1、局部变量,局部变量声明，格式：DECLARE变量名程数据类型,n可一次声明多个变量例：DECLAREACHAR(20)DECLAREffloat，cnCHAR(8),局部变量赋值格式1：SET/SELECT变量名=表达式格式2：SELECT变量名=表达式FROMtable_name,.nWHEREclause,.n例：DECLAREmystrchar(30)SET/SELECTmystr=ThisismytestPrintmystr,1、局部变量（续）,例：DECLAREmystrchar(30)SET/SELECTmystr=ThisismytestPrintmystrDECLAREmystrchar(30)SELECTmystr=Thisismytestselectmystrasmyvar,Thisismytest,Print与select的区别,1、局部变量（续）,例1：将常量直接赋值给对应的变量UseschoolDeclareffloat,cnchar(6)Setf=85Selectcn=c01Select*fromscwherecno=cnandgradef使用两个变量查询SC表中选修C01课程且成绩大于85分的,1、局部变量（续）,例2：查询语句中为变量赋值UseschoolDeclarenochar(6),namechar(8)Selectno=sno,name=snamefromswheresdept=ISPrintno+name注：如果select语句返回多个值，则将最后一个值赋予变量，如果select语句没有返回行，变量将保留当前值（常需要结合orderby子句）,95004张立,1、局部变量（续）,例3：使用排序规则UseschoolDeclarenochar(6),namechar(8)Selectno=S.sno,name=snamefromsjoinsconS.sno=SC.snoOrderbygradedescPrintno+name,95002刘晨,1、局部变量（续）,例4：使用聚合函数UseschoolDeclarefint,cnchar(6)Setcn=c01Selectf=max(grade)fromscwherecno=cnPrint最高分Printf思考：使用排序规则？,最高分92,1、局部变量（续）,2、全局变量,由系统提供且预先声明的变量用户只能引用；引用全局变量时，变量名前一定要加上标记；用户不能定义和全局变量同名的局部变量，否则将产生错误。,例：SELECTROWCOUNT:前一条命令处理的行数SELECTVERSION：返回MicrosoftSQLServer当前安装的日期、版本和处理器类型SELECTCONNECTIONS：返回自上次启动MicrosoftSQLServer以来连接或试图连接的次数。,2、全局变量（续）,2、全局变量（续）,分析：SELECTGETDATE()AS日期及时间SELECTCONnECTIONSAS目前的联结次数SELECTSERVERNAMEas服务器名SELECTVERSIONas版本号,显示到当前日期和时间为止试图登录的次数。,SQLServer包含多种不同的函数用以完成各种工作函数是由一个或多个T-SQL语句组成的子程序，可用于封装代码以便重读使用。每个函数都有一个名称，后跟小括号，括号中通常需要一个或多个参数。内置函数：由系统提供用户自定义函数：用户根据需要自行定义,5.2.3函数,内置函数,SQLServer2005提供了丰富的具有执行某项运算功能的内置函数，分为12类。,5.3批处理、控制流语句,5.3.1批处理5.3.2控制流语句,5.3.1批处理,批处理：包含一个或多个T-SQL语句的组，从应用程序一次行地发送到SQLServer执行，SQLServer将批处理语句编译成一个可执行单元。GO语句是一个批处理的结束语句用户自定义的局部变量的作用域限制在一个批处理中如果批处理中有10条语句，如果第5条语句有语法错误，则不执行批处理中的任何语句；如果编译了批处理，第2条语句执行失败，第1条语句不受影响（在一个事务中则除外）,UseschoolGODeclarenamechar(6)Selectname=snamefromsWheresno=95001GOPrintnameGO思考：1）有几个批处理2）程序会出什么错误？,5.3.1批处理（续）,5.3控制流语句,T-SQL提供称为控制流的特殊关键字，用于控制T-SQL语句、批处理和存储过程的执行流控制流语句：用来控制程序执行流程，提高编程语言的处理能力,BEGINEND语句：将多条T-SQL语句封装成为一个整体的语句块，该逻辑块作为一个整体被执行命令格式：BEGINT-SQL语句块END什么情况下用？while、case、if-else,1、BEGINEND语句,例：Begindeclaremyvarfloatsetmyvar=456.256beginprint变量myvar的值为：printCAST（myvarasvarchar(12）endend,变量myvar的值为：456.256,CAST（）系统函数，转换数据类型,1、BEGINEND语句,2、IFELSE语句,语法格式:IF布尔表达式T-SQL语句|语句块ELSEIF布尔表达式T-SQL语句|语句块在IF语句中，要求只能有一条语句用Begin-End可将多条语句视做逻辑上的一条语句IF和ELSE后面的子句都允许嵌套，且嵌套层次不受限制,If(selectavg(grade)fromscwherecno=c01)80beginprint课程：c01Print考试成绩良好endElsebeginprint课程：c01Print考试成绩一般Endgo,2、IFELSE语句（续）,课程：C01考试成绩良好,例：判断学生C01课程的成绩,2、IFELSE语句（续）,实例：usemasterIF(SELECTMAX(id)FROMsysobjects)50andtype=U,注:sysobjects系统表，在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行,IFEXISTS语句,用于检测数据是否存在，不考虑行数。IFEXISTS(查询语句)T-SQL语句|语句块ELSET-SQL语句|语句块,3、IFEXISTS语句,3、IFEXISTS语句（续）,useschoolIFEXISTS(SELECT*FROMsWHERESno=95008)PRINT该学生存在ELSEPRINT该学生不存在,该学生不存在,例：判断学号为95008的学生是否存在,4、Case表达式,Case是表达式，可进行多个分支的选择，允许按列值显示可选值CASE具有两种格式：简单CASE函数：将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索函数：计算一组布尔表达式以确定结果。,格式：CASEWHENTHENELSEEND当input_expr=when_expr的取值为“真”时，返回result_expr，否则返回else_result_expr如果没有else子句，则返回空值,4、Case表达式（续）,1）简单Case表达式,useschoolselecttnameas姓名,departas姓名,caseprofwhen教授then高级职称when讲师then中级职称when助教then初级职称endas职称类型fromt,1）简单Case表达式,例：检索判断教师是初、中、高级职称,格式CASEWHENTHENWHENTHENELSEEND搜索型Case的执行方式：当布尔表达式为“真”时，则返回后面的Result_exp表达式，然后跳出case语句；否则急需测试下一个WHEN后面的布尔表达式,2）搜索型Case表达式,4、Case表达式（续）,例：useschoolselecttnameas姓名,departas姓名,casewhenprof=教授then高级职称whenprof=讲师then中级职称whenprof=助教then初级职称endas职称类型fromt,2）搜索型Case表达式,useschoolselectsnoas学号,cnoas课程号,casewhengrade=90then优秀whengrade=80then良好whengrade=70then中等whengrade=60then及格whengrade=70andgrade80,2）搜索型Case表达式,例：检索判断学生成绩等级,5、While语句,设置重复执行T-SQL语句或语句块的条件。当条件为“真”时，重复执行T-SQL语句或语句块可使用Break和Continue关键字在循环体内部控制While语句的执行,语法格式：While布尔表达式T-SQL语句|语句块【Break从最内层的循环退出，执行end后面的语句T-SQL语句|语句块Continue是循环从新开始，忽略后面的任何语句在While语句中，要求只能有一条语句或语句块（begin-end），end关键字为循环结束的标志,5、While语句（续）,例：1100求和Declaresint,iintseti=0sets=0Whilei100BEGINsets=s+iseti=i+1ENDPrint1+2+100=+CAST(saschar(25),1+2+100=4950,5、While语句（续）,declarenasintsetn=0While(Selectmin(grade)FromSC)60BEGINUPDATEscSETgrade=grade+1wheregrade60setn=n+1ENDselect*fromscselectnas循环次数,5、While语句（续）,例：将成绩60分以下的成绩提高到60分,Break&continue,Break:终止while循环,接着执行在END关键字后面的第一条语句，END关键字为循环结束标记。Continue:结束本次循环，使WHILE循环重新开始执行,忽略CONTINUE关键字后的任何语句。,5、While语句（续）,USEschoolGOWhile(SelectAVG(grade)FROMSC)=100BREAKELSECONTINUEENDselect成绩超出范围！,5、While语句（续）,6、GOTO语句:无条件跳转,例：Declaresint,iintseti=0sets=0my_loop:sets=s+iseti=i+1ifi100gotomy_loopPrint1+2+100=+CAST(saschar(25),格式：GOTOlable,例：DeclarecounterintSetcounter=2Whilecounter=10Beginifcounter=4goto标签selectpower(counter,2)标签:setcounter=counter+1end,POWER函数返回指定数值表达式的指定幂的值。POWER(2,3),6、GOTO语句（续）,指定触发语句块、存储过程或事务执行的时间、时间间隔或事件使用WAITFOR语句，可以在指定时间或者过了一段时间后，执行语句块、存储过程或事务格式：WAITFORDELAY时间|TIME时刻,7、WAITFOR语句,7、WAITFOR语句（续）,例：DeclarecounterintSetcounter=2waitfortime0:04Whilecounter60groupbys.sno,sname,1、Insert触发器（续）,例：建立一个触发器，当选修表SC中增加一条记录时，修改该学生的总学分,2）创建触发器CREATETRIGGERtrig_update_creditONSCFORINSERTASBegindeclarethe_creditintselectthe_credit=ccreditfromCjoininsertedonC.cno=oUpdatestu_ctSetxuefen=xuefen+the_creditWheresno=(selectsnofrominserted)End,1、Insert触发器（续）,例：建立一个触发器trig_name，当向学生表S中插入数据时，如果出现姓名重复的情况，则回滚该事务,CREATETRIGGERtrig_nameONSafterinsertASBegindeclarenamechar(8)selectname=inserted.snamefromsjoininsertedonS.sno=inserted.snoifexists(select*fromSwheresname=name)beginraiserror(姓名重复，不能插入,16,1)rollbackendEnd,思考：在创建触发器之前存在重名的学生，是否能创建上述触发器？,1、Insert触发器（续）,在创建触发器后，则不能插入重名的学生记录。,1、Insert触发器（续）,RAISERROR：返回用户定义的错误信息并设系统标志，记录发生错误。这条消息在定义后就作为服务器错误信息返回给客户端。,语法RAISERROR(错误提示,严重级别,状态）,消息50000，级别16，状态1，过程trig_name，第11行姓名重复，不能插入消息3609，级别16，状态1，第1行事务在触发器中结束。批处理已中止,DELETEloanWHEREisbn=4ANDcopy_no=1,UPDATEcSETon_loan=NFROMcopycINNERJOINdeleteddONc.isbn=d.isbnANDc.copy_no=d.copy.no,2、delete触发器,2、delete触发器,例：建立delete触发器tric_del,该触发器防止删除学生表S中IS系的学生记录,CREATETRIGGERtrig_delONSafterdeleteASBeginifexists(select*fromdeletedwheresdept=is)beginraiserror(不能删除该系的记录,16,2)rollbackendEnd,UPDATEStatementtoatablewithanUPDATETriggerDefined,UPDATEmemberSETmember_no=10021WHEREmember_no=1234,UPDATEStatementloggedasINSERTandDELETEStatements,3、update触发器,例：建立一个触发器Tric_upd,当修改学生表中的学号时，同时修改该同学选课记录中的学号,3、update触发器(续）,CREATETRIGGERTrgUpdONSAfterUPDATEASupdateSCsetSC.sno=inserted.snofromSC,inserted,deletedwhereSC.sno=deleted.sno,3、update触发器（续）,修改前数据：,修改后数据：,insteadof触发器是代替触发操作执行一个表或视图可以创建After触发器，但只能创建一个insteadof触发器,4、insteadof触发器,CREATETRIGGERTrig2ONS1insteadofinsertASselect*fromS1,inserts1values(95007,李林,男,18,cs),CREATETRIGGERTrig3ONS1insteadofupdateASraiserror(不能对S1表修改,16,2),消息50000，级别16，状态2，过程Trig3，第4行不能对S1表进行修改,updates1setsdept=ISwheresno=95007,4、insteadof触发器,insteadof触发器是代替触发操作执行,5、DDL触发器,