mysql 8存储过程_MySQL 8 的存储过程的详细说明与用法

MySQL的存储过程可以将多个SQL语句放到一起来执行，而不用每次都输入所有的SQL语句，这样做非常方便。另外还有一些与单独的SQL语句不同的是，存储过程可以将结果保存在变量中，并且还可以在存储过程中使用编程性的语法，如IF，CASE和WHILE循环。MySQL中的存储函数与存储过程统一称为存储例程。

要创建存储过程需要

CREATE ROUTINE 权限。

存储函数有一个返回值。

存储过程没有返回值。

存储例程中的所有代码都要写在BEGIN和END代码块中。

存储函数可以直接在SELECT语句中调用。

存储过程要通过CALL语句来调用。

由于存储例程中的语句应该以分隔符(;)结束，因此必须更改MySQL的分隔符，MySQL不会用普通语句解释存储例程中的SQL语句。在创建过程之后，可以将分隔符更改为默认值。

如果你想要在数据库中添一个员工的信息时，就要插入三个表如employees,，salaries，和titles。我们创建一个存储过程来执行，这个存储过程的输出为员工的编号new_emp_no，输入有first_name、last_name、gender、birth_date、emp_dept_name、title：/* 删除已存在存储过程 */

DROP PROCEDURE IF EXISTS create_employee;

/* 改变SQL分隔符为 $$ */

DELIMITER $$

/* IN 表示输入参数,INOUT 表示输出变量*/

CREATE PROCEDURE create_employee (OUT new_emp_no INT,

IN first_name varchar(20), IN last_name varchar(20),

IN gender enum('M','F'), IN birth_date date,

IN emp_dept_name varchar(40),

IN title varchar(50))

BEGIN

/* 声明变量 emp_dept_no 和salary */

DECLARE emp_dept_no char(4);

DECLARE salary int DEFAULT 60000;

/* 选择最大的 employee 数值并存到 new_emp_no变量中 */

SELECT max(emp_no) INTO new_emp_no FROM employees;

/* 增加 new_emp_no 值 */

SET new_emp_no = new_emp_no + 1;

/* 向 employees 表中插入数据 */

/* 这个 CURDATE() 函数给定当前日期 */

INSERT INTO employees VALUES(new_emp_no,

birth_date, first_name, last_name, gender,

CURDATE());

/* Find out the dept_no for dept_name */

SELECT emp_dept_name;

SELECT dept_no INTO emp_dept_no FROM departments

WHERE dept_name=emp_dept_name;

SELECT emp_dept_no;

/* Insert into dept_emp */

INSERT INTO dept_emp VALUES(new_emp_no,

emp_dept_no, CURDATE(), '9999-01-01');

/* Insert into titles */

INSERT INTO titles VALUES(new_emp_no, title,

CURDATE(), '9999-01-01');

/* Find salary based on title */

IF title = 'Staff'

THEN SET salary = 100000;

ELSEIF title = 'Senior Staff'

THEN SET salary = 120000;

END IF;

/* Insert into salaries */

INSERT INTO salaries VALUES(new_emp_no, salary, CURDATE(), '9999-01-01');

END

$$

/* 将分隔改回;号 */

DELIMITER ;

注意，存储过程值返回方式不是直接返回的，而在参数中添加输出变量来实现的，这样就可以使用SELECT查看这个变量的值：mysql> select @new_emp_no;

可以将语句粘贴到命行执行。

可以通过导入的方法执行：mysql -u -p employees < stored_procedure.sql

使用SOURCE语句执行：mysql> SOURCE stored_procedure.sql;

要为emp_read_only赋于可执行权限：mysql> GRANT EXECUTE ON employees.* TO

'emp_read_only'@'%';

Query OK, 0 rows affected (0.05 sec)

使用CALL stored_procedure(OUT variable, IN values) 调用存储过程，成功执行！

注意emp_read_only用户只有读的权限。如果不想让这个用户执行，在创建存储过程时将SQL SECURITY这个属性设置为

INVOKER ，黙认是DEFINER。

向存储过程中传入输出变量new_emp_no：mysql> CALL create_employee(@new_emp_no, 'John',

'Smith', 'M', '1984-06-19', 'Research', 'Staff');

Query OK, 1 row affected (0.01 sec)

查看数据库中的存储过程：mysql> SHOW PROCEDURE STATUS\G

mysql> SHOW CREATE PROCEDURE \G