MySQL---自定义函数

用户自定义函数（user-defined function,UDF）。UDF可以没有参数,但UDF必须有且只有一个返回值

创建UDF

CREATE FUNCTION function_name(parameter_name type,[parameter_name2 type2,...])RETURNS  {STRING|INTEGER|REAL|DECIMAL}BEGIN    bodyEND

删除UDF

DROP FUNCTION fun_name 删除自定义函数

DELIMITER “字符串”

更改结束标识

mysql> DELIMITER "//"mysql> SHOW TABLES //+----------------+| Tables_in_test |+----------------+| provinces      || user           || user2          || userinfo       |+----------------+

实例

// 计算两个数的平均值mysql> CREATE FUNCTION f2_2(num1 SMALLINT,num2 SMALLINT)    -> RETURNS FLOAT(10,2)    -> RETURN (num1+num2)/2;

mysql> CREATE FUNCTION adduser(username VARCHAR(20))    -> RETURNS VARCHAR(20)    -> BEGIN    -> INSERT INTO user(username) VALUES(username);    -> RETURN LAST_INSERT_ID();    -> END    -> //

定义局部变量

DECLARE var_name [,var_name…] date_type [DEFAULT VALUE];

为变量赋值语法(全局变量也是如此):
- SET parameter_name = value[,parameter_name = value…]
- SELECT INTO parameter_name

# 使用default 指定默认值CREATE FUNCTION add(num INT)RETURNS INTBEGIN    DECLARE temp INT DEFAULT 10 ;     RETURN temp+num;END# 使用select into 为变量赋值CREATE FUNCTION userNum()RETURNS INTBEGIN    DECLARE temp INT DEFAULT 10 ;     SELECT COUNT(id) FROM user INTO temp;    RETURN temp;END

用户变量(可以理解成全局变量)

• 声名：SET @param_name = value；
• 使用：SELECT @allParam;

# 声名A=100mysql> SET @A = 100 ;#声名B= 200mysql> SET @B = 200;#声名C=A+Bmysql> SET @C = @A+@B;#输出Cmysql> SELECT @C ;+------+| @C   |+------+|  300 |+------+

判断语句 IF

IF condition THEN statement
[ELSEIF condition THEN statement]
[ELSE statement]
END IF

CREATE FUNCTION if_test(age INT)RETURNS VARCHAR(20)BEGINIF age>18 THEN RETURN "成年";ELSEIF age>10 THEN RETURN "未成年";ELSE RETURN "小学生";END IF;END

分支语句 CASE

CASE case_value
WHEN when_value THEN statement
[WHEN when_value THEN statement] …
[ELSE statement_list]
END CASE

另一种方式：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE

# 第一种方式CREATE FUNCTION case_test(age INT)RETURNS VARCHAR(20)BEGINCASE ageWHEN 20 THEN RETURN "20#";WHEN 30 THEN RETURN "30#";ELSE RETURN "#";END CASE;END# 第二种方式CREATE FUNCTION case_test2(age INT)RETURNS VARCHAR(20)BEGINCASE WHEN age=20 THEN RETURN "20#";WHEN age=30 THEN RETURN "30#";ELSE RETURN "#";END CASE;END

循环 LOOP

[begin_label:] LOOP
statement
END LOOP [end_label] ;

LEAVE

leave用于终止loop循环
LEAVE label

# 累加CREATE FUNCTION loop_test()RETURNS INT BEGIN    DECLARE count int DEFAULT 10;    adds: LOOP        SET count = count+1;        IF count > 100 THEN LEAVE adds;        END IF;    END LOOP adds ;    RETURN count;END

关于循环还有许多语句，以后用到再做总结