mysql---存储过程和函数

mysql从5.0版本开始支持存储过程和函数。

一、什么是存储过程和函数

存储过程和还是你的话简单的说是已经实现编译并存储在数据库中的一段SQL语句的集合，使得一些常用的命令不用每次去手动去写，可以直接从库中调用，可以简化开发，提高效率，减少数据在数据库和应用服务器之间的连接传输次数。

有点类似于java中封装好的方法（将一组完成特定功能的语句封装起来），只对外显示功能名称。

储存过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用in、out、inout类型，而函数的参数只能是in类型的。

操作存储过程和函数都需要相应的权限。如create routine权限，alter routine权限，execute权限。

二、存储过程的相关操作

2.1创建存储过程

创建存储过程的语句：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body

通用语句：CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型)
BEGIN
        存储过程体
END

注意：

1.参数模式param_name type ：IN（输入）、OUT（输出）、INOUT（可以输入和输出），其中IN可以省略
2.存储过程体的每一条sql语句都需要用分号结尾

proc_parameter（包括参数类型和参数名）为指定存储过程的参数列表。

1. 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表名子程序包含写（修改）数据的语句。系统默认包含SQL语句。
   • SQL SECURITY[DEFINER|INVOKER]：指明安全级别权限。DEFINER表示只能由定义者来操作。INVOKER表示有调用权限的用户才能执行。系统默认定义者执行。
   • COMMENT 'string'：注释信息，用来描述存储过程或函数。

routine_body是存储过程体，在begin...end中间，声明开始与结束sql语句。

2.2、调用存储过程

调用存储过程的语句：CALL 存储过程名(实参列表)
举例：
调用in模式的参数：CALL sp1（‘值’）;
调用out模式的参数：SET @name; CALL sp1(@name);SELECT @name;
调用inout模式的参数：SET @name=值; CALL sp1(@name); SELECT @name;

2.3查看存储过程

查看存储过程的语句：SHOW CREATE PROCEDURE 存储过程名

查看存储过程的状态：SHOW PROCEDURE STATUS LIKE  'pattern'

2.4删除存储过程

删除存储过程的语法：DROP PROCEDURE [IF EXISTS] 存储过程名

三、函数相关操作

3.1、创建函数

创建函数的语法：CREATE FUNCTION 函数名(参数名 参数类型) RETURNS 返回类型
BEGIN
    函数体
END

函数必须要有返回类型

3.2、调用函数

调用函数的语法：SELECT 函数名（实参列表）

3.3查看函数

查看函数定义：SHOW CREATE FUNCTION 函数名

查看函数状态：SHOW FUNCTION STATUS LIKE 'pattern'

3.4删除函数

删除函数的语法：DROP FUNCTION [IF EXISTS] 函数名