mysql自建函数使用_mysql--自定义函数使用

mysql语句模板

语句：

create function 函数名([参数列表]) returns 数据类型

begin

sql语句;

return 值;

end;

SELECT * FROM test_department

-- 仅返回一个数字

CREATE FUNCTION myselect() RETURNS INT

RETURN 666;

SELECT myselect()

-- 仅有一条sql的函数返回 varchar

CREATE FUNCTION myselect2() RETURNS VARCHAR(500)

BEGIN

DECLARE departmentN VARCHAR(500) DEFAULT '';

SELECT department_na FROM test_department WHERE COMPANY_ID='10001' LIMIT 1 INTO departmentN;

RETURN departmentN;

END;

SELECT myselect2()

-- 仅有一条sql的函数返回 int

create function myselect3() returns int

begin

declare c int;

select COMPANY_ID from test_department where DEPARTMENT_NA='abc' into c;

return c;

end;

SELECT myselect3()

--带参的函数 comid(参数)

CREATE FUNCTION myselect4(comid VARCHAR(50)) RETURNS VARCHAR(500)

BEGIN

DECLARE depid VARCHAR(500);

SELECT DEPARTMENT_ID FROM test_department WHERE COMPANY_ID=comid LIMIT 1 INTO depid;

RETURN depid;

END;

SELECT myselect4(10013)

--代参数的循环语句

CREATE FUNCTION myselect5(depid VARCHAR(50)) RETURNS VARCHAR(500)

BEGIN

DECLARE depids VARCHAR(500);

DECLARE depone VARCHAR(300);

SET depids='$';

SET depone=depid;

WHILE depone IS NOT NULL DO

SET depids=CONCAT(depids,',',depone);

SELECT group_concat(DEPARTMENT_ID) FROM test_department WHERE FIND_IN_SET(PARENT_ID,depone) INTO depone;

END WHILE;

RETURN depids;

END;

SELECT myselect5('122')