实验7 Transact-SQL程序设计
《SQL Server 数据库》Transact-SQL程序设计实验报告
北华航天工业学院《数据库系统管理》实验报告报告题目：Transact-SQL程序设计作者所在系部：计算机科学与工程系作者所在专业：网络工程作者学号：作者姓名：指导教师姓名：完成时间：2011年9月26日北华航天工业学院教务处制Transact-SQL程序设计一、实验目的1、掌握Transact-SQL语言中注释、局部变量的用法；2、掌握Transact-SQL语言中常用运算符的用法；3、掌握Transact-SQL语言中常用函数的用法；4、掌握Transact-SQL语言中流程控制语句的用法。
二、实验内容（一）附加上次实验所创建的数据库“tb_Library”，并回顾该数据库的数据表信息。
（二）练习Transact-SQL语言中两种注释的用法。
（三）使用系统函数查看SQL Server当前安装的日期、版本和处理器类型，将结果记录在实验报告中。
（四）按要求在数据库“db_Library”上，完成以下功能。
1、编制一个函数fc_avgp,根据输入的图书类别名称，返回该类别图书的平均价格，并输入实参调用该函数。
2、编制一个函数fc_count,返回本月借书的人数，并输入实参调用该函数。
3、定义两个变量分别存放价格上限和下限，根据给定的两个变量的值，返回在该价格范围内的所有图书的信息。
4、定义变量“@tsmc”，将某图书的名称赋值给该变量，查询出该图书的借阅情况。
如果没有读者借阅，则显示“该图书无人借阅”，否则，显示所借读者的编号、姓名。
5、调整图书的库存量，如果原库存量不超过5本，则各增加50%的库存量，并采用向上取整；如果超过5本但不超过10本，则各增加30%的库存量，采用向上取整；如果超过10本但不超过20本，则各增加20%的库存量，并采用向下取整；如果超过20本，则增加10%的库存量，并采用向下取整。
6、判定是否有图书过期未还，如果有，则将所有图书的归还日期增加1个月，直到所有图书的归还日期都大于当前日期或者有图书的归还日期超过了2011年12月31日时停止，然后等待3秒后，显示出所有图书信息。
TransactSQL程序设计.ppt
用来保存数据值的对象。此外，还允许用table数据类型的局部变量来 代替临时表。
SQL Server 2000实用教程
2020年2月17日
7.2 Transact-SQL语言要素
7.2.5 运算符和表达式 1.运算符 运算符是执行数学运算、字符串连接以及比较操
传递数据的方式之一。变量有名字和数据类型两个属性。由系统或用 户定义并赋值。 SQL Server中的变量可以分为两大类：全局变量和局部变量。 1.全局变量 全局变量以@@开头，由系统定义和维护，不能由用户创建，对用户 来说是只读的，大部分的全局变量记录了SQL Server服务器的当前状 态信息。全部变量是不可以赋值的。
作的一种符号。SQL Server 2000使用的运算符 共有七类：算术运算符、比较运算符、逻辑运算 符、字符串连接运算符、位运算符、赋值运算符 和一元运算符。
SQL Server 2000实用教程
2020年语言要素
7.2.5 运算符和表达式
2．表达式 表达式是符号和运算符的组合，通过运算符连接
对于不符合标准标识符规则的，比如对象或对象名称的一 部分使用了保留关键字的，或者标识符中包含嵌入空格的， 都必须分隔。
SQL Server 2000实用教程
2020年2月17日
7.2 Transact-SQL语言要素
7.2.4 全局变量与局部变量 变量是用来临时存放数据的对象。是SQL Server用于在T-SQL语句间
4.CASE语句
5.GOTO语句
6.RETURN语句
7.WAITFOR语句
SQL Server 2000实用教程
Transact-SQL程序设计
Transact-SQL程序设计Transact-SQL基本语句1、定义批处理语句语句格式：GO注：一个批处理中任何一条语句有语法错误，整个批处理将不能编译和执行。
2．变量(1) 局部变量声明局部变量语句：DECLARE @<局部变量名> <数据类型>[,…]声明一个datetime类型的局部变量。
DECLARE @date_var datetime声明两个局部变量。
DECLARE @var1 int, @var2 money局部变量赋值语句：SET|SELECT @<局部变量名>=<表达式>用SET语句和SELECT语句为局部变量赋值。
DECLARE @var1 datetime,@var2 char (10)SET @var1 = getdate ()SELECT @var2 = convert (char (10), @var1, 102)用SET语句将查询的结果赋给局部变量，并用SELECT语句显示局部变量的值。
Declare @date_var datetimeSet @date_var= (select min (birthday) from s)SELECT @date_var AS min_birthday局部变量名的命名：首字符为字母，长度不超过128的字母、数字和特殊符号（_、@、#）组成的字符串，并且不能与全局变量同名。
局部变量声明后的值初始化为NULL。
局部变量的作用域仅限于一个批处理中。
(2) 全局变量在SQL Server 2000中，将全局变量改称为函数。
由于这些函数不需要任何参数，所以又称为无参函数。
SQL Server 2000无参函数见表1。
全局变量是SQL Server系统内部变量，以@@开头。
表1：SQL Server 2000无参函数用全局变量查看SQL Server的版本、当前所使用的SQL Server 服务器的名称以及所使用服务的名称等信息。
数据库原理与应用07.Transact-SQL程序设计
1 注释 2 变量 3 运算符 4 函数 5 流程控制语句
1 注释
注释是程序代码中不执行的文本字符串（也称 为注解）。在SQL Server中，可以使用两种类型的 注释字符：一种是ANSI标准的注释符“--”，它用于 单行注释；另一种是与C语言相同的程序注释符号， 即“/* */”。
表2-1 位运算符
运算符 &（按位 AND） |（按位 OR） ^（按位（两个操作数）。 按位互斥 OR（两个操作数）。
4．比较运算符
比较运算符用于比较两个表达式的大小或 是否相同，其比较的结果是布尔值，即TRUE （表示表达式的结果为真）、FALSE（表示表 达式的结果为假）以及UNKNOWN。除了 text、 ntext 或 image 数据类型的表达式外，比较运算 符可以用于所有的表达式。
3 运算符
运算符是一些符号，它们能够用来执行算术运算、 字符串连接、赋值以及在字段、常量和变量之间进 行比较。在SQL Server 2000中，运算符主要有以 下六大类：算术运算符、赋值运算符、位运算符、 比较运算符、逻辑运算符以及字符串串联运算符。
1．算术运算符
算术运算符可以在两个表达式上执行数学运 算，这两个表达式可以是数字数据类型分类的任何 数据类型。算术运算符包括加（+）、减（—）、 乘（*）、除（/）和取模（%）。
SELECT { @local_variable = expression } [ ,...n ]
2. 全局变量
全局变量是SQL Server系统内部使用的变量， 其作用范围并不仅仅局限于某一程序，而是任何 程序均可以随时调用。全局变量通常存储一些 SQL Server的配置设定值和统计数据。用户可 以在程序中用全局变量来测试系统的设定值或者 是Transact-SQL命令执行后的状态值。
实验七 T_SQL的程序设计
实验七T_SQL的程序设计一、实验目的1、掌握T_SQL的简单的变量的声明、赋值，输出语句。
2、学生能够编写简单的程序。
二、实验要求使学生了解T_SQL的编程的基础知识，掌握简单的变量的声明、赋值，输出以及流程控制语句。
三、实验环境1、操作系统：WinXp Professional2、数据库：SQL Server 2000个人版四、实验内容和步骤局部变量在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。
局部变量必须以“@”开头，而且必须先用DECLARE命令说明后才可使用。
其说明形式如下：DECLARE @变量名变量类型[@变量名变量类型…]在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值。
必须使用SELECT或SET命令来设定变量的值，其语法如下：SELECT @局部变量=变量值或SET @局部变量=变量值DECLARE 命令用于声明一个或多个局部变量、游标变量或表变量。
在用DECLARE 命令声明之后，所有的变量都被赋予初值NULL。
需要用SELECT 或SET命令来给变量赋值。
变量类型可为系统定义的或用户定义的类型，但不能为TEXT、NTEXT、IMAGE类型。
CURSOR 指明名变量是局部的游标变量。
语法如下:DECLARE {{ @local_variable data_type }| { @cursor_variable_name CURSOR }| { table_type_definition }} [，...n]]例7-1：声明一个长度为10 个字符的变量“id”并赋值10010001declare @id char（10）这个小括号是英文的括号select @id=此处是英文的左单引号…‟10010001‟‟这两个单引号都要英文状态下的左单引号print @id在查询分析器中的结果如图7-1所示。
图7-1简单的赋值语句窗口注意：可以在Select命令查询数据时，在Select命令中直接将列值赋给变量例7-2在屏幕中输出“科技、SQL Server”字样declare @x char(4), @y char(7) 英文状态下的逗号select @x = 'SQL', @y = 'Server'print '科技'print @x +@y在查询分析器中的结果如图7-2所示。
实验7 T-SQL语言程序设计
实验七T-SQL语言程序设计实验目的与要求⏹熟练掌握变量的定义和赋值。
⏹熟练掌握用户自定义数据类型定义、使用和删除。
⏹熟练掌握各种运算符。
⏹熟练掌握流程控制语句，尤其是条件语句和循环语句。
⏹熟悉并掌握常用的系统函数。
二、实验内容和步骤1、为提高学生学习动力，学校推出一项举措：根据本年综合测评成绩来确定下一年允许图书借阅本书，综合测评<450 允许借阅3本；综合测评>=450且综合测评<500允许借阅4本；综合测评>=500且综合测评<550允许借阅5本；综合测评>=550允许借阅6本，请在学生表中添加“借阅本数”列存放根据综合测评列生成的信息。
添加“借阅本数”列：ALTER TABLE[ex7].[dbo].[学生]ADD借阅本数INT根据“综合测评”计算“借阅本数”：UPDATE[ex7].[dbo].[学生]SET借阅本数=CASEWHEN综合测评<450 THEN 3WHEN综合测评>=450 AND综合测评<500 THEN 4WHEN综合测评>=500 AND综合测评<550 THEN 5WHEN综合测评>=550 THEN 6END2、创建触发器实现，在向学生表中添加学生或将当前学生的综合测评值变更时，自动在借阅本数列输入相应的值。
--创建触发器CREATE TRIGGER TON学生AFTER UPDATEASDECLARE@OLD_SCORE FLOAT,@NEW_SCORE FLOAT,@BORROW_NUM INT,@ID FLOAT SELECT@OLD_SCORE=综合测评FROM DELETEDSELECT@NEW_SCORE=综合测评,@ID=学号FROM INSERTEDIF UPDATE(综合测评)BEGINUPDATE学生SET借阅本数=CASEWHEN@NEW_SCORE<450 THEN 3WHEN@NEW_SCORE>=450 AND@NEW_SCORE<500 THEN 4WHEN@NEW_SCORE>=500 AND@NEW_SCORE<550 THEN 5WHEN@NEW_SCORE>=550 THEN 6ENDWHERE学号=@IDEND验证：UPDATE学生SET综合测评= 455WHERE学号='1606050228'CREATE TRIGGER T1ON学生AFTER INSERTASDECLARE@NEW_SCORE FLOAT,@BORROW_NUM INT,@ID FLOATSELECT@NEW_SCORE=综合测评,@ID=学号FROM INSERTEDBEGINSET借阅本数=CASEWHEN@NEW_SCORE<450 THEN 3WHEN@NEW_SCORE>=450 AND@NEW_SCORE<500 THEN 4WHEN@NEW_SCORE>=500 AND@NEW_SCORE<550 THEN 5WHEN@NEW_SCORE>=550 THEN 6ENDWHERE学号=@IDENDINSERT INTO[ex7].[dbo].[学生](学号,综合测评)VALUES ('1',425)3、各学院对学生的综合测评进行汇总，其中统计分数在500分以上的学生的人数占学院总人数的百分比。
第7章 Transact-SQL程序设计
7.1 变量与表达式
7.1.1 变量
2．局部变量 （1）局部变量的声明
【格式】DECLARE @局部变量名 数据类型[,…n] 【功能】声明由“局部变量名”指定的局部变量。
7.3 程序控制语句
7.3.2 IF…ELSE语句
【格式】 IF <条件> <语句1> [ELSE <语句2>]
【功能】如果条件为TRUE执行语句1，如果条件为FALSE执行语句2。如 果省略“ELSE 语句2 ”，则条件为FALSE时什么也不执行。
【例7-3】求出课程号为“60002”的课程的优秀人数并输出。
7.1 变量与表达式
7.1.1 变量
2．局部变量 （2）局部变量的赋值
【格式1】SET <@变量名>=<表达式> 【功能1】把“表达式”的值赋给“@变量名”指定的局部变量。 【格式2】SET <@变量名>=<表达式> 【功能2】把“表达式”的值赋给“@变量名”指定的局部变量。 【格式3】SELECT <字段名1>，……，<字段名n>， <@变量名>=<表达式> SELECT 'sql server 6.5', 256*256 SELECT 字符串常量='sql server 6.5', 计算结果=256*256 FROM <表名> WHERE <条件> 【功能3】把从“表名”指定的表中，按照WHERE子句中“条件”选取 记录，把计算出来的“表达式”的值赋给“@变量名”指定的局部变量。 如果SELECT语句返回多个数值，则局部变量取最后一个返回值 。
第7章 Transact-SQL程序设计基础
@@FETCH_STATUS @@identity @@IDLE
说明
返回FETCH语句游标的状态 返回最新插入的identity列值 返回自SQL Server本次启动以来，CPU空闲 的时间，单位为毫秒 返回本地当前使用的语言标识符 返回当前使用的语言名称 返回当前的锁定超时设置，单位为毫秒 返回SQL Server允许同时连接的最大用户 数目 返回当前服务器设置的decimal和numeric数 据类型使用的精度 返回当前存储过程的嵌套层数 返回当前SET选项信息
。
7.7 控制流语句
（1）简单CASE格式 其语法格式如下：
•分隔标识符的格式规则
7.1 标识符
3. 使用标识符
数据库对象的名称被看成该对象的标识符。 一个对象的全称语法格式为： server.database.owner.object
•可用的简写格式包含下面几种： server.database..object server..owner.object server…object database.owner.object
BREAK
CONTINUE
跳出循环语句
重新开始循环语句
7.7 控制流语句
1. BEGIN…END语句 BEGIN…END语句用于将多个Transact-SQL语句组合 为一个逻辑块。
语法格式为：
BEGIN { sql语句|语句块 } END
7.7 控制流语句
下面几种情况经常要用到BEGIN和END语句：
7.2数据类型
2. 用户定义数据类型
•创建用户定义的数据类型时必须提供以下3个参数：
– 名称
– 新数据类型所依据的系统数据类型 – 该类型是否为空值
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验7 Transact-SQL程序设计
一、实验目的
1.掌握Transact-SQL的数据类型、常量变量、表达式等概念。
2.掌握SQL Server 2005中常用函数的用法。
3.掌握程序中注释的基本概念和使用方法。
4.了解程序中的流程控制语句。
二、实验准备
1．了解函数的使用方法。
2．了解系统提供的常用数学函数、日期和时间函数、字符串函数和数据类型转换函数的用法。
3．了解程序中注释的语法格式。
4．了解程序中的流程控制语句：IF-ELSE、CASE、WHILE等控制流语句。
三、实验内容和步骤
1．在查询分析器中，选择studentsdb数据库，在学生表中查找姓“张”的学生，并将该生姓名赋于变量@stu_name。
提示：首先要定义变量@stu_name。
2．定义int型局部变量@grademax、@grademin、@gradesum，在成绩表中查找课程编号是“C002”课程的最高分、最低分和总分，分别赋给@grademax、@grademin和@gradesum，并显示。
3. 使用SET命令将查询结果集记录数目赋值给int型局部变量@row。
给下面代码中的划线处填上适当的内容，以完成上述操作。
DECLARE @rows____int_______
SET _____@rows _______=(SELECT COUNT(*)FROM 成绩表)
_____ SELECT ______@rows --显示@rows的值
4．以下代码在课程表中插入新记录：
DECLARE @intCId int,@intErrorCode int
INSERT INTO 课程表(课程编号,课程名称,学分)
VALUES('0006','VB程序设计',2)
SELECT @intCId=@@identity,@intErrorCode=@@error
SELECT @intCId,@intErrorCode
将该代码段连续执行两次，观察两次显示的信息及课程表中数据的变化，为什么前后两次执行时显示的信息会不同?
提示：@@identity,@@error参看教材P172 表9-2
生和女生人数。
6．在成绩表中，使用适当函数找出“高等数学”课程的最高分、最低分和平
均分，并分别赋予@grademax、@grademin、@gradesum。
7．定义一个datetime型局部变量@studate，以存储当前日期。
计算学生表中的学生的年龄，并显示学生的姓名、年龄。
在以下代码的划线部分填人适当内容，以实现上述功能。
DECLARE___@studate ____ datetime
SET @studate=______ GETDATE()_____ --给@studate赋值为当前日期
SELECT 姓名，___ YEAR(GETDATE())-YEAR(出生日期)______ AS 年龄
FROM 学生表
提示：计算年龄可参考教材表9-9和9-10相关函数。
8．请用单行注释将下列命令行所实现的功能予以注释（填入括号内）。
（ --查找学生表中姓陈的人的姓名与家庭住址）
SELECT 姓名, 家庭地址
FROM 学生表
WHERE 姓名 LIKE '陈%‘
9.请用多行注释将下列命令行所实现的功能予以注释（填入括号内）。
（ /*查找学生表中80后学生的姓名、性别、出生年月和家庭住址情况
*/ ）
SELECT 姓名, 性别，出生年月，家庭地址
FROM 学生表
WHERE YEAR(出生年月)>80
10.在局部变量@stu_id记中存储了学号值。
编写代码查询学号为0001的学生的各科平均成绩，如果平均分>=60则显示“你的成绩及格了，恭贺你!!”，否
则显示“你的成绩不及格”。
11．运行以下代码段，写出运行的结果。
12．编写程序查询成绩表。
如果分数大于等于90，显示优秀；如果分数大于等于80小于90显示良好；如果分数大于等于70小于80显示中等；如果分数大于等于60小于70显示及格，其他显示不及格。
13．计算grade表的分数列的平均值。
如果小于80，则分数增加其值的5％；如果分数的最高值超过95，则终止该操作。
在以下代码划线处填入适当的内容以完成上述功能。
WHILE(SELECT A VG (分数)FROM成绩表)<80
BEGIN
UPDATE成绩表
SET分数=分数*1.05
IF(SELECT MAX(分数)FROM成绩表)>95
BREAK
ELSE
CONTINUE
END
四、实验思考
1．编写代码计算并显示@n=1+2+3+ (20)
代码：DECLARE @s int,@n int,@t int,@c int
SET @n=0
SET @s=1
SET @c=1
SET @t=0
WHILE @s<=20
BEGIN
SET @t=@t+ @c
SET @c=@c + 1
SET @s=@s+1
END
SET @n=@n+@t
SELECT @n
2．区分局部变量与全局变量的不同，思考全局变量的用处。
答：局部变量使用DECLARE语句定义，仅存在于声明它的批处理、存储过程或触发器中，处理结束后，存储在局部变量中的信息将丢失。
通常局部变量的首字母为单个@。
全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息，不能显示地被赋值或声明。
全局变量由系统维护，名称前常加@@符号。
全局变量的作用：通常被服务器用来跟踪服务器范围和特定会话期间的信息。
数据库技术与应用实验实验7 Transact-SQL程序设计南京师范大学中北学院11。