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);