SQL实用教程(第四版)实验五T-SQL编程

第6章T-SQL编程教学目标本章介绍了T-SQL程序设计中GO语句的使用、变量的定义、输入输出的格式以及注释的使用，介绍了流程控制语句以及游标的使用。

教学要求第6章T-SQL编程导读上一章介绍的插入、查询、更新和删除等SQL语句均是单条的语句，不能定义变量，没有流程控制语句，因此，无法实现复杂的业务规则控制。

为解决此问题，本章介绍SQL程序设计，将学习变量的定义、赋值、流程控制和游标等内容。

其中流程控制语句包括顺序、选择、循环结构的控制语句，是数据库应用程序设计的基础。

通过本章学习，读者应掌握如何用流程控制语句进行SQL设计程序，理解游标的意义，掌握游标的创建与使用。

6.1 T-SQL基础6.1.1 GO的使用批处理是一组SQL语句的集合，以结束符GO终结。

批处理中的所有语句被一次提交SQL Server 2005，SQL Server 2005将这些语句编译为一个执行单元，称为SQL Server 2005执行计划。

批处理用GO语句作为批处理的结束标志，若没有GO语句，默认所有的语句属于一个批处理。

提示：在一个批处理中，如果出现编译错误(如某条语句存在语法错误)，SQL Server 2005将取消整个批处理内所有语句的执行。

6.1.2 T-SQL变量变量是SQL Server 2005用来在语句之间传递数据的方式之一，是一种语言中必不可少的组成部分。

1．T-SQL变量T-SQL变量也称为局部变量，或用户自定义变量。

一般用于临时存储各种类型的数据，以便在SQL语句之间传递。

例如作为循环变量控制循环次数，暂时保存函数或存储过程返回的值，也可以使用table类型代替临时表临时存放一张表的全部数据。

局部变量的作用范围是在一个批处理、一个存储过程或一个触发器内，其生命周期从定义开始到它遇到的第一个GO语句或者到存储过程、触发器的结尾结束，即局部变量只在当前的批处理、存储过程、触发器中有效。

提示：如果在批处理、存储过程、触发器中使用其他批处理、存储过程、触发器定义的变量，则系统出现错误并提示“必须声明变量”。

5 T-SQL编程
5.5 事务
5.5.1 事务及事务语句
事务（Transaction）：一个指令序列，或全部完成，或全部都不完成的，是一个不可分割的逻辑工作单元。

5.5.2 三类事务示例
假设C表中已经存在记录('11', 'aaa',2)，但不存在记录('12', 'bbb',2)。

5.6 触发器
5.6.1 触发器概念
触发器：由INSERT/UPDATE/DELETE事件触发执行的程序。

触发器的意义：自动执行，实现用户定义的完整性约束。

1、限制插入不合适的记录、限制删除或更新特定的记录。

（超越CHECK约束、参照完整性约束等）
2、可以对相关表进行级联更改，在插入/修改/删除一条记录的同时在其他表中插入/修改/删除相关记录。

1
若两种触发器同时存在，先执行“INSTEAD OF”后执行“AFTER”。

2、参数结构
插入数据时：inserted表中是新值，deleted表为空。

删除数据时：deleted表中是待删除值，inserted表为空。

更新数据时：deleted表中是原值，inserted表中是新值。

5.6.2 INSTEAD OF类型的触发器
5.6.3 AFTER触发器。

实验五 T-SQL中的函数定义和调用一、实验目的1. 熟悉SQL Server常用的内置函数的功能和调用(包括日期时间函数、字符串处理函数和数学函数等)。

2. 熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。

3. 正确区分两种表值函数的应用场合。

二、实验环境SQL Server 2000个人版及其交互查询工具isqlw(即查询分析器)。

三、实验内容和要求以下各个题除非独立的程序设计，否则均以图书管理数据库为应用背景。

1. 定义函数ISPRIME，其功能是判断某个整数是否是素数。

ISPRIME接受一个INT参数num，返回BIT类型的判断结果，若参数num是素数返回1；否则返回0。

最后，通过对100～200之间的所有整数用ISPRIME函数检验，打印其中的所有素数。

(最好在设计ISPRIME 函数时使用内置函数SQRT缩小检查范围，即逐一判断2～sqrt(num)之间是否存在被num 整数的数。

另外，在打印时最好分若干行输出素数(提示：用CAST函数进行类型转换)) 2. 定义一个内嵌表值函数book_info，该函数根据调用时传递的书名，返回该书的有关信息。

book_info函数的调用语法是SELECT * FROM book_info('Web站点安全')。

3. 定义一个多语句表值函数reader_history，接受某位读者的借书证号，返回该读者的借阅历史，包括借书证号、姓名、专业名、所借书籍的ISBN号、借书时间和还书时间，其中还书时间是借书之日起的第30天。

(提示：可以考虑使用系统函数DATEADD来计算还书时间。

请大家通过联机帮助文档来了解函数DATEADD的语法格式)4. 定义一个函数，返回某个专业(调用时传递专业名)借书最多的同学的学号。

要求：提交源程序并标识必要的注释。

保证程序能正确编译和运行，并根据以下要求认真填写实验报告。

XX大学XX学院图1图22、用户存储过程的编辑修改首先创建存储过程select_students, 在此基础上，将该过程更改为检索计算机专业的学生信息，用修改过程命令重新定义该存储过程。

2.1 输入以下代码：alter procedure select_students with encryptionasselect*from Student whereSdept='CS'order by Sno2.2 通过EXEC语句执行存储过程输入语句：EXEC select_students输出结果如下：3、用户存储过程的删除3.1 通过DROP语句删除存储过程输入语句：DROP procedure select_students输出结果如下：二、编写触发器1、在SQL中展开Student_and_Course数据库，在其中选择对应的表，将表展开，在展开的选项中右击“触发器”，在弹出的快捷菜单中选择“新建触发器”命令，在如图3所示的触发器编辑区中给出对应的触发器代码。

2、创建一个名为TR_AGEde1的触发器，要求在插入和更新时检查Student表中Sage是否在15至60之中，如果不在提示“年龄不合法”。

输入语句：CREATE TRIGGER TR_AGEON StudentFOR INSERT,UPDATEASDECLARE@age INTSELECT@age=SageFROM StudentIF@age NOT BETWEEN 15 AND 60BEGINROLLBACK TRANSACTIONRAISERROR ('年龄不合法',16,10)END图33、执行以下更新数据库的SQL代码，将在显示框内显示有关的信息，表示触发了数据表Student中的触发器TR_AGE.输入语句：UPDATE StudentSET Sage=26WHERE Sname='李勇'执行结果如图5：图54、同样，执行以下命令也会触发触发器TR_AGE.输入语句：INSERT INTO Student VALUES('200215128','张博','男',17,'CS') 完成3、4两步的输出结果如图6：图65、查看所创建触发器的详细信息，输入以下代码：USE student_and_courseEXEC sp_helptrigger StudentEXEC sp_helptext TR_AGE执行结果如图7：图7。

实验五 T-SQL编程实验报告姓名：张娜成绩：___________【实验目的】1、掌握批处理、脚本的概念。

2、掌握SQL Server中局部变量、全局变量和内置函数的概念和应用。

3、掌握SQL Server流程控制语句的使用。

4、掌握SQL Server三种用户自定义函数的创建和使用方法。

5、掌握游标的建立和使用。

6、掌握事务的建立和使用。

【实验内容】2、解释每一命令的语义，执行以上语句，系统报错，说明错误原因并改正。

use salesgo__将当前数据库设置为sales数据库declare @maxprice money/*定义money类型的变量@maxprice*/select @maxprice=max(price) from productswhere price is not null/*在products表中选出最大的价格数并赋值给变量@maxprice*/select [@maxprice]=@maxprice/*输出变量@maxprice*/print @@rowcount/*输出全局变量@@rowcount*/变量名出现错误，第二个go的位置不对3、（1）编写使用ROUND()函数的示例。

（2）编写使用SUBSTRING()函数的示例。

（3）编写使用DATEPART()函数的示例。

（4）编写显示当前登录帐户名的语句。

Declare @a int,@b char(5)Select a=round(2.235,1)Select a=round(2.235,2)Set @b=substring('abcdefghij',3,5)select 'b'=@bSelect '月数'=datepart(mm,'2009-5-24')select 'suser id'=suser_id(),'suser name'=suser_name(),'suser sid'=suser_sid(),'suser sname'=suser_sname(),'user id'=user_id(),'user'=user,'suser sid1'=suser_sid('login')select 'current user'=current_user4、修改CASE表达式后的T-SQL语句。

（二 〇 一 五 年 四 月《数据库原理及应用》实验报告学校代码： 10128 学 号： 201120905048题 目：T-S Q L 编程 学生姓名：孙跃 学 院：理学院 系 别：数学系专 业：信息与计算科学 班 级：信计12-2 任课教师：侯睿一、实验目的（1）进一步巩固第2章～第4章所学内容；（2）掌握用户自定义类型的使用；（3）掌握变量的分类及其使用；（4）掌握各种运算符的使用；（5）掌握各种控制语句的使用；（6）掌握系统函数及用户自定义函数的使用。

二、实验内容1.自定义数据类型的使用。

（1）对于实验2给出的数据库表结构，再自定义一数据类型ID_type，用于描述员工编号。

（2）在YGGL数据库中创建Employees3表，表结构与Employees类似，只是EmployeeID列使用的数据类型为用户自定义类型ID_type。

2.变量的使用。

（1）对于实验2给出的数据库表结构，创建一个名为female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女员工的编号、姓名。

（2）定义一个变量，用于获取号码为102201的员工的电话号码。

3.运算符的使用。

（1）使用算数运算符“-”查询员工的实际收入。

（2）使用比较运算符“>”查询Employees表中工作时间大于5年的员工信息。

4.流程控制语句。

（1）判断Employees表中是否存在编号为11006的员工，如果存在则显示该员工信息，不存在则显示查无此人。

（2）假设变量X的初始值为0，每次加1，直至X变为5。

（3）使用CASE语句对Employees表按部门进行分类。

5.自定义函数的使用。

（1）定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。

（2）写一段T-SQL程序调用上述函数。

6.系统内置函数的使用。

（1）求一个数的绝对值。

（2）求财务部雇员的总人数。

实验5使用T-SQL编写存储过程访问数据库实验五使用T-SQL编写存储过程访问数据库【目的与要求】熟悉使用存储过程来进行数据库应用程序的设计。

【实验设备与环境】在实验一中安装的SQL SERVER 2008及其T-SQL执行引擎。

【实验内容】对学生-课程数据库，编写存储过程，完成下面的功能：（1）统计某课程（如离散数学）的成绩分布情况，即按照各分数段统计人数；（2）统计任意一门课的平均成绩。

要求：提交源程序并表示必要的注释。

保证程序能正确编译和运行并截取程序执行结果图。

附：实验内容(１)参考程序create procedure p_satscore @cno char(4) --course number asbegindeclare @score1 intdeclare @score2 intdeclare @num intdeclare @cname varchar(10) --course nameset @cname=(select cname from course where cno=@cno) print '<'+@cname+'>'+'考试成绩按照分数段统计情况'set @score1='100'set @score2='90'while(@score1>=0)beginset @num=(select count(*) from sc where cno=@cno and grade between @score2 and @score1)print str(@score2)+'至'+str(@score1)+'分人数为'+str(@num) set @score1=@score2-1if @score1>=60set @score2=@score2-10elseset @score2=0endend。

第五章Transant—SQL程序设计5．1 Transant—SQL程序设计基础5.1.1 变量5.1.2 运算符5.1.3函数5.2 程序流程控制5.3游标5.4 存储过程5.4.1 存储过程的概念5.4.2 在企业管理器中创建存储过程5.4.3 使用CREATE PROC语句创建存储过程5.4.4 修改和删除用户存储过程5.5 触发器5.5.1 触发器的概念5.5.2 创建触发器5.5.3 修改和删除触发器5．1概述：两个GO之间的SQL语句作为一个批处理。

在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。

这样的语句组从应用程序一次性地发送到SQL Server 服务器进行执行。

SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。

Go:执行自上一个GO语句以来的所有命令和语句.不能的T-SQL语句同处一行.在SQL Server中，有两种类型的注释字符：⏹单行注释：使用两个连在一起的减号“- -”作为注释符；例:Use teachdbSelect s_no,s_name from student --这是一个行注释信息语句⏹多行注释：使用“/* */”作为注释符。

/*这是一个多行注释的例子下面的代码是查询学生的学号和姓名的*/Use teachdbSelect s_no,s_name from student5.1.1 变量1 全局变量全局变量在整个SQL Server系统内使用。

存储的通常是一些SQL Server的配置设定值和统计数据。

这些变量不能由用户定义，也不能由用户赋值。

在使用全局变量时应该注意以下几点：⏹全局变量是在服务器级定义的。

⏹用户只能使用预先定义的全局变量。

⏹引用全局变量时，必须以标记符“@@”开头。

⏹全局变量对用户来说是只读的。

⏹局部变量的名称不能与全局变量的名称相同。

⏹见表5.1P175⏹例5.1:利用@@servername查看本地服务器名称,并显示截止到当前时刻试图登录SQL的次数:SELECT @@servername as …服务器名‟@@CONNECTIONS AS …登录次数‟例5.2查看STUDENT 表的报有记录并利用@@ROWCOUNT统计记录数.USE teachdbGoSelect * from studentSelect @@ROWCOUNT as …第一次查询返回的记录数‟6.3.2 局部变量利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。

实验5 使用T-SQL语句管理数据表数据一、实验目的熟悉数据表结构及使用特点；熟悉使用Management Studio界面方式管理数据表数据；熟悉使用T-SQL语句管理数据表数据。

二、实验环境已安装SQL Server 2005的计算机；具有局域网环境,有固定IP;三、实验学时2学时四、实验要求了解SQL Server数据表数据的管理方法；了解SQL Server数据类型；完成实验报告（给出T-SQL语句）。

五、实验内容及步骤(1)请分别使用Management Stuio界面方式及T-SQL 语句实现进行以下操作：向各个数据表中插入如下记录：学生信息表（student）Sno Sname Ssex Sage Sdept200515001 赵菁菁女23 CS200515002 李勇男20 CS200515003 张力男19 CS200515004 张衡男18 IS200515005 张向东男20 IS200515006 张向丽女20 IS200515007 王芳女20 CS200515008 王民生男25 MA200515009 王小民女18 MA200515010 李晨女22 MA200515011 张毅男20 WM200515012 杨磊女20 EN200515013 李晨女19 MA200515014 张丰毅男22 CS200515015 李蕾女21 EN200515016 刘社男21 CM200515017 刘星耀男18 CM200515018 李贵男19 EN200515019 林自许男20 WM200515020 马翔男21200515021 刘峰男25 CS200515022 牛站强男22200515023 李婷婷女18200515024 严丽女20200515025 朱小鸥女30 WM课程信息表（course）Cno Cname Cpno Ccredit1 数据库 5 42 数学 23 信息系统 1 44 操作系统 6 35 数据结构7 46 数据处理 27 PASCAL语言 6 48 大学英语 49 计算机网络 410 人工智能 2 选课信息表（sc）Sno Cno Grade200515001 1 75200515002 1 85200515002 3 53200515003 1 86200515004 1 74200515005 1 58200515006 1 84200515004 2 46200515005 2 89200515006 2 65200515008 2 72200515009 2 76200515010 2 96200515010 8 86200515011 8 62200515015 8 0200515018 8 58200515001 4 62200515002 4 85200515021 9 54200515001 5 58200515021 6 58200515001 7 70200515005 10 65200515016 8 Null200515017 8 Null2.修改CS系姓名为“李勇”的学生姓名为“李咏”；update studentset Sname ='李咏'where Sname ='李勇'and Sdept ='CS';3.修改课程“数据处理”的学分为3学分；update courseset Ccredit = 3where Cname ='数据处理';4.将选修课程“1”的同学成绩加5分；update scset Grade = Grade + 5where Cno = 1;5.将选修课程“大学英语”的同学成绩加5分；update scset Grade = Grade +5where Cnoin(select Cnofrom coursewhere ame ='大学英语'and o = o);6.将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；update studentset Sname='王丹丹',Ssex='女',Sage='20',Sdept='MA'where Sno ='200515010';7.删除数据表student中无专业的学生记录；delete studentwhere Sdept is NULL;9.删除数据表student中计算机系年龄大于25的男同学的记录；delete studentwhere Sdept ='CS'and Sage > 25 and Ssex ='男';10.删除数据表course中学分低于1学分的课程信息；delete coursewhere Ccredit < 1;。