《ransactSQL程序设计.ppt》由会员分享，可在线阅读，更多相关《ransactSQL程序设计.ppt（72页珍藏版）》请在三一办公上搜索。
1、1,第四章 Transact SQL 程序设计,2,本章内容：1、如何在T-SQL里面使用变量 2、如何使用语句控制程序的执行顺序 3、如何在T-SQL里面使用函数 4、如何使用批处理语句 5、如何使用游标处理数据,3,在进行数据库应用软件开发的过程中,sql server 和前端开发工具的通讯都是通过T-SQL代码实现的，因此从事数据库管理系统的开发都要了解T-SQL 程序设计,4,作用:在程序设计中变量是用来传递数据的方式之一,它由系统或用户自定义并赋值变量分为:全局变量局部变量,4.1 变 量,5,A:全局变量,作用：SQL使用全局变量来记录SQLSERVER 服务器的活动状态。是由系统
2、预先定义好的变量，以开头，用户无法对它进行修改或管理，对于用户来讲是只读的。全局变量的用途,6,举例说明,1、ERROR 作用:该全局变量将返回最后执行的T-SQL 语句的错误代码,返回值的数据类型 整数.输入SQL语句执行成功则返回0,如果不成功,则返回相关错误信息对 应的错误号,而错误信息对应的代码存放在 系统表中,7,eg:使用ERROR变量检验SQL语句是否执行成功,select sno from students where sname=周杰伦if error=0print 你的SQL 语句执行成功,8,Eg:使用ERROR全局变量检测在SQL语句中出现的限制冲突,假设在数据库的ST
3、UDENTS表中为BDAY字段建立了CHECK约束(bday1980-1-1and bday2000-1-1)update students set bday=1976-8-7 where sname=周迅if error=547print 你更改的出生日期是不允许的,9,2、rowcount使用,作用：该变量将返回受上面SQL语句影响的记录条数，返回的数据类型是整数型数据，如果不返回记录的SQL语句将返回0,10,eg:在update 语句中使rowcout检测是否存在发生更改的记录数,更新teachers表，给pay大于2000的教师工资增加500 Update teachers set
4、pay=pay+500 where pay2000 Print 共有+RTRIM(rowcount)+人增长工资,11,B：局部变量,在Transact-SQL批处理中用来保存数据值的对象。作用范围：从申明该局部变量的地方开始，到申明局部变量的批处理或存储过程的地方结尾。,12,B：局部变量,如何声明局部变量 使用DECLARE语句来声明T-SQL变量 格式：DECLEAR 变量名称 数据类型,可以是系统提供的数据类型，也可以是用户自己定义的数据类型,Eg:DECLARE V1 char(20),v2 int,13,如何为变量赋值,可以使用SET和SELECT给变量赋值,eg:set v1=常
5、量/变量表达式,eg:select v2=MAX(mgrade)from students print v2,14,补充：运算符,SQL SERVER支持的运算符有算术运算符、比较运算符、字符串连接运算符、逻辑运算符和位运算符,15,字符串连接运算符,字符串连接用+号来连接EG:ABC+123=ABC123,16,运算符优先权,括号（）求反（）乘除（、）加减（+、）按位异或（）按位与（&）按位或（|）逻辑非（NOT）逻辑与（AND）逻辑或（OR）,17,4.3 程序流程控制,作用：程序流程控制语句主要用于控制 T-SQL语句、语句块和存储过程的 执行过程。,18,4.3.1 BEGINEND语
6、句块,作用：将多条T-SQL语句组合在一起，组成一个逻辑块，当控制流语句必须执行一个包含一条或两条以上的T-SQL语句的语句块时使用。注意：相当与很多语言中的,19,4.3.2 IF ELSE语句,格式 IF(布尔表达式)SQL语句块 ELSE SQL 语句块,20,eg:如果学生表中有入学总分大于400分的学生，请输出信息，否则提示用户没有,IF exists(select sno from students where mgrade400)BEGIN PRINT下列学生成绩400 select*from students where mgrade400 END ELSE BEGIN PRI
7、NT没有总分大于400的学生 END,21,4.3.3.casewhen,CASE表达式是一个特殊的T-SQL表达式，它使用户能够方便的实现多重选择的情况。使用CASE表达式，实际上相当于使用多重IF.ELSE语句的嵌套,22,CASE表达式语法,CASE 字段名或变量名 when 逻辑表示式1 then 结果表达式1 when 逻辑表示式2 then 结果表达式2 when 逻辑表示式3 then 结果表达式3.ELSE 结果表示式END,23,Eg:分别输出teachers表中教师姓名和所在的部门，并在部门后面添加部门说明,SELECT tname,dept,部门说明=CASE dept
8、when基础部 then 教基础课程的部门 when经管系 then培养经济管理和行政管理的人才 when计算机系then培养计算机高级技术人才 when国际交流系then培养英语实用人才 else 其他 end from teachers,24,、无条件转移（GOTO）,格式：GOTO 标号处作用：本语句将T-SQL语句的执行顺序无条件的转移到用户指定的标号处,如何定义标号出：标号名称：,25,Eg:使用GOTO语句求10的阶乘,DECLARE I int,time int select I=1,time=2 label:select I=I*time select time=time+1
9、if time=10 GOTO Label else select i,time,26,GOTO语句的使用注意情况,在书写程序的过程中，应尽量避免使用GOTO语句，因为过多的GOTO语句可能造成T-SQL处理的逻辑混乱，而难于理解,27,4.3.5 WHILE语句,格式：,WHILE 布尔表达式BEGIN 命令程序块END,作用：当WHILE后面的布尔表达式为真，会重复执行命令程序块，直到条件不成立时为止,28,通常与WHILE语句同时使用的T-SQL语句有两条：BREAK和CONTINUE。,BREAK：执行该语句时，程序无条件的退 出整个WHILE循环结构,CONTINUE：执行该语句时，
10、程序无条件的跳过CONTINUE后面的语句，而立即执行下一层循环,29,EG：,判断teachers表中是否有老师的奖金少于300，如果有，则将所有老师的工资增加500，直到所有老师的奖金都多于300（教师工资的30%是奖金）,30,WHILE EXISTS(SELECT*FROM teachers where pay*0.3300)BEGIN UPDATE teachers set pay=pay+500 END,31,4.3.6 RETURN语句,作用：无条件终止查询、存储过程或批处 理。RETURN后面的语句将不再执 行。主要用于存储过程中,32,4.3.7 Wait for 语句,作用
11、：挂起执行连接，直到超过指定的时间间隔或者达到一天中指定的时间。语法格式：指定时间间隔：Wait for delay 时间间隔固定时间点：Wait for time 时间值,33,EG,在执行SELECT语句之前等待两秒Waitfor delay 00：00：02Select sno,snameFrom studentsWhere class=03物流2下例在晚上 10:20 执行指定存储过程 WAITFOR TIME 22:20 EXECUTE update_all_stats,34,4.2 SQL Server函数,1、系统函数可以使用户在不访问系统表的情况下，获取SQL Server系统
12、表中的信息。Col_length 返回列长度,35,EG:返回students表中sname字段的长度,Select col_length(students,sname)as 字段长度 From students,36,2、聚合函数作用：用于计算SELECT语句查询行的统计值，通常与GROUP BY语句一起使用，对表分组统计。,37,聚合函数,38,39,字符串函数日期与时间函数数学函数,40,字符串函数举例,1、SUBSTRING（expression,start,length）从expression的第start个字符处返回length个字符。Print SUBSTRING(DIELDOS
13、POE,5,3)结果：DOS,41,2、RTRIM RTRIM（expression）截断字符串中所有尾随空格。返回数据类型为varchar,42,3、STR(float_expr,length,decimal)作用：将一个数值型数据转换为字符串，length为字符串的长度，decimal小数点后的位数(char)Eg:STR(15.2222,10,6)则结果是：15.222200,43,日期和时间函数举例,1、GETDATE（）作用：取的当前的日期2、DATEPART(返回的日期部分，日期)作用：返回日期的指定日期部分的整数。返回类型：int,44,3、DATEDIFF(datepart1,
14、date1,date2)作用：返回两个日期之间的时间间隔DATEDIFF(year,1998-4-18,2004-4-9)最终结果：6,45,PRINT DATEADD(MONTH,-2,2006-6-6),4、DATEADD(datepart,number,date)作用：给指定的日期的某一部分增加值 datepart:指定部分 number:增加的整数值 date:指定的日期,46,数学函数举例,ABS(数字表达式)返回给定数字表达式的绝对值返回数据类型和给定数字表达式的数据类型相同。Eg:ABS(-235)结果：235,47,用户自己定义的函数,用户可以在SQL SERVER中创建自己的
15、函数，来补充和扩展系统支持的内置函数。用户自定义函数的输入参数可以有多个，也可以没有。,48,1、用户函数的定义,可以利用CREATE FUNCTION语句创建用户自己定义的函数，创建语句指定输入参数的数据类型，处理的过程，以及函数最后的返回值,49,CREATE FUNCTION 函数名（参数名称，数据类型）RETURNS 返回数据类型 BEGIN 函数内容 RETRUN 表示式 END,语法格式：,50,EG:定义个用来计算员工奖金的函数，返回员工的奖金(工资的0.3就是奖金),CREATE FUNCTION bonus(salary smallmoney)RETURNS smallmon
16、ey BEGIN RETURN(salary*0.3)END,51,2、函数的调用,调用刚刚定义的奖金的函数求所有教师的奖金select tname,dbo.bonus(pay)from teachers注意：在调用函数的过程中要指明函数的拥有者和函数的名称和参数,52,用户定义函数类型,SQL Server 2000 支持3种用户定义函数：标量函数(Scalar functions)、内嵌表值函数(Inline table-valued functions)多语句表值函数(Multistatement table-valued functions)。,53,标量函数返回在RETURNS子句中
17、定义的类型的单个数据值。可以使用所有标量数据类型，包括bigint和sql_variant。不支持timestamp数据类型、用户定义数据类型和非标量类型。表值函数返回table。对于内嵌表值函数，没有函数主体；表是单个SELECT语句的结果集。对于多语句表值函数，在BEGIN.END块中定义的函数主体包含TRANSACT-SQL语句，这些语句可生成行并将行插入返回的表中。,54,3、删除函数,DROP FUNCTION 函数名称 DROP FUNCTION bonus,55,4.5 游标,作用：通常情况下，关系数据库中的操作总是对整个记录集产生影响，例如使用SELECT语句检索数据表时，将得
18、到所有满足该语句where子句中条件的记录，而在实际应用过程中，经常需要每次处理一条或者一部分记录。在这种情况下，需要使用游标在服务器内部处理结果集合，他可以有助于识别一个数据集合内部指定的记录，从而可以有选择的按记录执行操作,56,一：声明游标,DECLARE cursor_name CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL READ_ONLY FOR select_statement FOR UPDATE OF column_name,.n,57,例子：声明一个游标,选取所有籍贯是“宁波”的学生作为结果集，为该结果集声明只读游标declare pm_c
19、ursor cursor scroll read_only for select*from students where bplace=宁波,58,二、打开和使用游标,打开游标：Open 游标,59,例：使用刚才申明的游标读取结果集中的所有记录。,open pm_cursorfetch next from pm_cursorwhile fetch_status=0beginfetch next from pm_cursorend,60,三、关闭和释放游标,在某个游标打开以后，SQL SERVER服务器会自动特地为这个游标开辟一定的内存空间，这些内存空间将用来存放游标操作的数据结果集。因此，如果
20、不再使用某个游标，一定要将该游标关闭，这样服务器就会释放该游标所占用的相关资源。,61,关闭游标的语法格式：Close 游标名称如果关闭了一个游标，可以在需要时再次打开和使用它，而不必重新申明。,62,即使关闭游标结构，还是会占用一定的计算机资源，所以如果一个游标不再使用，应及时将游标释放，收回被占用的计算机资源。释放游标的语法格式：Deallocate 游标名称注意：一旦释放游标，如果要再次使用，必须重新申明。,63,使用批处理,批处理是指包含一条或者多条Transact-SQL语句的语句组，这样的语句组从应用程序一次性的发送到SQL Server服务器执行。批处理命令是以GO为结束标志。,
21、64,作业与上机(2-1)：,书写语句将teachers表中计算机系教师的工资增长300，并使用全局变量检查更新过程中是否出错（所用到的表结构如下：teachers(tno,dept,pay)其中TNO代表教师编号，DEPT代表教师系别，PAY代表教师的工资）,65,Update teachersSet pay=pay+300Where dept=计算机系If error=0Print 更新过程没有出错ElsePrint 更新过程出错,66,查看STUDENTS信息表中是否有学号为0289339名字为李菊的学生，如果有则显示已经存在该学生的信息，否则插入该学生的信息（涉及到的表的结构为stud
22、ents(sno,sname)）分别输出sno,cno,grade,在成绩后面添加一列，将成绩从百分制转化为五级记分制,67,If exists(select*from students where sname=李菊)Begin print 存在此人信息：select*from students where sname=李菊 EndElse Begin Print 添加此人信息：insert into students(sno,sname)valuse(0289339,李菊)End,68,Select sno,cno,grade,成绩等级caseWhen grade=90 and grade=
23、80 and grade=70 and grade=60 and grade70 Then DElse EFrom sc,69,作业与上机(2-2)：,将字符串“sqlserver”的首字母变成大写Upper(rtrim(substring(sqlserver,1,1)将students表中的sno第2和第3个字符间增加一个字符9select substring(sno,1,2)+9+substring(sno,3,5)as sno,sname,classfrom students,70,定义一个自定义的函数，该函数是通过的出生年月和当前日期，计算两个日期之间的年份差；返回值为整数，然后调用此
24、函数计算学生信息表表中学生的年龄（学生信息表(学号，姓名，出生年月)）书写语句将SC表中是所有学生的成绩加10分,如果加分以后仍然有小于60分的,那么继续加10分,直到所有学生的成绩超过60分或者有学生的最高分超过100了(最高分不能超过100)所用到的表结构为SC(SNO,CNO,GRADE),71,CREATE FUNCTION age(sta as datetime,en as datetime)returns int beginreturn(datediff(year,sta,en)endselect 学号,姓名,年龄=dbo.age(bday,getdate()from students,72,while exists(select*from sc where grade90 breakend,
