MySQL存储过程和函数

使用语法

create procedure 用来创建存储过程，

create function用来创建函数

create procedure/function语句区别：

• 函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可
• definer和sql security子句指定安全环境
• definder是MySQL的特殊的访问控制手段，当数据库当前没有这个用户授权时，执行存储过程可能会报错
• sql security的值决定了调用存储过程的方式，取值：definer(默认)或者invoker
• definer：在执行存储过程前验证definer对应的用户如：cdq@127.0.0.1是否存在，以及是否具有执行存储过程的权限，若没有则报错
• invoker：在执行存储过程时判断invoker即调用存储过程的用户是否有相应权限，若没有则报错
• in，out,input三个参数前的关键词只适用于存储过程，对函数而言所有的参数都是输入参数
• in输入参数用于把数值传入到存储过程中；out输出参数将数值传递调用者，初始值是null，inout输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者。

创建一个存储过程：

但是只能返回一个值，如果返回值多的话就不行了，

    mysql> delimiter //
    mysql> create procedure simpleproc (in param1 int,out param2 int)
        -> begin
        -> select count(*) into param2 from students where sid>param1;
        -> end;
        -> //
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> call simpleproc(1,@2);//
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @2;
        -> //
    +------+
    | @2   |
    +------+
    |   11 |
    +------+
    1 row in set (0.00 sec)
    mysql> 

创建一个函数：

  mysql> delimiter //
   mysql> create function hello (s char(20))
       -> returns char(50)
       -> return concat('Hello',s,'~');
       -> //
   ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
   mysql> 

mysql> show variables like '%log_bin_trust%';
      -> //
  +---------------------------------+-------+
  | Variable_name                   | Value |
  +---------------------------------+-------+
  | log_bin_trust_function_creators | OFF   |
  +---------------------------------+-------+
  1 row in set (0.50 sec)
  
  mysql> 
  全局设置参数mysql> set global log_bin_trust_function_creators=on;
      -> //
  Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
 mysql> create function hello (s char(20))
     -> returns char(50)
     -> return concat('Hello',s,'~');
     -> //
 Query OK, 0 rows affected (0.03 sec)
 
 mysql>mysql> select hello('narisu');
     -> //
 +-----------------+
 | hello('narisu') |
 +-----------------+
 | Hellonarisu~    |
 +-----------------+
 1 row in set (0.02 sec)
 mysql> 

函数的简单使用

mysql> select hello(sname) from students;//
  +-----------------+
  | hello(sname)    |
  +-----------------+
  | Hellonarisu~    |
  | Hellogutianle~  |
  | Hellobbb~       |
  | Helloccc~       |
  | Hellonight~     |
  | Helloaa_aa~     |
  | Helloavvvvaccc~ |
  | Helloab~        |
  | Helloabcad~     |
  | Helloa~         |
  | Helload~        |
  | Helloa%~        |
  +-----------------+
  12 rows in set (0.02 sec)

函数结合SQL使用

    mysql> select ROUTINE_CATALOG,ROUTINE_NAME,ROUTINE_TYPE,CREATED from ROUTINES  order by created desc limit 3;
    +-----------------+--------------+--------------+---------------------+
    | ROUTINE_CATALOG | ROUTINE_NAME | ROUTINE_TYPE | CREATED             |
    +-----------------+--------------+--------------+---------------------+
    | def             | hello        | FUNCTION     | 2021-12-19 18:03:17 |
    | def             | simpleproc   | PROCEDURE    | 2021-12-19 17:52:02 |
    | def             | simpleproc1  | PROCEDURE    | 2021-12-19 17:48:39 |
    +-----------------+--------------+--------------+---------------------+
    3 rows in set (0.00 sec)