存储过程

如果需要在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    |       |
+-----------+---------+------+-----+---------+-------+

必须传递的信息包括员工的ﬁrst_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

mysql — 存储过程

存储过程

操作过程

存储过程安全性

SpringBoot高频面试题

echarts关于自定义饼图数据刷新和颜色渲染问题

发表评论点击这里取消回复。

猜你喜欢

《C#与.NET6 开发从入门到实践》预售，作者亲自来打广告了！

一天吃透Java并发面试八股文

Docker部署 .Net程序

静态免杀

Python面向对象（上）

day02-Spring基本介绍02

MySQL（十三）MySQL性能分析工具：慢查询日志与PROFILE查询成本

对于程序员来说，怎样才算是在写有“技术含量”的代码？

在 Rainbond 上使用 Curve 云原生存储

在npm发布自己造的轮子

热门浏览

java 答案判题程序

基于阿里云、七牛云、宝塔面板，从零开始用Halo搭建个人博客网站

博客添加评论功能及定制化样式

MySQL 8.026 下载及安装教程-Windows 10系统环境

【注意】当同一个Class的多个对象出现在同一段代码内的时候，极易出错。

热评文章

SpringSecurity认证和授权流程详解

day09-函数高级

加入云原生实战营（星球），带你进阶 Go + 云原生高级开发工程师

从零开始学Spring Boot系列-集成MyBatis-Plus

arch配置中文和输入法

热门标签

mysql — 存储过程

存储过程

操作过程

存储过程安全性

SpringBoot高频面试题

echarts关于自定义饼图数据刷新和颜色渲染问题

猜你喜欢

深入理解MySQL：数据类型、查询优化、索引、事务处理和数据备份与恢复

MySQL的索引详解

druid开启sql监控平台

MySQL – 索引下推

MySQL – 并发事务出现的问题

MySQL – 分页问题

发表评论 点击这里取消回复。

猜你喜欢

《C#与.NET6 开发从入门到实践》预售，作者亲自来打广告了！

热门浏览

java 答案判题程序

基于阿里云、七牛云、宝塔面板，从零开始用Halo搭建个人博客网站

博客添加评论功能及定制化样式

MySQL 8.026 下载及安装教程-Windows 10系统环境

【注意】当同一个Class的多个对象出现在同一段代码内的时候，极易出错。

热评文章

SpringSecurity认证和授权流程详解

day09-函数高级

加入云原生实战营（星球），带你进阶 Go + 云原生高级开发工程师

从零开始学Spring Boot系列-集成MyBatis-Plus

arch配置中文和输入法

热门标签

关注我们的公众号

发表评论点击这里取消回复。