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

创建UDF

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

删除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 INT BEGIN DECLARE temp INT DEFAULT 10 ; RETURN temp+num; END # 使用select into 为变量赋值 CREATE FUNCTION userNum() RETURNS INT BEGIN DECLARE temp INT DEFAULT 10 ; SELECT COUNT(id) FROM user INTO temp; RETURN temp; END

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

声名：SET @param_name = value；使用：SELECT @allParam; # 声名A=100 mysql> SET @A = 100 ; #声名B= 200 mysql> SET @B = 200; #声名C=A+B mysql> SET @C = @A+@B; #输出C mysql> 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) BEGIN IF 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) BEGIN CASE age WHEN 20 THEN RETURN "20#"; WHEN 30 THEN RETURN "30#"; ELSE RETURN "#"; END CASE; END # 第二种方式 CREATE FUNCTION case_test2(age INT) RETURNS VARCHAR(20) BEGIN CASE 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

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