MYSQL8存储过程
MYSQL存储过程常见用法
创建存储过程
// 定义结束
delimiter // CREATE PROCEDURE ShowStuScore1()
BEGIN
SELECT *
FROM tb_students_score; END //
创建带参数的存储过程
通过call语句调用存储过程
// 通过call语句调用存储过程
delimiter ; CALL GetStu('李军')
查看存储过程
查看存储过程的状态
// 查看存储过程的状态
SHOW PROCEDURE STATUS LIKE 'GetStu';
查看存储过程的定义
// 查看存储过程的定义
show CREATE PROCEDURE GetStu;
删除存储过程
// 删除存储过程
drop PROCEDURE ShowStuScore1
修改存储过程
只能修改存储过程的状态，修改内容必须先删除再修改
// 修改存储过程
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
MYSQL8存储过程
什么是存储过程?
多句sql同时执行，先编译好存储服务端，就是数据库里的自定义方法。
作用
执行效率高
重复使用
MYSQL变量
局部变量
MySQL的局部变量在begin 和and 之间，出了end不能使用。
MySQL中的DECLARE语句用于定义局部变量。
set 变量名=变量值
用户变量
-- 定义一个存储过程
delimiter $$
CREATE procedure 存储过程名()
begin
-- 声明用户变量var_name1
set @var_name1='ZS'
SELECT @var_name1;
END $$
delimiter;
-- 调用存储过程,会打印用户变量"var_name1"的值
call 存储过程名()
-- 也可以直接使用用户变量
select @var_name1;
系统变量–全局变量
全局变量的修改是持久性的，即使在MySQL Server重启后，新的变量值仍然会被保留，并应用于所有连接。
-- 查看全局变量
show global variables;
-- 修改全局变量的值
set global sort_buffer_size=40000;
-- 查看指定全局变量
SELECT @@global.auto_increment_increment;
-- 修改全局变量
set global sort_buffer_size=40000;
-- 查看该变量的值
SELECT @@global.sort_buffer_size;
-- 修改全局变量
set @@global.sort_buffer_size=35000;
-- 查看该变量的值
SELECT @@global.sort_buffer_size;
持久化修改系统变量
https://blog.csdn.net/weixin_43424368/article/details/106720931
SET PERSIST ：立即修改当前配置，并永久保存配置到DATADIR/mysqld-auto.cnf
SET PERSIST_ONLY ：只更新配置，当前不生效。
SET GLOBAL ：只对当前服务器立即生效，重启MySQL服务器后失效。SET PERSIST 本质上结合了 SET GLOBAL 和 SET PERSIST_ONLY。
-- 将MySQL Server的最大连接数增加到200，并且会在服务器重新启动后保留该值。
-- PERSIST 持久配置
SET PERSIST max_ connections=200;
-- 只会在服务器重启时保留该值，并不会立即生效。
SET PERSIST_ ONLY max_ connections=200;
-- 清空持久化配置
reset persist;
重启MySQL服务器后失效。
-- 将MySQL Server的最大连接数增加到200，但是在服务器重新启动后不会保留该值。
SET GLOBAL max_ connections=200;
会话变量
-- 查看会话变量
show session variables;
-- 将当前会话的最大连接数设置为200，但不会影响其他会话或者在服务器重启后保留该值。
SET SESSION max_ connections=200;
-- 修改会话变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=60000;
-- 查看修改后的会话变量的值
SELECT @@session.sort_buffer_size;
-- 查看某会话变量
SELECT @@session.auto_increment_increment;
select … into 为变量赋值
-- 定义一个存储过程
delimiter $$
CREATE procedure 存储过程名()
begin
-- 声明局部变量
declare 局部变量名 VARCHAR(20);
-- 从emp表中查询ename结果符值给"局部变量名"
SELECT ename into 局部变量名 FROM emp WHERE empno=1001;
-- 打印局部变量的值
SELECT 局部变量名;
END $$
delimiter;
-- 调用存储过程,会打印"局部变量名"的值
call 存储过程名()
创建存储过程
-- 临时改变结束结束符合
delimiter $$
CREATE PROCEDURE show_studnet()
BEGIN
SELECT * FROM student;
END $$
-- 再将结束符改回;
delimiter ;
调用存储过程
-- 调用存储过程
CALL show_studnet();
示例
查看存储过程创建sql
-- 查看存储过程创建sql
SHOW CREATE PROCEDURE show_studnet;
创建带参数的存储过程
-- 创建带参数的存储过程
delimiter $$
CREATE PROCEDURE show_studnet_un(IN name VARCHAR(30))
BEGIN
SELECT * FROM student WHERE sname=name;
END $$
delimiter ;
调用带参数的存储过程
-- 调用存储过程
CALL show_studnet_un('吊炸天')
参数传递
存储过程传参–in
-- 传入员工编号，查找员工信息
delimiter $$
CREATE procedure proc_find_emp(in empno int)
begin
SELECT * FROM emp WHERE emp.empno=empno;
END $$
delimiter;
-- 调用存储过程
call proc_find_emp(1001);
多个入参
存储过程传参–out
-- 创建存储过程：传入员工编号，返回员工名字和工资
delimiter $$
create procedure proc_find_emp01(in in_empno int, out out_ename VARCHAR(50),out out_sal decimal(7,2))
begin
-- 将查询结果中的ename赋值给out_ename,sal赋值给out_sal
SELECT ename,sal into out_ename,out_sal
FROM emp
WHERE emp.empno=in_empno;
END $$
delimiter;
-- 调用存储过程，传入员工编号1001，输出ename为@o_ename，sal为@o_sal
call proc_find_emp01(1001,@o_ename,@o_sal);
-- 打印出参out_ename，并用@o_ename接收
SELECT @o_ename;
-- 打印出参out_sal，并用@@o_sal接收
SELECT @o_sal
存储过程传参–inout
inout既是入参，也是出参
-- 传入员工名字，输出员工年薪
delimiter $$
-- 员工名字入参: inout_ename
-- 员工月薪入参: inout_sal
create procedure proc11(inout inout_ename varchar(50), inout inout_sal int)
begin
-- 将查询出的员工名字和员工薪水 通过字符串拼接为 姓名_薪水
SELECT concat_ws('_',deptno,ename) into inout_ename from emp WHERE emp.ename=inout_ename;
-- 将传入的月薪*12，求出年薪
set inout_sal=inout_sal*12;
END $$
delimiter;
-- 定义员工名字
set @inout_ename='关羽';
-- 定义员工薪水
set @inout_sal=3000;
-- 调用存储过程
call proc11(@inout_ename,@inout_sal);
-- 打印员工姓名
SELECT @inout_ename;
-- 打印员工年薪
SELECT @inout_sal
删除存储过程
-- 删除存储过程
drop PROCEDURE show_studnet_un
流程控制
流程控制-判断
调用上面定义的存储过程。
流程控制-case
流程控制-循环
while
while+leave
leave类似于break，跳出，结束当前所在的循环
while+iterate
iterate类似于continue，跳过本次循环。
repeat
loop
游标
mysql的游标类似于python中的迭代器。
循环一次，读取一次。
- 声明游标
- 打开游标
- 通过游标获取每一行数据
- 关闭游标
没有handle做异常处理，数据读完后，没有数据可读了，就会报错。
1329是错误代码：没有数据
异常处理-handle句柄
用游标循环读取数据时，因为是一行一行读取，读完最后一行还会继续读下一行，下一行没有数据，就会报错1329。
存储过程综合练习
需求：创建下个月的每日表
这段SQL代码是创建一个MySQL存储过程，用于自动生成每天的表格来记录用户搜索和购买行为。这个存储过程的实现思路是通过循环构建表名来达到目的。
具体操作流程如下：
- 获取当前日期，计算出下个月的年份、月份和该月最后一天的日期。
- 使用while循环生成每天的表名，并拼接出创建表的SQL语句。
- 将SQL语句编译成准备语句并执行。
- 释放准备语句。
-- 数据库准备
CREATE DATABASE IF NOT EXISTS mydb18_proc_demo;
-- 进入数据库
USE mydb18_proc_demo;
-- 如果存储过程存在则删除
DROP PROCEDURE IF EXISTS proc22_demo;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE proc22_demo()
BEGIN
-- 定义变量
-- 定义下一个月的年份
DECLARE next_year INT;
-- 定义下个月的月份
DECLARE next_month INT;
-- 定义下个月的最后一天
DECLARE next_month_day INT;
-- 定义下个月的月份字符串
DECLARE next_month_str VARCHAR(2);
-- 定义下个月的最后一天字符串
DECLARE next_month_day_str VARCHAR(2);
-- 定义下个月的每天表名称字符串
DECLARE table_name_str VARCHAR(10);
-- 定义下个月的天数(while循环初始值) 初始值 1
DECLARE t_index INT DEFAULT 1;
-- 获取日期变量值
SET next_year = YEAR(DATE_ADD(NOW(), INTERVAL 1 MONTH));
SET next_month = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
SET next_month_day = DAY(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 = CAST(next_month AS CHAR);
END IF;
-- 循环构建表名并执行创建表语句
-- 如果t_index 小于等于 下个月的最后一天，就执行循环
WHILE t_index <= next_month_day DO
-- 生成下个月的最后一天字符串
-- 判断：如果t_index 值小于10，就补0并转换为字符串，
IF t_index < 10 THEN
SET next_month_day_str = CONCAT('0', t_index);
ELSE
-- 如果不小于10，就直接转成字符串
SET next_month_day_str = CAST(t_index AS CHAR);
END IF;
-- 拼接表名日期格式2023_06_04
SET table_name_str = CONCAT(next_year, '_', next_month_str, '_', next_month_day_str);
-- 拼接 创建下个月的每日表的sql语句
SET @create_table_sql = CONCAT(
'CREATE TABLE user_', table_name_str,
'(`uid` INT, `uname` VARCHAR(50), `information` VARCHAR(50)) COLLATE=\'uft8_general_cli\' ENGINE=InnoDB;'
);
-- 将@create_table_sql变量里的sql语句编译为准备语句create_table_stmf
PREPARE create_table_stmf FROM @create_table_sql;
-- 执行 SQL 语句
EXECUTE create_table_stmf;
-- 释放准备语句create_table_stmf
DEALLOCATE PREPARE create_table_stmf;
-- 更心循环的值
SET t_index = t_index + 1;
END WHILE;
END$$
DELIMITER ;
6893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
