mysql 地区全名 存储过程

mysql mysql 地区全名 存储过程

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

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

知识点

1. SQL SELECT INTO 语句.
2. 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) thenselect 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) thenselect 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) thenselect 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) thenset @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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！