MySQL存储过程和函数(超详细)

MySQL存储过程和函数

一、什么是存储过程

存储过程就是一些SQL语句的集合，可以简单理解为类似Java中的一个接口函数，函数里面可以使用查询SQL、流程控制语句、定义参数、条件等，用来实现更复杂逻辑的处理。

二、存储过程的作用（优点）

1.执行速度更快（因为不需要从应用程序调用MySQL服务，减少了获取连接、网络传输的耗时）

2.相比普通SQL实现了复杂的逻辑处理。比如可以应用在测试数据的预置，有时我们在性能测试的时候，需要预置大量的测试数据，利用代码当然能实现预置测试数据，但是一方面测试代码有时候是不能上传到测试环境的，修改发布也不方便。这时候用存储过程就可以轻松修改变量，直接在数据库中执行。

三、存储过程的缺点

书写复杂的存储过程，会显得晦涩难懂。
存储过程难以调试，很少工具可以调试存储过程，使得开发和维护都不容易。
不能移植，存储过程只能在数据中执行。

四、创建存储过程

1.具体语法详解

CREATE PROCEDURE sp_name ([proc_parameter])
[characteristice ...] routine_body

CREATE PROCEDURE：创建存储过程的关键字
sp_name：存储过程的名称
proc_parameter：存储过程的参数列表，列表形式如 [IN | OUT | INOUT] param_name type
- IN：输入
- OUT：输出
- INOUT：输入或输出
- param_name：参数名
- type：参数类型，可以是MySQL数据库中的任意类型
characteristics：存储过程的特性，有以下取值
- LANGUAGUE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值
- [NOT]DETERMINISTIC：存储过程执行的结果是否确定。DETERMINISTIC表示是确定的，输入相同的参数，只会得到相同的结果。如果没有指定值，默认为 NOT DETERMINISTIC。
- { CONTAINTS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制
  - CONTAINTS SQL：表明子程序包含SQL语句，但不包含读写数据的语句
  - NO SQL：表明子程序不包含SQL语句
  - READS SQL DATA：表明子程序包含读写数据的语句
  - MODIFIES SQL DATA：表明子程序包含读写数据的语句
  - 默认默认为 CONTAINTS SQL
- SQL SECURITY{ DEFINER | INVOKER }：指明谁有权执行
  - DEFINER：表示只有定义者才能执行
  - INVOKER：表示有权调用者可以执行
  - 系统默认为 DEFINER
- COMMENT 'string'：注释信息

2.例子

创建表

CREATE TABLE fruits(
id bigint primary key auto_increment,
number varchar(255),
name varchar(255),
price double
);

创建存储过程，求平均值

DELIMITER //
CREATE PROCEDURE avgPrice( )
BEGIN
  SELECT AVG(price) AS avgprice FROM fruits;
END;//

DELIMITER // 的意思是将MySQL的结束符改为 //，输入 ; 就不会被识别为结束符而误执行。后面的例子，我都是将结束符定义为//的前提来举例的。

查看创建的存储过程

mysql> show procedure status like 'a%'\G

*************************** 1. row ***************************
         Db: dh_sys
        Name: avgPrice
        Type: PROCEDURE
      Language: SQL
       Definer: root@localhost
      Modified: 2024-06-22 16:16:41
       Created: 2024-06-22 16:16:41
    Security_type: DEFINER
       Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
 Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

创建带参数的存储过程

CREATE PROCEDURE CountProc(OUT param1 INT)

BEGIN 

SELECT COUNT(*) INTO param1 FROM fruits;

END; //

五、创建存储函数

1.具体语法详解

CREATE FUNCTION func_name([func_parameter])

RETURNS type

[characteristic ...] routine_body

CREATE FUNCTION：创建存储函数的关键字
func_name：存储函数的名称
func_parameter：存储函数的参数列表，列表形式如 [IN | OUT | INOUT] param_name type
- IN：输入
- OUT：输出
- INOUT：输入或输出
- param_name：参数名
- type：参数类型，可以是MySQL数据库中的任意类型
RETURNS type：函数返回结果的类型
characteristics：存储过程的特性，和存储函数的一致

2.例子

set global log_bin_trust_function_creators=TRUE;

CREATE FUNCTION getName()

RETURNS varchar(255)

RETURN (select name from fruits where id = 1);//

如果不设置log_bin_trust_function_creators，调用会报错

六、调用存储过程和函数

1.具体语言详解

调用存储函数

CALL sp_name([parameter[,..]])

CALL：调用存储过程关键字
sp_name：存储过程名称
parameter：参数列表

调用函数

SELECT function_name([parameter[,..]])

2.例子

创建一个加法存储过程并调用

CREATE PROCEDURE myProcedure(IN num1 INT, IN num2 INT, OUT num3 INT)
BEGIN
  set num3 = num1 + num2;
END;

mysql> call myProcedure(100,20,@num);
  -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select @num;//
+------+
| @num |
+------+
| 120 |
+------+
1 row in set (0.00 sec)

创建加法函数并调用

CREATE FUNCTION myFunction(num1 INT, num2 INT)
RETURNS INT
BEGIN
  DECLARE num3 INT DEFAULT 0;
  set num3 = num1 + num2;
  RETURN num3;
END;

mysql> select myFunction(100, 30);//
+---------------------+
| myFunction(100, 30) |
+---------------------+
|         130 |
+---------------------+
1 row in set (0.01 sec)

可以看出存储过程和函数的调用区别

存储过程可以直接调用，不依赖select语句，函数则必须使用在语句中
存储过程的参数需要标记IN OUT，函数的参数中不带IN OUT
存储过程没有返回参数，函数需要定义返回参数RETURNS type，并使用RETRUN返回

七、变量的使用

声明变量，在存储过程或函数中使用

1.具体语法详解

1.1定义变量

DECLARE var_name[,var_name]... data_type [DEFAULT value];

DECLARE：定义变量的关键字
var_name：变量名
data_type：变量类型
DEFAULT：默认值，如果没有设置默认是NULL

1.2为变量赋值

SET var_name = expr [,var_name = expr]...;

expr 可以是常量、另一个变量、表达式

2.例子

DECLARE a_param INT DEFAULT 10; //

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE a_param INT DEFAULT 10' at line 1

这样直接在MySQL执行会报错，为什么？变量仅在存储过程或者函数中使用，不能单独定义。

正确的用法是放到存储过程中去

CREATE PROCEDURE procHasDefineParam()
BEGIN
DECLARE a_param INT; 
set a_param =  100;
END;//

八、定义条件和处理程序

在执行的存储过程或函数的过程中，在遇到错误时，通过定义的错误类型的处理程序，执行自定义的逻辑。

1.具体语法详解

1.1定义条件

DECLARE condition_name CONDITION FOR [condition_type]
[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

DECLARE：定义条件关键字
condition_name：条件名
CONDITION FOR：条件为...类型的关键字
condition_type：条件类型
sqlstate_value：长度为5的字符串类型错误码
mysql_error_code：数值类型错误码
sqlstate_value和mysql_error_code都可以表示MySQL的错误。

例如，ERROR 1142（42000）中，sqlstate_value的值是42000，mysql_error_code的值是1142

1.2定义处理程序

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
  CONTINUE | EXIT | UNDO
condition_value:
  SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

DECLARE：定义处理程序关键字
handler_type：错误处理方式，有三个值
- CONTINUE：遇到错误不处理，继续执行
- EXIT：遇到错误马上退出
- UNDO：遇到错误撤回之前的操作，MySQL中暂时不支持这样的操作

condition_value：表示错误类型，可以有以下取值
- SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值
- condition_name：表示DECLARE CONDITION定义的错误条件名称
- SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
- NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
- SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误码
- MySQL_error_code：匹配数值类型错误码
- sp_statement：程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数

2.例子

思路：创建一张fruits表，定义一个主键id，利用插入相同ID的数据，模拟出错误码’23000‘主键冲突的场景。测试handler处理程序跳过错误，继续执行存储过程。

CREATE TABLE fruits(
id bigint primary key auto_increment,
number varchar(255),
name varchar(255),
price double
);

mysql> CREATE PROCEDURE handlertest()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 100; 
    -> SET @x1 = 1;
    -> INSERT INTO fruits(id,number,name,price) VALUES (10, 'banana' ,'香蕉' , 20);
    -> SET @x1 = 2;
    -> INSERT INTO fruits(id,number,name,price) VALUES (10, 'banana' ,'香蕉' , 20);
    -> SET @x1 = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call handlertest();//
Query OK, 0 rows affected (0.00 sec)

mysql> select @x1;//
+------+
| @x1  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> select @x2;//
+------+
| @x2  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

从结果可以看出，在插入第二条SQL后，应该会报错的，由于handler的作用，@x1还是执行了，修改了参数值为3。错误发生时的sp_statement也执行了，将@x2参数值修改为100。

九、光标

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

1.具体语法解析

1.1创建光标

DECLARE cursor_name CURSOR FOR select_statement

1.2打开光标

OPEN cursor_name

1.3使用光标

FETCH cursor_name INTO var_name [, var_name]... [参数名]

1.4关闭光标

CLOSE cursor_name

DECLARE：声明光标的关键字
cursor_name：光标的名称
CURSOR FOR：光标作用在...地方的关键字
select_statement：具体执行的语句
OPEN：打开光标的关键字
FETCH：使用光标的关键字
CLOSE：关闭光标关键字

2.例子

创建一个存储过程cursorDemo，定义好参数，找不到数据的处理程序，开启光标，循环执行光标取数，将光标读取的数据打印，退出条件为HANDLER NOT FOUND将is_exit修改为1（跳出循环的条件）。实现将fruits表的每一行打印输出

CREATE PROCEDURE cursorDemo()
BEGIN
    DECLARE is_exit INT DEFAULT 0;
    DECLARE row_id INT;
    DECLARE row_cur_number VARCHAR(255);
    DECLARE row_cur_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT id, number, name FROM fruits;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_exit = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO row_id, row_cur_number, row_cur_name;
        IF is_exit THEN
            LEAVE read_loop;
        END IF;
        -- 这里可以进行你需要的操作
        -- 比如打印或者插入到其他表
        SELECT row_id, row_cur_number, row_cur_name;
    END LOOP; 
    
    CLOSE cur;
END; //

十、流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL的流程控制语句有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILE。

1.IF语句

条件判断语句，值只能是true,false

IF expr_condition THEN statement_list
  [ELSEIF expr_condition THEN statement_list] ...
  [ELSE statement_list]
END IF

2.CASE语句

条件判断语句，值可以是true,false，或者其他

CASE case_expr
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list]...
  [ELSE statement_list]
END CASE

3.LOOP语句

用来循环重复执行某些语句

[loop_label:] LOOP
  statement_list
END LOOP [loop_label]

4.LEAVE语句

用来退出任何被标注的流程控制构造

LEAVE label

5.ITERATE语句

将执行顺序转到语句开头处。

ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为再次循环某个标签的语句

ITERATE label

6.REAPEAT语句

创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果为真则循环结束，否则执行循环中的语句。

[repeat_label:] REPEAT
  statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

7.WHILE语句

创建一个带条件判断的循环过程，与REPEAT相反，WHILE对条件表达式进行判断，如果为真则执行，否则退出循环。

[while_label:] WHILE expr_condition DO 
  statement_list
END WHILE [while_label]

7.例子

通过while加条件判断退出循环

定义参数param_a值为0，一直自增直到值大于5退出

CREATE PROCEDURE ifdemo()
BEGIN
  DECLARE param_a INT;
  SET param_a = 0;
add_loop: while param_a <> -1 DO
  IF param_a > 5 THEN SET param_a = -1;
  ELSE SET param_a = param_a + 1;
  END IF;
  select param_a;
END WHILE;
END;//

通过条件判断，LEAVE的方式退出循环

CREATE PROCEDURE leavedemo()
BEGIN
  DECLARE id INT DEFAULT 0;
  add_num: LOOP 
    SET id = id + 1;
    select id;
      IF id = 10 THEN LEAVE add_num;
      END IF;
  END LOOP add_num;
END;

CASE根据值执行不同的语句

CREATE PROCEDURE casedemo()
BEGIN
  DECLARE id INT DEFAULT 0;
  SET id = 10;
  CASE id
    WHEN 10 THEN select 'id 等于10';
    WHEN 20 THEN select 'id 等于20';
    ELSE select 'id 等于' + id;
  END CASE;
END;

REPEATE循环重复执行，直到id>=10结束

CREATE PROCEDURE repeatedemo()
BEGIN
  DECLARE id INT DEFAULT 0;
  REPEAT 
    SET id = id + 1;
    UNTIL id >= 10
  END REPEAT;
  select id;
END;

ITERATE循环重复执行，直到id>=10结束

CREATE PROCEDURE iteratedemo()
BEGIN
  DECLARE id INT DEFAULT 0;
  add_loop: LOOP
    SET id = id + 1;
    select id;
    IF id < 10 THEN ITERATE add_loop;
    ELSEIF id >=10 THEN LEAVE add_loop;
    END IF;
  END LOOP add_loop;
END;

十一、查看存储过程和函数

MySQL存储了存储过程和函数的状态信息，可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可以直接从系统的information_schema数据库中查询。

1.具体语法解析

1.1使用SHOW STATUS查看状态信息

SHOW [PROCEDURE|FUNCTION] STATUS LIKE 'pattern'

例如，查看以“i”开头的存储过程

mysql> SHOW PROCEDURE STATUS LIKE 'i%'\G

*************************** 1. row ***************************
                  Db: dh_sys
                Name: ifdemo
                Type: PROCEDURE
            Language: SQL
             Definer: root@localhost
            Modified: 2024-06-23 15:54:07
             Created: 2024-06-23 15:54:07
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
                  Db: dh_sys
                Name: iteratedemo
                Type: PROCEDURE
            Language: SQL
             Definer: root@localhost
            Modified: 2024-06-23 17:13:49
             Created: 2024-06-23 17:13:49
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

备注：

“\G”、“\g”、“;” 都是用来作为SQL语句的结束符

“\g”、“;”作用完全等价

“\G”是将字段横排显示转换成纵列显示。

1.2使用SHOW CREATE查看创建语句

SHOW CREATE [ PROCEDURE | FUNCTION ] sp_name

例如，查看iteratedemo的存储过程

mysql> SHOW CREATE PROCEDURE dh_sys.iteratedemo \G

*************************** 1. row ***************************
           Procedure: iteratedemo
            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 `iteratedemo`()
BEGIN
DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id = id + 1;
select id;
IF id < 10 THEN ITERATE add_loop;
ELSEIF id >=10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

1.3从information_schema.Routines查看存储过程和存储函数

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'sp_name';

information_schema.Routines：MySQL中存储过程和函数信息存储在这张表
ROUTINE_NAME：存储过程和函数名称关键字
sp_name：存储过程/函数名

例如，查看casedemo存储过程

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'casedemo';\G

*************************** 1. row ***************************
           SPECIFIC_NAME: casedemo
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: dh_sys
            ROUTINE_NAME: casedemo
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
DECLARE id INT DEFAULT 0;
SET id = 10;
CASE id
WHEN 10 THEN select 'id 等于10';
WHEN 20 THEN select 'id 等于20';
ELSE select 'id 等于' + id;
END CASE;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2024-06-23 16:54:06
            LAST_ALTERED: 2024-06-23 16:54:06
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

十二、修改存储过程和函数

1.具体语法解析

ALTER [PROCEDURE|FUNCTION] sp_name [characteristic...]

ALTER PROCEDURE：选择创建存储过程的关键字
sp_name：存储过程的名称
proc_parameter：存储过程的参数列表，列表形式如 [IN | OUT | INOUT] param_name type
- IN：输入
- OUT：输出
- INOUT：输入或输出
- param_name：参数名
- type：参数类型，可以是MySQL数据库中的任意类型
characteristics：存储过程的特性，有以下取值
- LANGUAGUE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值
- [NOT]DETERMINISTIC：存储过程执行的结果是否确定。DETERMINISTIC表示是确定的，输入相同的参数，只会得到相同的结果。如果没有指定值，默认为 NOT DETERMINISTIC。
- { CONTAINTS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制
  - CONTAINTS SQL：表明子程序包含SQL语句，但不包含读写数据的语句
  - NO SQL：表明子程序不包含SQL语句
  - READS SQL DATA：表明子程序包含读写数据的语句
  - MODIFIES SQL DATA：表明子程序包含读写数据的语句
  - 默认默认为 CONTAINTS SQL
- SQL SECURITY{ DEFINER | INVOKER }：指明谁有权执行
  - DEFINER：表示只有定义者才能执行
  - INVOKER：表示有权调用者可以执行
  - 系统默认为 DEFINER
- COMMENT 'string'：注释信息

例如，将casedemo的 SECURITY_TYPE，由“DEFINER”改为“INVOKER”，定义者才能执行->调用者可以执行

mysql> ALTER PROCEDURE casedemo MODIFIES SQL DATA SQL SECURITY INVOKER;//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME = 'casedemo';//
+---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS   | SECURITY_TYPE |
+---------------+-------------------+---------------+
| casedemo      | MODIFIES SQL DATA | INVOKER       |
+---------------+-------------------+---------------+
1 row in set (0.00 sec)

十三、删除存储过程和函数

1.具体语法解析

DROP [PROCEDURE|FUNCTION] [IF EXISTS] sp_name

DROP：删除关键字
PROCEDURE|FUNCTION：表示删除存储过程或者函数
IF EXISTS：判断存储过程/函数是否存在，防止删除报错
sp_name：存储过程/函数名

例如，删除testProcedureDrop

mysql> CREATE PROCEDURE testProcedureDrop() BEGIN END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE testProcedureDrop;//
Query OK, 0 rows affected (0.00 sec)

十四、全局变量的持久化

MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。

例如，设置服务器语句超时的限制

SET GLOBAL MAX_EXECUTION_TIME = 2000;

MySQL 8.0版本新增了SET PERSIST命令。

例如，设置服务器的最大连接数为1000

mysql> SHOW VARIABLES LIKE '%max_connection%';//
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> SET PERSIST max_connections = 1000;//
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%max_connection%';//
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

=========================================================================
创作不易，请勿直接盗用，使用请标明转载出处。

喜欢的话，一键三连，您的支持是我一直坚持高质量创作的原动力。