存储过程
如果需要在MySQL中执行一系列语句，可以将所有语句封装在单个程序中，并在需要的时候调用这个程 序，而不是每次发送所有SQL语句。存储过程处理的是一组SQL语句，且没有返回值。
除了SQL语句，还可以使用变量来存储结果并在存储过程中执行程序化的内容。例如可以使用if,case语句、逻辑操作和while循环。
-
存储的函数（function）和过程（procedure [prəˈsiːdʒər]）都称为存储例程（routine [ruːˈtiːn] ）
-
要创建存储过程，应该具有CREATE ROUTINE [ruːˈtiːn] 权限
-
存储函数具有返回值
-
存储过程没有返回值
-
所有代码写在BEGIN和END会之间
-
存储函数可以直接在SELECT语句中调用
-
可以使用CALL语句调用存储过程 由于存储过程中的语句应以分隔符（;）结尾，因此必须要更改MySQL的分隔 符，以便MySQL会用正常语句解释存储例程中的SQL语句。创建过程结束后，可以将分隔符更改回默值。
操作过程
假设想要添加新员工，你需要更新3个表，分别是employees[ɛmˈplɔɪiz]表、salaries [ˈsæləriz] 表和titles[ˈtaɪtlz]表。可以开发一个 存储过程并调用它来创建新的employee，而不是执行三条语句。
# 查询三表结构： hire [ˈhaɪər] 雇用
> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
> desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| title | varchar(50) | NO | PRI | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| salary | int(11) | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
必须传递的信息包括员工的first_name、last_name、gender和birth_date以及员工加入部门 department。可以使用输入变量来传递这些变量，并且应该将员工编号作为输出。存储过程不返回 值，但它可以更新一个变量并使用它。
实例：实现的是创建新的employee并更新salaries表和departments表：
# 在创建之前，如果存在任何相同名字的存储过程，则删除已经存在的存储过程 [prəˈsiːdʒər]
> DROP PROCEDURE IF EXISTS create_employee;
# 修改分隔符为“$$” delimiter 分隔符
> DELIMITER $$
# "IN" 指定作为参数的变量， "OUT" 指定输出的变量
> CREATE PROCEDURE create_employee (
-> OUT new_emp_no INT,
-> IN first_name varchar(20),
-> IN last_name varchar(20),
-> IN gender e num('M','F'),
-> IN birth_date date,
-> IN emp_dept_name varchar(40),
-> IN title varchar(50))
-> begin
# 为emp_dept_no和salary声明变量 declare [dɪˈkler] 声明
-> DECLARE emp_dept_no char(4);
-> DECLARE salary int DEFAULT 60000;
# 查询employees表的emp_no的最大值，赋值给变量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());
# 找到dept_name对应的dept_no
-> SELECT emp_dept_name;
-> SELECT dept_no INTO emp_dept_no FROM departments WHERE dept_name=emp_dept_name;
-> SELECT emp_dept_no;
# 插入dept_emp
-> INSERT INTO dept_emp VALUES(new_emp_no,emp_dept_no,CURDATE(),'9999-01-01');
# 插入titles
-> INSERT INTO titles VALUES(new_emp_no,title,CURDATE(),'9999-01-01');
# 以title为条件查询的薪水
-> IF title = 'Staff'
-> THEN SET salary = 100000;
-> ELSEIF title = 'Senior Staff'
-> THEN SET salary = 120000;
-> END IF;
# 插入salaries
-> INSERT INTO salaries VALUES(new_emp_no,salary,CURDATE(),'9999-01-01');
-> END
-> $$
# 改回分隔符
> DELIMITER ;
要创建存储过程，可以有以下方法:
# 1. 将上述代码粘贴到命令行客户端中
# 2. 将以上代码保存成文件
# 并使用mysql -u {user} -p employees < stored_procedure.sql将其导入到 MySQL中
$ mysql employees < stored_procedure.sql
# 3. 使用SOURCE从文件加载：source ./stored_procedure.sql:
> source stored_procedure.sql;
# 查询新建立的存储过程是否存在：
> show procedure status like '%emp%'\G
*************************** 1. row ***************************
Db: employees
Name: create_employee
Type: PROCEDURE
Definer: root@localhost
Modified: 2020-03-02 16:32:16
Created: 2020-03-02 16:32:16
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
# 把你要传递的输出值存储在@new_emp_no中，并传递所需的输入值
> CALL create_employee(@new_emp_no,'John','Smith','M','1984-06-19','Research','Staff');
+---------------+
| emp_dept_name |
+---------------+
| Research |
+---------------+
1 row in set (0.01 sec)
+-------------+
| emp_dept_no |
+-------------+
| d008 |
+-------------+
1 row in set (0.01 sec)
# 查询存储在@new_emp_no变量中的emp_no的值:
> select @new_emp_no;
+-------------+
| @new_emp_no |
+-------------+
| 500000 |
+-------------+
# 检查是否在employees表、salaries表和titles表中创建了行：
> select * from employees where emp_no=500000;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1984-06-19 | John | Smith | M | 2020-03-04 |
+--------+------------+------------+-----------+--------+------------+
> select * from salaries where emp_no=500000;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 500000 | 100000 | 2020-03-04 | 9999-01-01 |
+--------+--------+------------+------------+
> select * from titles where emp_no=500000;
+--------+-------+------------+------------+
| emp_no | title | from_date | to_date |
+--------+-------+------------+------------+
| 500000 | Staff | 2020-03-04 | 9999-01-01 |
+--------+-------+------------+------------+
可以通过 show create procedure create_employee \G;命令查看存储过程定义的语句
# 查看存储过程定义的语句
> show create procedure create_employee \G
*************************** 1. row ***************************
Procedure: create_employee
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` 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
DECLARE emp_dept_no char(4);
DECLARE salary int DEFAULT 60000;
SELECT max(emp_no) INTO new_emp_no FROM employees;
SET new_emp_no = new_emp_no + 1;
INSERT INTO employees VALUES(
new_emp_no,
birth_date,
first_name,
last_name,
gender,
CURDATE());
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 VALUES(new_emp_no,emp_dept_no,CURDATE(),'9999-01-01');
INSERT INTO titles VALUES(new_emp_no,title,CURDATE(),'9999-01-01');
IF title = 'Staff'
THEN SET salary = 100000;
ELSEIF title = 'Senior Staff'
THEN SET salary = 120000;
END IF;
INSERT INTO salaries VALUES(new_emp_no,salary,CURDATE(),'9999-01-01');
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
存储过程安全性
用户需要拥有针对存储过程的EXECUTE权限才能执行它。
> GRANT EXECUTE ON employees.* TO 'emp_read_only'@'%';
即使emp_read_only对表没有写访问权限，也可以通过调用存储过程来写入，这就产生了安全问题。
为了避免这种问题，如果存储过程的SQL SECURITY创建为INVOKER，则只读用户不能修改数据。
根据存储例程的定义：
DEFINER ( //dɪˈfaɪnr// 定义者) 子句指定存储例程的创建者。 如果没有指定，则获取当前用户。
SQL SECURITY [sɪˈkjʊrəti] 子句指定存储例程的执行上下文。它可以是DEFINER或INVOKER ( //ɪnˈvoʊkr// 调用程序)。
DEFINER：即使只有EXECUTE[ˈeksɪkjuːt] 权限的用户也可以调用并获取存储例程的输出，而不管该用户是否具有对基础表的操作权限。 如果DEFINER具有权限，那就足够了。
INVOKER（推荐）：安全上下文被切换到调用存储例程的用户。在这种情况下，调用者应具有读写基础表权限才能执行成功。
CREATE [DEFINER = '{用户名}'@'{主机地址}'] PROCEDURE schema.proc_name()
# 此处指定优先级高
SQL SECURITY [DEFINER | INVOKER]
BEGIN
/* 计算逻辑 */
END