2-6 T-SQL 程序设计基础实训

u      掌握T-SQL语言中几个常用流程控制语句的使用；
u      掌握系统内置函数的概念及其应用；
u      通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用？
u      完成T-SQL基础实训和自定义函数实训任务
2-6-1　T-SQL实训
1．实训任务
按照下列要求，在 school 数据库之中进行 T-SQL 的编写工作：
（ 1 ）在学生情况表（ student ）中如果存在学号为“ 1006 ”的学生则显示该学生的信息，否则插入该学生的信息 (1006,' 丽萍 ',' 女 ',95033, '1986-4-5')

sno
sname
sex
class
birthday
1006
丽萍
女
95033
1986-4-5
（ 2 ）查找 1002 号学生，如果该学生的“数据库”课程成绩低于“数据库”课程的平均成绩，则删除学生情况表（ student ）中该学生相关记录。
（ 3 ）利用流程控制语句打印刘朝阳所学课程的平均成绩，如果成绩大于 85 分，则打印‘优秀’，如果成绩在在 60-85 之间打印‘中’，如果小于 60 ，则打印不及格。
（ 4 ）使用 WHILE 语句求 1 到 100 之间的累加和并输出
（ 5 ）定义一个用户自定义的函数 Score_ReChange ，将成绩从百分制转化为五级记分制。将该用户定义的函数用在查询每个学生的成绩中，给出五级记分制的成绩。
（ 6 ）定义一个用户自定义的函数，完成如下功能：如果学生有不及格的成绩，则在学生情况表的备注列中输入“有不及格的成绩”，否则输入“没有不及格的成绩”。
（ 7 ）杨辉三角的结构如图 2-22 （具体请参见杨辉三角的算法），请按照下面的代码录入 T-SQL 代码，并分析讨论算法思想。
 

图2-22 杨辉三角基本图形
IF OBJECT_ID('PascalTriangle') IS NOT NULL
    DROP FUNCTION PascalTriangle
GO
CREATE FUNCTION PascalTriangle
(@x INT, @y INT)
RETURNS INT
AS
BEGIN  
    if(@y=1 OR @y=@x)
        return 1
    return dbo.PascalTriangle(@x-1,@y-1) + dbo.PascalTriangle(@x-1,@y)
END
GO
DECLARE @i INT, @j INT , @str VARCHAR(200)
SELECT @i =1
WHILE(@i <= 10)
BEGIN
    SELECT @j = 1, @str =''
    WHILE(@j<=@i)
    BEGIN
        SET @str = @str + LEFT(CONVERT(VARCHAR(200),
         dbo.PascalTriangle(@i,@j))+SPACE(10),7)
        SET @j=@j+1
    END
PRINT @str
SET @i=@i+1
END
2-6-2　用户自定义函数实训
1．实训任务
按照下列要求，在 school 数据库之中进行下列用户定义函数的编写工作：
（ 1 ）输入一个学生的学号，返回该学生全部课程的平均成绩；
create function stu_jg
(@stu_no varchar(12),@cnurse_no varchar(12))
returns varchar(100)
as
begin
 declare @message varchar(100),
 @sname varchar(12),@cname varchar(10)
 if exists(select sname,cname
           from score,course,student
           where student.sno=@stu_no and
                 course.cno=@cnurse_no and
           student.sno=score.sno and course.cno=score.cno)
   begin
           select @sname=sname,@cname=cname
           from score,course,student
           where student.sno=@stu_no and course.cno=@cnurse_no and
           student.sno=score.sno and course.cno=score.cno
           set @message='您查询的学生是:'+RTRIM(LTRIM(@sname))+',选择的课程是:'+RTRIM(LTRIM(@cname))
   end
 else
          set @message='对不起查无此人,您输入的学生号码错误!'
 return @message
end
--下面开始测试该函数
declare @mess varchar(500)
exec @mess=dbo.stu_jg '108','3-105'
print @mess
（ 2 ）输入教师编号，返回字符串，如果无该教师则打印错误信息，如果有该教师，则显示该教师的姓名，性别，所在系和工资信息；
CREATE function fun_teacher(@tno varchar(12))
returns varchar(150)
as
begin
 declare @mess varchar(150),@salary money,@tname varchar(10),@depart varchar(10)
 if exists(select name from teacher where tno=@tno)
 begin
    select @tname=NAME,@salary=SALARY,@depart=DEPART from teacher where tno=@tno
    set @mess='您所查询的教师信息是：姓名['+RTRIM(LTRIM(@tname))+'],所在系['+RTRIM(LTRIM(@depart))+']，工资是['+RTRIM(LTRIM(cast(@salary as varchar(10))))+']'
 end
 else
 begin
   set @mess='无此教师！！！！！！！！！！！！'
 end
 return @mess
end
--下面开始测试该函数
declare @ee varchar(150)
select @ee=dbo.fun_teacher('804')
（ 3 ）输入某学生的学号和某课程号，返回字符串。如果没有该学生，则显示“没有找到学生”的错误信息；如果没有该课程，则显示“没有该课程”的错误信息。如果既有学生又有课程，则求该学生该门课程的平均成绩，以及该学生平均成绩和该课程的平均成绩，并以字符串打印出来。
CREATE function fun_student_score(@sno varchar(12),@cno varchar(12))
returns varchar(300)
as
begin
declare @mess varchar(300),@sname varchar(50),@cname varchar(50),@degree float,@avg_snodegree float,@avg_cnodegree float
  
if exists(select sno from student where sno=@sno)
     begin
        if exists(select cno from course where cno=@cno)
           begin
           select @sname=sname,@cname=cname,@degree=degree
           from student,score,course
           where student.sno=score.sno and course.cno=score.cno
           and student.sno=@sno and course.cno=@cno
 
           select @avg_snodegree=avg(degree) from score where sno=@sno
           group by sno
 
           select @avg_cnodegree=avg(degree) from score where cno=@cno
           group by cno
          
           set @mess='您所查询的信息是：学生姓名['+RTRIM(LTRIM(@sname))+'],课程['+RTRIM(LTRIM(@cname))+']，该学生的平均成绩是['+RTRIM(LTRIM(cast(@avg_snodegree as varchar(10))))+']'+'，查询课程的平均成绩是['+RTRIM(LTRIM(cast(@avg_cnodegree as varchar(10))))+']'
           end
        else
           begin
           set @mess='查询出错，无所需课程信息。'
           end
     end
   else
     begin
          set @mess='查询出错，无所需学生信息。'
     end
return @mess
end
--下面开始测试该函数
declare @ee varchar(300)
select @ee=dbo.fun_student_score('103','6-105')
print @ee
（ 4 ） 该问题的解法不仅仅只有一种 ， 下面我们看另一种解决办法 ：
--首先建立函数averc，输入课程号，返回该课程的平均成绩
CREATE function averc(@cno varchar(12))
returns int
as
begin
 declare @aver int
 select @aver=
 (select avg(degree) from score where cno=@cno group by cno)
 return @aver
end
--建立函数avers，输入学号，返回该学生的平均成绩
CREATE function avers(@sno varchar(12))
returns int
as
begin
 declare @aver int
 select @aver=
 (select avg(degree) from score where sno=@sno group by sno)
 return @aver
end
--建立函数c，输入学号和课程号，返回该学生该门课的成绩
CREATE function c(@sno varchar(12),@cno varchar(12))
returns int
as
begin
 declare @aver int
 select @aver=
 (select degree from score where cno=@cno and sno=@sno)
 return @aver
end
----------------------------------
--下面建立函数stu_jg，满足题目规定的要求，最重要的学习如何在函数中执行自定义函数
create function stu_jg
(@sno varchar(12),@cno varchar(12))
returns varchar(500)
as
begin
 declare @message varchar(500),
 @sname varchar(12),@cname varchar(10),@score int,@avs int,@avc int
 if exists(select * from score,course,student where student.sno=@sno and
            course.cno=@cno and student.sno=score.sno and course.cno=score.cno)
   begin
     select @sname=sname,@cname=cname from score,course,student
           where student.sno=@sno and course.cno=@cno and
           student.sno=score.sno and course.cno=score.cno
           --此处关键是学习如何在定义函数中执行自定义函数
           exec @score=dbo.c @sno,@cno
           exec @avs=dbo.avers @sno
           exec @avc=dbo.averc @cno
           set @message='您查询的学生是:'+RTRIM(LTRIM(@sname))+',选择的课程是:'+RTRIM(LTRIM(@cname))+'成绩个人：'+RTRIM(LTRIM(cast(@score as varchar(20))))+'平均成绩：'+RTRIM(LTRIM(@avs))+'科平均：'+RTRIM(LTRIM(@avc))
   end
 else
          set @message='对不起查无此人,您输入的学生号码错误!'
 return @message
end
--下面开始测试该函数
declare @mess varchar(500)
set @mess='chushi'
exec @mess=dbo.stu_jg '103','3-105'
print @mess
 
l        了解 T-SQL代码的基本格式及注释方式，学习T-SQL语法的全局变量与局部变量，了解T-SQL的临时表和全局表，掌握T-SQL的运算符号有哪些？
l        熟练掌握 T-SQL的基本语法格式，包括：IF…ELSE条件语句，WHILE…CONTINUE…BREAK循环语句，CASE多条件分支语句，GOTO跳转语句，Try …Catch错误与意外处理语句。重点掌握循环语句和意外处理语句，特别是学习防止死循环的技巧。
l        学习系统函数、行集函数和 Ranking函数；重点掌握字符串函数、日期时间函数和数学函数的使用参数以及使用技巧。
l        重点掌握用户定义的标量函数以及自定义函数的执行方法，掌握用户定义的内嵌表值函数以及与用户定义的标量函数的主要区别。
l        了解游标的基本概念及特点，学会使用游标的基本步骤；掌握两个系统全局变量： @@cursor_rows和@@FETCH_ STATUS在定义游标中的作用，掌握在游标中使用FETCH获取游标技术，掌握FETCH语句使用过程中的移动关键词；掌握如何使用游标修改或删除数据；了解如何使用递归游标遍历树算法解决家族树的问题，了解改进的非游标查询策略。
l        了解什么是全文检索，全文检索和普通索引的区别是什么；熟练掌握配置全文检索服务，了解配置全文检索服务意外处理办法；熟练掌握通过 CONTAINS及FREETEXT谓词进行查询的技巧，并可以区分二者之间的差异；了解全文检索中降噪词的作用。
 
一 . 填空题
1、T-SQL就是_____________，是标准 SQL 在MSSQL环境下程式设计语言的增强版。
2、两个GO之间的SQL语句作为一个___________。
3、单行注释的注释符是________，多行注释的注释符是________。
4、引用全局变量时，必须以标记符___________开头；引用局部变量必须以标记符___________开头。
5、T-SQL局部变量赋值有两种语法形式，分别通过关键词_________和_________完成的。
6、局部临时表就是那些名称以_________开头的表，全局临时表是以_________开头的表。
7、在T-SQL运算符的优先级中，“>”与“OR”的优先级________高于________。
8、一个语句块是以_________开始，以_________语句作为终止，作为一个完全独立的逻辑单元存在于流程控制语句之中。
9、Transact-SQL 编程语言提供了四种函数分别是：_________、_________、_________和_________。
10、自定义函数的执行方法有两种，第一种是_________，第二种是______________。
11、使用游标的基本步骤分别是第一步：_______，第二步：_______，第三步：_______，第四步：_______。
二 . 选择题
1、请不要在下面的位置使用分号，除了_______
A. 不要在try end后面添加分号 B. 不要在if语句后面添加分号
C. 必须在SET之前添加分号     D. 不要在GO语句后面添加分号
2、Waitfor delay ’00:00:02’语句的意思是_______
A. 等待2秒时间   B. 等到凌晨0点0分2秒时刻触发
C. 还剩余2秒钟   D. 以上都不对
3、系统函数REPLACE（’第一个字符串’，’第二个字符串’，’第三个字符串’），下列表述正确的是_______
A. 用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式  
B. 用第一个表达式替换第二个字符串表达式中出现的所有第三个给定字符串表达式
C. 用第三个表达式替换第二个字符串表达式中出现的所有第一个给定字符串表达式  
D. 用第二个表达式替换第一个字符串表达式中出现的所有第三个给定字符串表达式
4、在游标中使用 FETCH过程中，参数 @@FETCH_ STATUS如果是1表示_______
A. 最近一次fetch命令成功的获取到一行数据 
B. 最近一次获取的行不可用，该行已经被删除
C. 最近一次fetch命令到达结果集的尾部  
D. 以上都不对
三 . 简答题
1、请你简述全局临时表和局部临时表的差异。
2、在Ranking函数中，ROW_NUMBER ( )函数和RANK( )函数的差异是什么？
3、游标的概念是是什么？游标具体使用的过程是什么？游标概念引入的目的是什么？
4、全文检索的概念，降噪词的作用。