关于mysql:mysql-存储过程

36次阅读

共计 11902 个字符,预计需要花费 30 分钟才能阅读完成。

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

正文完
 0