MySQL函数定义案例

delimiter $$
create function fun_hello(x int)
returns int
DETERMINISTIC
comment '这是注释' 
begin 
declare i int default 1; 
declare j int default 10; 
	case x
		when i then set x=i; 
		when j then set x=j;
		else set x=i+j;
	end CASE;
return x;
end $$
delimiter ;

select fun_hello(2);

循环

DROP FUNCTION if EXISTS REGEXP_SUBSTR_CONNECT_BY_LEVEL;
delimiter $$
create function REGEXP_SUBSTR_CONNECT_BY_LEVEL(expr VARCHAR(50),split VARCHAR(10),pos int,match_type VARCHAR(4),level int)
returns VARCHAR(100)
DETERMINISTIC
begin
declare id int default 1; 
declare levStr VARCHAR(5);
declare result VARCHAR(50) default '';
declare isFirst boolean default true;
add_loop: LOOP
 SET levStr = REGEXP_SUBSTR(expr, split, pos, id ,match_type);
 IF levStr is NULL THEN LEAVE add_loop; END IF;
 SET result = if(isFirst,CONCAT('\'',levStr,'\''),CONCAT(result,',\'',levStr,'\''));
 SET isFirst = false;
 SET id = id + 1;
 IF id > level THEN LEAVE add_loop;
 END IF;
END LOOP add_loop;
return result;
end $$
delimiter ;

select REGEXP_SUBSTR_CONNECT_BY_LEVEL('2,5,6,10,14,9','[^,]+',1,'i',2);