MYSQL存储过程（一）

2022-06-17 06:23:44

1. 环境说明

 MYSQL  5.6

2. 使用说明

 存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参出参(类似于java中的方法的书写)。创建时会预先编译后保存，用户后续调用不需要再次编译。

3. 优缺点

 优点：

在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。

执行速度快，存储过程经过编译之后会比单独一条一条执行要快。

减少网络传输流量。

方便优化。

 缺点：

过程化编程，复杂业务处理的维护成本高。

调试不便，无法像JAVA一样断点调试

不同数据库之间可移植性差。（不同数据库语法不一致！）

4. 语法

 官方参考网址

 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

  • 语法结构
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();
  • 变量及赋值
    • 局部变量：用户自定义，在begin/end代码块中有效
语法：
声明变量 declare var_name type [default var_value];
举例：declare p_name varchar(32);

-- set赋值
create procedure p_01()
begin
    declare p_name varchar(32) default 'unkown';
    set p_name = 'ZS';
    -- set p_name := 'ls';
    select p_name;
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 p_02()
begin
    set @p_name = 'ZS';
    -- set p_name := 'ls';
end$$
call p_02() $$
select @p_name$$  --可以看到结果
    • 会话变量：由系统提供，当前会话（连接）有效 
语法：
@@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;
  • 入参出参
-- 语法
in | out | inout param_name type

-- IN类型演示
delimiter $$
create procedure sp_01(in age int)
begin
    set @user_age = age;
end$$
call sp_01(10) $$
select @user_age$$

-- OUT类型，只负责输出！
-- 需求：输出传入的地址字符串对应的部门编号。
delimiter $$

create procedure sp_02(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_01('DALLAS',@dept_no);
select @dept_no;

-- INOUT类型 
delimiter $$
create procedure sp_03(inout name varchar)
begin
    set name = concat('hello' ,name);
end$$
delimiter ;

set @user_name = '小明';
call sp_03(@user_name);
select @user_name;
  • 流程控制-判断
    • 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 ;
  • 流程控制-循环
    • 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$$
  • 流程控制-退出、继续循环
    • leave
-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label
    • iterate
-- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label
上一篇：MYSQL 逗号分割字段行转列


下一篇：利用大数据分析实现互联网精准营销