mysql函数返回结果集_MySQL自定义函数

自定义函数的格式：

CREATE

[DEFINER = { user | CURRENT_USER }]

FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

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

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body:

Valid SQL procedure statement

自定义函数与存储过程的区别

1，函数方法的参数列表只允许IN类型的参数，并且不允许指定IN关键字

2，函数方法返回一个单一的值，值的类型在存储方法的头部定义

3，函数方法可以在SQL语句内部调用

4，函数方法不能返回结果集

官方文档：

创建自定义函数

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL}

BEGIN

//函数实现的语句

END;

aggregate 指定创建的函数是普通的自定义函数，还是AGGREGATE函数。

function_name 是用在SQL声明中以备调用的函数名字。

RETURNS 子句说明函数返回值的类型。

每次服务器启动的时候会重新加载所有有效函数，除非使用--skip-grant-tables参数启动mysqld。在这种情况下， 将跳过UDF的初始化，UDF不可用。

mysql文档说明：一个AGGREGATE函数就像一个MySQL固有的集合(总和)函数一样起作用，比如，SUM或COUNT()函数。要使得AGGREGATE 起作用，mysql.func表必须包括一个type列。如果mysql.func表没有这一 列，则应该运行mysql_fix_privilege_tables脚本来创建此列。

创建自定义函数：

mysql> delimiter //

mysql> DROP FUNCTION IF EXISTS fun_rand_key //

mysql> CREATE FUNCTION fun_rand_key(iparam int) RETURNs int

-> BEGIN

-> declare i_return int;

-> set i_return =iparam + floor(rand()*100);

-> return i_return;

-> END;

-> //

mysql> delimiter ;

使用自定义函数

mysql> select id from tb;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0.00 sec)

mysql> select fun_rand_key(id) from tb;

+------------------+

| fun_rand_key(id) |

+------------------+

|                9 |

+------------------+

1 row in set (0.00 sec)

查看自定义函数

SHOW CREATE FUNTION function_name;

或   SHOW FUNCTION STATUS [ LIKE '' ];

如：mysql> show function status \G;

*************************** 1. row ***************************

Db: testdb

Name: fun_rand_key

Type: FUNCTION

Definer: root@localhost

Modified: 2013-08-21 14:46:53

Created: 2013-08-21 14:46:53

Security_type: DEFINER

Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: gbk_chinese_ci

1 row in set (0.01 sec)

mysql> show function status like '%rand%' \G;

*************************** 1. row ***************************

Db: testdb

Name: fun_rand_key

Type: FUNCTION

Definer: root@localhost

Modified: 2013-08-21 14:46:53

Created: 2013-08-21 14:46:53

Security_type: DEFINER

Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: gbk_chinese_ci

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> show create function fun_rand_key \G;

*************************** 1. row ***************************

Function: fun_rand_key

sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU

TION

Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `fun_rand_key`(

iparam int) RETURNS int(11)

BEGIN

declare i_return int;

set i_return =iparam + floor(rand()*100);

return i_return;

END

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: gbk_chinese_ci

1 row in set (0.00 sec)

删除自定义函数

DROP FUNCTION [ IF EXISTS ] function_name;