mysql stored procedure

MySQL stored procedure

0. Environment description:

software	Version
mysql	5.6
HeidiSQL

1. Instructions for use

A stored procedure is an important object of the database, which can encapsulate a set of SQL statements, can be used to complete some more complex business logic, and can input and output parameters (similar to the writing of methods in java).

When created, it will be pre-compiled and saved, and subsequent calls by the user do not need to be compiled again.

// 把editUser类比成一个存储过程
public void editUser(User user,String username){
    String a = "nihao";
    user.setUsername(username);
}
main(){
    User user = new User();
    editUser(user,"张三");
    user.getUseranme();   //java基础还记得不
}

You may think that it is necessary to re-learn to use sql to process business logic. Can’t I use java to process logic (such as loop judgment, loop query, etc.)? So, why use stored procedures to handle business logic?

优点：
    在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。
    执行速度快，存储过程经过编译之后会比单独一条一条执行要快。
    减少网络传输流量。
    方便优化。
缺点：
    过程化编程，复杂业务处理的维护成本高。
    调试不便
    不同数据库之间可移植性差。-- 不同数据库语法不一致！

2. Prepare:

The sql script in the database reference material;
delimiter $$ --声明结束符

3. Grammar

-- 官方参考网址
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 Syntax structure

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

Simple demo:

-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突
delimiter $$

CREATE PROCEDURE hello_procedure ()
BEGIN
    SELECT 'hello procedure';
END $$

call hello_procedure();

3.1 Variables and assignments

类比一下java中的局部变量和成员变量的声明和使用

local variable:

User-defined, valid in begin/end blocks

语法：
声明变量 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$$

User variables:

User-defined, the current session (connection) is valid. Similar to member variables of java

语法： 
@var_name
不需要提前声明，使用即声明

-- 赋值
delimiter $$
create procedure sp_var02()
begin
    set @nickname = 'ZS';
    -- set nickname := 'SF';
end$$
call sp_var02() $$
select @nickname$$  --可以看到结果

session variable:

Provided by the system, the current session (connection) is valid

语法：
@@session.var_name

show session variables; -- 查看会话变量
select @@session.unique_checks; -- 查看某会话变量
set @@session.unique_checks = 0; --修改会话变量

Global variable:

Provided by the system, the whole mysql server works

语法：
@@global.var_name

Example

-- 查看全局变量中变量名有char的记录
show global variables like '%char%'; 

-- 查看全局变量character_set_client的值
select @@global.character_set_client;

3.2 Input parameters and output parameters

-- 语法
in | out | inout param_name type

Example

-- 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 Process Control - Judgment

官网说明
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

Example:

-- 前置知识点：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`

This syntax can be used not only in stored procedures, but also in query statements!

-- 语法一（类比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

Example:

-- 需求：入职年限年龄<=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 Flow Control - Loop

`loop`

-- 语法
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

Example

It should be noted that loop is an infinite loop, and we need to exit the loop manually. We can use leave to exit.
You can think of leave as the break in our java; correspondingly, there is iterate (continue to loop) - analogous to java's 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 Flow Control - Exit, Continue Loop

`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 Cursors

Use the cursor to get a result set and process the data row by row.

类比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');

pay attention:
In the grammar, variable declaration, cursor declaration, and handler declaration must be written in order, otherwise an error occurs when creating a stored procedure.

3.7 Handlers in Stored Procedures

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. Practice

——Please note that the business process of stored procedures can generally be implemented in java code. Our following requirements are to practice stored procedures

4.1 Updating data using stored procedures

为某部门(需指定)的人员涨薪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 Loop to create table

创建下个月的每天对应的表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 Other scenarios:

“为用户添加购物积分，并更新到用户的总积分表中”等需要对多张表进行CRUD操作的业务。
而且内部可以使用事务命令。

5. Others

5.1 characteristic

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

Among them, the meaning of SQL SECURITY is as follows:

    MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；
    如果SQL SECURITY子句指定为DEFINER(定义者)，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储
    过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象(存储过程中的表等对象)的权限；
    如果SQL SECURITY子句指定为INVOKER(调用者)，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；
    如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。

5.2 Infinite loop processing

-- 如有死循环处理，可以通过下面的命令查看并结束
show processlist;
kill id;

5.3 You can write case in the select statement

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 Temporary tables

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 stored procedure

Please refer to the video of station B

https://www.bilibili.com/video/BV1a7411Z7BN

5.6 Copying tables and data

CREATE TABLE dept SELECT * FROM procedure_demo.dept;
CREATE TABLE emp SELECT * FROM procedure_demo.emp;
CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade;

mysql stored procedure