《数据库》第六讲T-SQL程序设计.ppt

1、第六讲第六讲 T-SQL程序设计程序设计教学内容教学内容要求学完本章，掌握以下内容：要求学完本章，掌握以下内容：nT-SQL的变量的变量n程序流程控制程序流程控制lIF.ELSElWHILElCASE 学习目标及重点学习目标及重点学习目标学习目标n掌握在掌握在T-SQL程序设计中使用变量程序设计中使用变量n掌握流程控制语句的使用掌握流程控制语句的使用重点与难点重点与难点n如何在如何在T-SQL程序设计中使用变量程序设计中使用变量n如何使用语句控制程序的执行顺序如何使用语句控制程序的执行顺序6.1 变量变量 n变量是变量是SQL Server用来在其语句间传递数据的用来在其语句间传递数据的方式之

2、一，它由系统或用户定义并赋值。方式之一，它由系统或用户定义并赋值。nSQL Server中的变量分为局部变量和全局变量中的变量分为局部变量和全局变量两种两种n全局变量的名称以两个全局变量的名称以两个字符开始，由系统字符开始，由系统定义和维护；定义和维护；n局部变量名称以一个局部变量名称以一个字符开始，由用户自字符开始，由用户自己定义和赋值。己定义和赋值。6.1.1 全局变量全局变量nSQL Server使用全局变量来记录使用全局变量来记录SQL Server服务器的活动状态。服务器的活动状态。n全局变量是一组由全局变量是一组由SQL Server事先定义事先定义好的变量，这些变量不能由用户参与

3、定好的变量，这些变量不能由用户参与定义，是只读的。义，是只读的。几个常用的全局变量的使用方法几个常用的全局变量的使用方法n（1）ERRORl该全局变量将返回最后执行的Transact-SQL语句的错误代码，数据类型为integer。l在SQL Server执行一个Transact-SQL语句之后，如果语句成功执行，则ERROR将返回0；l如果出现一个错误，则返回一条错误信息。lERROR变量将返回这条错误信息的代码，可以从sysmessages系统表中查看有关错误信息代码的含义n例：使用例：使用ERROR变量在一个变量在一个UPDATE语句中检测限制检语句中检测限制检查冲突（错误代码为查冲突（

4、错误代码为#547）nUPDATE StudInfo Set StudSex=ll Where StudNo=99070470 nIF ERROR=547 PRINT 出现限制检查冲突，请检查需要更新的数据限制出现限制检查冲突，请检查需要更新的数据限制(2)ROWCOUNTn该变量将返回受上一条该变量将返回受上一条SQL语句影响的记录语句影响的记录数，返回的数据类型为数，返回的数据类型为integer。n任何不返回记录的语句都将这一变量设置任何不返回记录的语句都将这一变量设置为为0。n例：在例：在UPDATE语句中使用语句中使用ROWCOUNT变变量来检测是否存在发生更改的记录。量来检测是否存

5、在发生更改的记录。nUPDATE StudInfo Set StudSex=ll Where StudNo=99070470 nIF ROWCOUNT=0nPrint 警告：没有发生记录更新警告：没有发生记录更新6.1.2 局部变量局部变量n局部变量是指在局部变量是指在Transact-SQL批处理和批处理和脚本中用来保存数据值的对象。脚本中用来保存数据值的对象。n通常用于下面三种情况：通常用于下面三种情况：l作为计数器，计算循环执行的次数或控制循环执行的次数；l保存数据值以供控制流语句测试；l保存由存储过程返回代码返回的数据值。（1）声明局部变量）声明局部变量n格式：格式：DECLARE 变

6、量名称变量名称 数据类型数据类型n这个语句将会对变量执行下列操作：这个语句将会对变量执行下列操作：l为变量指派名称，名称的第一个字符必须为;l为变量指派数据类型和长度，如果是数字局部变量，那么还需要指定精度和小数位数；l将变量值设置为NULLn如：声明一个数据类型为如：声明一个数据类型为int的的temp_counter局部变局部变量量DECLARE temp_counter INTn注：使用一个注：使用一个DECLARE语句声明多个局部变量时，在语句声明多个局部变量时，在每个局部变量定义之间使用逗号分隔。每个局部变量定义之间使用逗号分隔。 Declare StudNo Varchar(15)

7、,StudScore Numeric(5,1)（2）为局部变量赋值）为局部变量赋值n通常使用通常使用SET语句为局部变量赋值，格式为：语句为局部变量赋值，格式为：SET 变量名称变量名称=变量值变量值n如：如：SET temp_counter=0n也可以使用也可以使用SELECT语句为局部变量赋值语句为局部变量赋值Select temp_counter=0n如：定义一个变量如：定义一个变量MaxScoren-声明局部变量声明局部变量nDECLARE MaxScore numeric(5,1)n-将其赋值为学号为将其赋值为学号为99070470的最高分的最高分nSELECT MaxScore=M

8、AX(StudScore) StudScoreInfo Where StudNo=20071002局部变量和全局变量示例局部变量和全局变量示例nDeclare StudName varchar(20)nDeclare Salary int,Today datetimenSet StudName=李明李明nSet Salary=50nSelect Today=Getdate()nSelect StudName,SalarynPrint TodaynSelect Rowcount,Error6.2 程序流程控制程序流程控制nTransact-SQL语言不但允许用户使用变语言不但允许用户使用变量和函

9、数，同时还提供了控制流语言的量和函数，同时还提供了控制流语言的特殊关键字来控制特殊关键字来控制Transact-SQL语句、语句、语句块和存储过程的执行过程。语句块和存储过程的执行过程。6.2.1 BEGING.END语句块语句块n语法语法BEGIN sql_statement| statement_block ENDn参数参数 sql_statement | statement_block是任何有效的是任何有效的 Transact-SQL 语句或以语句块定义的语句分语句或以语句块定义的语句分组。组。 n通常用于下列情况：通常用于下列情况：lWHILE循环需要包含多条语句lCASE函数的元素需

10、要包含多条语句lIF或ELSE子句中需要包含多条语句6.2.2 IFELSE语句语句n语法语法IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block n参数参数lBoolean_expression1、是返回 TRUE 或 FALSE 的表达式2、如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。lsql_statement | statement_blockTransact-SQL 语句或用语句块定义的语句分组。IF语句示例语句示例D

11、eclare i IntegerSet i=3IF I5 PRINT I的值大于为的值大于为5-Declare i IntegerSet i=3IF I5 PRINT I的值大于的值大于5Else PRINT I的值不大于的值不大于5IF语句示例语句示例IF Exists(Select * From StudScoreInfo Where StudScore=100) Print 有考有考100分的学生分的学生Else Print 没有考没有考100分的学生分的学生IF Exists(Select * From StudScoreInfo Where StudScore=100) begin

12、Print 有考有考100分的学生分的学生 Select * From StudScoreInfo Where StudScore=100 endElse Print 没有考没有考100分的学生分的学生IF多条件分支示例多条件分支示例Declare AvgScore numeric(5,1)Declare ScoreLevel Varchar(10)Select AvgScore=Avg(StudScore) From StudScoreInfo Where StudNo=99070470if AvgScore=90 Set ScoreLevel=优秀优秀else if AvgScore=80

13、 Set ScoreLevel=良好良好else if AvgScore=70 Set ScoreLevel=中等中等else if AvgScore=60 Set ScoreLevel=及格及格else Set ScoreLevel=不及格不及格print ScoreLevelCASE多条件分支多条件分支简单简单 CASE 函数：函数：nCASE input_expression WHEN when_expressionTHEN result_expression .n ELSE else_result_expression ENDCASE 搜索函数：搜索函数：nCASE WHEN Bool

14、ean_expression THEN result_expression .n ELSEelse_result_expression ENDCASE示例示例Declare a int,Answer Char(10)set a=cast(rand()*10 as int)print aSet Answer=Case aWhen 1 Then AWhen 2 Then BWhen 3 Then CWhen 4 Then DWhen 5 Then EELSE OthersENDPRINT The answer is +AnswerCASE示例示例Declare a int,Answer Char(

15、10)set a=cast(rand()*10 as int)print aSet Answer=CaseWhen a=1 Then AWhen a3 Then CWhen a=90 Then 优秀优秀When AvgScore=80 Then 良好良好When AvgScore=70 Then 中等中等When AvgScore=60 Then 及格及格else 不及格不及格endprint ScoreLevel6.2.3 WHILE语句语句n语法语法 WHILE Boolean_expression sql_statement | statement_block BREAK sql_sta

16、tement | statement_block CONTINUE n说明说明lBREAK导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。 lCONTINUE使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后的任何语句。WHILE示例一示例一Declare i intSet i=10while i0beginset i=i-1If i=8 continueif i=2 breakif i%2=0 print iendWhile示例二示例二Declare i int,Result intSet i=2SELECT

17、Result=5While i0Begin Set Result=Result+Power(i ,2) Set i=i-1EndPRINT Result While示例三示例三Declare i int,Result intSet i=10Set Result=4while i=0beginset i=i-2if i=2 breakif i%3=0 Set Result=Result+iendPrint Result While示例四示例四Declare I int,Result IntSet I=3Set Result=10While IResultBeginSet I=I+Result%3

18、Set Result=Result-I%4EndSet I=I+Resultprint I6.2.4 RETURN语句语句n功能功能l从查询或过程中无条件退出。lRETURN 即时且完全，可在任何时候用于从过程、批处理或语句块中退出。l不执行位于 RETURN 之后的语句。n语法语法RETURN integer_expression n参数参数linteger_expressionl是返回的整型值。存储过程可以给调用过程或应用程序返回整型值。l返回类型l可以选择是否返回 int。RETURN ExampleCreate Procedure CheckScore AsIf exists(Sele

19、ct * From StudScoreInfo Where StudScore=100)return (Select Count(*) From StudScoreInfo Where StudScore=100)else return 0GoDeclare Count intExec Count=CheckScoreprint Count6.2.5 WAITFOR语句语句n功能：指定触发语句块、存储过程或事务执行的时功能：指定触发语句块、存储过程或事务执行的时间、间、 时间间隔或事件。时间间隔或事件。n语法语法WAITFOR DELAY time | TIME time n参数参数lDELA

20、Y指示 SQL Server 一直等到指定的时间过去，最长可达 24 小时。ltime要等待的时间。可以按 datetime 数据可接受的格式指定 time，也可以用局部变量指定此参数。不能指定日期。因此，在 datetime 值中不允许有日期部分。lTIME：指示 SQL Server 等待到指定时间。 WAITFOR ExampleDeclare Count intSet Count=0While CountAvgScore查询大于课程平均分的学生成绩信息查询大于课程平均分的学生成绩信息nSelect S.* From scoreInfo S,courseInfo C Where S.courseID=C.courseID And CourseName=嵌入式系统 And score(Select Avg(score) From scoreInfo S,courseInfo C Where S.courseID=C.courseID And courseName=嵌入式系统)插入记录插入记录nDeclare StudNo char(10),StudName char(30)nSet StudNo=(Select Max(stuID)+1 From stuInfo)nSe