第10章-T-SQL-程序设计.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,10,章,Transact SQL,程序设计,1,本章内容：,1,、如何在,T-SQL,里面使用变量,2,、如何使用语句控制程序的执行顺序,3,、如何在,T-SQL,里面使用函数,4,、如何使用批处理语句,2,在进行数据库应用软件开发的过程中,sql,server,和前端开发工具的通讯都是通过,T-SQL,代码实现的，因此从事数据库管理系统的开发都要了解,T-SQL,程序设计。,3,作用,:,在程序设计中变量是用来传递数据,的方式之一,它由系统或用户自定义并赋值,变量分为,:,全局变量,局部变量,10.2,变 量,4,A:,全局变量,作用：,SQL,使用全局变量来记录,SQL,SERVER,服务器的活动状态。是由系统,预先定义好的变量，以,开头，用户,无法对它进行修改或管理，对于用户来,讲是只读的。,全局变量的用途见表,10.1,5,举例说明,1,、,ERROR,作用,:,该全局变量将返回最后执行的,T-SQL,语,句的错误代码。,返回值的数据类型：整数。,输入,SQL,语句执行成功则返回,0,，如果不成,功，则返回相关错误信息对应的错误号。,错误代码的具体含义在,sysmessages,表,而,错误信息对应的代码存放在,SYSMESSAGE,系统表中,6,eg,:,使用,ERROR,变量检验,SQL,语句是否执行成功,select,sno,from students,where,sname,=,周迅,print error,if error=0,print,你的,SQL,语句执行成功,7,Eg,:,使用,ERROR,全局变量检测在,SQL,语句中出现的限制冲突,假设在数据库的,STUDENTS,表中为,ssex,字段,建立了,CHECK,约束,(,ssex,=,男,or,ssex,=,女,),Insert into,students(sno,sname,ssex,bday,),Values(0405102,刘品言,好,1978-6-7),print error,if error=547,print,出现核查约束冲突,8,2,、,rowcount,使用,作用：该变量将返回受上面,SQL,语句影响的记录条数，返回的数据类型是整数型数据，如果不影响记录的,SQL,语句则返回,0,9,eg,:,在,update,语句中使,rowcount,检测发生更改的记录数,更新,teachers,表，给,pay,少于,2000,的教师,工资增加,500,Update teachers,set pay=pay+500,where pay400),BEGIN,select*,from students,where,mgrade,400,END,ELSE,print,没有总分大于,400,的学生,18,CASE,语句,CASE,表达式是一个特殊的,T-SQL,表达式，它使用户能够方便的实现多重选择的情况。使用,CASE,表达式，实际上相当于使用多重,IF.ELSE,语句的嵌套。,简单,CASE,语句,搜索,CASE,语句,19,简单,CASE,语句,CASE,测试表达式（常量、列名、函数）,when,测试值,1 then,结果表达式,1,when,测试值,2 then,结果表达式,2,when,测试值,3 then,结果表达式,3,n,ELSE,结果表示式,n+1,END,20,Eg,:,分别输出,teachers,表中教师姓名和所在的部门，并在部门名称后添加说明,SELECT,tname,dept,部门说明,=,CASE,dept,when,基础部,then,负责基础课程教学,when,经管系,then,培养经济管理的人才,when,计算机系,then,培养计算机高级技术人才,when,国际交流系,then,培养英语实用人才,ELSE,其他,END,FROM teachers,21,CASE,语句,CASE,表达式是一个特殊的,T-SQL,表达式，它使用户能够方便的实现多重选择的情况。使用,CASE,表达式，实际上相当于使用多重,IF.ELSE,语句的嵌套。,简单,CASE,语句,搜索,CASE,语句,22,搜索,CASE,语句,CASE,when,逻辑表示式,1 then,结果表达式,1,when,逻辑表示式,2 then,结果表达式,2,when,逻辑表示式,3 then,结果表达式,3,n,ELSE,结果表示式,n+1,END,23,分别输出,SC,中的,sno,cno,grade,在成绩后面添加一列，将成绩从百分制转化为五级记分制。,24,Select,sno,cno,grade,成绩等级,=,Case,When grade=100 and grade90 and grade80 and grade70 and grade=60 Then D,Else E,End,From sc,25,Wait for,语句,作用：可以暂停程序执行，在达到指定时间或时间间隔后继续执行程序。,语法格式：,指定时间间隔：,Wait for delay,时间间隔,固定时间点：,Wait for time,时间值,26,输出,03,物流,1,信息后,4,秒钟输出,03,物流,2,信息,Select sno,sname,From students,Where class=03,物流,1,go,Waitfor delay 00:00:04,go,Select sno,sname,From students,Where class=03,物流,2,27,WHILE,语句,格式：,WHILE,布尔表达式,BEGIN,命令程序块,END,作用：当,WHILE,后面的布尔表达式为,真,，,会重复执行,命令程序块，,直到条件不成立,时为止,28,通常与,WHILE,语句同时使用的,T-SQL,语句有两条：,BREAK,和,CONTINUE,。,BREAK,：,执行该语句时，程序无条件的退,出整个,WHILE,循环结构,CONTINUE,：,执行该语句时，程序无条件,的跳过,CONTINUE,后面的语句，而立即执,行下一层循环,29,EG：,判断,teachers,表中是否有老师的奖金少于,500,，如果有，则将所有老师的工资增加,300,，直到所有老师的奖金都多于,500,为止。,注,:,教师工资,(pay),的,30%,是奖金,30,WHILE,EXISTS,(Select,*,From teachers,Where pay*0.3500),BEGIN,Update teachers,Set pay=pay+300,END,31,EG：,判断,teachers,表中是否有老师的奖金少于,500,，如果有，则将所有老师的工资增加,300,，直到所有老师的奖金都多于,500,注,:,教师工资,(pay),的,30%,是奖金,或者有老师的工资都超过了,10000,32,WHILE,EXISTS,(SELECT*,FROM teachers,where pay*0.310000,break,else,continue,33,RETURN,语句,作用：无条件终止查询、存储过程或批处,理。,RETURN,后面的语句将不再执,行。,主要用于存储过程中,34,10.4 SQL Server,函数,常用内置函数：,标量函数、聚合函数、行集函数、排名函数,1,、标量函数,对单一值操作，返回单一值。能够使用表达,式的地方都可以使用标量函数。,35,数学函数,（表,10.2,）,日期与时间函数,字符串函数,数据类型转换函数,36,数学函数举例,ABS(,数字表达式,),返回给定数字表达式的绝对值,返回数据类型和给定数字表达式的数据类,型相同。,Eg,:ABS(-235),结果：,235,37,数学函数,（表,10.2,）,日期与时间函数,（表,10.3,）,字符串函数,数据类型转换函数,38,日期和时间函数举例,1,、,GETDATE,（）,作用：取的当前的日期,2,、,DATEPART(,返回的日期部分，日期,),作用：返回日期的指定日期部分的整数。,返回类型：,int,print datepart(Y,ear,GETDATE(),=,print datepart(YY,GETDATE(),39,作用：返回两个日期之间的时间间隔,print DATEDIFF(year,1998-4-18,2004-4-9),最终结果：,6,想一想：如何来计算,students,中学生年龄,Select,sno,年龄,=,datediff(year,bday,getdate,(),From students,3,、,DATEDIFF(datepart,date1,date2),40,PRINT DATEADD(MONTH,-2,2006-6-6),2006-4-6,4,、,DATEADD(datepart,number,date,),作用：给指定的日期的某一部分增加值,datepart,:,指定部分,number:,增加的整数值,date:,指定的日期,41,数学函数,（表,10.2,）,日期与时间函数,（表,10.3,）,字符串函数,（表,10.4,）,数据类型转换函数,42,字符串函数举例,1,、,SUBSTRING,（,expression,start,length,）,从,expression,的第,start,个字符处返回,length,个字符。,Print SUBSTRING(DIELDOSPOE,5,3),结果：,DOS,43,查询,Students,表中的学号和姓名，要,求显示学号的后,3,位,Select substring(sno,5,3),姓名,From students,函数应用,44,删除指定长度的字符并在指定的起始点插入另,一组字符。,例：,取字符串“,sqlserver,”,的首字母并将它变成大写,print stuff(sqlserver,1,1,S),Sqlserver,2,、,Stuff(,表达式,1,，开始位置，长度，表达式,2),45,3,、,STR(float_expr,length,decimal,),作用：将一个数值型数据转换为字符串，,length,为字符串的长度，,decimal,小数点,后的位数,(char),Eg,:print STR(15.2222,10,6),则结果是：,15.222200,46,4,、,LTRIM/RTRIM,LTRIM,（,expression,）删除字符串之,前所有空格。返回数据类型为,varchar,。,RTRIM,（,expression,）截断字符串中,所有尾随空格。返回数据类型为,varchar,。,47,数学函数,（表,10.2,）,日期与时间函数,（表,10.3,）,字符串函数,（表,10.4,）,数据类型转换函数,48,CAST,和,CONVERT,系统提供了,CAST,和,CONVERT,函数实现数据类型的转换。,CAST(expression,as,date_type,),CONVERT(date_type,expression),select,sno+,同学平均成绩,+cast(avg(grade)as char(3)+,分,from sc,group by sno,49,2,、聚合函数,作用：用于计算,SELECT,语句查询行的统,计值，通常与,GROUP BY,语句一起使用，,对表分组统计。,50,聚合函数,函数,参数,功能,COUNT,（,ALL/DISTINCTexpression,）,计算表示式的数量，,DISTINCT,用于删除重复,MIN,（expression）,表达式的最小值，忽略空值,MAX,(expression),表达式的最大值，忽略空值,51,函数,参数,功能,SUM,（,ALL/DISTINCTexpression,）,计算表达式所有值的和，忽略空值,AVG,（,ALL/DISTINCTexpression,）,计算表达式平均值，忽略空值,52,用户自己定义的函数,用户可以在,SQL SERVER,中创建自己的函数，来补充和扩展系统支持的内置函数。用户自定义函数的输入参数可以有多个，也可以没有。,53,1,、用户函数的定义,可以利用,CREATE FUNCTION,语句创建用户自己定义的函数，创建语句指定输入参数的数据类型，处理的过程，以及函数最后的返回值,54,CREATE FUNCTION,函数名,（,参数名称,，数据类型）,RETURNS,返回数据类型,BEGIN,函数内容,RETRUN,返回值表达式,END,语法格式：,55,EG:,定义个用来计算员工奖金的函数，返回员工的奖金,(,工资的,0.3,就是奖金,),CREATE FUNCTION,bonus,(salary,smallmoney,),RETURNS,smallmoney,BEGIN,RETURN(salary*0.3),END,注：自定义标量值函数,56,2,、函数的调用,调用刚刚定义的奖金的函数求所有教师的,奖金,select,tname,dbo.bonus(pay,),from teachers,注意：在调用函数的过程中要指明函数的,拥有者和函数的名称和参数,57,3,、删除函数,DROP FUNCTION,函数名称,DROP FUNCTION,bonus,58,创建一个带返回值的用户自定义函数,grade_avg,。通过该函数可以查询任意一位学生所学课程的平均分。,59,Create function,grade_avg(sno,char(7),Returns,int,Begin,declare,grade_avg,int,select,grade_avg,=,avg(grade,),from sc,group by,sno,having,sno,=,sno,Return,grade_avg,end,60,调用,grade_avg,（）函数计算所有学生所学课程的平均分,select,sno,dbo.grade_avg(sno,),from sc,61,10.1.1,批处理,批处理是指包含一条或者多条,Transact-,SQL,语句的语句组，这样的语句组从应用,程序一次性的发送到,SQL Server,服务器执,行。,批处理命令是以,GO,为结束标志。,62,10.1.2,脚本,脚本是存储在文件中的一系列,T-SQL,语句。,脚本文件（,.,sql,）用于建立起可以重复使用的模块化代码，可在不同计算机之间传递。,63,10.1.3,注释,注释：程序中用来对程序内容进行解释说明的语句。,SQL SERVER,支持两种形式的注释：,-,注释语句：用于单行注释,/*,注释语句*,/:,用于多行注释,64,