【数据库——MySQL】（15）存储过程、存储函数和事务处理习题及讲解

1. 题目

1.1 存储过程

1. 创建表 RandNumber ：字段：id 自增长， data int； 创建存储过程向表中插入指定个数的随机数（1-99），但如果插入的数为 50，则终止插入。

2. 创建存储过程，根据员工的工作时间，如果大于 6 年时，将其转到经理办公室工作，并调用该存储过程。

3. 创建存储过程，比较两个员工的实际收入，若前者比后者高输出 1，若两者相等输出 0，若后者比前者高输出 -1，并调用该存储过程。

4. 创建存储过程 p(in name char(10),out income decimal(7,2))，计算一个员工的实际收入，并调用该存储过程，将员工 朱骏 的实际收入保存在一个用户变量中。

5. 创建存储过程 raise(in edu char(6)，in x decimal(5,1)) 将所有某种学历的员工的收入提高 %x, 并调用该存储过程，将所有硕士学历的员工的收入提高 10%。

1.2 存储函数

1. 创建存储函数 getAver(did int)，计算某个部门的平均工资（实际收入）;

2. 调用该函数，显示平均工资最高和最低的部门名称。

1.3 事务处理

设置事务处理为手动提交，建立两个连接。

1. 观察 @@transaction_isolation 设置为 read-uncommited 时，脏读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 read-uncommited，开始事务，显示 employees 表中‘ 王林 ’员工信息；
   2）在另一个连接 B 中，修改‘ 王林 ’的 workYear 为 10 年；
   3）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear；
   4）在一个连接 B 中，回滚刚才的修改操作；
   5）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear。

2. 观察 @@transaction_isolation 设置为 read-commited 时，不可重复读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 read-commited；
   2）开始事务，显示 employees 表中‘王林’员工信息；
   3）在另一个连接 B 中，修改‘王林’的 workYear 为 10 年；
   4）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear；
   5）在一个连接 B 中，提交刚才的修改操作；
   6）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear，提交事务。

3. 观察 @@transaction_isolation 设置为 repeatable-read 时，幻读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 repeatable-read；
   2）开始事务，显示 employees 表中所有员工信息，观察记录的数目；
   3）在另一个连接 B 中，在 employees 表插入一条记录，并提交事务；
   4）在连接 A 中显示 employees 表的员工信息，观察记录的数目；
   5）在连接 A 中，将所有员工的 workYear 增加一年，观察被修改的记录的数目；
   6）在连接 A 中提交事务；
   8）在连接 A 再次显示 employees 表中所有员工信息，观察记录的数目；

4. 设置@@transaction_isolation 设置为 serializable；
   重复第 3 个实验的操作，观察操作中出现的现象。

2. 解答

2.1 存储过程

use yggl;

1. 创建表 RandNumber ：字段：id 自增长， data int； 创建存储过程向表中插入指定个数的随机数（1-99），但如果插入的数为 50，则终止插入。

   drop table if EXISTS `yggl`.`RandNumber`;
   CREATE TABLE if not EXISTS`yggl`.`RandNumber`  (`id` int NOT NULL AUTO_INCREMENT,`data` int NOT NULL,PRIMARY KEY (`id`)
   );drop PROCEDURE if EXISTS p_RandNumber;
   delimiter $
   create procedure p_RandNumber(in n int)
   begindeclare temp int;declare i int default(1);set temp = 1 + floor(rand()*99);while i <= n and temp != 50 doinsert into randnumber values (null, temp);set temp = 1 + floor(rand()*99);set i = i + 1;end while;
   end$
   delimiter ;set @n=100;
   call p_RandNumber(@n);
   select * from randnumber;
   

2. 创建存储过程，根据员工的工作时间，如果大于 6 年时，将其转到经理办公室工作，并调用该存储过程。

   drop PROCEDURE if EXISTS p2;
   delimiter $
   create procedure p2()
   begindeclare did char(3);  # 部门编号declare eid char(6);  # 员工编号select departments.DepartmentID into didfrom departmentswhere departments.DepartmentName = '经理办公室';select employees.EmployeeID into eidfrom employeeswhere employees.WorkYear > 6;update employeesset DepartmentID = didwhere employees.EmployeeID in(eid);
   end$
   delimiter ;call p2();
   

3. 创建存储过程，比较两个员工的实际收入，若前者比后者高输出 1，若两者相等输出 0，若后者比前者高输出 -1，并调用该存储过程。

   drop PROCEDURE if EXISTS p3;
   delimiter $
   create procedure p3(in mname1 char(10), in mname2 char(10))
   begindeclare m1 float;  # 第一个人的实际收入declare m2 float;  # 第二个人的实际收入declare flag int;  # 1,0,-1select salary.InCome - salary.OutCome into m1from salary join employees on salary.EmployeeID = employees.EmployeeIDwhere employees.`Name` = mname1;select salary.InCome - salary.OutCome into m2from salary join employees on salary.EmployeeID = employees.EmployeeIDwhere employees.`Name` = mname2;if m1 > m2 thenset flag = 1;elseif m1 = m2 thenset flag = 0;elseset flag = -1;end if;select flag;
   end$
   delimiter ;call p3('王浩', '伍容华');
   

4. 创建存储过程 p(in name char(10),out income decimal(7,2))，计算一个员工的实际收入，并调用该存储过程，将员工 朱骏 的实际收入保存在一个用户变量中。

   drop PROCEDURE if EXISTS p;
   delimiter $
   create procedure p(in `name` char(10),out income decimal(7,2))
   beginselect salary.InCome - salary.OutCome into incomefrom salary join employees on salary.EmployeeID = employees.EmployeeIDwhere employees.`Name` = `name`;end$
   delimiter ;set @c=1;
   call p('朱骏', @c);
   select @c;
   

5. 创建存储过程 raise(in edu char(6)，in x decimal(5,1)) 将所有某种学历的员工的收入提高 %x, 并调用该存储过程，将所有硕士学历的员工的收入提高 10%。

   drop PROCEDURE if EXISTS raise;
   delimiter $
   create procedure raise(in edu char(6), in x decimal(5,1))
   beginupdate salaryset salary.InCome = salary.InCome*(1+x/100)where EmployeeID in(select employees.EmployeeIDfrom employeeswhere employees.Education = edu);
   end$
   delimiter ;call raise('硕士', 10);
   

2.2 存储函数

1. 创建存储函数 getAver(did int)，计算某个部门的平均工资（实际收入）;

   set GLOBAL log_bin_trust_function_creators = 1;    # 一共只需要设置一次drop FUNCTION if exists getAver;
   delimiter $
   create FUNCTION getAver(did int)
   returns float    # 返回某个部门的平均工资（实际收入）
   begindeclare aver float;select AVG(salary.InCome - salary.OutCome) into averfrom employees join salary on employees.EmployeeID = salary.EmployeeIDwhere employees.DepartmentID = did;return aver;
   end$
   delimiter ;
   

2. 调用该函数，显示平均工资最高和最低的部门名称。

   # 平均工资最高的部门
   select departments.DepartmentName, getAver(departments.DepartmentID) as avg_salary
   from departments
   ORDER BY avg_salary desc
   limit 1;
   # 平均工资最低的部门
   select departments.DepartmentName, getAver(departments.DepartmentID) as avg_salary
   from departments
   ORDER BY avg_salary asc
   limit 1;
   

2.3 事务处理

设置事务处理为手动提交，建立两个连接。

set @@autocommit = 0;

1. 观察 @@transaction_isolation 设置为 read-uncommited 时，脏读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 read-uncommited，开始事务，显示 employees 表中‘ 王林 ’员工信息；

   

   2）在另一个连接 B 中，修改‘ 王林 ’的 workYear 为 10 年；

   

   3）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear；

   

   4）在一个连接 B 中，回滚刚才的修改操作；

   

   5）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear。

   

   结论：一个事务 B 读取了另一个未提交的并行事务 A 写的数据。【脏读】

2. 观察 @@transaction_isolation 设置为 read-commited 时，不可重复读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 read-commited；

   

   2）开始事务，显示 employees 表中‘王林’员工信息；

   

   3）在另一个连接 B 中，修改‘王林’的 workYear 为 10 年；

   

   4）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear；

   

   5）在一个连接 B 中，提交刚才的修改操作；

   

   6）在连接 A 中显示 employees 表的员工信息，观察‘王林’的 workYear，提交事务。

   

   结论：一个事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务B修改过。【不可重复读】

3. 观察 @@transaction_isolation 设置为 repeatable-read 时，幻读的情况。
   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 repeatable-read；

   

   2）开始事务，显示 employees 表中所有员工信息，观察记录的数目；

   

   3）在另一个连接 B 中，在 employees 表插入一条记录，并提交事务；

   

   4）在连接 A 中显示 employees 表的员工信息，观察记录的数目；

   

   5）在连接 A 中，将所有员工的 workYear 增加一年，观察被修改的记录的数目；

   

   6）在连接 A 中提交事务；

   

   7）在连接 A 再次显示 employees 表中所有员工信息，观察记录的数目；

   

   结论：一个事务重新执行一个查询，返回一套符合查询条件的行， 发现这些行因为其他最近提交的事务而发生了改变。【幻读】

4. 设置@@transaction_isolation 设置为 serializable；
   重复第 3 个实验的操作，观察操作中出现的现象，即：

   1）在一个连接 A 中，设置 @@transaction_isolation 设置为 serializable；

   

   2）开始事务，显示 employees 表中所有员工信息，观察记录的数目；

   

   3）在另一个连接 B 中，在 employees 表插入一条记录，并提交事务；

   

   4）在连接 A 中显示 employees 表的员工信息，观察记录的数目；

   

   5）在连接 A 中，将所有员工的 workYear 增加一年，观察被修改的记录的数目；

   

   6）在连接 A 中提交事务；

   

   7）在连接 A 再次显示 employees 表中所有员工信息，观察记录的数目；

   

   结论：对于同一个数据来说，在同一个时间段内，只能有一个会话可以访问，包括SELECT和DML，这样可以避免幻读问题。也就是说，对于同一（行）记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。【可序列化】

上一篇文章：【数据库——MySQL】（14）过程式对象程序设计——游标、触发器
下一篇文章：【数据库——MySQL】（16）游标和触发器习题及讲解