欢迎各位彦祖与热巴畅游本人专栏与博客
你的三连是我最大的动力
以下图片仅代表专栏特色 [点击箭头指向的专栏名即可闪现]
专栏跑道一
➡️网络空间安全——全栈前沿技术持续深入学习
专栏跑道二
➡️ 24 Network Security -LJS
专栏跑道三
➡️ MYSQL REDIS Advance operation
专栏跑道四
➡️HCIP；H3C-SE;CCIP——LJS[华为、华三、思科高级网络]
专栏跑道五
➡️RHCE-LJS[Linux高端骚操作实战篇]
专栏跑道六
➡️数据结构与算法[考研+实际工作应用+C程序设计]
专栏跑道七
➡️RHCSA-LJS[Linux初级及进阶骚技能]
上节回顾
Mysql And Redis基础与进阶操作系列（9）之存储过程
1.概念
存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于 JAVA语言中的方法；
存储过程就是数据库 SQL 语言层面的代码封装与重用。
2. 作用之优点
存储过程的优点
| 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以 完成复杂的判断和较复杂的运算。 |
存储过程允许标准组件式编程。 存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。 而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。 |
存储过程能实现较快的执行速度。 如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。 因为存储过程是预编译的。 在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。 而批处理的 Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。 |
存储过程能过减少网络流量。 针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉 的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。 |
存储过程可被作为一种安全机制来充分利用。 系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。 |
格式
delimiter 自定义结束符号 create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...) begin sql语句 end 自定义的结束符合 delimiter ;
示例
创建存储过程
delimiter $$ create procedure proc01() begin select empno,ename from emp; end $$ delimiter ;
调用存储过程
call proc01();
3.变量
局部变量:
用户自定义，在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();
还可以使用 SELECT..INTO 语句为变量赋值。语法如下：
select col_name [...] into var_name[,...] from table_name wehre condition 其中： col_name 参数表示查询的字段名称； var_name 参数是变量的名称； table_name 参数指表的名称； condition 参数指查询条件。
注意：
当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
举例：
elimiter $$ 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();
举例
delimiter $$ create procedure proc04() begin set @var_name01 = 'ZS'; end $$ delimiter;
调用存储过程
call proc04() ; select @var_name01 ;--可以看到回显结果
系统变量：
- 系统变量又分为全局变量与会话变量全局变量
- 在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这 个文件来更改。
- 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
- 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
- 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
- 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
系统变量-全局变量：由系统提供，在整个数据库有效。
格式
语法： @@global.var_name
举例
查看全局变量
show global variables;
查看某全局变量
select @@global.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 ;
4. 参数传递
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);
out
out 表示从存储过程内部传值给调用者
use mysql7_procedure;
封装有参数的存储过程，传入员工编号，返回员工名字
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 empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2)) begin select ename,sal into out_ename,out_sal from emp where emp.empno = empno; end $$ delimiter ; call proc09(1001, @o_dname,@o_sal); select @o_dname; select @o_sal;
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 ;
小结
in 输入参数，也就是参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变，并向外输出
inout 输入输出参数，既能输入一个值又能传出来一个值
5. 流程控制
5.1流程控制-判断
格式
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
举例
输入学生的成绩，来判断成绩的级别：
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) /*score < 60 :不及格 score >= 60 , score <80 :及格 score >= 80 , score < 90 :良好 score >= 90 , score <= 100 :优秀 score > 100 :成绩错误 */
输入员工的名字，判断工资的情况
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('庞统');
5.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);
5.3 流程控制-循环
概述
循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
循环分类： while ;repeat ;loop
循环控制：
leave 类似于 break，跳出，结束当前所在的循环
iterate类似于 continue，继续，结束本次循环，继续下一次
编辑
流程控制-循环-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);
流程控制-循环-repeat
格式
[标签:]repeat 循环体; until 条件表达式 end repeat [标签];
举例
use mysql7_procedure; truncate table user;
存储过程-循环控制-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 end repeat label; select '循环结束'; end $$ delimiter ; call proc18_repeat(100);
流程控制-循环-loop
格式
[标签:] loop 循环体; if 条件表达式 then leave [标签]; end if; end loop;
举例
truncate table user;
存储过程-循环控制-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.4 游标
简介
游标(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
举例
use mysql7_procedure;
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('销售部');
本例sql汇总
use mysql7_procedure; 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('销售部');
5.5 句柄
简介
MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.
语法格式
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
S1:定义局部变量
declare var_empno int; declare var_ename varchar(50); declare var_sal decimal(7,2); declare flag int default 1;
S2:声明游标
declare my_cursor cursor for select empno,ename,sal from dept a, emp b where a.deptno = b.deptno and a.dname = in_dname;
S3:定义句柄，当数据未发现时将标记位设置为0
declare continue handler for NOT FOUND set flag = 0;
S4:打开游标
open my_cursor;
通过游标获取值
label:loop fetch my_cursor into var_empno, var_ename,var_sal;
S5:判断标志位
if flag = 1 then select var_empno, var_ename,var_sal;练习 else leave label; end if; end loop label;
S6:关闭游标
close my_cursor; end $$; delimiter ; call proc21_cursor_handler('销售部');
上述sql语句整合
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_2021_11_02、...
需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时
如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的 统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表
思路：
循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
知识补充和预备
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) 返回日
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();
本题上述sql语句以及注释整合
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); -- 获取下个月的年份第11章 存储函数 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();
