mysql 存储过程实例

mysql 存储过程实例

1. 存储过程 - 递归查询数据字典树数据

CREATE DEFINER=`lihongyuan`@`%` PROCEDURE `GetDataDictionaryTree`( in tkey varchar(200))
BEGIN
    WITH RECURSIVE cte AS (
                                SELECT id, name, ParentId,`Order` FROM lbd_app_datadictionary WHERE `key`=tkey
                                UNION ALL
                                SELECT n.id, n.name, n.ParentId,n.`Order` FROM lbd_app_datadictionary n JOIN cte ON cte.id = n.ParentId
                                )
                                SELECT * FROM cte cc where cc.ParentId is not null order by cc.`Order`;
END

调用存储过程

call GetDataDictionaryTree('SelectOrganization');

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1