数据库中自定义存储函数详解

秒速五厘米 2022-07-25 15:56 175阅读 0赞

MySQL数据库中有很多已经存在的函数，比如count、sun、min、max和avg等等，除了这些，我们还可以自定义自己所需要的存储函数，其创建存储函数的基本形式如下：

CREATE FUNCTION func_name ([func_parameter[,...]])  RETURNS type  
    characteristic
    routine_body

说明：  
                func\_name参数是存储函数的名称；  
                func\_parameter表示存储函数的参数列表；func\_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式为“param\_name type” （param\_name参数名称；type指定参数类型，该类型可以是MySQL数据库的任意数据类型）。  
                type指定存储函数返回值的类型；  
                characteristic参数指定函数的特性，存储函数只支持DETERMINISTIC（没有NOT DETERMINISTIC）、NO SQL 和 READS SQL DATA ：  
                        DETERMINISTIC：指明存储函数的执行结果是确定的。  
                        NO SQL：表示子程序中不包含SQL语句；  
                        READS SQL DATA：表示子程序中包含读数据的语句；注意：如果开启了bin-log,就必须为存储函数指定相应的特性；  
                routine\_body为函数体，以BEGIN开始END结束。函数体可以是SELECT SQL语句（不包括UPDATE、INSERT、DELETE以及创建表的SQL语句），也可以嵌入调用存储过程的代码，还可以是其它代码（参见博客：《 [数据库中的控制语句][Link 1]》）。  
        下面以示例的方式给大家演示如何创建存储函数：  
        示例一——查询表结构中数据：  
        创建表结构及其数据：

CREATE TABLE `answer` (
      `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `content` VARCHAR(100) NOT NULL COMMENT '回复内容',
      `create_time` DATETIME NOT NULL COMMENT '创建时间',
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  COMMENT='回复表';
    insert  into `answer`(`id`,`content`,`create_time`) values (1,'回复内容','2016-04-02 11:06:44');

创建存储函数：

DELIMITER &&  
    CREATE  FUNCTION  answer_content (answer_id INT(11))  RETURNS VARCHAR(100)  
    READS SQL DATA /*也可以为DETERMINISTIC*/
    BEGIN  
    RETURN  (SELECT  content  FROM  answer WHERE  id= answer_id );  
    END&&  
    DELIMITER;

说明：

answer\_content为存储函数的名称；该函数的参数为answer\_id；返回值是VARCHAR类型。

调用存储函数：

SELECT answer_content (1);

示例二——存储函数调内部函数：  
        创建函数：

DELIMITER &&  
    CREATE  FUNCTION  curr_date ()  RETURNS DATETIME  
    READS SQL DATA
    BEGIN  
    RETURN  (SELECT  NOW());  
    END&&  
    DELIMITER ;

调用函数：

SELECT curr_date () ;

示例三——存储函数调用存储过程：  
        创建存储过程：

DELIMITER //    
    CREATE PROCEDURE sex_name (IN sex_type INT, OUT sex_name VARCHAR(4))    
    NO SQL   
    BEGIN   
    IF (sex_type = 0) THEN SET sex_name = '女';  
    ELSEIF (sex_type = 1) THEN SET sex_name = '男';  
    ELSE SET sex_name = '其它';  
    END IF;
    END //
    DELIMITER ;

创建存储函数：

DELIMITER &&  
    CREATE  FUNCTION sex_name_from_proc (sex_type INT )  RETURNS VARCHAR(4)  
    NO SQL
    BEGIN  
    DECLARE sex_name VARCHAR(4);
    CALL sex_name (sex_type, sex_name );  
    RETURN (sex_name );  
    END&&  
    DELIMITER ;

调用存储函数：

SELECT sex_name_from_proc (0);

存储函数与存储过程的区别

1、存储过程中的routine\_body可以是UPDATE、INSERT、DELETE以及创建表的SQL语句，存储函数则不行；

2、存储过程参数列表需要指定参数的输入输出类型，否则默认为输入类型，而存储函数参数列表不区分输入参数输出类型（实质均为IN类型但是不允许显式地指出）；

3、不能在 MySQL存储过程中使用return返回数据，存储过程中返回数据需要在参数列表中定义相应的变量并且使用out或inout修饰，存储函数返回数据只能使用return（即return关键字不能用在存储过程中）。

4、存储过程使用call关键字调用，存储函数可以在SQL语句内部调用；

5、存储过程可以返回结果集，存储函数不能返回结果集；

[Link 1]: http://blog.csdn.net/gaohuanjie/article/details/51024476