mysql 存储过程 删除机构树及子节点

一、采用mysql存储过程，递归遍历机构树，从而删除机构及其子机构：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_org_delete`(p_tenantId varchar(20),p_orgId varchar(20))

begin

declare child_orgId varchar(20);

declare has_org boolean default 1;

declare org_cursor cursor for select orgId from `tenant_org` where parentId = p_orgId;

-- 当出现02000错误时把局部变量的值设为true

  declare continue handler for sqlstate '02000' set has_org=0;

set @@max_sp_recursion_depth = 100;

open org_cursor;

    fetch next from org_cursor into child_orgId;

    while has_org

do

call p_org_delete(p_tenantId,child_orgId);

fetch next from org_cursor into child_orgId;

  end while;

  close org_cursor;

delete from tenant_org where orgId = p_orgId;

end