第7章_T-SQL高级编程.ppt

《第7章_T-SQL高级编程.ppt》由会员分享，可在线阅读，更多相关《第7章_T-SQL高级编程.ppt（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL Server 2005第1页第七章第七章T-SQL高级编程SQL Server 2005第2页回顾n建库的语法？n补充下列语句：IF EXISTS（SELECT*FROM?WHERE name=student）DROP DATABASE studentn建表的语法？n补充下列语句：IF EXISTS（SELECT*FROM?WHERE name=tblstudent）DROP TABLE tblstudentn解释下列语句的含义？EXEC sp_helpindex tblstudent SQL Server 2005第3页n补充下列语句：1.ALTER TABLE tblstudent

2、 ADD CONSTRAINT?PRIMARY KEY(stuNo)2.ALTER TABLE tblstudent ADD CONSTRAINT UQ_identity_id?3.ALTER TABLE tblstudent ADD CONSTRAINT DF_stud_sex?4.ALTER TABLE tblstudent ADD CONSTRAINT CK_stud_sex CHECK(?)5.ALTER TABLE tblscore ADD CONSTRAINT FK_stud_id FOREIGN KEY?REFERENCES?回顾SQL Server 2005第4页学习T-SQL

3、高级编程的目的n在SQL SERVER的应用程序开发中，有两种访问数据库的方法：一是使用应用程序编程接口（API)，另一种是使用数据库语言。对于前一种方法是定义如何编写连接并传送命令到数据库的应用程序代码，通过应用程序开发工具编写数据库应用系统；后一种方法是在SQL SERVER中使用T-SQL语言。n对于许多应用程序的开发，都是一方面在客户端进行应用程序的开发，另一方面在数据库服务器端进行复杂的完整性设计，为了确保数据库数据的完整性，需要使用T-SQL语言编写复杂的触发器、存储过程、自定义函数，游标和事物等数据库语言程序。SQL Server 2005第5页n 掌握IF、WHILE、CASE

4、逻辑控制语句n 学习用户自定义函数的建立n 理解游标知识并学会运用本章目标SQL Server 2005第6页Begin end 语句n将两条或两条以上的T-SQL语句组合成一个整体成为块语句，相当于C 语言中的n块语句的句法结构：nBegin end 说明：块语句中至少要包含一条T-SQL语句。Begin e nd 必须成对初向，单独各占一行 块语句常用于分支结构和循环结构中 块语句可以嵌套在其他块语句中SQL Server 2005第7页nIFELSE语句C语言中的if-else语句if（条件）语句1；语句2；else 语句1；语句2；SQL中的IF-ELSE语句IF（条件）BEGIN 语

5、句1 语句2 ENDELSE BEGIN 语句1；语句2；END 同C语言一样：lELSE是可选部分l如果有多条语句，才需要BEGIN-END语句块 ifelse语句SQL Server 2005第8页示例示例7.1示例示例7.1答案答案查询学生基本情况表（tblstudent），若其中存在学号“38071304”的学生，就显示已存在些信息，若无则插入该学生的记录。if exists(select stud_id from tblstudent where stud_id=38071304)print 已经存在学号为的学生else insert tblstudent(stud_id,stud_

6、name,stud_sex,birth,identity_id,class_id)values(38071304,刘表,男,1988-1-23,012345678900004,380713)ifelse语句SQL Server 2005第9页示例示例7.2示例示例7.2答案答案查询学生基本情况表（tblstudent），若其中存在学号“38071304”的学生，就显示已存在些信息，若无则插入该学生的记录。if exists(select stud_id from tblstudent where stud_id=38071304)print 已经存在学号为的学生 select*from tbl

7、student where stud_id=38071304 else insert tblstudent(stud_id,stud_name,stud_sex,birth,identity_id,class_id)values(38071304,刘表,男,1988-1-23,012345678900004,380713)ifelse语句SQL Server 2005第10页设置输出结果的格式n为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式SQL Server 2005第11页nWHILE循环语句C语言中的while语句while（条件）语句1；continue;语句2；brea

8、k；SQL中的WHILE语句WHILE（条件）BEGIN 语句1 CONTINUE 语句2 BREAK END 同C语言一样：lBREAK表示退出循环,CONTINUE结束本次循环继续下一循环。l如果有多条语句，才需要BEGIN-END语句块 while循环语句SQL Server 2005第12页示例示例7.3示例示例7.3答案答案使用while语句求1-100的累加和并输出。declare sum int,i intset i=1set sum=0while i=100beginset sum=sum+iset i=i+1endprint 1-100总和为=+convert(char(6)

9、,sum)while循环语句END对不能少!SQL Server 2005第13页示例示例7.4利用break和continue语句的求1-100之间小于50奇数之和。declare sum int,i intset i=0set sum=0while i=100beginset i=i+1if(i%2)=0)continue -当i是偶数跳过下面的语句set sum=sum+i if(i=49)-当i=49时结束循环breakendprint 1-50中奇数和为=+convert(char(6),sum)while循环语句SQL Server 2005第14页nCASEEND多分支语句形式1

10、case when 条件判断表达式结果1 then when 条件判断表达式结果2 then when 条件判断表达式结果n then else endcase多分支语句 SQL Server 2005第15页示例示例7.5判断学生成绩表(tblscoretblscore)中 course_idcourse_id (课程号)的值,如果为“30001”则返回“VB”，为“30002”则返回“数据结构”，为“30003”则返回“数据库”，为“30004”则返回“软件工程”，为“30005”则返回“J2EE”，否则返回“未知课程”。select stud_id 学号,课程名称=case course

11、_idwhen 30001 then VBwhen 30002 then 数据结构when 30003 then 数据库when 30004 then 软件工程when 30005 then J2EEelse 未知课程end,term 学期,score 成绩 from tblscorecase多分支语句 else前不能有when!SQL Server 2005第16页nCASEEND多分支语句形式2casewhen 条件表达式1 then when 条件表达式2 then when 条件表达式n then else endcase多分支语句 SQL Server 2005第17页 除实现示例7

12、.5的功能外根据学生成绩表(tblscore)中 score(成绩)输出对应的分数等级。select stud_id 学号,课程名称=case course_idwhen 30001 then VBend,term 学期,成绩=case when score=90 and score=80 and score=70 and score=60 and score70 then 及格else 不及格end from tblscorecase多分支语句 SQL Server 2005第18页Goto、return 语句n1 goto 语句 句法 goto例：查询学生成绩表（tblscore），若存在

13、学号为“31022001”的学生，就显示存在的学生成绩，如果没有则跳过这些语句。if(31022001 not in(select distinct stud_id from tblscore)Goto abcBeginPrint 已经存在该学生的成绩Select stud_id 学号，course _id 课程号，term 学期，score 成绩From tblscore where stud_id=31022001 end abc:print 没有该学生的成绩SQL Server 2005第19页n2 return 语句 该语句可以在存储过程，触发器，函数，批处理和语句块的任何位置使用，其

14、作用是无条件地从存储过程，触发器，函数，批处理和语句块中退出，在return之后的其他语句不会被执行。SQL Server 2005第20页n标量值型函数标量值型函数create function （参数名 输出参数的类型=默认值,.）returns 函数返回值类型with encryption -对函数定义的文本加密asbegin函数体return 函数返回值函数返回值 -该语句是必须包括的end用户自定义函数 SQL Server 2005第21页create function dbo.fu_dj(score int)returns varchar(6)as begin return ca

15、se when score=90 and score=80 and score=70 and score=60 and score70 then 及格 else 不及格 endendselect stud_id,course_id,dbo.fu_dj(score)from tblscore 标量值函数 在student数据库中创建一个自定义函数，函数名为fu_dj，该函数用于返回学生成绩的分数等级返回学生成绩的分数等级，调用该函数查询学生成绩信息。表名前缀不能少！表名前缀不能少！SQL Server 2005第22页n内嵌表值型函数内嵌表值型函数 create function （参数名 输出

16、参数的类型=默认值,.）returns tablewith encryption asreturn(select语句)用户自定义函数 函数返回值为表函数返回值为表table对函数定义的文本加密对函数定义的文本加密SQL Server 2005第23页create function fu_class(class_id varchar(8)returns tableas return (select*from tblstudent where class_id=class_id)goselect *from fu_class(380713)表名前缀可以不要！表名前缀可以不要！内嵌表值型函数 在st

17、udent数据库中创建一个自定义函数，函数名为fu_class，该函数用于返回某班级学生基本情况信息，调用该函数在学生基本情况表（tblstudent）中查询班级编号为“380713”学生学号、姓名、性别、班级编号信息。示例示例7.9SQL Server 2005第24页create function （参数名 输出参数的类型=默认值,.）returns 局部变量 table with encryptionasbegin函数体returnendn多语句表值型函数多语句表值型函数 用户自定义函数 用于存放函数返回的值用于存放函数返回的值对函数定义的文本加密对函数定义的文本加密必须定义返回表必须定

18、义返回表SQL Server 2005第25页用户定义表！用户定义表！多语句表值型函数多语句表值型函数 在student数据库中创建一个自定义函数,根据输入的班级号查询出该班级所有学生的相关信息（要求包括学号，姓名，性别，出生日期，班级号，班级名称，系别编号，系别名称）。create function dbo.class_student(class_id char(6)returns class_student_tab table(student_id varchar(8),student_name varchar(8),student_sex char(2),birthdate dateti

19、me,class_identity char(6),class_name varchar(20),deprtment_id char(1),department_name varchar(20)begin insert class_student_tab select s1.stud_id,stud_name,stud_sex,birth,s2.class_id,class_name,s3.depart_id,depart_name from tblstudent s1,tblclass s2,tbldepart s3 where s1.class_id=s2.class_id and s2.

20、depart_id=s3.depart_id and s1.class_id=class_id returnend select*from dbo.class_student(310220)SQL Server 2005第26页游标的使用n在数据库应用程序中，操作数据行集合通常有两种方法：一种是数据行集合的整体处理方式，有用户直接对数据行使用T-SQL命令；但是有时应用程序不能有效地将一个数据行集合作为一个整体来处理，他需要一种每次只处理一行或几行数据的机制，此时可以使用另一种基于游标的处理方式n游标是以逐行的方式集中处理数据，实际上，它可以看做是一个指针，用以识别数据行集合内指定的行，该行由

21、select 查询语句返回，它包含了满足where子句中查询条件的所有数据行。使用游标可以控制对某个特定行的操作，也可以不需要索引定位要操作的数据行，因而可以提供更多的灵活性SQL Server 2005第27页n使用游标的步骤：使用游标的步骤：1）声明游标：使用declare cursor声明游标。declare 游标名 cursor for select 语句 2）打开游标：使用open语句填充该游标3）读取游标：使用fetch语句，从结果集中检索特定的一行。在打开一个游标后，它将被放在游标结果集的首行前，必须用fetch语句访问该首行。fetch next from 游标名 into 变

22、量名 4）关闭游标：使用close语句关闭游标5）删除游标：使用deallocate语句删除游标引用游标的使用SQL Server 2005第28页n声明游标语法结构：声明游标语法结构：declare insensitivescrollstatic|keyset|dynamic|fast_forword cursorfor for read only|updateof 列名1，列名2，.游标的使用n打开游标语法结构：打开游标语法结构：open n读取游标语法结构：读取游标语法结构：fetch next|prior|first|last|absolute n|nvar|relativen|nva

23、rfrom into 变量，.nn关闭和释放游标语法结构：关闭和释放游标语法结构：close deallocate SQL Server 2005第29页示例示例7.15fetch next from string_course_31031001 into nif(fetch_status=0)begin set string=n fetch next from string_course_31031001 into n while(fetch_status=0)begin set string=string+、+n fetch next from string_course_31031001

24、 into n end endclose string_course_31031001 -关闭游标关闭游标deallocate string_course_31031001 -释放游标释放游标declare string nvarchar(50),n nvarchar(10)declare string_course_31031001 cursor -声明游标for select course_name from tblscore,tblcoursewhere stud_id=31031001 and tblscore.course_id=tblcourse.course_idopen str

25、ing_course_31031001 -打开游标游标的使用游标的使用 游标使用综合示例。使用游标得到学号为“31031001”的学生选修过的课程名组成的字符串数据。SQL Server 2005第30页n定位修改游标数据的语法结构：定位修改游标数据的语法结构：update set 列名1=表达式1，列名2=表达式2,where current of 定位、更新和删除游标的使用n定位删除游标数据的语法结构：定位删除游标数据的语法结构：delete where current of SQL Server 2005第31页示例示例7.16游标的使用游标的使用 声明一个游标声明一个游标score_c

26、ursor，对课程号为，对课程号为30001上的上的score列进行更新操作：及格的学生成绩每门减列进行更新操作：及格的学生成绩每门减5分。分。select stud_id,course_id,score from tblscore where course_id=30001declare stud_id char(8),course_id char(5),score tinyint declare score_cursor scroll cursor for select stud_id,course_id,score from tblscore where course_id=30001

27、for update of scoreopen score_cursor select stud_id,course_id,score from tblscore where course_id=30001declare stud_id char(8),course_id char(5),score tinyint declare score_cursor scroll cursor for select stud_id,course_id,score from tblscore where course_id=30001 for update of scoreopen score_curso

28、r fetch next from score_cursor into stud_id,course_id,score while(fetch_status=0)begin if(score=60)update tblscore set score=score-5 where current of score_cursor fetch next from score_cursor into stud_id,course_id,score endclose score_cursor deallocate score_cursor select stud_id,course_id,score fr

29、om tblscore where course_id=30001SQL Server 2005第32页n本章介绍了利用T-SQL的流程控制语句实现复杂的数据库语言编程。nT-SQL提供了为数不多的流程控制语句，但它已足以使T-SQL比标准的SQL功能强大很多。流程控制语句包括分支（if else和case）、循环（只有while一种，但它可以带continue和break选项）、无条件跳转（goto）和返回语句（return）。nT-SQL还提供了系统函数，用来处理字符串、转换数据类型、加工和格式化日期信息的函数，这在第三章中我们已作了介绍。T-SQL还允许用户自己定义函数。本章花了不少篇幅对自定义函数作了比较详细的讲解。n本章我们还利用T-SQL中的流程控制语句对游标的建立和使用作了详细的介绍。总结