利用游标实现数据库一对多关系的字符串拼接

先说一下目标效果

||
\/

利用数据库视图和游标实现此效果

drop view view_pmapproval_configuration; -- 删除已存在的视图
create view view_pmapproval_configuration AS
	select DISTINCT sys_project.prj_name, get_ongoing_phase(pmapproval_configuration.prj_code) as '所处阶段' from sys_project join pmapproval_configuration 
		where sys_project.prj_code = pmapproval_configuration.prj_code;

get_ongoing_phase函数

create function get_ongoing_phase( prjcode varchar(50) ) returns varchar(500)
begin 
  declare done int default 0;
	declare phasename varchar(500);
	declare temp varchar(50);
	declare phase_cursor cursor for select phase_name from pmapproval_configuration where prj_code = prjcode and phase_state = '1';
	declare continue handler for sqlstate '02000' set done = 1;
	set phasename = '';
 	open phase_cursor; --开启游标
 	fetch next from phase_cursor into temp; --从游标中取出数据，数据可以是一个或多个
  	repeat --循环
    	set phasename = concat(phasename,temp,',');
    	fetch next from phase_cursor into temp;
  	until done end repeat;
 	close phase_cursor; --关闭游标

  set phasename = substring(phasename,1,char_length(phasename)-1); -- 去掉最后一个逗号
	return phasename;
end