博客

-- set global log_bin_trust_function_creators=TRUE;


DROP FUNCTION IF EXISTS getOrgChildList;
-- CREATE FUNCTION getOrgChildList(rootId varchar(32))
CREATE FUNCTION getOrgChildList(rootId INT)
RETURNS varchar(1000)

BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
			IF sTemp != '' THEN
					SET sTemp = concat(sTemp,',',sTempChd);
			ELSE
					SET sTemp = sTempChd;
			END IF;
-- 					SET sTemp = concat(sTemp,',',sTempPar);
    SELECT group_concat(ORG_ID) INTO sTempChd FROM t_sys_org where FIND_IN_SET(PARENT_ORG_ID, sTempChd) > 0;
END WHILE;
RETURN sTemp;
END





//查询，select语句结果作为参数，则需在select那层包裹一对小括号
select getOrgChildList((select org_id from t_sys_user where USER_ID = '机构id'));