MySQL存储过程学习笔记(1)，2024年最新java开发数据库面试题

2401_84103844

于 2024-04-20 06:28:43 发布

阅读量864

点赞数 21

分类专栏： 2024年程序员学习文章标签：数据库 mysql 学习

本文链接：https://blog.csdn.net/2401_84103844/article/details/137986393

版权

2024年程序员学习专栏收录该内容

191 篇文章 0 订阅

订阅专栏

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

正文

– 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老

delimiter $$

create procedure sp_hire_case()

begin

declare result varchar(32);

declare message varchar(64);

case

when timestampdiff(year,‘2001-01-01’,now()) > 40

then

set result = ‘元老’;

set message = ‘老爷爷’;

when timestampdiff(year,‘2001-01-01’,now()) > 38

then

set result = ‘老员工’;

set message = ‘油腻中年人’;

else

set result = ‘新手’;

set message = ‘萌新’;

end case;

select result;

end$$

delimiter ;

3.4 流程控制-循环

`loop`

– 语法

[begin_label:] LOOP

statement_list

END LOOP [end_label]

举例

需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。

可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue

–需求：循环打印1到10

– leave控制循环的退出

delimiter $$

create procedure sp_flow_loop()

begin

declare c_index int default 1;

declare result_str varchar(256) default ‘1’;

cnt:loop

if c_index >= 10

then leave cnt;

end if;

set c_index = c_index + 1;

set result_str = concat(result_str,‘,’,c_index);

end loop cnt;

select result_str;

end$$

– iterate + leave控制循环

delimiter $$

create procedure sp_flow_loop02()

begin

declare c_index int default 1;

declare result_str varchar(256) default ‘1’;

cnt:loop

set c_index = c_index + 1;

set result_str = concat(result_str,‘,’,c_index);

if c_index < 10 then

iterate cnt;

end if;

– 下面这句话能否执行到？什么时候执行到？当c_index < 10为false时执行

leave cnt;

end loop cnt;

select result_str;

end$$

`repeat`

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3xOCz6YT-1633920266336)(assets/1586956047152.png)]

[begin_label:] REPEAT

statement_list

UNTIL search_condition – 直到…为止，才退出循环

END REPEAT [end_label]

– 需求：循环打印1到10

delimiter $$

create procedure sp_flow_repeat()

begin

declare c_index int default 1;

– 收集结果字符串

declare result_str varchar(256) default ‘1’;

count_lab:repeat

set c_index = c_index + 1;

set result_str = concat(result_str,‘,’,c_index);

until c_index >= 10

end repeat count_lab;

select result_str;

end$$

`while`

类比java的while(){}

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

– 需求：循环打印1到10

delimiter $$

create procedure sp_flow_while()

begin

declare c_index int default 1;

– 收集结果字符串

declare result_str varchar(256) default ‘1’;

while c_index < 10 do

set c_index = c_index + 1;

set result_str = concat(result_str,‘,’,c_index);

end while;

select result_str;

end$$

3.5 流程控制-退出、继续循环

`leave`

类比java的breake

– 退出 LEAVE can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE).

LEAVE label

`iterate`

类比java的continue

– 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements

ITERATE label

3.6 游标

用游标得到某一个结果集，逐行处理数据。

类比jdbc的ResultSet

– 声明语法

DECLARE cursor_name CURSOR FOR select_statement

– 打开语法

OPEN cursor_name

– 取值语法

FETCH cursor_name INTO var_name [, var_name] …

– 关闭语法

CLOSE cursor_name

– 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）

delimiter $$

create procedure sp_create_table02(in dept_name varchar(32))

begin

declare e_no int;

declare e_name varchar(32);

declare e_sal decimal(7,2);

declare lp_flag boolean default true;

declare emp_cursor cursor for

select e.empno,e.ename,e.sal

from emp e,dept d

where e.deptno = d.deptno and d.dname = dept_name;

– handler 句柄

declare continue handler for NOT FOUND set lp_flag = false;

open emp_cursor;

emp_loop:loop

fetch emp_cursor into e_no,e_name,e_sal;

if lp_flag then

select e_no,e_name,e_sal;

else

leave emp_loop;

end if;

end loop emp_loop;

set @end_falg = ‘exit_flag’;

close emp_cursor;

end$$

call sp_create_table02(‘RESEARCH’);

特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

3.7 存储过程中的handler

DECLARE handler_action HANDLER

FOR condition_value [, condition_value] …

statement

handler_action: {

CONTINUE

| EXIT

| UNDO

}

condition_value: {

mysql_error_code

| SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

}

CONTINUE: Execution of the current program continues.

EXIT: Execution terminates for the BEGIN … END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.

SQLWARNING: Shorthand for the class of SQLSTATE values that begin with ‘01’.

NOT FOUND: Shorthand for the class of SQLSTATE values that begin with ‘02’.

SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with ‘00’, ‘01’, or ‘02’.

– 各种写法：

DECLARE exit HANDLER FOR SQLSTATE ‘42S01’ set @res_table = ‘EXISTS’;

DECLARE continue HANDLER FOR 1050 set @res_table = ‘EXISTS’;

DECLARE continue HANDLER FOR not found set @res_table = ‘EXISTS’;

4.练习

——大家注意，存储过程的业务过程在java代码中一般也可以实现，我们下面的需求是为了练习存储过程

4.1 利用存储过程更新数据

为某部门(需指定)的人员涨薪100;如果是公司总裁，则不涨薪。

delimiter //

create procedure high_sal(in dept_name varchar(32))

begin

declare e_no int;

declare e_name varchar(32);

declare e_sal decimal(7,2);

declare lp_flag boolean default true;

declare emp_cursor cursor for

select e.empno,e.ename,e.sal

from emp e,dept d

where e.deptno = d.deptno and d.dname = dept_name;

– handler 句柄

declare continue handler for NOT FOUND set lp_flag = false;

open emp_cursor;

emp_loop:loop

fetch emp_cursor into e_no,e_name,e_sal;

if lp_flag then

if e_name = ‘king’ then

iterate emp_loop;

else

update emp e set e.sal = e.sal + 100 where e.empno = e_no;

end if;

else

leave emp_loop;

end if;

end loop emp_loop;

set @end_falg = ‘exit_flag’;

close emp_cursor;

end //

call high_sal(‘ACCOUNTING’);

4.2 循环创建表

创建下个月的每天对应的表comp_2020_04_01、comp_2020_04_02、…

（模拟）需求描述：

我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！

– 知识点预处理 prepare语句from后使用局部变量会报错

– https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name [USING @var_name [, @var_name] …]

{DEALLOCATE | DROP} PREPARE stmt_name

– 知识点时间的处理

– EXTRACT(unit FROM date)截取时间的指定位置值

– DATE_ADD(date,INTERVAL expr unit) 日期运算

– LAST_DAY(date) 获取日期的最后一天

– YEAR(date) 返回日期中的年

– MONTH(date) 返回日期的月

– DAYOFMONTH(date) 返回日

– 思路：循环构建表名 comp_2020_05_01 到 comp_2020_05_31；并执行create语句。

delimiter //

create procedure sp_create_table()

begin

declare next_year int;

declare next_month int;

declare next_month_day int;

declare next_month_str char(2);

declare next_month_day_str char(2);

– 处理每天的表名

declare table_name_str char(10);

declare t_index int default 1;

– declare create_table_sql varchar(200);

– 获取下个月的年份

set next_year = year(date_add(now(),INTERVAL 1 month));

– 获取下个月是几月

set next_month = month(date_add(now(),INTERVAL 1 month));

– 下个月最后一天是几号

set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));

if next_month < 10

then set next_month_str = concat(‘0’,next_month);

else

set next_month_str = concat(‘’,next_month);

end if;

while t_index <= next_month_day do

if (t_index < 10)

then set next_month_day_str = concat(‘0’,t_index);

else

set next_month_day_str = concat(‘’,t_index);

end if;

– 2020_05_01

set table_name_str = concat(next_year,‘‘,next_month_str,’’,next_month_day_str);

– 拼接create sql语句

set @create_table_sql = concat(

‘create table comp_’,

table_name_str,

‘(grade INT(11) NULL,losal INT(11) NULL,hisal INT(11) NULL) COLLATE=‘utf8_general_ci’ ENGINE=InnoDB’);

– FROM后面不能使用局部变量！

prepare create_table_stmt FROM @create_table_sql;

execute create_table_stmt;

DEALLOCATE prepare create_table_stmt;

set t_index = t_index + 1;

end while;

end//

call sp_create_table()

4.3 其他场景

“为用户添加购物积分，并更新到用户的总积分表中”等需要对多张表进行CRUD操作的业务。

而且内部可以使用事务命令。

5.其他

5.1 characteristic

characteristic:

COMMENT ‘string’

| LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

其中，SQL SECURITY的含义如下：

MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；

如果SQL SECURITY子句指定为DEFINER(定义者)，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储

过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象(存储过程中的表等对象)的权限；

如果SQL SECURITY子句指定为INVOKER(调用者)，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；

如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。

5.2 死循环处理

– 如有死循环处理，可以通过下面的命令查看并结束

show processlist;

kill id;

5.3 可以在select语句中写case

https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html

select

case

when timestampdiff(year,e.hiredate,now()) <= 38 then ‘新手’

when timestampdiff(year,e.hiredate,now()) <= 40 then ‘老员工’

else ‘元老’

end hir_loc,

e.*

from emp e;

5.4 临时表

create temporary table 表名(

字段名类型 [约束],

name varchar(20)

)Engine=InnoDB default charset utf8;

– 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）

delimiter $$

create procedure sp_create_table02(in dept_name varchar(32))

begin

declare emp_no int;

declare emp_name varchar(32);

declare emp_sal decimal(7,2);

declare exit_flag int default 0;

declare emp_cursor cursor for

select e.empno,e.ename,e.sal

from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;

declare continue handler for not found set exit_flag = 1;

– 创建临时表收集数据

CREATE temporary TABLE temp_table_emp (

empno INT(11) NOT NULL COMMENT ‘员工编号’,

ename VARCHAR(32) NULL COMMENT ‘员工姓名’ COLLATE ‘utf8_general_ci’,

sal DECIMAL(7,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘薪资’,

PRIMARY KEY (empno) USING BTREE

)

COLLATE=‘utf8_general_ci’

ENGINE=InnoDB;

open emp_cursor;

c_loop:loop

fetch emp_cursor into emp_no,emp_name,emp_sal;

if exit_flag != 1 then

insert into temp_table_emp values(emp_no,emp_name,emp_sal);

else

leave c_loop;

end if;

end loop c_loop;

select * from temp_table_emp;

select @sex_res; – 仅仅是看一下会不会执行到

close emp_cursor;

end$$

call sp_create_table02(‘RESEARCH’);

读者福利

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

更多笔记分享

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
temporary TABLE temp_table_emp (

empno INT(11) NOT NULL COMMENT ‘员工编号’,

ename VARCHAR(32) NULL COMMENT ‘员工姓名’ COLLATE ‘utf8_general_ci’,

sal DECIMAL(7,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘薪资’,

PRIMARY KEY (empno) USING BTREE

)

COLLATE=‘utf8_general_ci’

ENGINE=InnoDB;

open emp_cursor;

c_loop:loop

fetch emp_cursor into emp_no,emp_name,emp_sal;

if exit_flag != 1 then

insert into temp_table_emp values(emp_no,emp_name,emp_sal);

else

leave c_loop;

end if;

end loop c_loop;

select * from temp_table_emp;

select @sex_res; – 仅仅是看一下会不会执行到

close emp_cursor;

end$$

call sp_create_table02(‘RESEARCH’);

读者福利

[外链图片转存中…(img-QH3xKpd1-1713565705001)]

更多笔记分享

[外链图片转存中…(img-7jIxiQgj-1713565705001)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-J0kJdqUt-1713565705002)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

2401_84103844

关注

21
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
MySQL存储过程学习笔记(1)，2024年最新java开发数据库面试题

我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！如果SQL SECURITY子句指定为INVOKER(调用者)，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；并执行create语句。（注意，此处仅仅演示游标用法）
复制链接

扫一扫