Reprint Address: Http://www.2cto.com/database/201212/177382.html the features and functions of a custom Function (UDF) www.2cto.com functions can be divided into return strings, integers or real numbers; can define a simple function for one row, or a set function that acts on a group of multiple rows; Basic operation 1, creating a custom function CREATE [AGGREGATE] FUNCTION function_name RETURNS {string| integer| real} begin //function-implemented statements end;& nbsp Aggregate Specifies whether the function created is a normal custom function or a aggregate function. Function_name is the name of the function used in the SQL declaration for invocation. The RETURNS clause describes the type of function return value. All valid functions are reloaded every time the server starts, unless you start mysqld with the--skip-grant-tables parameter. In this case, the initialization of the UDF is skipped, and the UDF is not available. A aggregate function works just like a MySQL intrinsic set (sum) function, such as the sum or count () function. To make aggregate work, the Mysql.func table must include a type column. If the Mysql.func table does not have this column, you should run the Mysql_fix_privilege_tables script to create the column. Example:[sql] 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;   2, using custom functions example:[sql] mysql> select ID from Test_inn; +------+ | ID | +------+ | 1 | | 1 | | 1 | | 1 | +------+ mysql> Select Fun_add_rand (ID) from Test_inn; +------------------+ | Fun_add_rand (ID) | +------------------+ | 91 | | 34 | | 93 | | 66 | +------------------+ 3, delete custom functions DROP function [IF EXISTS] Function_na me; Example:[sql] mysql> drop function if exists fun_add_rand; 4, viewing custom function creation information SHOW create FUNTION function_name; Example: [sq L] mysql> Show Create function Fun_add_rand; , &NB Sp &NBSp , &NB Sp +--------------+----------+----------------------------------------------------- ------+----------------------+----------------------+--------------------+ | Function | Sql_mode | Create Function and nbsp | character_set_client | collation_connection | Database Collation | +--------------+----------+-----------------------------------------------------------+------------------ ----+----------------------+--------------------+ | Fun_add_rand | | CREATE definer= ' root ' @ ' localhost ' FUNCTION ' fun_add_rand ' ( )   &nbs P In_int int , &N Bsp ) RETURNS Int (one) , &NB Sp begin &NBSP ; , &NB Sp declare i_rand int;   , &NB Sp declare i_return int; , &NB Sp  &NBS P , &NB Sp   &NBSP ; Set I_rand=floor (rand () *100); , &NB Sp  &NBS P Set I_return = In_int + I_rand; , &NB Sp  &NBS P , &NB Sp   &NBSP ; return I_return; , &NB Sp end | Latin1 | Latin1_swedish_ci | Latin1_swedish_ci |   , &NB Sp +--------------+----------+----------------------------------------- ------------------+----------------------+----------------------+--------------------+ 5, viewing custom function states show FUNCTION STATUS [like ']; Example:[sql] mysql> show func tion status like ' fun% '; +------+--------------+----------+----------------+---------------------+---------------------+---------- -----+ | Db | Name | Type | Definer | Modified | Created | Security_type | +------+--------------+----------+----------------+---------------------+---------------------+---------- -----+ | Test | Fun_add_rand |FUNCTION | [Email protected] | 2012-12-18 20:08:50 | 2012-12-18 20:08:50 | Definer | +------+--------------+----------+----------------+---------------------+---------------------+---------- -----+
