MySQL存储过程
0.环境阐明：
| 软件 | 版本 |
|---|---|
| mysql | 5.6 |
| HeidiSQL |
1.应用阐明
存储过程时数据库的一个重要的对象，能够封装SQL语句集，能够用来实现一些较简单的业务逻辑，并且能够入参出参(相似于java中的办法的书写)。
创立时会事后编译后保留，用户后续的调用都不须要再次编译。
// 把editUser类比成一个存储过程public void editUser(User user,String username){ String a = "nihao"; user.setUsername(username);}main(){ User user = new User(); editUser(user,"张三"); user.getUseranme(); //java根底还记得不}大家可能会思考，用sql解决业务逻辑还要从新学，我用java来解决逻辑（比方循环判断、循环查问等）不行吗？那么，为什么还要用存储过程解决业务逻辑呢？
长处： 在生产环境下，能够通过间接批改存储过程的形式批改业务逻辑（或bug），而不必重启服务器。 执行速度快，存储过程通过编译之后会比独自一条一条执行要快。 缩小网络传输流量。 不便优化。毛病： 过程化编程，简单业务解决的保护老本高。 调试不便 不同数据库之间可移植性差。-- 不同数据库语法不统一！2.筹备：
数据库参阅材料中的sql脚本；
delimiter $$ --申明结束符
3.语法
-- 官网参考网址https://dev.mysql.com/doc/refman/5.6/en/sql-statements.htmlhttps://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html3.0 语法结构
CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body -- proc_parameter参数局部，能够如下书写： [ IN | OUT | INOUT ] param_name type -- type类型能够是MySQL反对的所有类型 -- routine_body(程序体)局部，能够书写非法的SQL语句 BEGIN ... END简略演示：
-- 申明结束符。因为MySQL默认应用‘;’作为结束符，而在存储过程中，会应用‘;’作为一段语句的完结，导致‘;’应用抵触delimiter $$CREATE PROCEDURE hello_procedure ()BEGIN SELECT 'hello procedure';END $$call hello_procedure();3.1 变量及赋值
类比一下java中的局部变量和成员变量的申明和应用局部变量：
用户自定义，在begin/end块中无效
语法：申明变量 declare var_name type [default var_value];举例：declare nickname varchar(32);-- set赋值create procedure sp_var01()begin declare nickname varchar(32) default 'unkown'; set nickname = 'ZS'; -- set nickname := 'SF'; select nickname;end$$-- into赋值delimiter $$create procedure sp_var_into()begin declare emp_name varchar(32) default 'unkown' ; declare emp_no int default 0; select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839; select emp_no,emp_name;end$$用户变量：
用户自定义，以后会话（连贯）无效。类比java的成员变量
语法： @var_name不须要提前申明，应用即申明-- 赋值delimiter $$create procedure sp_var02()begin set @nickname = 'ZS'; -- set nickname := 'SF';end$$call sp_var02() $$select @nickname$$ --能够看到后果会话变量：
由零碎提供，以后会话（连贯）无效
语法：@@session.var_nameshow session variables; -- 查看会话变量select @@session.unique_checks; -- 查看某会话变量set @@session.unique_checks = 0; --批改会话变量全局变量：
由零碎提供，整个mysql服务器无效
语法：@@global.var_name举例
-- 查看全局变量中变量名有char的记录show global variables like '%char%'; -- 查看全局变量character_set_client的值select @@global.character_set_client; 3.2 入参出参
-- 语法in | out | inout param_name type举例
-- IN类型演示delimiter $$create procedure sp_param01(in age int)begin set @user_age = age;end$$call sp_param01(10) $$select @user_age$$-- OUT类型，只负责输入！-- 需要：输入传入的地址字符串对应的部门编号。delimiter $$create procedure sp_param02(in loc varchar(64),out dept_no int(11))begin select d.deptno into dept_no from dept d where d.loc = loc; --此处强调，要么表起别名，要么入参名不与字段名统一end$$delimiter ;--测试set @dept_no = 100;call sp_param01('DALLAS',@dept_no);select @dept_no;-- INOUT类型 delimiter $$create procedure sp_param03(inout name varchar)begin set name = concat('hello' ,name);end$$delimiter ;set @user_name = '小明';call sp_param03(@user_name);select @user_name;3.3 流程管制-判断
官网阐明https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.htmlif
-- 语法IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list]END IF举例：
-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unitselect timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499';-- 需要：入职年限<=38是老手 >38并且<=40老员工 >40元老delimiter $$create procedure sp_hire_if()begin declare result varchar(32); if timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; elseif timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; else set result = '老手'; end if; select result;end$$delimiter ;case
此语法是不仅能够用在存储过程，查问语句也能够用！
-- 语法一（类比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举例：
-- 需要：入职年限年龄<=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_listEND 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
[begin_label:] REPEAT statement_listUNTIL search_condition -- 直到…为止，才退出循环END REPEAT [end_label]-- 需要：循环打印1到10delimiter $$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_listEND WHILE [end_label]-- 需要：循环打印1到10delimiter $$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 labeliterate
类比java的continue-- 持续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statementsITERATE label3.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] ... statementhandler_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.htmlPREPARE stmt_name FROM preparable_stmtEXECUTE 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.htmlselect 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');5.5 oracle存储过程
请参阅B站视频
https://www.bilibili.com/video/BV1a7411Z7BN5.6 复制表和数据
CREATE TABLE dept SELECT * FROM procedure_demo.dept;CREATE TABLE emp SELECT * FROM procedure_demo.emp;CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade;