第8章 T-SQL编程(基础与函数).ppt

第八章T-SQL编程,TRANSACTSQL,Transact-SQL简写为T-SQL。T-SQL是SQL语言的一种版本，且只能在SQLSERVER上使用。它是ANSISQL的加强版语言、提供了标准的SQL命令。另外，T-SQL还对SQL做了许多补允，提供了类似C、Basic和Pascal的基本功能，如变量说明、流控制语言、功能函数等。,T-SQL基础,标识符,标识符是指用户在SQLServer中定义的服务器、数据库、数据库对象、变量等对象的名称。SQLServer标识符分为常规标识符和分隔标识符。,标识符,1.常规标识符常规标识符应遵守以下命名规则：(1)标识符长度可以为1128个字符。对于本地临时表，标识符长度最多可以有116个字符。(2)标识符的首字符必须为Unicode3.2标准所定义的字母或_、#(3)标识符首字符后面的后续字符为Unicode3.2标准中所定义的字母、数字或、#、$、_(4)标识符内不允许嵌入空格或其他特殊字符。(5)标识符不能与SQLServer中的保留关键字同名。,例如：SELECT*FROMteacher其中teacher即为常规标识符,标识符,2.分隔标识符分隔标识符允许在标识符中使用SQLServer保留关键字或常规标识符中不允许使用的一些特殊字符。分隔标识符是由双引号或方括号分隔符进行分隔的标识符。,标识符,2.分隔标识符,例1：CreatedatabaseMyDB由于数据库名MyDB中包含空格，所有用方括号分隔例2：Createtabletable(column1char(10)primarykey,column2int)由于所创建的表名table与SQL的保留关键字相同，故需要加方括号来分隔,标识符,批处理,多条语句放在一起依次执行，称为批处理执行，批处理语句之间用GO分隔。,例如：UseteachingGoCreateviewvw_teacherAsselect*fromteacherGoSelect*fromvw_teacherGo,注意：Go不是T-sql语句，不能和T-sql语句在同一行。我们将go解释为应该向SQLserver实例发送当前批处理语句的信号。当前批处理由上一个go命令后的所有语句组成。,使用批处理时有如下限制：（1）CHECK约束不能在同一个批处理中既定义又使用（2）在同一个批处理中不能删除对象又重新创建该对象。（3）使用SET语句改变的选项在批处理结束时生效（4）不能在同一个批处理中更改表，然后引用新列。,批处理,注释,用来对某些sql语句及存储过程做注释，注释的长度没有限制。1、”-”,注释一行语句。2、”/*/”,注释多行语句，可以换行。,语法格式如下：/*/,例：P179【任务1-3】,1.Print语句,如：print你好!printname,输出语句,PRINTmsg_str|local_variable|string_exprmsg_str：字符串或Unicode字符串常量。local_variable：任何有效的字符型变量。注意：local_variable的数据类型必须为char、nchar、varchar或nvarchar，或者必须能够隐式转换为这些数据类型。string_expr：返回字符串的表达式。,2、raiserror:返回用户定义的错误信息。,RAISERROR(msg_id|msg_str,severity,state),msg_id：存储于sys.messages视图中的用户定义的错误信息号。用户定义错误消息的错误号应当大于50000。msg_str：是一个用户定义消息，该错误消息最长可以有2047个字符；当指定msg_str时，RAISERROR将引发一个错误号为50000的错误消息。severity：用户定义的与该消息关联的严重级别。任何用户都可以指定0到18之间的严重级别。state：介于1至127之间的任意整数（state默认值为1）。如果在多个位置引发相同的用户定义错误，则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。,2、raiserror:返回用户定义的错误信息。,RAISERROR(msg_id|msg_str,severity,state),例如：PrintHelloWorld！Raiserror(发生错误,16,1),T-SQL变量和运算符,变量,变量是SQLServer用来在语句之间传递数据的方式之一，由系统或用户定义并赋值。SQLServer中的变量分为局部变量和全局变量。全局变量：由系统定义和维护，名称以字符开始。局部变量：由用户定义和赋值，名称以字符开始,局部变量定义,declare语句用来定义一个或多个局部变量，这个变量必须以开头，跟着一个标识符。,语法格式如下：declarevariable_namedatatype,variable_namedatatype,例：declareaint，bchar,局部变量赋值,在使用DECLARE声明变量后，所有的变量都会被赋上一个初值NULL，需要使用SET或者SELECT语句为其赋值。,语法格式如下：setv_name1=valueselectv_name1=value,v_name1=valueFROMWHERE,局部变量,例：分别使用set和select子句为变量a和b赋值1234，abcd。,declareaint,bchar(4)/*seta=1234setb=abcd*/selecta=1234,b=abcdprintaprintb,全局变量,使用全局变量应注意以下几点：1.全局变量不是由用户的程序定义的，它们是定义在服务器级的。2.用户只能使用预先定义的全局变量3.全局变量，必须以开头4.局部变量的名称不能与全局变量的名称相同。,全局变量,常用的全局变量有：error：error返回最后执行的T-SQL语句的错误代码(integer)(注：0表示正常；)rowcount：返回受上一语句影响的行数，除了declare语句外，其他任何语句都可以改变其值。,例：了解error和rowcount的用法updatecustomerssetc_gender=nullwherec_id=c0001iferror=515print错误：违反非空约束ifrowcount=0print警告：没有数据被更新,例：在teaching数据库，使用变量查找成绩大于70的学生学号、姓名、课程名、成绩。并使用全局变量查看是否正确执行。,declarefsdecimal(3,1)Setfs=70selectsno,sname,cname,markFo=oWheremarkfsprinterror,练习：使用WebShop数据库,1.声明变量birthday存储出生日期，age存储年龄；为所声明的birthday变量赋值为“1999-4-14”（使用SET语句）；将customers表中的会员的最大年龄赋值给变量age（使用select语句）；输出birthday、age的值。,答案：,1.declarebirthdaydatetime,ageintsetbirthday=1999-4-14usewebshopselectage=max(year(getdate()-year(c_birth)fromcustomersprint-变量的输出结果-printbirthday的值为：printbirthdayprint最大年龄为：printage,运算符,1.常用运算符P1822.运算符优先级P184,程序控制语句,程序块语句begin.end语句,BEGINEND用来设置一个程序块，该程序块可以被视为一个单元执行。BEGINEND经常在条件语句中使用，如IFELSE语句。如果当IF或ELSE子句为真时，想让程序执行其后的多条语句，这时就要把这多条语句用BEGINEND括起来使之成为一个语句块。在BEGINEND语句中可以嵌套另外的BEGINEND语句来定义另一程序块。,语法格式如下：Begin代码End,判断语句ifelse语句,IFELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，IF或ELSE只能执行一条命令。IFELSE可以嵌套使用，最多可嵌套32级。,语法格式如下：if代码1else代码2,例：从student1表中，查询学号为9704学生的成绩状况，若全部90分以上显示“该学生成绩全部优秀！”若全部60分以上显示“该学生成绩全部合格！”否则显示“该学生有的成绩不合格！”并且要显示最低分。,DeclareverygoodintSelectverygood=min(mark)Fromstudent1Wheresno=9704Ifverygood=90Print该学生成绩全部优秀！elseIfverygood=60Print该学生成绩全部合格！elseprint存在不及格科目！Selectverygoodas最低分,Print最低分：+cast(verygoodaschar(4),或者,If(Selectmin(mark)Fromstudent1Wheresno=9704)=90Print该学生成绩全部优秀！elseIf(Selectmin(mark)Fromstudent1Wheresno=9704)=60Print该学生成绩全部合格！elseprint存在不及格科目！Selectmin(mark)as最低分Fromstudent1Wheresno=9704,CASE语句,语法如下：CASEWHEN条件表达式0THEN结果表达式0,nELSE结果表达式nEND,CASE语句,例：Declare成绩级别char(4),分数intSet分数=88Set成绩级别=CaseWhen分数=90and分数=80and分数=70and分数=60and分数=70then及格When分数=90andmark=80andmark=70andmark=60andmark=2000andg_price=0Beginseti=i+1ifi100beginprint0100之间的奇数和：+cast(sumaschar(10)breakendifi%2!=0beginsetsum=sum+iendelsebegincontinueendend,练习：在student1表中，学号为9705学生的平均成绩如果小于75，则将该学生的每门成绩以5%的比例提高，当平均成绩大于等于75或者所有课程都及格时，终止操作。,While(selectavg(mark)fromstudent1wheresno=9705)=60breakend,错误处理语句,T-SQL语句组可以包含在TRY块中。如果在TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。,语法格式如下：BEGINTRYENDTRYBEGINCATCHENDCATCH,例：使用TRY-CATCH捕捉SQL语句执行过程中的异常,BEGINTRYSELECT1/0ENDTRYBEGINCATCHSELECTERROR_NUMBER()AS错误号,ERROR_SEVERITY()AS错误等级,ERROR_STATE()AS错误状态,ERROR_PROCEDURE()AS错误过程,ERROR_LINE()AS错误行,ERROR_MESSAGE()AS错误信息ENDCATCH,系统函数：ERROR_NUMBER()：返回错误的错误号。ERROR_SEVERITY()：在CATCH块中调用时，返回导致CATCH块运行的错误消息的严重级别。ERROR_STATE()：当在CATCH块中调用时，返回导致CATCH块运行的错误消息的状态号。ERROR_PROCEDURE()：在CATCH块中调用时，返回出现错误的存储过程名称。ERROR_LINE()：当在CATCH块中调用时，返回发生错误的行号。ERROR_MESSAGE()：在CATCH块中调用时，返回导致CATCH块运行的错误消息的完整文本,跳转语句goto语句,使用GOTO将执行控制从代码中的一个位置转移至用户定义标签之后的另一位置。标签是带冒号的文本，用于标识转移执行控制的目标位置。可在过程、批或语句块的任何位置使用GOTO语句和标签，但不能转至GOTO命令所在批之外的标签。,语法格式如下：label1:gotolabel1,例：,DeclarecountsmallintSetcount=1Restart:printhello!Setcount=count+1Whilecount=4Gotorestart,延期执行语句waitfor语句,waitfor语句用来定义某天中的某一个时刻，执行一个语句块，常用于定时备份数据库，定时显示数据等操作。,语法格式如下：waitfordelaytime|timetime,说明：1、delay后面的time说明了sqlserver为了执行语句要等待的时间。2、Time后面的time说明了sqlserver要等到哪个时