mysql 地区全名存储过程

最新推荐文章于 2023-10-07 11:59:59 发布

city324

最新推荐文章于 2023-10-07 11:59:59 发布

阅读量211

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43803409/article/details/106758700

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mysql mysql 地区全名存储过程

- 场景：查询地区所有上级名称组成地区全名
- 知识点

场景：查询地区所有上级名称组成地区全名

知识点

// 
CREATE DEFINER=`root`@`%` PROCEDURE `FULL_NAME_PROC`(in areaId varchar(32) , out fullName varchar(64))
BEGIN
-- 根据地区id得到地区全名
declare `@n1` VARCHAR(32);
declare `@n2` VARCHAR(32);
declare `@n3` VARCHAR(32);
declare `@n4` VARCHAR(32);
declare `@n5` VARCHAR(32);
declare `@p1` VARCHAR(32);
declare `@p2` VARCHAR(32);
declare `@p3` VARCHAR(32);
declare `@p4` VARCHAR(32);
declare `@p5` VARCHAR(32);


select  a5.name,a5.parent_id into @n5 , @p5 from sys_area as a5  where  a5.id = areaId;

if exists(select a5.parent_id from sys_area as a5  where a5.id = areaId limit 1) then

select a4.name  , a4.parent_id into @n4 , @p4 from sys_area as a4  where a4.id = @p5;

if exists(select a4.parent_id from sys_area as a4  where a4.id = @p5 limit 1) then

select a3.name  , a3.parent_id into @n3 , @p3 from sys_area as a3  where a3.id = @p4;

if exists(select a3.parent_id from sys_area as a3  where a3.id = @p4 limit 1) then

select a2.name  , a2.parent_id into @n2 , @p2  from sys_area as a2  where a2.id = @p3;

if exists(select a2.parent_id from sys_area as a2  where a2.id = @p3 limit 1) then

set @n1 =  (select a1.name  from sys_area as a1  where a1.id = @p2);

set  fullName =  CONCAT(IFNULL(@n1,""),IFNULL(@n2,""),IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
-- SELECT @n1,@n2,@n3,@n4,@n5, @p6,fullName ;
ELSE
set  fullName =  CONCAT(IFNULL(@n2,""),IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  CONCAT(IFNULL(@n3,""),IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  CONCAT(IFNULL(@n4,""),IFNULL(@n5,""));
END IF;
ELSE
set  fullName =  IFNULL(@n5,"");
END IF;

-- set @n1 =NULL;
-- set @n2 =NULL;
-- set @n3 =NULL;
-- set @n4 =NULL;
-- set @n5 =NULL;
-- set @p1 =NULL;
-- set @p2 =NULL;
-- set @p3 =NULL;
-- set @p4 =NULL;
-- set @p5 =NULL;
-- 

END