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

实验实验 4 4 T SQLT 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