MySQL-存储过程（Stored Procedure）使用

参考：
MySQL 存储过程 | 菜鸟教程
https://www.runoob.com/w3cnote/mysql-stored-procedure.html

SQL 存储过程 procedure 讲解+代码实例_sql procedure-CSDN博客
https://blog.csdn.net/baishuiniyaonulia/article/details/105378977

SQL 语法错误1064 (42000)|极客教程
https://geek-docs.com/sql/sql-ask-answer/96_sql_error_1064_42000_you_have_an_error_in_your_sql_syntax.html

本地环境： win10，mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

---

基础

MySQL 5.0开始支持存储过程（Stored Procedure）。它相当于一组完成特定功能的SQL语句，经过创建和编译后保存在数据库中，用户只要指定名称和参数即可执行。

优点

1. 相当于封装代码，能隐藏复杂的商业逻辑
2. 可以接受和返回参数
3. 可重用性好
4. 执行时比直接执行一系列SQL语句效率高，因为它的执行计划已经在编译时生成了，不必再重复优化

缺点

1. 往往定制化于特定的数据库上，如果更换系统，可能需要重写
2. 性能受限于所属数据库

语法

• 声明语句（就是create…）的结束符支持自定义。比如下面将结束符设置为$$，那么写完END之后就要紧跟这个表示结束

  delimiter $$
  

• 注释：--是单行注释，c风格的是多行注释

• 变量赋值

  SET @p_in=1
  

• 调用：call 存储过程名称(参数)

• 创建procedure的完整语法：
  

• routine_body里包含dml（数据操作语句）、ddl（数据定义语句）、if-then-else、while-do、declare等（复杂的流程控制语句以后用到再写，本文不展开）

  • BEGIN和END是可以嵌套的，里面嵌套的BEGIN-END块和里面的每条语句，必须以分号结束，但是最外层的END要以delimiter结束（这个是可以自定义的）
  • begin_label 和 end_label 是一一对应的，使用label可以增强可读性，而且某些语句如 leave 和 iterate 会用到 label 。对应是指：

    label1: BEGIN
    	label2: BEGIN
    		label3: BEGIN
    			statements; 
    			END label3;
    	END label2;
    END label1
    

• proc_parameter 里 IN 表示是输入参数，就是向procedure里传值的，可以是literal也可以是变量；OUT 表示输出参数，是procedure向外传的，可以返回多个，但是传出的只能是变量；INOUT表示传入兼传出，只能是变量 （最好别用）。同时，参数名字不要等于列名，否则参数名会被当成列名处理

  mysql> set @p_in=1;
  mysql> call in_param(@p_in);
  

  

• 变量定义：局部变量定义一定要放在body开始。datatype是sql里合法的

  DECLARE l_int int unsigned default 4000000; 
  DECLARE l_numeric number(8,2) DEFAULT 9.95;  
  DECLARE l_date date DEFAULT '1999-12-31';  
  DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; 
  

• 用户变量：是在MySQL客户端使用的变量

  mysql> select 'hello' into @x;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select @x;
  +-------+
  | @x    |
  +-------+
  | hello |
  +-------+
  1 row in set (0.00 sec)
  

  
  加上@才能区分变量名和列名，这个要注意。

  • 在存储过程中可以直接使用用户变量：

    mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
    mysql > SET @greeting='Hello';  
    mysql > CALL GreetWorld( );  
    

  • 在某个procedure中可以使用另一个procedure中的变量：

    	mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
    	mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
    	mysql> CALL p1( );  
    	mysql> CALL p2( );  
    

• 删除procedure：跟删除表一样，用drop

• 修改：alter

• 查询procedure：

  show procedure status where db='xxx';
  

• 查询procedure详细内容：show create procedure 数据库名.过程名;

实例

创建一个procedure

首先指定数据库，修改end_label为$$，然后写创建语句：这里包含两个字符串类型的参数 n 和 utype。创建结束后恢复 end_label 为;。如果不恢复的话普通sql语句都难以结束。但如果不改这个end_label，可能会在END处报1064错误。

mysql> use vector;
Database changed
mysql> delimiter $$
mysql> create procedure SearchBU(in n varchar(200), in utype varchar(100))
    -> BEGIN
    -> select name, ustart, uend, direction, unit_type, seq
    -> from basic_unit
    -> where
    ->     name like concat('%', n, '%')
    ->     and
    ->     unit_type like concat('%', utype, '%');
    -> END$$
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;

调用

mysql> call SearchBU("AmpR promoter","promoter");
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
| name          | ustart | uend | direction | unit_type | seq                                                                                                       |
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
| AmpR promoter |      1 |  105 | f         | promoter  | CGCGGAACCCCTATTTGTTTATTTTTCTAAATACATTCAAATATGTATCCGCTCATGAGACAATAACCCTGATAAATGCTTCAATAATATTGAAAAAGGAAGAGT |
+---------------+--------+------+-----------+-----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)