实验3  PL/SQL
1．实验目的与要求
（1）熟悉T-SQL的流程控制语句、游标的使用
（2）掌握创建、执行、修改及删除存储过程的方法
2．实验内容
（1）使用T-SQL的流程控制语句、游标，完成对数据的处理
编写程序完成以下功能，在查询分析器中执行程序，并记录结果。
1）在score表中求某班学生某门课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。
2）查询某班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌。
3）在student表中先插入三条新记录，其中的Pollity字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“众”。
4）根据score表中考试成绩，查询某班学生某门课程的平均成绩，并根据平均成绩输出相应的提示信息。
5）根据score表中考试成绩，查询某班学生的考试情况，并使用CASE将课程号替换为课程名称。
6）根据t_score表中考试成绩，查询某班学生的考试情况，并根据考试分数输出考试等级。
（2）存储过程的创建与调用
a. 按要求完成以下功能，并记录结果。
1）创建一个存储过程stu_scoreinfo，完成的功能是在表student、表course和表score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
2）创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student中查询此学生的信息。
3）创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student中计算此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。
4）执行stu_scoreinfo存储过程（无参）。
5）执行存储过程stu_info（该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值）。
6）执行存储过程stu_age（该存储过程有一个输入参数“学号”和一个输出参数@age。存储过程执行完后应有一个返回的状态值，这个值可以从返回的错误号得到）。
7）使用系统存储过程sp_help、sp_helptext、sp_depends、sp_stored_procedures查看用户创建的存储过程。
8）删除存储过程stu_scoreinfo。
b. 按要求完成以下功能，在查询分析器中执行程序，并记录结果。
1）编写一个存储过程pro_avgscores。求score表中各班级的各门课程的平均分。主程序调用该存储过程，显示各班级、各课程的平均分。
2）编写一个存储过程pro_sumscores。要求根据输入的班级号和课程号，求得此班此门课程的总分。主程序调用该存储过程，查询指定班级的某课程的总分。
3）编写一个存储过程，用于查询给定姓名的学生，如果没到，返回0，否则返回满足条件的学生人数。主程序调用该存储过程，查询“李浩”的学生，并根据返回值进行输出。
思考与实践
对“图书借阅数据库”中的JS、TS、CBS、JY表进行以下操作：
1．查询各借书人的借阅量（若重复借阅一本书，则以一本书计），要求得到：借书证号、姓名、借书量。
2．查询指定指定借书人的借书最多的一次所借的图书信息及还书时间。
知识准备
1．批处理与执行计划
T-SQL程序可以由若干批处理组合而成，各批处理之间用GO语句分隔；一个批处理是由多条T-SQL语句组成的语句组，是从应用程序一次性发送到SQL Server服务器执行。
执行计划为一个可执行单元，由批处理编译而成。
2．注释
SQL Server 2005中有两种注释方法：
（1）单行注释  用 “--”开始，到行结束
（2）多行注释  用“/*”开始，到“*/”结束
3．变量
SQL Server 2005允许使用全局变量和局部变量。
（1）全局变量  以@@开始，由系统定义和维护，用于记录SQL Server 2005的运行状态和有关信息。
常用的全局变量有：
@@CURSOR_ROWS  返回本次连接最后打开的游标中当前存在的合格行数
@@ERROR  返回最后执行的T-SQL语句的错误代码，0表示未出错
@@FETCH_STAUS  返回最近一次FETCH语句的状态值
@@ROWCOUNT  返回上一次语句影响的数据行的行数，0表示不返回记录或操作不成功
（2）局部变量  以@开始，由用户定义，作用范围为定义所在的批处理中。
定义格式：Declare  @变量名 数据类型
局部变量不能定义为text、ntext或image数据类型。定义后，系统设置其初始值为NULL，用户可以通过SET命令或SELECT命令重新设置局部变量的值；使用SELECT命令返回局部变量值。
设置局部变量：
Set  @变量名=表达式
Select  @变量名1=表达式，[@变量名2=表达式，…]
返回局部变量：
Select  @变量名 As 字符串  --将变量的值显示在字符串（列标题）的下方
4．运算符
SQL Server 2005中运算符主要有六类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串连接运算符。
（1）算术运算符  用于完成数学运算。
加（+）、减（-）、乘（*）、除（/）：计算对象的数据类型为数值型
取模（%）：计算对象的数据类型为ints、mallint和tinyint
（2）赋值运算符 
    赋值号（=）：将数据值指派给特定对象；用于SELECT命令中，在列标题与列对应值表达式之间建立对应关系。
（3）位运算符
按位与（&）、按位或（|）、按位异或（^）和求反(~)：操作对象为整型或二进制数据（image数据类型除外）；按位与（&）、按位或（|）、按位异或（^）的两个操作数不能同时为二进制数据。
（4）比较运算符
等于（=）、大于（>）、大于等于（>=）、小于（<）、小于等于（<=）、不等于（<>或!=）、不小于（!<)、不大于（!>)：比较对象的类型为除text、ntext或image类型以外的数据类型；结果值为TRUE（比较结果为真）、FALSE（比较结果为假）以及UNKNOWN。
（5）逻辑运算符  用于连接比较表达式。
非（NOT）、与（AND）和或（OR）：结果值为TRUE或FALSE。
（6）字符串连接运算符 
连接符（+）：将多个字符串依次连接成一个字符串。
（7）运算符的优先级
由高到低依次为：括号（）→乘（*）、除（/）、取模（%）→加（+）、减（-）→比较（=、>、>=、<、<=、<>或!=、!<、!> →非（NOT）→与（AND）→或（OR）
5．函数
常用的内置函数有：
（1）字符串函数
LTRIM（）、RTRIM（）、SUBSTRING（）、LEFT（）、RIGHT（），SPACE（）
（2）日期和时间函数
GETDATE（）、YEAR（）、MONTH（）、DAY（）
（3）数学函数
ABS（）、RAND（）、SIGN（）、SQRT（）、EXP（）、LOG（）
（4）查询聚集函数
COUNT（）、SUM（）、AVG（）、MAX（）、MIN（）
（5）转换函数
CAST（）
6．流程控制语句
（1）Begin …End语句
将多条语句组合成一条语句
（2）If…Else语句（单分支语句）
If  条件
      语句1
Else
      语句2
执行过程：如果条件成立，则执行语句1
否则，当有Else子句时，执行语句2；没有Else子句则不执行操作。 
（3）Case语句（多分支语句）
格式1：
Case
        When  条件1  Then 结果表达式1
When  条件2  Then 结果表达式2
        …
        Else  结果表达式n+1
End
执行过程：依次比较WHEN后的条件，返回成立的条件所在行的THEN后的表达式的值，如果没有成立条件，则返回ELSE后的表达式值。
格式2：
Case  表达式
        When 可能值1  Then 结果表达式1
When 可能值2  Then 结果表达式2
        …
        Else  结果表达式n+1
End
执行过程：先计算Case后的表达式，依次与When后的表达式值比较，返回等值所在行的Then后的表达式的值，如果没有，则返回Else后的表达式值。
（4）While…Continue…Break语句
While  条件
Begin
语句1
Break
语句2
Continue
语句3
END
执行过程：当条件成立时，执行循环体，当执行到Break时，循环终止；执行到Continue时，回到While，进入下一次循环；条件不成立时，结束循环。
（5）Waitfor语句
sql语句替换表中内容格式1：
Waitfor  Delay  ‘时间间隔’
等过了设定的时间间隔，再继续执行
格式2：
Waitfor  Time ‘某一时间’
等到了设定的时间，再继续执行
时间间隔和某一时间的形式均为：时：分：秒
（6）Return语句
Return 整型表达式
返回整型表达式的值。一般用于自定义函数中。
7．游标
关系数据库管理系统实质是面向集合数据的，而应用程序则是面向行数据的，游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。游标允许应用程序对查询语句Select 返回的行结果集中每一行进行相同或不同的操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力。
使用游标的基本步骤：声明游标、打开游标、提取数据、关闭游标和释放游标。
（1）声明游标
像使用其它类型的变量一样，使用一个游标之前，首先应当声明它。游标的声明包括两个部分：游标的名称、该游标所用到的SQL语句。
Declare  游标名 Cursor  For  SQL语句
（2）打开游标
声明了游标后在作其它操作之前，必须打开它。打开游标是执行与其相关的一段SQL语句。
Open  游标名
（3）提取数据
当用Open语句打开了游标并在数据库中执行了查询后，并不能立即利用在查询结果集中的数据。必须用Fetch语句来取得数据。
Fetch  [First| Last|Prior|Next]  From 游标名 Into  目标变量名1, …
[First| Last|Prior|Next] 用于说明提取数据的位置，含义依次为第一行、最后一行、当前行的上一行和当前行的下一行。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。