MYSQL basics-MySQL

MYSQL Getting started: basic operations for user-defined functions (bitsCN.com)

Step 13: basic operations of user-defined functions

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

MYSQL getting started 6: MYSQL operators

Http: // database/201212/175862 .html

MYSQL entry 7: MYSQL common functions

Http: // database/201212/175864 .html

Step 8: basic database and table operations

Http: // database/201212/175867 .html

MYSQL entry 9: simple indexing operations

Http: // database/201212/176772 .html

MYSQL Getting started: Basic View operations

Http: // database/201212/176775 .html

MYSQL getting started 11: basic trigger operations

Http: // database/201212/176781 .html

MYSQL entry 12: basic operations of stored procedures

Http: // database/201212/177380 .html

I. features and functions of user-defined functions (UDFs)

The function can return strings, integers, or real numbers;

You can define a simple function acting on a row or a set function acting on a group of multiple rows;

II. Basic operations

1. create a UDF

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

BEGIN

// Function implementation statement

END;

Aggregate specifies whether the created function is a common user-defined function or an AGGREGATE function.

Function_name is the name of the function used in SQL statement for calling.

The RETURNS clause specifies the type of the function return value.

Every time the server is started, all valid functions are reloaded unless mysqld is started using the -- skip-grant-tables parameter. In this case, the UDF initialization is skipped and the UDF is unavailable.

An AGGREGATE function works like a set (SUM) function inherent in MySQL, such as a SUM or COUNT () function. To make AGGREGATE take effect, the mysql. func table must contain a type column. If the mysql. func table does not have this column, run the mysql_fix_privilege_tables script to create this 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. use user-defined 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 a user-defined function

Drop function [if exists] function_name;

Example:

[SQL]

Mysql> drop function if exists fun_add_rand;

4. View user-defined function creation information

Show create funtion function_name;

Example:

[SQL]

Mysql> show create function fun_add_rand;

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

| Function | SQL _mode | Create Function | character_set_client | collation_connection | Database Collation |

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

| Fun_add_rand | create definer = 'root' @ 'localhost' FUNCTION 'fun _ add_rand '(

In_int int

) RETURNS int (11)

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 | latin1 | latin1_swedish_ci | latin1_swedish_ci |

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

5. view the status of a custom function

Show function status [LIKE ''];

Example:

[SQL]

Mysql> show function status like 'fun % ';

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

| Db | Name | Type | Definer | Modified | Created | Security_type |

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

| Test | fun_add_rand | FUNCTION | root @ localhost | 20:08:50 | 20:08:50 | DEFINER |

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

BitsCN.com