MySQL存储过程

存储过程

存储过程：就是一组经过 预先编译 的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

优点：

• 简化操作，提高了 SQL 语句的重用性。
• 减少操作过程中的失误，提高效率。
• 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）。
• 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性。

和视图、函数的对比：

同样的清晰、安全，还可以减少网络传输量。视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以 直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值 的。

分类

存储过程的参数类型可以是 IN、OUT 和 INOUT，IN、OUT、INOUT 都可以在一个存储过程中带多个。根据这点分类如下：

• 没有参数（无参数无返回）
• 仅仅带 IN 类型（有参数无返回）
• 仅仅带 OUT 类型（无参数有返回）
• 既带 IN 又带 OUT（有参数有返回）
• 带 INOUT（有参数有返回）

存储过程创建

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
    存储过程体

END

例如：
CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '查看最高薪资'
    BEGIN
        SELECT MAX(salary) FROM emps;
    END //

DELIMITER ;

参数说明：

• IN：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

• OUT：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

• INOUT：当前参数既可以为输入参数，也可以为输出参数。

characteristics：

characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

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

• LANGUAGE SQL：说明存储过程执行体是由 SQL 语句组成的，当前系统支持的语言为 SQL。
• [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为 NOT DETERMINISTIC。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用 SQL 语句的限制。
  • CONTAINS SQL 表示当前存储过程的子程序包含 SQL 语句，但是并不包含读写数据的 SQL 语句；
  • NO SQL 表示当前存储过程的子程序中不包含任何 SQL 语句；
  • READS SQL DATA 表示当前存储过程的子程序中包含读数据的 SQL 语句；
  • MODIFIES SQL DATA 表示当前存储过程的子程序中包含写数据的 SQL 语句。
  • 默认情况下，系统会指定为 CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  • DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  • 如果没有设置相关的值，则 MySQL 默认指定值为DEFINER。
• COMMENT ‘string’：注释信息，可以用来描述存储过程。

结束标记

DELIMITER 新的结束标记

因为 MySQL 默认的语句结束符号为分号“;。为了避免与存储过程中 SQL 语句结束符相冲突，需要使用 DELIMITER 改变存储过程的结束符。
比如：“DELIMITER //”语句的作用是将 MySQL 的结束符设置为 //，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
    sql语句1;
    sql语句2;

END $

存储过程

创建存储过程 show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过 OUT 参数“ms”输出

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
    BEGIN
        SELECT MIN(salary) INTO ms FROM emps;
    END //

DELIMITER ;

创建存储过程 show_someone_salary2()，查看“emps”表的某个员工的薪资，并用 IN 参数 empname 输入员工姓名，用 OUT 参数 empsalary 输出员工薪资。

DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
    BEGIN
        SELECT salary INTO empsalary FROM emps WHERE ename = empname;
    END //

DELIMITER ;

存储过程调用

调用 in 模式的参数：

CALL sp1('值');

调用 out 模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;

调用 inout 模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;

原文链接：https:/www.pendulumye.com/mysql/250.html