MySQL 存储过程和函数

最后更新时间：2022年12月11日

初始化数据库表

mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(250),
    -> email varchar(100),
    -> age tinyint
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user (name, email, age) values
    -> ('ronie', 'ronie@163.com', 20),
    -> ('jack', 'jack@163.com', 34),
    -> ('jerry', 'jerry@163.com', 22),
    -> ('lucy', 'lucy@163.com', 21);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+-------+---------------+------+
| id | name  | email         | age  |
+----+-------+---------------+------+
|  1 | ronie | ronie@163.com |   20 |
|  2 | jack  | jack@163.com  |   34 |
|  3 | jerry | jerry@163.com |   22 |
|  4 | lucy  | lucy@163.com  |   21 |
+----+-------+---------------+------+
4 rows in set (0.00 sec)

存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

创建存储过程和函数

  1. 创建存储过程
    语法：CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics ...] routine_body。

    • proc_parameter为指定存储过程的参数列表，[ IN | OUT | INOUT ] param_name type，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

    • characteristics指定存储过程的特性，取值：

      • LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。
      • [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
      • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
      • SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
      • COMMENT 'string'：注释信息，可以用来描述存储过程或函数。
    -- DELIMITER //语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号;。
-- 为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以END //结束存储过程。
-- 存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

mysql> DELIMITER //
mysql> create procedure avg_age()
 -> begin
 -> select avg(age) from user;
 -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> call avg_age();
+----------+
| avg(age) |
+----------+
|  24.2500 |
+----------+
 1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> create procedure num_of_user(out arg int)
 -> begin
 -> select count(*) into arg from user;
 -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call num_of_user(@num);
Query OK, 1 row affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
|    4 |
+------+
 1 row in set (0.00 sec)

  2. 创建存储函数
    语法：CREATE FUNCTION func_name ([proc_parameter]) return type [characteristics ...] routine_body。CREATE FUNCTION为用来创建存储函数的关键字；func_name表示存储函数的名称；func_parameter为存储过程的参数列表，RETURNS type语句表示函数返回数据的类型；characteristic指定存储函数的特性，取值与创建存储过程时相同。如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。

    mysql> DELIMITER //
mysql> create function max_age()
 -> returns tinyint
 -> reads sql data
 -> return (select max(age) from user);
 -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select max_age();
+-----------+
| max_age() |
+-----------+
|        34 |
+-----------+
 1 row in set (0.00 sec)

变量

  1. 在存储过程中使用DECLARE语句定义变量，DECLARE var_name[,varname]… date_type [DEFAULT value];。var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

    mysql> DELIMITER //
mysql> create procedure example(out arg int)
-> begin
-> declare var int default 10;
-> select var into arg;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> call example(@num);
-> //
Query OK, 1 row affected (0.00 sec)

mysql> select @num//
+------+
| @num |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql>

  2. 为变量赋值，为变量赋值可以改变变量的默认值。在MySQL中，使用SET语句为变量赋值，SET var_name = expr [, var_name = expr] ...;。

    mysql> DELIMITER //
mysql> create procedure example(out arg int)
-> begin
-> declare var int default 10;
-> set var=30;
-> select var into arg;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> call example(@num)//
Query OK, 1 row affected (0.00 sec)

mysql> select @num//
+------+
| @num |
+------+
|   30 |
+------+
1 row in set (0.00 sec)

mysql> select * from user;
-> //
+----+-------+---------------+------+
| id | name  | email         | age  |
+----+-------+---------------+------+
|  1 | ronie | ronie@163.com |   20 |
|  2 | jack  | jack@163.com  |   34 |
|  3 | jerry | jerry@163.com |   22 |
|  4 | lucy  | lucy@163.com  |   21 |
+----+-------+---------------+------+
4 rows in set (0.00 sec)

mysql> drop procedure example//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure example(out n char(50), out m int)
-> begin
-> select name, age into n, m from user where id=1;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call example(@name, @age);
-> //
Query OK, 1 row affected (0.00 sec)

mysql> select @name, @age//
+-------+------+
| @name | @age |
+-------+------+
| ronie |   20 |
+-------+------+
1 row in set (0.00 sec)

定义条件和处理程序

  1. 定义条件
    定义条件使用DECLARE语句，DECLARE condition name CONDITION FOR [condition type][condition_ type] :SQLSTATE [VALUE] sqlstate value| mysql error code。condition_name参数表示条件的名称；condition_type参数表示条件的类型；sqlstate_value和MySQL_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_error_code为数值类型错误代码。

  2. 处理程序
    使用DECLARE语句定义处理程序，语法：

    DECLARE handler_ type HANDLER FOR condition value[， . ..] sp_ statement
handler_ type:
 CONTINUEI EXIT| UNDO
condition value:
 SQLSTATE [VALUE] sqlstate_ value | condition name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_ error code

    handler_type为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；
    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参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。

    mysql> DELIMITER //
mysql> create procedure example(in tb_name varchar(250), out num int)
 -> begin
 -> declare drop_tb_error condition for 1051;
 -> declare exit handler for drop_tb_error select 1051 into num;
 -> drop table @tb_name;
 -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> set @tb_name='not_exists'//
Query OK, 0 rows affected (0.00 sec)

mysql> call example(@tb_name, @num)//
Query OK, 1 row affected (0.00 sec)

mysql> select @num//
+------+
| @num |
+------+
| 1051 |
+------+
 1 row in set (0.00 sec)

声明：Hello World|版权所有，违者必究|如未注明，均为原创|本网站采用BY-NC-SA协议进行授权

转载：转载请注明原文链接 - MySQL 存储过程和函数

我的朋友，理论是灰色的，而生活之树是常青的！