mysql循环生成记录

需求：已经有一张表roomList是房间列表，要根据此表生成第二张表deviceList，大概是一个房间有10个设备，每个设备记录里有房间号。

初始想法：循环读出roomList房间号，读一个房间号生成10个设备记录，插入deviceList

坑：（1） while循环只能在存储过程中调用，否则报错。而且报的错误是while下一行，新手查错很麻烦。

SET @n=1
WHILE @n<183 DO -- 
    SELECT roomNo INTO @roomNo FROM roomList WHERE tid=1;
    INSERT INTO deviceList VALUES ('NA', @roomNo, 'GuestControl', 'MotionSensor', 'MOTIONSENSOR-RENGAN-2',   '走廊人感', '', '');
    INSERT INTO deviceList VALUES ('NA', @roomNo, 'GuestControl', 'Scene',        'SCENE-SHUIMEIRENMOSHI-1', '睡美人模式', '', 'switch');
    SET @n=@n+1;
END WHILE;

查询：set @n=1 WHILE @n<183 DO select roomNo INTO @roomNo from roomList where tid=1

错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE @n<183 DO -- 
    select roomNo INTO @roomNo from roomList where tid=1' at line 2
(2) 局部变量与@

(3) while BEGIN ... END 与while do... end while