【MySQL基础】08：存储过程（Procedure）和函数（Function）

 

     

     本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

     所有MySQL文章的目录为：总目录    https://blog.csdn.net/zyplanke/article/details/102968014     

   存储过程（Procedure）和函数（Function）是事先编译好，并存放在数据库服务端的一段SQL代码。使用存储过程和函数可以减少应用逻辑，也减少客户端与服务器端的交互次数，提高对数据库数据的执行效率。

   两者的区别有：

• 存储过程无返回值，函数必须有返回值（或者说通过参数返回）
• 存储过程的参数可以使用IN、OUT、INOUT。而函数只能使用IN

创建存储过程或函数

   创建存储过程（Procedure）或函数（Function）需要用户具有Create routine权限。删除和修改需要Alter routine权限，执行需要Execute权限。创建语法为：

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

LANGUAGE SQL是指示下面body内容使用SQL语言编写

DETERMINISTIC表示相同的输入一定得到相同的输出。默认是NOT DETERMINISTIC

SQL SECURITY 以谁的权限来表示执行该存储过程或函数：DEFINER 以创建者的权限； INVOKER 以调用者的权限。默认是DEFINER。

body代码需要使用BEGIN ... END 包起来。 语句之间的分隔符为分号；

存储过程和函数的代码中，可以调用其他的存储过程和函数。

如果在mysql客户端中执行创建语句。由于body每个语句都有分号分隔符，会被mysql认为语句结束而立即执行该语句。因此需要设置delimiter修改mysql客户端语句的结束符号。例如：

存储过程创建举例：

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql>  CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 r