《T-SQL编程(基础与函数).ppt》由会员分享，可在线阅读，更多相关《T-SQL编程(基础与函数).ppt（54页珍藏版）》请在三一办公上搜索。
1、第八章 T-SQL编程,TRANSACT SQL,Transact-SQL简写为T-SQL。T-SQL是SQL语言的一种版本，且只能在SQL SERVER上使用。它是ANSI SQL的加强版语言、提供了标准的SQL命令。另外，T-SQL还对SQL做了许多补允，提供了类似C、Basic和Pascal的基本功能，如变量说明、流控制语言、功能函数等。,T-SQL基础,标识符,标识符是指用户在SQL Server中定义的服务器、数据库、数据库对象、变量等对象的名称。SQL Server标识符分为常规标识符和分隔标识符。,标识符,1.常规标识符常规标识符应遵守以下命名规则：(1)标识符长度可以为1128
2、个字符。对于本地临时表，标识符长度最多可以有116个字符。(2)标识符的首字符必须为Unicode 3.2标准所定义的字母或_、#(3)标识符首字符后面的后续字符为 Unicode 3.2标准中所定义的字母、数字或、#、$、_(4)标识符内不允许嵌入空格或其他特殊字符。(5)标识符不能与SQL Server中的保留关键字同名。,例如：SELECT*FROM teacher其中teacher即为常规标识符,标识符,2.分隔标识符分隔标识符允许在标识符中使用SQL Server保留关键字或常规标识符中不允许使用的一些特殊字符。分隔标识符是由双引号或方括号分隔符进行分隔的标识符。,标识符,2.分隔标
3、识符,例1：Create database My DB由于数据库名My DB中包含空格，所有用方括号分隔例2：Create table table(column1 char(10)primary key,column2 int)由于所创建的表名table与SQL的保留关键字相同，故需要加方括号来分隔,标识符,批处理,多条语句放在一起依次执行，称为批处理执行，批处理语句之间用GO分隔。,例如：Use teaching Go Create view vw_teacher As select*from teacher Go Select*from vw_teacher Go,注意：Go不是T-sql
4、语句，不能和T-sql语句在同一行。我们将go 解释为应该向SQL server实例发送当前批处理语句的信号。当前批处理由上一个go命令后的所有语句组成。,使用批处理时有如下限制：（1）CHECK约束不能在同一个批处理中既定义又使用（2）在同一个批处理中不能删除对象又重新创建该对象。（3）使用SET语句改变的选项在批处理结束时生效（4）不能在同一个批处理中更改表，然后引用新列。,批处理,注释,用来对某些sql语句及存储过程做注释，注释的长度没有限制。1、”-”,注释一行语句。2、”/*/”,注释多行语句，可以换行。,语法格式如下：/*/,例：P179【任务1-3】,1.Print语句,如：pr
5、int 你好!print name,输出语句,PRINT msg_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视图
6、中的用户定义的错误信息号。用户定义错误消息的错误号应当大于 50000。msg_str：是一个用户定义消息，该错误消息最长可以有 2047 个字符；当指定 msg_str 时，RAISERROR 将引发一个错误号为 50000 的错误消息。severity：用户定义的与该消息关联的严重级别。任何用户都可以指定 0 到 18 之间的严重级别。state：介于 1 至 127 之间的任意整数（state 默认值为1）。如果在多个位置引发相同的用户定义错误，则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。,2、raiserror:返回用户定义的错误信息。,RAISERROR(msg_id|
7、msg_str,severity,state),例如：Print Hello World！Raiserror(发生错误,16,1),T-SQL变量和运算符,变量,变量是SQL Server用来在语句之间传递数据的方式之一，由系统或用户定义并赋值。SQL Server中的变量分为局部变量和全局变量。全局变量：由系统定义和维护，名称以字符开始。局部变量：由用户定义和赋值，名称以字符开始,局部变量定义,declare语句用来定义一个或多个局部变量，这个变量必须以开头，跟着一个标识符。,语法格式如下：declare variable_name datatype,variable_name dataty
8、pe,例：declare a int，b char,局部变量赋值,在使用DECLARE声明变量后，所有的变量都会被赋上一个初值NULL，需要使用SET或者SELECT语句为其赋值。,语法格式如下：set v_name1=value select v_name1=value,v_name1=valueFROM WHERE,局部变量,例：分别使用set和select子句为变量a和b赋值 1234，abcd。,declare a int,b char(4)/*set a=1234 set b=abcd*/select a=1234,b=abcdprint aprint b,全局变量,使用全局变量应注
9、意以下几点：1.全局变量不是由用户的程序定义的，它们是定义在服务器级的。2.用户只能使用预先定义的全局变量3.全局变量，必须以开头4.局部变量的名称不能与全局变量的名称相同。,全局变量,常用的全局变量有：error：error 返回最后执行的 T-SQL 语句的错误代码(integer)(注：0表示正常；)rowcount：返回受上一语句影响的行数，除了declare语句外，其他任何语句都可以改变其值。,例：了解error和rowcount的用法update customers set c_gender=nullwhere c_id=c0001if error=515print 错误：违反非空
10、约束if rowcount=0print 警告：没有数据被更新,例：在teaching数据库，使用变量查找成绩大于70的学生学号、姓名、课程名、成绩。并使用全局变量查看是否正确执行。,declare fs decimal(3,1)Set fs=70select sno,sname,cname,markFrom student1 join course1 on o=oWhere markfsprint error,练习：使用WebShop数据库,1.声明变量birthday存储出生日期，age存储年龄；为所声明的birthday变量赋值为“1999-4-14”（使用SET语句）；将custome
11、rs表中的会员的最大年龄赋值给变量age（使用select语句）；输出birthday、age的值。,答案：,1.declare birthday datetime,age int set birthday=1999-4-14 use webshop select age=max(year(getdate()-year(c_birth)from customers print-变量的输出结果-printbirthday的值为：print birthday print 最大年龄为：print age,运算符,1.常用运算符P1822.运算符优先级P184,程序控制语句,程序块语句begin.en
12、d语句,BEGINEND用来设置一个程序块，该程序块可以被视为一个单元执行。BEGINEND经常在条件语句中使用，如IFELSE语句。如果当IF或ELSE子句为真时，想让程序执行其后的多条语句，这时就要把这多条语句用BEGINEND括起来使之成为一个语句块。在BEGINEND语句中可以嵌套另外的BEGIN END语句来定义另一程序块。,语法格式如下：Begin 代码 End,判断语句if else语句,IFELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，IF或ELSE只能执行一条命令。IFELSE可以嵌套使用，最多可嵌套32级。,语法格式如下：i
13、f 代码1 else 代码2,例：从student1表中，查询学号为9704学生的成绩状况，若全部90分以上显示“该学生成绩全部优秀！”若全部60分以上显示“该学生成绩全部合格！”否则显示“该学生有的成绩不合格！”并且要显示最低分。,Declare verygood intSelect verygood=min(mark)From student1Where sno=9704 If verygood=90 Print 该学生成绩全部优秀！else If verygood=60 Print 该学生成绩全部合格！else print 存在不及格科目！Select verygood as 最低分,P
14、rint 最低分：+cast(verygood as char(4),或者,If(Select min(mark)From student1 Where sno=9704)=90 Print 该学生成绩全部优秀！else If(Select min(mark)From student1 Where sno=9704)=60 Print 该学生成绩全部合格！else print 存在不及格科目！Select min(mark)as 最低分From student1 Where sno=9704,CASE语句,语法如下：CASE WHEN 条件表达式 0 THEN 结果表达式 0,n ELSE 结
15、果表达式nEND,CASE语句,例：Declare 成绩级别 char(4),分数 intSet 分数=88Set 成绩级别=Case When 分数=90 and 分数=80 and 分数=70 and 分数=60 and 分数=70 then 及格 When 分数=60 then 不及格 EndPrint 成绩级别,CASE语句,在一般的 SELECT 中，其语法如下：SELECT.,=CASE WHEN 条件表达式 0 THEN 结果表达式 0,n ELSE 结果表达式n END 这是case语句最典型的用法。,CASE语句,实例：根据学生的成绩，评定出等级。90100分为“优秀”，80
16、90为“良好”，7080为“中等”，6070为“合格”，60分以下为“不及格”；要求显示学生的学号、姓名、课程号、成绩和等级。,CASE语句,Select sno,sname,cno,mark,等级=Case When mark=90 and mark=80 and mark=70 and mark=60 and mark=70 then 及格 When mark=60 then 不及格 EndFrom student1,CASE语句,练习1：在webshop数据库的goods表中查询商品类别号，并将所查询到的类别号为“01”的用“通信产品”表示，类别号为“02”的用“电脑产品”表示，类别号为
17、“03”的用“家用电器”表示，其余的类别号用“其他”表示，要求不重复显示。,CASE语句,1.Select distinct t_id as 类别号,类别名称=Case When t_id=01 then 通信产品 When t_id=02 then 电脑产品 When t_id=03 then 家用电器 else 其他 End From goods,CASE语句,练习2：为webshop数据库的goods表中的商品价格设置对应的等级，商品价格在2000元以下（含2000元）设置为“低档商品”，20006000元之间（含6000元）设置为“中档商品”，6000元以上设置为“高档商品”,CASE
18、语句,2.Select g_id,g_name,g_price,等级=Case When g_price6000 then 高档商品 When g_price=2000 and g_price6000 then 中档商品 else 低档商品 End From goods,循环语句while语句,WHILE语句用来表示当条件为真时，重复执行某语句或程序块。WHILECONTINUEBREAK语句，只要指定的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。
19、,语法格式如下：WHILE begin BREAK CONTINUE end,例：实现1-100之间的奇数和。,Declare sum intDeclare i intSet sum=0Set i=0While i=0 Begin set i=i+1 if i100 begin print 0100之间的奇数和：+cast(sum as char(10)break end if i%2!=0 begin set sum=sum+i end else begin continue end end,练习：在student1表中，学号为9705学生的平均成绩如果小于75，则将该学生的每门成绩以5%的
20、比例提高，当平均成绩大于等于75或者所有课程都及格时，终止操作。,While(select avg(mark)from student1 where sno=9705)=60 break end,错误处理语句,T-SQL语句组可以包含在TRY块中。如果在TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。,语法格式如下：BEGIN TRY END TRYBEGIN CATCH END CATCH,例：使用TRY-CATCH捕捉SQL语句执行过程中的异常,BEGIN TRY SELECT 1/0END TRYBEGIN CATCH SELECT ERROR_NUMBER()A
21、S 错误号,ERROR_SEVERITY()AS 错误等级,ERROR_STATE()AS 错误状态,ERROR_PROCEDURE()AS 错误过程,ERROR_LINE()AS 错误行,ERROR_MESSAGE()AS 错误信息END CATCH,系统函数：ERROR_NUMBER()：返回错误的错误号。ERROR_SEVERITY()：在 CATCH 块中调用时，返回导致 CATCH 块运行的错误消息的严重级别。ERROR_STATE()：当在 CATCH 块中调用时，返回导致 CATCH 块运行的错误消息的状态号。ERROR_PROCEDURE()：在 CATCH 块中调用时，返回出
22、现错误的存储过程名称。ERROR_LINE()：当在 CATCH 块中调用时，返回发生错误的行号。ERROR_MESSAGE()：在 CATCH 块中调用时，返回导致 CATCH 块运行的错误消息的完整文本,跳转语句goto 语句,使用 GOTO 将执行控制从代码中的一个位置转移至用户定义标签之后的另一位置。标签是带冒号的文本，用于标识转移执行控制的目标位置。可在过程、批或语句块的任何位置使用 GOTO 语句和标签，但不能转至 GOTO 命令所在批之外的标签。,语法格式如下：label1:goto label1,例：,Declare count smallintSet count=1Resta
23、rt:print hello!Set count=count+1While count=4 Goto restart,延期执行语句waitfor 语句,waitfor 语句用来定义某天中的某一个时刻，执行一个语句块，常用于定时备份数据库，定时显示数据等操作。,语法格式如下：waitfor delay time|time time,说明：1、delay后面的time说明了sql server为了执行语句要等 待的时间。2、Time 后面的time说明了sql server要等到哪个时刻。,例：,waitfor delay 00:00:03select*from student1,waitfor
24、time 11:12:00 select*from student1,无条件返回语句return 语句,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。,语法格式如下：return int_expression,说明：其中，参数integer_expression为返回的整型值。,练习：,1.查找姓名为“刘津津”的会员的会员号，如果查找到该会员，显示其籍贯和联系电话，否则显示“查无此人”（使用ifelse语句）2.求1100之间能被7整除的整数之和，并将这些整数显示出来（使用while语句）3.在webshop数据库中的goods表中查询商品类别号，并将所查询到的类别号为01的用“通信产品”表示，类别号为02的用“电脑产品”表示，类别号为03的用“家用电器”表示，其余类别号用“其他”表示（不重复显示）4.在webshop数据库中根据goods表中的商品的价格设置对应的等级，商品价格在2000元以下（含2000元）设置为“低档商品”，20006000元之间（含6000元）设置为“中档商品”，6000元以上设置为“高档商品”。5.在5分钟以后对Orders表进行查询，在23:00时对goods表进行查询。,常用函数P189-P193,
