关于mysql:mysql-存储过程

MySQL 存储过程

0. 环境阐明：

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.html
https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html

3.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_name

show 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.html

if

-- 语法
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

举例：

-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值 exp2-exp1 差值，单位是 unit
select 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_list
END 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_list
UNTIL search_condition    -- 直到…为止，才退出循环
END REPEAT [end_label]

-- 需要：循环打印 1 到 10
delimiter $$
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_list
END WHILE [end_label]

-- 需要：循环打印 1 到 10
delimiter $$
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 label

iterate

类比 java 的 continue

-- 持续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label

3.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] ...
    statement

handler_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.html
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) 返回日

-- 思路：循环构建表名 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.html

select 
    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/BV1a7411Z7BN

5.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;