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