数据库原理实验4指导书新.doc

数据库原理实验4指导书新
实验4 T-SQL程序设计
实验目的：
1. 掌握T-SQL的基本语法；
2. 会编写存储过程实现复杂功能； 3. 会编写触发器实现复杂的约束条件; 4. 掌握事务的定义方式。 实验内容：
1. 定义变量并且通过select语句给变量赋值.
查询学号为“200215121”的学生的姓名和年龄，将其分别赋予变量name和age。
declare @name varchar(10) declare @age int
select @name=sname,@age=sage from student where sno='200215121' select @name as 姓名,@age as 年龄
运行结果如下： 姓名 年龄
-------- -----------
李勇 31
练习：查询学号为“200215121”的学生选修“1”号课程的成绩，并赋值给变量grade。
2. if-lese选择结构的使用
查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息。
DECLARE @num int
SELECT @num=count(*) from student print '学生人数:'+convert(varchar(5),@num) IF (@num>10) BEGIN
print '前五名学生信息'
SELECT TOP 5 * FROM student ORDER BY sno DESC END ELSE BEGIN
print '所有学生信息'
SELECT * FROM student END
练习：查询“1”号课程的平均成绩，如果平均成绩大于等于70，则显示前5名的成绩，否则显示后5名的成绩。 3. while循环结构的使用
创建一个测试表，并使用循环结构快速插入20000条记录。
create table TableIndex ( ID int identity(1,1), DataValue decimal(18,2))
/*---向TestIndex数据库表中插入20000条数据---*/ declare @r numeric(15,8) declare @n int set @n = 0 while(1=1) begin set @r = rand() insert into TableIndex (DataValue) values(@r) set @n = @n + 1 if(@n>20000) break
end
练习：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生“1”号课程成绩均及格。
4. 编写带有通配符参数的存储过程，查询学生表和成绩表，返回指定姓名的学生姓名、课程名和考试成绩。要求：执行该存储过程时，如果未提供参数，则使用预设的默认值（以“张”打头的姓） /*---创建存储过程---*/
CREATE PROCEDURE sp_stuInfo @sname varchar(50)='张%' AS
PRINT @sname+'同学的考试成绩单如下：' SELECT sname,cname,grade FROM student,course,sc
WHERE student.sno=sc.sno and o=o
and sname LIKE @sname
练习：创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。要求及格分数线作为参数由用户输入，默认为60。

5. 创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：
create table count(
id char(3) primary key, balance int)
insert into count values('001', 1000) create table traninfo(
id char(3) foreign key references count(id), amount int)
/*---创建触发器---*/
create trigger autoupdate_balance on traninfo for insert as
update count set balance = balance +i.amount from inserted i where count.id=i.id 练习1：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。 练习2：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
6. 编写事务进行银行转账。 --创建农行帐户表bank CREATE TABLE bank(
customerName CHAR(10), --顾客姓名 currentMoney int --当前余额 )
/*---添加约束：根据银行规定，帐户余额不能少于1元，除非销户----*/ ALTER TABLE bank
ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)
/*--插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 ---*/ INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000) INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)
/*---创建事务---*/
/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/ BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT
SET @errorSum=0 --初始化为0，即无错误
/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error --累计是否有错误 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误 /*--根据是否有错误，确定事务是提交还是撤销---*/ IF @errorSum0 --如果有错误 BEGIN
print '交易失败，回滚事务' ROLLBACK TRANSACTION END ELSE BEGIN
print '交易成功，提交事务，写入硬盘，永久的保存' COMMIT TRANSACTION END
练习：使用第5题中的count表和traninfo表，编写事务实现存取款过程。 要求首先插入交易记录，然后更新账户余额。 提示:
1. Transact-SQL局部变量和全局变量
Transact-SQL中可以使用两种变量，一种是局部变量（Local Variable）另外一种是全局变量（Global Variable）。
局部变量是用户可自定义的变量，它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。局部变量必须以“@”开头，而且必须先用DECLARE命令说明后才可使用。其说明形式如下： DECLARE @变量名 变量类型 [@变量名 变量类型…]
其中变量类型可以是SQL Server 支持的所有数据类型，也可以是用户自定义的数据类型。 在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值。必须使用SELECT或SET命令来设定变量的值，其语法如下： SELECT@局部变量=变量值 SET @局部变量=变量值
例声明一个长度为10 个字符的变量“id”并赋值 declare @id char（10） select @id=?10010001?
注意：可以在Select命令查询数据时，在Select命令中直接将列值赋给变量。
全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用全局变量通常存储一些SQL Server的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。
注意：全局变量不是由用户的程序定义的，它们是在服务器级定应义的。只能使用预先说明及定义的变局变量引用全局变量时，必须以“@@”开头。局部变量的名称不能与全局变量的名称相同、否则会在应用中出错。
2. 逻辑控制语句的语法结构 (1)SQL中的IF-ELSE语句
IF （条件） BEGIN 语句1 语句2 … … END ELSE BEGIN 语句1 语句2 … … END
(2)SQL中的WHILE语句
WHILE （条件）

BEGIN 语句1 语句2 …… BREAK END

3. 定义存储过程的语法 CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT, ? ?
@参数n 数据类型 = 默认值 OUTPUT AS
SQL语句
4. 创建触发器的语法：
CREATE TRIGGER trigger_name ON table_name
FOR [DELETE, INSERT, UPDATE] AS
T-SQL语句
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，通常用于强制业务规则。它是一种高级约束，可以定义比用CHECK 约束更为复杂的约束，可执行复杂的SQL语句（if/while/case），也可引用其它表中的列。
触发器触发时，系统自动在内存中创建deleted表或inserted表。这两个表只读，不允许修改；触发器执行完成后，自动删除。
inserted表，临时保存了插入或更新后的记录行，可以从inserted表中检查插入的数据是否满足业务需求。
deleted 表，临时保存了删除或更新前的记录行，可以从deleted表中检查被删除的数据是否满足业务需求。
5 / 5