SQL执行过程:
存储过程:是SQL语句和控制语句的预编译集合,以一个名称存储并且作为一个单元处理。存储过程存储在数据库内,可以由应用程序调用执行,允许用户声明变量以及流程控制,存储过程可以接受参数,并且可以存在多个返回值;存储过程的执行效率要比单一的SQL执行的效率高,由CALL调用;例如在执行SQL语句的过程中,MySQL引擎会先分析,正确之后再编译语句,最后才执行。而存储过程在第一执行的时候才会逐一分析和编译,但是会存储整个过程,等下一次客户端再执行这样命令的时候,会直接调用执行结果,省去分析和编译的过程。
存储过程优点:增强SQL语句的功能和灵活性;实现较快的执行速度;减少了网络流量;
创建存储过程:
create
[definer = {user | current_user}]//创建者
procedure sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[IN | OUT | INOUT] param_name type
IN:表示该参数的值必须在调用存储过程时指定;不能反回;
OUT:表示该参数的值可以被存储过程改变,并且可以反回;
INOUT:表示该参数在调用时指定,并且可以被存储过程的过程体改变,可以反回给调用者;
过程体:过程体如果为复合结构则使用begin...end语句,复合语句可以包含声明,循环,IF语句等;
创建存储过程:
mysql> create procedure spl() select version();
-> //
Query OK, 0 rows affected (0.09 sec)
mysql> call spl;
-> //
+-----------+
| version() |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call spl;
+-----------+
| version() |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call spl();
+-----------+
| version() |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
创建带有IN类型参数的存储过程:
mysql> select * from goods;
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
| 15 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | 3 | 3999.000 | 1 | 0 |
| 17 | pad-mini | 3 | 3 | 1999.000 | 0 | 1 |
+----+-----------+---------+----------+-----------+---------+------------+
17 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure remove(in p_id int unsigned)
-> begin
-> delete from goods where id = p_id;
-> end;
-> //
Query OK, 0 rows affected (0.10 sec)
mysql> call remove(17);//
Query OK, 1 row affected (0.02 sec)
mysql> select * from goods;
-> //
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
| 15 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | 3 | 3999.000 | 1 | 0 |
+----+-----------+---------+----------+-----------+---------+------------+
16 rows in set (0.00 sec)
mysql>
创建带有IN和OUT类型参数的存储过程:
SET @num;为创建用户变量,这个用户本地可以访问;into param:给变量赋值;
mysql> create procedure removeReturns(in p_id int unsigned,out userNums int unsigned)
-> begin
-> delete from goods where id = p_id;
-> select count(id) from goods into userNums;
-> end//
Query OK, 0 rows affected (0.05 sec)
mysql> select count(id) from goods;
-> //
+-----------+
| count(id) |
+-----------+
| 15 |
+-----------+
1 row in set (0.00 sec)
mysql> call removeReturns(15,@num);//
Query OK, 1 row affected (0.07 sec)
mysql> select count(id) from goods;//
+-----------+
| count(id) |
+-----------+
| 14 |
+-----------+
1 row in set (0.00 sec)
mysql> select @num;//
+------+
| @num |
+------+
| 14 |
+------+
1 row in set (0.00 sec)
mysql>
创建多个OUT类型参数的存储过程;
mysql> select * from goods;
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
+----+-----------+---------+----------+-----------+---------+------------+
14 rows in set (0.00 sec)
mysql> create procedure rev(in p_age smallint unsigned,out delUser smallint unsigned,out userCount smallint unsigned)
-> begin
-> delete from goods where cate_id = p_age;
-> select row_count() into delUser;
-> select count(id) from goods into userCount;
-> end//
Query OK, 0 rows affected (0.02 sec)
mysql> call rev(2,@userChange,@userCount);//
Query OK, 1 row affected (0.02 sec)
mysql> select * from goods;
-> //
+----+----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
+----+----------+---------+----------+-----------+---------+------------+
11 rows in set (0.00 sec)
mysql>
存储过程与自定义函数:存储过程实现的功能复杂一些,可以反回多个值,一般独立执行;而函数只能反回一个值,针对性更强,一般作为其他SQL语句的组成部分来实现;