mysql 自定义函数case when else写法

qq_43716882

于 2024-07-02 14:15:56 发布

阅读量495

点赞数 11

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43716882/article/details/140125734

版权

DELIMITER $$

USE demo$$

DROP FUNCTION IF EXISTS queryLevelPerson$$

CREATE DEFINER=root@localhost FUNCTION queryLevelPerson(flevel VARCHAR(10),pid VARCHAR(32),cid VARCHAR(32),sid VARCHAR(32),oid VARCHAR(32)) RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
#Routine body goes here…

DECLARE personNames VARCHAR(20);

CASE

WHEN flevel=‘project’ THEN

SET personNames = ( SELECT GROUP_CONCAT(H.personName SEPARATOR ‘,’)
FROM ( SELECT objectId,projectid,personid,countryId,siteId,roleLevelCode,roleid FROM etmf_sys_project_teammembers WHERE deleted=0 GROUP BY projectid,personid,roleid) F
LEFT JOIN etmf_sys_role G ON F.roleid=G.objectid
LEFT JOIN etmf_sys_person H ON F.personid=H.objectid
LEFT JOIN etmf_sys_role_operations I ON I.roleid=F.roleid
LEFT JOIN etmf_sys_operations J ON I.operationid=J.objectid
WHERE F.projectid=pid AND F.countryid=‘’ AND F.siteid=‘’ AND G.deleted=‘0’ AND H.deleted=‘0’ AND I.deleted=‘0’ AND J.deleted=‘0’ AND I. checkvalue=‘1’ AND J.objectid=‘5’
AND I.level=‘0’
);

WHEN flevel=‘country’ THEN

SET personNames = ( SELECT GROUP_CONCAT(H.personName SEPARATOR ‘,’)
FROM ( SELECT objectId,projectid,personid,countryId,siteId,roleLevelCode,roleid FROM etmf_sys_project_teammembers WHERE deleted=0 GROUP BY projectid,personid,roleid) F
LEFT JOIN etmf_sys_role G ON F.roleid=G.objectid
LEFT JOIN etmf_sys_person H ON F.personid=H.objectid
LEFT JOIN etmf_sys_role_operations I ON I.roleid=F.roleid
LEFT JOIN etmf_sys_operations J ON I.operationid=J.objectid
WHERE F.projectid=pid AND F.countryid=cid AND F.siteid=‘’ AND G.deleted=‘0’ AND H.deleted=‘0’ AND I.deleted=‘0’ AND J.deleted=‘0’ AND I.checkvalue=‘1’ AND J.objectid=‘5’
AND I.level=‘1’
);

WHEN flevel=‘site’ THEN

SET personNames = ( SELECT GROUP_CONCAT(H.personName SEPARATOR ‘,’)
FROM ( SELECT objectId,projectid,personid,countryId,siteId,roleLevelCode,roleid FROM etmf_sys_project_teammembers WHERE deleted=0 GROUP BY projectid,personid,roleid) F
LEFT JOIN etmf_sys_role G ON F.roleid=G.objectid
LEFT JOIN etmf_sys_person H ON F.personid=H.objectid
LEFT JOIN etmf_sys_role_operations I ON I.roleid=F.roleid
LEFT JOIN etmf_sys_operations J ON I.operationid=J.objectid
WHERE F.projectid=pid AND F.countryid=cid AND F.siteid=sid AND G.deleted=‘0’ AND H.deleted=‘0’ AND I.deleted=‘0’ AND J.deleted=‘0’ AND I.checkvalue=‘1’ AND J.objectid=‘5’
AND I.level=‘2’
);

ELSE
SET personNames = ‘’;

END CASE;

RETURN personNames;
END$$

DELIMITER ;