WHILE (MySQL)

Voorbeeld: Like-operator met een variabele als argument

CREATE DEFINER=`root`@`localhost` PROCEDURE `explode_03`()
BEGIN

set @i="a,b,c";

while @i like "%,%" do

	set @i=substring_index(@i,",",1);
	select @i;

end while;    

END

Voorbeeld: count() als argument

Details:

• Keyword select kun je niet weglaten
• Er zijn haakjes om het argument, maar niet om de vergelijking.

CREATE DEFINER=`root`@`localhost` PROCEDURE `explode_03`()
BEGIN

# Data is available in table "tmp"
#
while (select count(*) from tmp where tmp.applications like "%,%")>0 do

drop table if exists tmp1;
create table tmp1
	(select * from tmp where applications not like "%,%")
	union
	(select sku,substring_index(applications,",",1) from tmp where applications like "%,%")
	union
	(select sku,right(applications,length(applications)-length(substring_index(applications,",",1))-1) from tmp where applications like "%,%");

drop table tmp; 
create table tmp select * from tmp1;

# select * from tmp;

end while;    

END

Bronnen

• https://dev.mysql.com/doc/refman/5.7/en/while.html