概念
存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
- 函数 ： 是一个有返回值的过程 ；
- 过程 ： 是一个没有返回值的函数 ；
存储过程的使用
创建存储过程
delimiter 自定义结束符号 例如 $$
CREATE PROCEDURE 存储过程名称 ([in，out，inout] 参数名 数据类型...)
begin
-- SQL语句
end 自定义的结束符号
delimiter ;
示例 ：
delimiter $$
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end $$
delimiter ;
DELIMITER: 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号(;)。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
调用存储过程
call 存储过程名称() ;
查看存储过程
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name ；
存储过程语法
存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。
变量
- 局部变量:通过
DECLARE可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。 - 用户变量：变量名称前面加上“@”符号，叫做用户会话变量，用户自定义的变量。当前会话（链接）有效,在BEGIN…END 块外也有效，类似于java的成员变量。
- 系统变量：变量前加上 "@@" 符号, 叫做 系统变量。系统变量系统已经创建好了，但是你不可以自己定义系统变量以及修改。系统变量又分为全局变量以及会话变量
- 全局变量：全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值,，这些默认值可以通过更改my.ini这个文件来更改。S
- 会话变量：会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
- 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。只是作用域不同而已。
- 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
- 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
局部变量
-- 局部变量创建语法：
DECLARE 变量名 类型 [DEFAULT 默认值]
示例 :
delimiter $$
create procedure pro_test()
begin
declare num int default 5; -- 创建一个int类型局部变量num，默认值为5
select num + 10; -- 输出变量num的值
end $$
delimiter ;
--调用存储过程
CALL pro_test();
执行结果：
用户变量
语法：
@用户变量名 --不需要提前声明，使用时声明即可
例如：
DELIMITER $$
CREATE PROCEDURE pro_test4 () BEGIN
SET @var_name = 'wk'; -- 创建用户变量名为@var_name并赋值'wk'
END $$
DELIMITER;
CALL pro_test4 (); -- 调用存储过程pro_test4
SELECT @var_name; -- 查看用户变量
执行结果：
系统变量
全局变量语法：
@@global.var_name
例如：
show.global variables;--查看全局变量
select @@globa1.auto_increment_increment; -- 查看某全局变量
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000 ; -- 修改全局变量的值
会话变量语法：
@@session.var_name
例如：
show session variables;-- 查看会话变量
select @@session. auto_increment increment;-- 查看某会话变量
set session sort buffer size = 50000 ;-― 修改会话变量的值
set @@session. sort_buffer_size = 50000 ;-― 修改会话变量的值
赋值
SET赋值:
给局部变量直接赋值使用 SET，可以赋常量或者赋表达式。
语法：
SET 变量名称1 = 值1；
SET 变量名称2 = 值2；
...
示例 :
DELIMITER $
CREATE PROCEDURE pro_test3() -- 创建一个名为pro_test3的存储过程
BEGIN
DECLARE NAME VARCHAR(20); -- 创建一个变量名为NAME，其类型为varchar类型的局部变量；
SET NAME = 'MYSQL';-- 将局部变量NAME赋值为MYSQL
SELECT NAME ;-- 输出这个局部变量
END$
DELIMITER ;
执行结果：
select ... into 赋值操作
除了set可以进行赋值，使用 select ... into 也可以进行赋值操作。
语法：
DELIMITER $$
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city; -- 将count(*)的值赋值给变量countnum
select countnum; -- 输出变量
END $$
DELIMITER ;
参数
语法格式 :
delimiter $$
create procedure procedure_name([in/out/inout] 参数名 参数类型)
begin
...
end $$
delimiter;
-- IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
-- OUT: 该参数作为输出，也就是该参数可以作为返回值
-- INOUT: 既可以作为输入参数，也可以作为输出参数
IN
需求 :
根据定义的身高变量，判定当前身高的所属的身材类型。 示例 :
-- 案例一
delimiter $$
create procedure pro_test5(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
select concat('身高 ', height , '对应的身材类型为:',description); -- 输出语句
end $$
delimiter ;
-- 调用存储过程
call pro_test5(1.80);
-- 案例二
delimiter $$
create procedure pro_test6(in height_ int)
begin
select * from emp where emp.height = height_;
end $$
delimiter ;
-- 调用存储过程
call pro_test5(1.80);
OUT
需求 :根据传入的id，获取用户名和密码。
示例:
delimiter $$
CREATE PROCEDURE pro_test8(IN in_id VARCHAR(20),OUT out_name VARCHAR(20),OUT out_pass VARCHAR(200))
BEGIN
SELECT username,password INTO out_name,out_pass FROM sys_user WHERE id = in_id;-- 查询出的username赋值给了out_name,password赋值给了out_pass;
END $$
delimiter;
CALL pro_test8(1,@o_name,@o_pass);-- 调用存储过程，并把查询out_name值赋值给用户变量@o_name；out_pass赋值给用户变量@o_pass;
SELECT @o_name;-- 查询用户变量@o_name；
SELECT @o_pass;-- 查询用户变量@o_pass；
INOUT
需求：传入一个数字，然后输出这个数字的10倍值
示例：
delimiter $$
CREATE PROCEDURE pro_test9(INOUT num int)
BEGIN
SET num = num * 10;
END $$
delimiter;
SET @into_num = 2;-- 设置用户变量@into_num为2；
CALL pro_test9(@into_num);-- 调用存储过程，传入参数@into_num;
SELECT @into_num;-- 查询用户变量@into_num；
分支语句
IF语句
案例一：
delimiter $$
CREATE PROCEDURE pro_test10(in score INT)
BEGIN
IF score < 60
THEN SELECT '不及格';
ELSEIF score >= 60 AND 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 pro_test10(100);-- 调用语句
案例二：
delimiter $$
create PROCEDURE pro_teset11(in in_name varchar(20))
BEGIN
DECLARE var_sal DECIMAL(7,2); -- 声明一个工资变量
DECLARE result VARCHAR(20); -- 声明一个结果变量
SELECT sal INTO var_sal FROM emp WHERE ename = in_name; -- 执行SQL语句
if val_sal < 1000
THEN SET result = '试用薪资';
ELSEIF var_sal <= 20000
THEN SET result = '转正薪资';
ELSE
SET result = '元老薪资';
END IF;
SELECT result;
END $$
delimiter ;
CALL pro_test11('武科');
CASE语句
语法1:
-- 当case_value与when_value一致时，就执行后面的语句statement_list
-- 当case_value与when_value都不一致时，就执行else的语句
case case_value
when when_value then statement_list
[when when_value then statement_list]
...
[else statement_list]
end case
语法2:
-- search_condition条件成立，执行statement_list
-- 如果上面的 search_condition 都不成立，就执行statement_list
case
when search_condition then statement_list
[when search_condition then statement_list]
...
[else statement_list]
end case
案例一:
-- 第一种格式：
/*
支付方式：
1.微信支付
2.支付宝支付
3.银行卡支付
*/
delimiter $$
CREATE PROCEDURE pro_test12(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 pro_test12(1);-- 调用存储过程
-- 第二种格式:
delimiter $$
CREATE PROCEDURE pro_test13(in score int)
BEGIN
CASE
WHEN score <= 50 THEN SELECT '不及格';
WHEN score > 50 AND score <= 80 THEN SELECT '良好';
WHEN score >80 AND score <= 100 THEN SELECT '优秀';
ELSE SELECT '成绩输入有误';
END CASE;
END $$;
delimiter ;
CALL pro_test13(88);
循环语句
while循环
语法结构:
WHILE 循环条件 DO
循环体
END
END WHILE;
示例 :
-- 循环向表中添加数据
delimiter $$
create procedure insertTest(n int)
begin
declare num int default 6; -- 创建 num 变量,默认值给1
while num <= n do -- 循环条件 num <= n
insert into t_user values(num,concat('李白',num),concat('LiBai',num),concat('123456',num),concat('咸阳市',num),0,NOW());
set num = num + 1;
end while;
end $$
delimiter ;
call insertTest(30);
-- 计算从1加到n的值
delimiter $$
create procedure pro_test14(n int)
begin
declare total int default 0; -- 创建 total 变量,默认值给0
declare num int default 1; -- 创建 num 变量,默认值给1
while num <= n do -- 循环条件 num <= n
set total = total + num;
set num = num + 1;
end while;
select total;
end $$
delimiter ;
call pro_test14(10);
-- 案例二 循环向表中插入数据
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1;
label:while i <= insertCount do -- label:只是一个标识(可有可无）表示该循环从哪开始
insert into user (uid, username, password)values(i,concat(user,i),'123456');
set i = i + 1;
end while label;-- label表示结束哪一个循环
end $$
delimiter ;
call proc16_while(10);
-- while + leave
-- leave：直接跳出while循环
delimiter $$
create procedure proc17_while_leave(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; --当i = 5时，就跳出以label:标识开头的此循环；
end if;
set i = i + 1;
end while label; 结束以label:标识开头的循环；
select '循环结束';
end $$
delimiter ;
call proc17_while_leave(10);
-- while + iterate
-- iterate:跳过本次循环的剩余代码，进入下一次循环
delimiter $$
create procedure proc17_while_iterate(in insertCount int)
begin
declare i int default 0;
label:while i < insertCount do
set i = i + 1;
if i = 5 then iterate label;-- 当i = 5时 就执行下面的代码，直接进行下一次循环。
end if;
insert into user2(uid, username, password) values(i,concat('user-',i),'123456');
end while label;
select '循环结束';
end $$
delimiter ;
call proc17_while_iterate(10); -- 1，2，3，4，6，7，8，9 会跳过5
repeat语句
有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行，repeat 是满足条件就退出循环。
语法结构 :
[标签：]REPEAT
循环体；
UNTIL 条件表达式 -- 当UNTIL条件成立的时候，才会跳出循环
END REPEAT;
示例 :
delimiter $$
create procedure proc18_repeat(in insertCount 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 > insertCount -- 当 i > insertCount,就跳出循环
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc18_repeat(100);
loop语句
LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：
[标签:] LOOP
循环体；
if 条件表达式 then
leave [标签]；
END IF；
END LOOP；
如果不在IF中增加退出循环的语句，那么 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 > insertCount then leave label; -- 如果i > insertCount时 ，该循环退出；
end if ;
end loop label; -- 结束循环
end $$
delimiter ;
call proc19_loop(100);
游标/光标
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。
声明游标：
DECLARE cursor_name CURSOR FOR select_statement
打开游标：
OPEN cursor_name ;
通过游标获取值：
FETCH cursor_name INTO var_name [, var_name] ...
关闭游标：
CLOSE cursor_name ;
示例 :
初始化脚本:
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
复制代码
-- 查询emp表中数据, 并逐行获取进行展示
delimiter $$
create procedure pro_test11()
begin
-- 定义变量
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
-- 定义游标
declare emp_result cursor for select * from emp;
-- 打开游标
open emp_result;
-- 通过游标取值
fetch emp_result into e_id,e_name,e_age,e_salary; -- 通过游标emp_result将查出来的值赋值给声明的三个变量
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
close emp_result;
end $$
delimiter ;
通过循环结构 , 获取游标中的数据 :
DELIMITER $$
create procedure pro_test12()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
open emp_result;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
until has_data = 0
end repeat;
close emp_result;
end $$
DELIMITER ;
异常处理
-- 游标 + 句柄
/*
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
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
*/
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(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;
-- 定义句柄：定义异常的处理方式
/*
1:异常处理完之后程序该怎么执行
continue :继续执行剩余代码
exit : 直接终止程序
undo: 不支持
2: 触发条件
条件码:
1329
条件名：
SQLWARNING
NOT FOUND
SQLEXCEPTION
3:异常触发之后执行什么代码
设置flag的值 ---》 0
*/
declare continue handler for 1329 set flag = 0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
-- 判断flag，如果flag的值为1，则执行，否则不执行
if flag = 1 then
select var_empno, var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- xxxxx
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc21_cursor_handler('教研部');
练习
-- 练习
/*
创建下个月的每天对应的表user_2021_12_01、user_2022_12_02、...
需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！
*/
-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
create database 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);
declare t_index int default 1;
-- declare create_table_sql varchar(200);
-- 获取下个月的年份
set next_year = year(date_add(now(),INTERVAL 1 month)); -- 2021
-- 获取下个月是几月
set next_month = month(date_add(now(),INTERVAL 1 month)); -- 11
-- 下个月最后一天是几号
set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); -- 30
if next_month < 10
then set next_month_str = concat('0',next_month); -- 1 ---》 01
else
set next_month_str = concat('',next_month); -- 12
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 ,`uname` 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();
