Step 13: basic operations of user-defined functions

MYSQL entry 13: basic operations for user-defined functions http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.htmlMYSQL 10: basic view operations http://www.bkjia.com/database/201212/176775.htmlMYSQL Quick Start 11: Basic trigger operations http://www.bkjia.com/database/201212/176781.htmlMYSQL 12: basic operations of Stored Procedures http://www.bkjia.com/database/201212/177380.html 1. the features and functions of a user-defined function (UDF) function www.2cto.com can return strings, integers, or real numbers. You can define a simple function that acts on a row at a time, or a set function Acting on a group of multiple rows; ii. Basic operation 1. CREATE a custom function create [AGGREGATE] FUNCTION function_name RETURNS {STRING | INTEGER | REAL} BEGIN // The END statement of FUNCTION implementation; 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. Example of using a custom function: [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 the custom function drop function [if exists] function_name; example: [SQL] mysql> drop function if exists fun_add_rand; 4. view the custom function creation Information show create funtion function_name; example: [SQL] mysql> show create function fun_add_rand; + -------------- + ---------- + hour + | Function | SQL _mode | Create Function | character_set_client | collation_connection | Database Collation | + -------------- + ---------- + hour -------------------- + | 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 | + -------------- + ---------- + upper + lower + upper + ------------------ + 5. view the User-Defined function status show function status [LIKE '']. Example: [SQL] mysql> show function status like 'fun % '; + ------ + -------------- + ---------- + -------------- + signature + | Db | Name | Type | Definer | Modified | Created | Security_type | + ------ + ---------------- + accept --------------------- + tests + --------------- + | test | functions | FUNCTION | root @ localhost | 20:08:50 | 20:08:50 | DEFINER | + ------ + -------------- + ---------- + ---------------- + Summary + ------------- +