MySQL存储过程知识点解析

一.什么是存储过程？

百度百科：存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，一次编译永久有效，可通过调用语句进行复用。

维基百科：存储过程又称存储程序，是在数据库存取复杂程序，以便外部程序调用的数据库队形，可以视为数据库的一种"函数"或"子程序"。

简单地说：就是把SQL语句进行封装，并且可以使用简单的语句进行调用，这样就可以不用重复写一样的SQL，提高工作效率。

二.怎么用存储过程？

三步走：

• 打开编辑器
• 定义存储过程
• 调用存储过程

接下来进行具体的讲解如何定义存储过程：

1.无参数的存储过程

即不带参数的存储过程，基本语法格式：

create procedure <存储过程名称>() begin <SQL语句> ； end；

例：这里用学生信息表st_info6，查询学生姓名

SQL语句：

select distinct name  from st_info6 ; -- 查询结果： +--------+ | name | +--------+ | 王思瑶 | | 王绮梦 | | 李若彤 | +--------+ 3 rows in set (0.00 sec)

放入存储过程中，并命名为get_st_name：

create procedure get_st_name() begin  select distinct name  from st_info6; end;

再使用call来进行调用：

mysql> call get_st_name;  +--------+ | name | +--------+ | 王思瑶 | | 王绮梦 | | 李若彤 | +--------+ 3 rows in set (0.00 sec)

2.带参数的存储过程

即在调用存储过程的时候必须带上特定的参数，基本语法格式：

create procedure <存储过程名称>([参数名称] [参数类型]) begin <SQL语句> ； end；

例：用学生信息表st_info6分别查询出性别为’男‘和性别为’女‘的同学姓名：

SQL语句：

-- 查询男同学的姓名： mysql> select distinct name -> from st_info6 -> where gender="男"; +--------+ | name | +--------+ | 李若彤 | +--------+ 1 row in set (0.01 sec)  -- 查询女同学的姓名 mysql> select distinct name -> from st_info6 -> where gender="女"; +--------+ | name | +--------+ | 王思瑶 | | 王绮梦 | +--------+ 2 rows in set (0.00 sec)

如果把上面的语句放到存储过程中：

create procedure get_st_name(sex varchar(4)) begin  select distinct name  from st_info6  where gender = sex ; end;

再分别调用：

-- 查询男同学的姓名： mysql> call get_st_name("男"); +--------+ | name | +--------+ | 李若彤 | +--------+  -- 查询女同学的姓名： mysql> call get_st_name("女"); +--------+ | name | +--------+ | 王思瑶 | | 王绮梦 | +--------+

注意：

• 存储过程默认是在当前数据库里创建，且名称需要避免跟MySQL内置的函数名重复，无论是否带参数都需要有括号
• 带参数的存储过程中，参数名不能与数据表的字段名相同，否则不会进行条件的筛选，也不会进行报错
• 参数列表中间用逗号","分隔开
• SQL语句后面必须加上";"

三.存储过程的优缺点

1.优点

• 高性能：只需要在创建时进行编译，并且能存储在数据库服务器里，能够直接在服务器中运行，所以可以有效提高数据库的执行速度
• 可复用性：由于SQL语句是封装起来的，所以能够只需要简单地调用就能重复使用
• 降低网络负荷：如果在存储过程中有多次数据的交互，就可以减轻服务器的网络负荷，如果是简单的SQL语句就会相差无几
• 安全性：需要对指定用户进行授权，并且有一定可能防止SQL注入式攻击
• 灵活性：可以使用流程控制语句，有较强的灵活性，能够完成和解决一些复杂的业务逻辑问题

2.缺点

• 难调试：由于IDE问题，存储过程的调试要比一般的程序困难一些，用PL/SQL Developer能够在一定程度上弥补这个缺点
• 移植问题：一般情况下是绑定在某个数据库的，所以会难移植，但是如果是作为工程项目就不存在这个问题
• 权限：为了安全所以不是所有人都有这个权限，因此可能
• 难维护：一般情况下复杂业务逻辑的代码可读性较差，因此造成维护困难

参考资料：

1.https://www.zhihu.com/question/55231277

2.https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html