【MySQL基础操作08--存储过程】
存储过程
1. 创建存储过程
'''
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
sql语句
end 自定义的结束符合
delimiter ;
'''
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01();
2. 变量定义
2.1 局部变量
用户自定义，在begin/end块中有效
'''
语法： 声明变量 declare var_name type [default var_value];
举例：declare nickname varchar(32);
'''
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default ‘aaa’; -- 定义局部变量
set var_name01 = ‘zhangsan’;
select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();
MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ;
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();
2.2 用户变量
用户自定义，当前会话（连接）有效。类比java的成员变量
语法： @var_name 不需要提前声明，使用即声明
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01 ; #可以看到结果
2.3 系统变量
- 系统变量又分为全局变量与会话变量
- 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
- 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
- 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
- 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
- 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
2.3.1 全局变量
语法：@@global.var_name
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;
2.3.2 会话变量
语法：@@session.var_name
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;
3. 存储过程传参
### 3.1 In
in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param0x('学工部',20000);
3.2 out
out 表示从存储过程内部传值给调用者
-- ---------传出参数：out---------------------------------
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter ;
call proc08(1001, @o_ename);
select @o_ename;
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
CREATE PROCEDURE proc09 (
IN in_empno INT,
OUT out_ename VARCHAR ( 20 ),
OUT out_sal DECIMAL ( 10, 2 )) BEGIN
SELECT
ename,
sal INTO out_ename,
out_sal
FROM
emp
WHERE
empno = in_empno;
END $$delimiter;
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;
3.3 inout
inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
select concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;
4. 流程控制
4.1 IF
IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似
-- 语法
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2] ...
[else statement_list_n]
end if
-- 输入学生的成绩，来判断成绩的级别：
/*
score < 60 :不及格
score >= 60 , score <80 :及格
score >= 80 , score < 90 :良好
score >= 90 , score <= 100 :优秀
score > 100 :成绩错误
*/
delimiter $$
create procedure proc_12_if(in score int)
begin
if score < 60
then
select '不及格';
elseif score < 80
then
select '及格' ;
elseif score >= 80 and score < 90
then
select '良好';
elseif score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end if;
end $$
delimiter ;
call proc_12_if(120)
-- 输入员工的名字，判断工资的情况。
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
declare result varchar(20);
declare var_sal decimal(7,2);
select sal into var_sal from emp where ename = in_ename;
if var_sal < 10000
then set result = '试用薪资';
elseif var_sal < 30000
then set result = '转正薪资';
else
set result = '元老薪资';
end if;
select result;
end$$
delimiter ;
call proc12_if('庞统');
4.2 CASE
CASE是另一个条件判断的语句，类似于编程语言中的switch语法
-- 语法一（类比java的switch）：
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case
-- 语法二：
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
case pay_type
when 1
then
select '微信支付' ;
when 2 then select '支付宝支付' ;
when 3 then select '银行卡支付';
else select '其他方式支付';
end case ;
end $$
delimiter ;
call proc14_case(2);
call proc14_case(4);
-- 语法二
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when score < 60
then
select '不及格';
when score < 80
then
select '及格' ;
when score >= 80 and score < 90
then
select '良好';
when score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end case;
end $$
delimiter ;
call proc_15_case(88);
4.3 循环
•循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
•循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
循环分类：while\repeat\loop
循环控制：
leave 类似于 break，跳出，结束当前所在的循环
iterate类似于 continue，继续，结束本次循环，继续下一次
4.3.1 while
'''【标签:】while 循环条件 do
循环体;
end while【 标签】;'''
-- 创建测试表
create table user (
uid int primary key,
username varchar ( 50 ),
password varchar ( 50 )
);
-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while(10);
-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
if i=5 then leave label;
end if;
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while2(10);
-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
set i=i+1;
if i=5 then iterate label;
end if;
insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
end while label;
end $$
delimiter ;
call proc16_while3(10);
4.3.2 repeat
'''
[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];
'''
delimiter $$
CREATE PROCEDURE proc18 ( IN count INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
REPEAT
INSERT INTO USER ( uid, username, PASSWORD )
VALUES
( i, concat( 'user-', i ), '123456' );
SET i = i + 1;
UNTIL i > count
END REPEAT label;
END $$delimiter;
call proc18(15);
4.3.3 loop
'''
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
'''
delimiter $$
CREATE PROCEDURE proc19_loop ( IN insertCount INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
LOOP
INSERT INTO USER ( uid, username, PASSWORD )
VALUES
( i, concat( 'user-', i ), '123456' );
SET i = i + 1;
IF
i > 5 THEN
LEAVE label;
END IF;
END LOOP label;
SELECT
'循环结束';
END $$delimiter;
call proc19_loop(10);
5. 游标
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.
-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno varchar(50);
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for
select empno , ename, sal
from dept a ,emp b
where a.deptno = b.deptno and a.dname = in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取每一行数据
label:loop
fetch my_cursor into var_empno, var_ename, var_sal;
select var_empno, var_ename, var_sal;
end loop label;
-- 关闭游标
close my_cursor;
end
-- 调用存储过程
call proc20_cursor('销售部');
6. 异常处理
'''
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
'''
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
declare flag int default 1; -- ---------------------
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a, emp b
where a.deptno = b.deptno and a.dname = in_dname;
-- 定义句柄，当数据未发现时将标记位设置为0
declare continue handler for NOT FOUND set flag = 0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
-- 判断标志位
if flag = 1 then
select var_empno, var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc21_cursor_handler('销售部');
练习
-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
use mysql7_procedure;
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 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;
-- 2021_11_01
set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
-- 拼接create sql语句
set @create_table_sql = concat(
'create table user_',
table_name_str,
'(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) 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 $$
delimiter ;
call proc22_demo();