Mysql杂志（六）

存储过程

存储过程（Stored Procedure）是预编译的SQL语句集合，存储在数据库中作为一个可执行单元，它类似于其他编程语言中的函数或方法。

DELIMITER //
CREATE PROCEDURE process_order(IN order_id INT)
BEGIN
    -- SQL语句集合
    UPDATE orders SET status = 'processing' WHERE id = order_id;
    INSERT INTO order_logs VALUES(order_id, NOW(), 'Process started');
END //
DELIMITER ;

这个DELIMITER是MySQL客户端命令（不是SQL语句），用于临时修改语句结束符。因为Mysql的默认结束符号是;分号，所以会导致存储过程提前结束了，这个时候就需要DELIMITER来临时修改结束符号。下面这个是存储过程的语法结构主包一个一个关键词的说。

DELIMITER delimiter_character  -- 临时修改语句分隔符

CREATE PROCEDURE procedure_name([parameter_list])
    [characteristic ...]  -- 特性选项
BEGIN
    -- 声明部分
    DECLARE variable_name datatype [DEFAULT value];
    
    -- 执行部分
    SQL statements;
    Control statements;
    
    -- 异常处理(可选)
    DECLARE handler_type HANDLER FOR condition_value handler_statements;
END delimiter_character

DELIMITER ;  -- 恢复默认分隔符

[parameter_list]

(
    [IN | OUT | INOUT] param_name data_type [(length)] 
        [,
        [IN | OUT | INOUT] param_name data_type [(length)]
        ...]
)

这里说一下这个IN可以在存储过程内部修改，但是外部查询的话值是不会改变的，然后变量名后面是必须要定义数据类型的，这个数据类型有哪些我就不多说了，大家用的基本上都有。然后这个参数是可以为空的，如果存储过程中有查询，就算没有定义输出参数也会返回到客户端的。

[characteristic ...]

CREATE PROCEDURE proc_name()
[
    [NOT] DETERMINISTIC
    | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
    | SQL SECURITY {DEFINER | INVOKER}
    | COMMENT 'string'
    | LANGUAGE SQL
] ...

DETERMINISTIC（确定性）

声明存储过程是否是确定性的（相同输入是否总是产生相同输出），默认是不确定性的。

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

这个是声明存储过程的SQL访问特性（帮助优化器优化）。也就是告诉Mysql我这个存储过程是什么类型的，当然这个也不是必填的。

SQL SECURITY（安全控制）

定义执行存储过程时的权限检查方式，这个有两个选项，DEFINER（默认）：以定义者权限执行、INVOKER：以调用者权限执行。这个就是用来权限控制的，如果是默认的那就以创建这个存储过程的用户权限来执行，反之就是调用者（也就是实际使用者）。假如一下表只能管理员访问那么如果是管理员创建的存储过程，前者DEFINER大家都可以使用，后者就只能有管理员权限的才能使用。

COMMENT（注释）

这个也很好理解就是为存储过程添加描述信息（显示在SHOW CREATE PROCEDURE中），就是加注释告诉大家这个存储过程是干什么的。

LANGUAGE（语言）

指定存储过程语言（MySQL中始终为SQL），这个就更加不用管了，基本上不会使用这个，大家了解一下就可以了。

DECLARE variable_name datatype [DEFAULT value]

声明局部变量或条件/异常处理器，在MySQL存储过程中，​DECLARE语句必须出现在BEGIN-END块的最开始部分，在任何可执行语句之前。这是MySQL存储过程语法的硬性规定，违反会导致编译错误。

这个就和我们使用的函数方法就不同了，只能在最开始的时候定义局部变量，不能随便定义。

BEGIN
    DECLARE v_total DECIMAL(10,2) DEFAULT 0.0;
    DECLARE v_discount_rate FLOAT DEFAULT 0.1;
    
    SET v_total = 100 * (1 - v_discount_rate);
    
    #业务部分省略。。。
END

这个默认值是可选的哦，不是必须要设置的。

执行部分

这个部分就不多说了，除了增删改查还有各种函数外，也可以使用if分支选择以及循环结构等等，就和其他的函数是一样的，就是太多太广泛了，这边就不多说了。

异常处理(可选)

DECLARE handler_type HANDLER FOR condition_value [, condition_value]... handler_statements

​handler_type（处理器类型）

condition_value（异常条件）

handler_statements（处理逻辑）

可以是简单语句或BEGIN-END复合语句，常见的操作就是记录错误日志、回滚、设置返回值。

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    INSERT INTO error_log VALUES(NOW(), 'PROCEDURE_ERROR');
    SET out_result = -1;
END;

这部分也是比较复杂的，写的方式也有很多种，这边把大概的写法都列一下，大家自行看看吧。

#特定错误
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'  -- 唯一键冲突
BEGIN
    SET duplicate_count = duplicate_count + 1;
END;

#多条件错误
DECLARE EXIT HANDLER FOR 
    SQLSTATE 'HY000' OR 1062 OR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;
    
    INSERT INTO error_log 
    VALUES(@err_no, @err_msg, CURRENT_TIMESTAMP());
END;

#获取详细信息
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @sqlstate = RETURNED_SQLSTATE,
    @errno = MYSQL_ERRNO,
    @text = MESSAGE_TEXT;
    
    -- 记录完整错误信息
    INSERT INTO error_details 
    VALUES(@sqlstate, @errno, @text, NOW());
END;

总结

本篇主要讲的就是存储过程的语法结构和各个关键词的作用和使用方式。