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

目录

  • 1. 题目
    • 1.1 存储过程
    • 1.2 存储函数
    • 1.3 事务处理
  • 2. 解答
    • 2.1 存储过程
    • 2.2 存储函数
    • 2.3 事务处理

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，这样可以避免幻读问题。也就是说，对于同一（行）记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。【可序列化】

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/150352.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

【软件测试】自动化测试selenium(二)

【软件测试】自动化测试selenium(二)

文章目录 三. 掌握Selenium常用的API使用1. webdriver API2. 操作测试对象3. 添加等待4. 打印信息5. 浏览器的操作6. 键盘事件7. 鼠标事件8. 定位一组元素9. 多层框架/窗口定位10. 下拉框处理11. 弹窗处理12. 上传文件13. 关闭浏览器14. 切换窗口15. 截图操作 三. 掌握Selenium…
阅读更多...
基于electron25+vite4创建多窗口|vue3+electron25新开模态窗体

基于electron25+vite4创建多窗口|vue3+electron25新开模态窗体

在写这篇文章的时候&#xff0c;查看了下electron最新稳定版本由几天前24.4.0升级到了25了&#xff0c;不得不说electron团队迭代速度之快&#xff01; 前几天有分享一篇electron24整合vite4全家桶技术构建桌面端vue3应用示例程序。 https://www.cnblogs.com/xiaoyan2017/p/17…
阅读更多...
【雷达原理】雷达测距原理及实现方法

【雷达原理】雷达测距原理及实现方法

目录 一、雷达测距原理1.1 基本原理1.2 实现方法1.3 与距离有关的概念 二、MATLAB仿真实验2.1 应用案例2.2 MATLAB代码 一、雷达测距原理 1.1 基本原理 我们知道&#xff0c;电磁波的传播速度为光速 c c c&#xff0c;若雷达与目标之间的距离为 R R R&#xff0c;则雷达发出…
阅读更多...
QT实现tcp服务器客户端

QT实现tcp服务器客户端

服务器.cpp #include "widget.h" #include "ui_widget.h"Widget::Widget(QWidget *parent): QWidget(parent), ui(new Ui::Widget) {ui->setupUi(this);//实例化一个服务器server new QTcpServer(this);// 此时&#xff0c;服务器已经成功进入监听状态…
阅读更多...
LVGL_基础控件滑动条slider

LVGL_基础控件滑动条slider

LVGL_基础控件滑动条slider 1、创建滑动条控件 // 创建一个 slider 组件(对象)&#xff0c;他的父对象是活动屏幕对象 lv_obj_t *slider lv_slider_create(lv_scr_act()); LV_LOG_USER("lv_slider_get_value(slider) %d", lv_slider_get_value(slider));/* 设置位…
阅读更多...
全屋灯具选购指南，如何选择合适的灯具。福州中宅装饰，福州装修

全屋灯具选购指南，如何选择合适的灯具。福州中宅装饰，福州装修

灯具装修指南 灯具就像我们家里的星星&#xff0c;在黑暗中带给我们明亮&#xff0c;可是灯具如果选择的不好&#xff0c;这个效果不仅体现不出来&#xff0c;还会让人觉得烦躁。 灯具到底该怎么选呢&#xff1f;装修灯具有哪些注意事项呢&#xff1f;给大家做了一个总结&#…
阅读更多...
基于springboot的论坛网站

基于springboot的论坛网站

目录 前言 一、技术栈 二、系统功能介绍 用户信息管理 普通管理员管理 交流论坛 交流论坛评论 三、核心代码 1、登录模块 2、文件上传模块 3、代码封装 前言 随着信息技术在管理上越来越深入而广泛的应用&#xff0c;管理信息系统的实施在技术上已逐步成熟。本文介绍了…
阅读更多...
C语言qsort函数

C语言qsort函数

排序qsort int int cmp(const void *a, const void *b) {return *(int *)a - *(int *)b;//先强转成int型&#xff0c;后解引用取值比较大小 }字符串数组 char a[] “hello world” //字符串数组&#xff0c;存放的是字符 int cmp(const void *a, const void *b) {return *(…
阅读更多...
罗彻斯特大学探讨ChatGPT等人工智能将如何影响高等教育

罗彻斯特大学探讨ChatGPT等人工智能将如何影响高等教育

人工智能聊天机器人ChatGPT持续引起互联网用户的热议&#xff0c;它能够回答关于各个领域的问题&#xff0c;创作歌曲、食谱&#xff0c;起草电子邮件等等。罗切斯特的教职员工和管理人员就他们如何处理 ChatGPT 以及它如何影响未来的教学和学习提出了他们的想法。 “让这项技…
阅读更多...
快排（三种单趟排序法，递归非递归算法）

快排（三种单趟排序法，递归非递归算法）

快排发明者：霍尔 (Sir Charles Antony Richard Hoare) 是一位英国计算机科学家。 计算机领域的爵士——托尼霍尔（Tony Hoare）（1934年1月11日出生），英文全称Sir Charles Antony Richard Hoare，常被称为Tony Hoare或者C. A. R. Hoare，1959年博士毕业于
阅读更多...
idea清空缓存类

idea清空缓存类

解决办法 网上有很多是让你去清空什么maven依赖&#xff0c;但假如这个项目是你不可以大刀阔斧的话 可以清空idea缓存 选择 Invalidate 开头的 然后全选 运行重启idea OK
阅读更多...
FISCO BCOS(三十七)———FISCOBCOS应用开发，交易hash、区块高度的获取

FISCO BCOS(三十七)———FISCOBCOS应用开发，交易hash、区块高度的获取

这个需求怎么做？ 交易hash的获取方式有很多，这里先介绍一种方式。 根据块高查询区块信息 https://webasedoc.readthedocs.io/zh_CN/latest/docs/WeBASE-Front/interface.html那我们如何知道现在的区块高度是多少？ https://webasedoc.readthedocs.io/zh_CN/latest/docs/W…
阅读更多...
VSCode Intellij IDEA CE 数据库连接

VSCode Intellij IDEA CE 数据库连接

VSCode & Intellij IDEA CE 数据库连接 大概记一下现在正在用的几个工具/插件 VSCode VSCode 里面的工具我下载了很多&#xff0c;如果只是链接 MySQL 的话&#xff0c;可能用 Jun Han 这位大佬的 MySQL 就好了&#xff1a; 使用这个插件直接打开 .sql 文件单击运行就能…
阅读更多...
nodejs+vue临沂特色产品销售平台elementui

nodejs+vue临沂特色产品销售平台elementui

从实际工作出发&#xff0c;对过去的临沂特色产品销售平台存在的问题进行分析&#xff0c;完善用户的使用体会。采用计算机系统来管理信息 提高了工作的效率。 随着信息化社会的形成和微电子技术日新月异的发展&#xff0c;临沂特色产品销售平台是针对目前临沂特色产品销售…
阅读更多...
win10、win11彻底永久关闭自动更新的方法

win10、win11彻底永久关闭自动更新的方法

win10、win11彻底永久关闭自动更新的方法 前言彻底关闭自动更新方法步骤一、禁用Windows Update服务二、在组策略里关闭Win10自动更新相关服务四、在注册表中关闭Win10自动更新 完结 前言 win系统的自动更新可谓是非常顽固&#xff0c;很多用户在网上试了各种关闭win系统自动更…
阅读更多...
Ubuntu 20.04源码安装sysbench 1.0.20，源码安装sysstat v12.7.2

Ubuntu 20.04源码安装sysbench 1.0.20，源码安装sysstat v12.7.2

源码安装sysbench 1.0.20 参考的博客&#xff1a;《压测数据库1&#xff1a; Ubuntu 20 安装sysbench1.0.20》 sudo apt install -y automake libtool pkg-config下载依赖包&#xff0c;需要注意的是我这台计算机已经安装过mysql&#xff0c;所以我没有安装libmysqlclient-de…
阅读更多...
【重拾C语言】六、批量数据组织（二）线性表——分类与检索（主元排序、冒泡排序、插入排序、顺序检索、对半检索）

【重拾C语言】六、批量数据组织（二）线性表——分类与检索（主元排序、冒泡排序、插入排序、顺序检索、对半检索）

目录 前言 六、批量数据组织——数组 6.1~3 数组基础知识 6.4 线性表——分类与检索 6.4.1 主元排序 6.4.2 冒泡排序 6.4.3 插入排序 6.4.4 顺序检索&#xff08;线性搜索&#xff09; 6.4.5 对半检索&#xff08;二分查找&#xff09; 算法比较 前言 线性表是一种常…
阅读更多...
NPM 常用命令（九）

NPM 常用命令（九）

目录 1、npm link 1.1 使用语法 1.2 描述 2、npm login 2.1 描述 3、npm logout 3.1 描述 4、npm ls 4.1 使用语法 4.2 描述 5、npm org 5.1 使用语法 5.2 示例&#xff1a; 6、npm outdated 6.1 使用语法 6.2 描述 6.3 示例 7、npm owner 7.1 使用语法 7.2…
阅读更多...
windows 任务计划自动提交 笔记到github 、gitee

windows 任务计划自动提交 笔记到github 、gitee

一、必须有个git仓库托管到git上。 这个就不用说了&#xff0c;自己在github或者码云上新建一个仓库就行了。 二、创建自动提交脚本 这个bat脚本是在windows环境下使用的。 注意&#xff1a;windows定时任务下 调用自动提交git前&#xff0c;必须先进入该git仓库目录&#x…
阅读更多...
机器学习---RBM、KL散度、DBN

机器学习---RBM、KL散度、DBN

1. RBM 1.1 BM BM是由Hinton和Sejnowski提出的一种随机递归神经网络&#xff0c;可以看做是一种随机生成的 Hopfield网络&#xff0c;是能够通过学习数据的固有内在表示解决困难学习问题的最早的人工神经网络之 一&#xff0c;因样本分布遵循玻尔兹曼分布而命名为BM。BM由二…
阅读更多...
最新文章

Copyright @ 2022~2023