详解MariaDB数据库的存储过程

1.什么是存储过程

很多时候，SQL语句都是针对一个或多个表的单条语句。但是也有时候有的查询语句需要进行多次联表查询才能完成，此时就需要用到存储过程了。

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中经过第一次编译后，再次调用不需要再次编译。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

2.为什么要使用存储过程

1.存储过程增强了SQL语言的功能和灵活性。存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
2.存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
3.存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
4.存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
5.存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3.定义存储过程

语法：

create procedure 过程名（参数1，参数2....）
begin 
    sql语句;
end

创建存储过程之前我们必须修改mysql语句默认结束符;

3.1 使用delimiter可以修改执行符号

DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错。

所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

例子：

# delimiter 新执行符号
MariaDB [book]> delimiter %     # 使用delimter后，每条SQL语句的结束符就变为%
MariaDB [book]> create procedure selCg()
    -> begin
    -> select * from category;
    -> end %
Query OK, 0 rows affected (0.08 sec)

4.调用存储过程

语法：

call 过程名（参数1，参数2）;

例子：

MariaDB [book]> call selCg() %          # 调用上面定义的存储过程 selCg
+---------+---------------+
| bTypeId | bTypeName     |
+---------+---------------+
|       1 | windows应用   |
|       2 | 网站          |
|       4 | linux学习     |
|       5 | Delphi学习    |
|       6 | 黑客          |
|       7 | 网络技术      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技术   |
+---------+---------------+
9 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [book]> delimiter ;             # 使用delimter把SQL语句的结束符改回原来的';'

5.存储过程参数类型

5.1 In参数

特点：读取外部变量值，且有效范围仅限存储过程内部

例子一：

MariaDB [book]> delimiter //    # 使用delimter把SQL语句的结束符改为的'//'
MariaDB [book]> create procedure pin(in p_in int)       # 定义存储过程，读取外部的整型变量p_in
    -> begin
    -> select p_in;             # 读取参数中传递的p_in的值，p_in来自存储过程外部
    -> set p_in = 2;            # 更改参数p_in的值为2
    -> select p_in;             # 再次查询参数p_in的值
    -> end//
Query OK, 0 rows affected (0.01 sec)

MariaDB [book]> delimiter ;             # 使用delimter把SQL语句的结束符改回原来的';'
MariaDB [book]> set @num = 1;           # 在console中设定一个变量num，值为1
Query OK, 0 rows affected (0.01 sec)

MariaDB [book]> call pin(@num);         # 把前面定义的变量num做为参数，调用存储过程pin
+------+                                # 第一次查询到的值为1，经过存储过程为参数重新赋值，第二次查询到的值为2
| p_in |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

+------+
| p_in |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

MariaDB [book]> call pin(6);            # 再次调用存储过程pin,传入参数为整数6
+------+                                # 同样第一次查到的值为6，第二次查到的值改为2
| p_in |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

+------+
| p_in |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [book]> select @num;            # 在console中查询num变量的值，仍为1，存储过程的作用范围只在存储过程内部有效
+------+
| @num |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

例子二：定义存储过程 getOneBook，当输入某书籍 id 后，可以调出对应书籍记录

MariaDB [book]> delimiter //                        # 修改SQL语句结束符为'//'
MariaDB [book]> create procedure get_book_by_id(in b int)   # 定义存储过程get_book_by_id,传入book的id,查询对应book的信息记录
    -> begin
    -> select * from books where bId=b;
    -> end//
Query OK, 0 rows affected (0.00 sec)

MariaDB [book]> delimiter ;             # 修改SQL语句结束符为';'

MariaDB [book]> call get_book_by_id(4);         # 查询book的id为4的记录
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
| bId | bName                           | bTypeId | publishing            | price | pubDate    | author    | ISBN       |
+-----+---------------------------------+---------+-----------------------+-------+------------+-----------+------------+
|   4 | pagemaker 7.0短期培训教程       | 9       | 中国电力出版社        |    43 | 2005-01-01 | 孙利英    | 7121008947 |
+-----+---------------------------------+---------