mysql自定义函数

一、概述

1 MySQL存储过程和函数

过程和函数，它们被编译后保存在数据库中，称为持久性存储模块（Persistent Stored Module，PSM），可以反复调用，运行速度快。

1.1 存储过程

存储过程是由过程化 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

1.2 函数

这里指自定义函数，因为是用户自己使用过程化 SQL 设计定义的。函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回类型。

二、 语法结构

CREATE
[DEFINER = { user | CURRENT_USER }]
[aggregate]
FUNCTION sp_name ([func_parameter[,…]])
RETURNS type
[characteristic …] routine_body
创建一个函数，sp_name表示函数名。函数的定义与存储过程定义非常相似，只是以下两个参数略有不同：
aggregate 指定创建的函数是普通的自定义函数，还是AGGREGATE函数。
func_parameter - 函数参数列表，其形式如下：
param_name type
param_name - 参数名
type - 参数类型，可以是任何合法的MySQL数据类型
无in、out、inout类型，函数的参数都是入参
RETURNS type - 表示函数的返回值类型

自定义函数两个必要条件：

参数：可以有零个或多个

返回值：只能有一个

创建自定义函数：

CERATE FUNCTION function_name(［指定参数类型］, … )

RETURNS {STRING|INTEGER|REAL|DECIMAL}

[characteristic …] routine_body

注释：RETURNS后接返回值的类型

routine_body指的是函数体：

（1）函数体有合法的SQL语句构成

（2）函数体可以是简单的SELECT或INSERT语句

（3）函数体如果为复合结构则使用BEGIN…END语句

（4）复合结构可以包含声明，循环，控制结构，…

characteristic是指自定义函数的特性，和存储过程相同，形式为：COMMENT ‘string’ | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY {DEFINER | INVOKER}

COMMETN:注释

CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句

NO SQL:不包含SQL语句

READS SQL DARA:包含读数据的语句

MODIFIES SQL DATA:包含

调用函数：SELECT function_name(［参数］)

函数和某个数据库关联的，即存储在具体的某个数据库中，所有上面的创建语句使用的前提：
use databasename；//切换到指定数据库

或者，在创建时使用数据库名. 前缀的方式
2.2 自定义函数的使用

定义一个返回字符串的自定义函数：

DELIMITER //

CREATE FUNCTION getName(id INT)
RETURNS CHAR(50)
RETURN (SELECT name FROM t WHERE id=id);
//
DELIMITER ;
函数的调用

定义函数后，可以通过SELECT语句来调用函数：

SELECT getName(1);
±---------------+
| getName(1) |
±---------------+
| itbilu.com |
±---------------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> create function fun_add_rand(
-> in_int int
-> )
-> RETURNS int
-> BEGIN
-> declare i_rand int;
-> declare i_return int;
->
-> set i_rand=floor(rand()*100);
-> set i_return = in_int + i_rand;
->
-> return i_return;
-> END;
-> //
mysql> delimiter ;

三、修改存储过程和函数

修改存储过程和函数是指修改已经定义好的存储过程和函数。

MySQL中通过ALTER PROCEDURE语句来修改存储过程。

通过ALTER FUNCTION语句来修改存储函数。

MySQL中修改存储过程和函数的语句的语法形式如下：

    ALTER 
     { 
    PROCEDURE 
     |  
    FUNCTION 
    } sp_name [characteristic ...]   
   

    characteristic:   
   

    {  
    CONTAINS 
     SQL |  
    NO 
     SQL | READS SQL DATA | MODIFIES SQL DATA }   
   

    | SQL SECURITY { DEFINER | INVOKER }   
   

    | COMMENT  
    'string' 

其中，sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句；

READS SQL DATA表示子程序中包含读数据的语句；

MODIFIES SQL DATA表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。

DEFINER表示只有定义者自己才能够执行；

INVOKER表示调用者可以执行。

COMMENT ‘string’是注释信息。

说明：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。

但是，这两个语句的结构是一样的，语句中的所有参赛都是一样的。

而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

修改存储过程和函数，只能修改他们的权限，目前MYSQL还不提供对已存在的存储过程和函数代码的修改

如果要修改，只能通过先DROP掉，然后重新建立新的存储过程和函数来实现

四、删除函数

drop FUNCTION [if exists] 存储过程名;

函数和某个数据库关联的，所有上面的创建语句使用的前提：
use databasename；//切换到指定数据库

或者，在创建时使用数据库名. 前缀的方式

五、变量、光标、定义条件和处理程序、流控制语句

变量、光标、定义条件和处理程序、流控制语句也可以使用在函数中，使用方式和存储过程相同，不再赘述

六、存储过程和函数的区别

一、 存储函数有且只有一个返回值，而存储过程不能有返回值。
二、 函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
三、 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
四、 存储过程可以调用存储函数。但函数不能调用存储过程。
五、 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。