本文介绍MySQL-5.7 存储过程及函数详解 

  1. CREATE
  2.     [DEFINER = { user | CURRENT_USER }]
  3.     PROCEDURE sp_name ([proc_parameter[,...]])
  4.     [characteristic ...] routine_body
  6. CREATE
  7.     [DEFINER = { user | CURRENT_USER }]
  8.     FUNCTION sp_name ([func_parameter[,...]])
  9.     RETURNS type
  10.     [characteristic ...] routine_body
  12. proc_parameter:
  13.     [ IN | OUT | INOUT ] param_name type
  15. func_parameter:
  16.     param_name type
  18. type:
  19.     Any valid MySQL data type
  21. characteristic:
  22.     COMMENT \'string\'
  23.   | LANGUAGE SQL
  24.   | [NOT] DETERMINISTIC
  25.   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  26.   | SQL SECURITY { DEFINER | INVOKER }
  28. routine_body:
  29.     Valid SQL routine statement

参数详解：
（1）DEFINER
表示创建存储过程及函数的用户，默认为当前用户；
（2）func_parameter
表示存储过程的参数。在Create Procedure 语句中，可以声明一个或多个参数。当调用该存储过程时，用户必须给出所有的参数值，除非定义了参数的缺省值。若参数的形式以 @parameter=value 出现，则参数的次序可以不同，否则用户给出的参数值必须与参数列表中参数的顺序保持一致。若某一参数以@parameter=value 形式给出，那么其它参数也必须以该形式给出。一个存储过程至多有1024 个参数。
（3）characteristic

  • LANGUAGE SQL:表示此存储过程和函数的创建语言；
  • [NOT] DETERMINISTIC:表明输入相同的参数会返回相同的结果，反之表示相同的参数不会是相同的结果，默认是not deterministic；
  • — CONTAINS SQL :子程序不包含读或写数据的语句；
  • — NO SQL : 子程序不包含SQL语句；
  • — READS SQL DATA :子程序包含读数据的语句；
  • — MODIFIES SQL DATA : 子程序包含写数据的语句；
  • — 如果这些特征没有明确给定，默认的是CONTAINS SQL；

（4）SQL SECURITY { DEFINER | INVOKER }
子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。
（5）COMMENT
是一个MySQL的扩展，它可以被用来描述存储程序。这个信息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION语句来显示。

（1）存储过程与函数的区别

  • 函数调用有返回值
  • 存储过程调用用call语句，函数调用直接饮用函数名+参数

（2）Definer和sql security

  • Definder是MySQL的特殊访问控制手段，当数据库当前没有这个用户权限时，执行存储过程可能会报错；
  • sql security的值决定了调用存储过程的方式，取值：definer（默认）或invoker；
  • definer在执行时先验证definer对应的用户，如：cdq@127.0.0.1是否存在，以及是否具有执行存储过程的权限，若没有则报错；
  • invoker在执行存储过程时判断invoker，即调用该存储过程的用户是否有相应权限，若没有则报错

（3）IN，OUT，INOUT
只适用于存储过程，对函数而言所有参数默认都是输入参数

  • IN用于把数值传入到存储过程中
  • OUT用于输出参数将数值传递给调用者
  • INOUT输入输出参数把数据传入到存储过程，在存储过程中修改后再传递给调用者

（1）简单存储过程

  1. mysql> delimiter //
  2. mysql> create procedure simpleproc(in param1 int,out param2 int)
  3.     -> begin
  4.     -> select count(*) into param2 from students where sid > param1;
  5.     -> end//
  6. Query OK, 0 rows affected (0.02 sec)
  8. mysql> delimiter ;
  9. mysql> call simpleproc(1,@a);
  10. Query OK, 1 row affected (0.00 sec)
  12. mysql> select @a;
  13. +------+
  14. | @a   |
  15. +------+
  16. |    7 |
  17. +------+
  18. 1 row in set (0.00 sec)

说明：

  • delimiter命令是改变语句的结束符，MySQL默认结束符为；号，由于存储过程和函数中的；号并不代表结束，所以要替换另外的结束符；

（2）简单的函数

  1. mysql> create function hello(s char(20))
  2.     -> returns char(50)
  3.     -> return concat(\'Hello\',s,\'!\');
  4. Query OK, 0 rows affected (0.00 sec)
  6. mysql> select hello(\'world\');
  7. +----------------+
  8. | hello(\'world\') |
  9. +----------------+
  10. | Helloworld!    |
  11. +----------------+
  12. 1 row in set (0.00 sec)

（3）复杂的示例

  1. mysql> delimiter //
  2. mysql> create function simplefunc(param1 int)
  3.     -> returns int
  4.     -> begin
  5.     -> update students set gender=1 where sid=param1;
  6.     -> select count(*) into @a from students where sid > param1;
  7.     -> return @a;
  8.     -> end//
  9. Query OK, 0 rows affected (0.00 sec)
  11. mysql> delimiter ;
  12. mysql> select * from students where sid=1;
  13. +-----+--------+--------+---------+
  14. | sid | sname  | gender | dept_id |
  15. +-----+--------+--------+---------+
  16. |   1 | Andrew | 0      |       1 |
  17. +-----+--------+--------+---------+
  18. 1 row in set (0.00 sec)
  20. mysql> select simplefunc(1);
  21. +---------------+
  22. | simplefunc(1) |
  23. +---------------+
  24. |             7 |
  25. +---------------+
  26. 1 row in set (0.03 sec)
  28. mysql> select * from students where sid=1;
  29. +-----+--------+--------+---------+
  30. | sid | sname  | gender | dept_id |
  31. +-----+--------+--------+---------+
  32. |   1 | Andrew | 1      |       1 |
  33. +-----+--------+--------+---------+
  34. 1 row in set (0.01 sec)

说明：

  • MySQL存储过程和函数中也可以包含类似create和drop等DDL语句；
  • rontine_body子句可以包含一个简单的SQL语句，也可以包含多个SQL语句，通过begin…end将多个SQL语句包含在一起；
  1. [root@localhost ~]# mysql -uabc2 -p
  2. Enter password: 
  4. mysql> show databases;
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | course             |
  10. +--------------------+
  11. 2 rows in set (0.01 sec)
  13. mysql> use course;
  14. Reading table information for completion of table and column names
  15. You can turn off this feature to get a quicker startup with -A
  17. Database changed
  18. mysql> delimiter //
  19. mysql> create procedure simpleproc2(IN param1 int,out param2 int)
  20.     -> begin
  21.     -> select count(*) into param2 from students where sid > param1;
  22.     -> end//
  23. Query OK, 0 rows affected (0.00 sec)
  25. [root@localhost ~]# mysql -p
  26. Enter password: 
  28. mysql> use course;
  29. Reading table information for completion of table and column names
  30. You can turn off this feature to get a quicker startup with -A
  32. Database changed
  33. mysql> call simpleproc2(1,@a);
  34. Query OK, 1 row affected (0.00 sec)
  36. mysql> drop user \'abc2\'@\'localhost\';
  37. Query OK, 0 rows affected (0.00 sec)
  39. mysql> call simpleproc2(1,@a);
  40. ERROR 1449 (HY000): The user specified as a definer (\'abc2\'@\'localhost\') does not exist
  42. 查看存储过程及函数的相关数据库信息（在information_schema库）
  43. mysql> select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION,DEFINER from ROUTINES where ROUTINE_SCHEMA = \'course\';
  44. +----------------+--------------+--------------+--------------+-------------------------------------------------------------------------------------------------------------------------------+----------------+
  45. | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | ROUTINE_BODY | ROUTINE_DEFINITION                                                                                                            | DEFINER        |
  46. +----------------+--------------+--------------+--------------+-------------------------------------------------------------------------------------------------------------------------------+----------------+
  47. | course         | hello        | FUNCTION     | SQL          | return concat(\'Hello\',s,\'!\')                                                                                                  | root@localhost |
  48. | course         | simplefunc   | FUNCTION     | SQL          | begin
  49. update students set gender=1 where sid=param1;
  50. select count(*) into @a from students where sid > param1;
  51. return @a;
  52. end | root@localhost |
  53. | course         | simpleproc   | PROCEDURE    | SQL          | begin
  54. select count(*) into param2 from students where sid > param1;
  55. end                                                       | root@localhost |
  56. | course         | simpleproc2  | PROCEDURE    | SQL          | begin
  57. select count(*) into param2 from students where sid > param1;
  58. end                                                       | abc2@localhost |
  59. +----------------+--------------+--------------+--------------+-------------------------------------------------------------------------------------------------------------------------------+----------------+
  61. mysql> alter procedure simpleproc2 sql security invoker;
  62. Query OK, 0 rows affected (0.00 sec)
  64. mysql> call simpleproc2(1,@a);
  65. Query OK, 1 row affected (0.00 sec)

说明：
相关属性短语只有咨询含义，并不是强制性约束

  • contains sql表明此存储过程或函数不包含读或者写数据的语句，这是默认属性；
  • no sql表明此存储过程或函数不包含SQL语句
  • reads sql data表示此存储过程包含诸如select的查询数据的语句，但不包含插入或删除数据的语句
  • modifies sql data表示存储过程包含插入或删除数据的语句
  1. DROP {procedure | function} {IF EXISTS} sp_name;

if exists用来避免在删除一个本身不存在的存储过程或函数时，MySQL返回错误；

  1. mysql> drop procedure oldboy;
  2. ERROR 1305 (42000): PROCEDURE course.oldboy does not exist
  3. mysql> drop procedure if exists oldboy;
  4. Query OK, 0 rows affected, 1 warning (0.00 sec)
  1. mysql> show create procedure simpleproc;
  2. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  3. | Procedure  | sql_mode                                                                                                                                  | Create Procedure                                                                                                                                               | character_set_client | collation_connection | Database Collation |
  4. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  5. | simpleproc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in param1 int,out param2 int)
  6. begin
  7. select count(*) into param2 from students where sid > param1;
  8. end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
  9. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  1. mysql> show create function simplefunc;
  2. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  3. | Function   | sql_mode                                                                                                                                  | Create Function                                                                                                                                                                                                   | character_set_client | collation_connection | Database Collation |
  4. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  5. | simplefunc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `simplefunc`(param1 int) RETURNS int(11)
  6. begin
  7. update students set gender=1 where sid=param1;
  8. select count(*) into @a from students where sid > param1;
  9. return @a;
  10. end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
  11. +------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  12. 1 row in set (0.01 sec)

版权声明：本文为tongxiaoda原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/tongxiaoda/p/8022418.html