如何创建一个游标循环，使其循环X次，并与列中的数据串联

Question

你好，因为有些事情可能是不可能的，或者只是简单的，我已经把它复杂化了。

我试图做的是，根据我的理解，我需要把一个循环放在一起，并且对MySQL中的游标和循环没有很好的了解。

我想做的是：

这就是数据。

To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 1,  14751 - 125 STREET NW

第一行是发生的事情，所以要建造6个单元的住宅，我希望它循环6次(6个住宅)然后连接'Unit'，'house_num'<--来自括号中的详细信息，单位1-6)，"街道">

所以我想要的是这样的输出：

Unit 1,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 2,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 3,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 4,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 5,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  
Unit 6,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  

这就是我目前所拥有的。。。我似乎就是找不到下一步行动。请帮忙。。。一个解释会很好，因为我非常非常想不仅了解它是如何工作的，而且也理解它。非常感谢。

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
-- declared values as I had forgotten this, was previously set to default 0 on count... which was maybe why I kept getting 0 back...
DECLARE count INT;
DECLARE units_add INT;
-- Input how many times to loop the procedure 
WHILE count < units_add 
DO
/**output should be: 02/permit_number/001.. Unit 1,2,3 etc and address**/
SELECT Concat('02/', permit_number, '/', LPAD(LAST_INSERT_ID(), 3, '0')),
Concat('Unit ', 
COUNT, 
', ',
RIGHT(address, Length(address) - Locate(',', (address )) - 0))
FROM permits_prep
WHERE job_descr like '%To construct%';
-- setting the Unit number start count
SET count = house_num + 1;
END WHILE;
END$$
DELIMITER ;  

在这之后。。。我迷路了。。。

我把这个拿回来了。。。。

> 02/100516029-008/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-009/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-010/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-011/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-016/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-017/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-018/000  Unit 0,  14751 - 125 STREET NW
> 02/100516029-019/000  Unit 0,  14751 - 125 STREET NW
> 02/101151068-006/000  Unit 0,  14220 - YELLOWHEAD TRAIL NW
> 02/101384288-003/000  Unit 0,  11246 - 122 STREET NW
> 02/102010513-004/000  Unit 0,  10345 - 107 STREET NW
> 02/104497468-023/000  Unit 0,  3625 - 144 AVENUE NW
> 02/104497468-024/000  Unit 0,  3625 - 144 AVENUE NW
> 02/104497468-025/000  Unit 0,  3625 - 144 AVENUE NW
> 02/104497468-026/000  Unit 0,  3625 - 144 AVENUE NW

我的单元号没有增加1，也没有循环请求的6次。。。。

许可证_代表

CREATE VIEW permits_prep 
AS
SELECT Concat('02/', permit_number)      as id, 
permit_number        as permit_num, 
permit_date          as permit_date, 
job_description      as job_descr,
LEFT(address, Length(address) - Locate(',', REVERSE(address )) - 1)     as house_num,   
address              as address, 
legal_description    as legal_description, 
floor_area           as floor_area, 
units_added          as units_add, 
construction_value   as Constr_val, 
RIGHT(address, Length(address) - Locate('-', (address )) - 1) as street, 
latitude             as lattitude, 
longitude            as longitude, 
location             as location, 
neighborhood.nbhrd_id            as neighborhood_id, 
neighborhood.CITY_city_id        as city_id 
FROM   edmonton_upto_10may2016_mostly_text 
INNER JOIN neighborhood 
ON neighbourhood = nbhrd_desc 
WHERE address LIKE '%,%'
GROUP  BY permit_number, 
address 
ORDER  BY permit_number; 

这是一个数据样本(无论如何，主要焦点)：

job_descr                                                              house_num        address
To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)  1   1, 14751 - 125 STREET NW
To construct 7 Dwellings of Row Housings (Units 7-13 - Building 16)     7   7, 14751 - 125 STREET NW
To construct 6 Dwellings of Row Housings (Units 14-19 - Building 4).    19  19, 14751 - 125 STREET NW
To construct 8 Dwellings of Row Housings (UNITS 20-27 - Building 3).    27  27, 14751 - 125 STREET NW
To construct 7 Dwellings of Row Housings (Units 58-64 - Building 8).    58  58, 14751 - 125 STREET NW
To construct 4 Dwellings of Row Housings (Units 65-68 - Building 9)     65  65, 14751 - 125 STREET NW
To construct 7 Dwellings of Row Housings (Units 69-75 - Buiding 15).    69  69, 14751 - 125 STREET NW
To construct 8 Dwellings of Row Housings (Units 76-83 - Building 14)    76  76, 14751 - 125 STREET NW

Answer 1

我还没有测试过，但我确信这会达到你想要的效果：

DROP PROCEDURE IF EXISTS list_permits;
DROP TABLE IF EXISTS permits_temp;
CREATE TEMPORARY TABLE IF NOT EXISTS permits_temp
(lineid INT AUTO_INCREMENT,
permit VARCHAR(512));
DELIMITER $$
CREATE PROCEDURE list_permits()
BEGIN
DECLARE count INT;
DECLARE v_id VARCHAR(256);
DECLARE v_house_num INT;
DECLARE v_units_add INT;
DECLARE v_street_address VARCHAR(256);
DECLARE v_job_descr VARCHAR(256);
DECLARE done INT DEFAULT FALSE;
DECLARE permits CURSOR FOR
SELECT id, house_num, units_add,
RIGHT(address, Length(address) - Locate(',', REVERSE(address )) - 1) AS street_address,
job_descr
FROM permits_prep
WHERE job_descr like '%To construct%';
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET done = TRUE;
OPEN permits;
read_loop: LOOP
FETCH permits INTO
v_id, v_house_num, v_units_add, v_street_address, v_job_descr;
IF done THEN
LEAVE read_loop;
END IF;
SET count = 0;
WHILE count < v_units_add DO
INSERT INTO permits_temp
(permit)
VALUES
(CONCAT('Unit ', (v_house_num + count), ', ', v_street_address, ', ', v_job_descr));
SET count = count + 1;
END WHILE;
END LOOP;
CLOSE permits;
-- To retrieve the result, execute
--  SELECT permit FROM permits_temp ORDER BY lineid;
END $$
DELIMITER ;

注意，DELIMITER子句不是SQL，而是特定于mysql命令行客户端和MySQL工作台；其他客户可能不理解。

理想情况下，这将是一个"集返回函数"，但遗憾的是，MySQL并没有提供这些功能。相反，我们必须创建一个临时表来保存并返回结果。临时表是当前会话的本地表，因此多个用户可以同时运行此过程而不会发生冲突，并且在会话结束时会自动删除临时表。

工作原理：我们从声明开始。它们的顺序很重要：首先声明所有变量，然后声明条件(我们这里没有这些)，然后声明游标，然后是条件处理程序。

count用于控制WHILE循环(稍后解释)，v_*变量将保持从光标读取的值，当我们用完要处理的数据时，done将进行标记。

光标的基本习惯用法是DECLARE光标(这将光标名称与SELECT语句关联)、OPEN指针(这执行SELECT并创建结果集)、FETCH，并从结果集中一次处理一条记录，直到不再存在记录为止，然后CLOSE光标以释放资源。

在这个应用程序中，我们需要一次读取permits_prep一行，并为每一行生成X行的输出；为此，我们使用嵌套循环。外部的LOOP将"永远"执行，直到我们用完要提取的行，这时我们用LEAVE语句脱离循环。我们FETCH每一行，然后设置内部WHILE循环以产生所需数量的输出行。(您也可以在此处使用FOR，但IMHO的WHILE看起来更干净。)

在没有INTO子句的情况下调用SELECT会导致过程生成一个结果集，该结果集将返回给调用代码。

在我们FETCH从光标上移到最后一行之后，对FETCH的下一个调用将导致执行NOT FOUND处理程序，将done设置为TRUE，进而导致LEAVE语句脱离外循环。

希望能有所帮助。

Answer 2

我认为有一种更简单的方法可以满足您的需求：

• 创建临时表
• 存储所需的任意数量的值
• 对该临时表的查询：

类似这样的东西：

delimiter $$
create procedure myProc(n int)
begin
drop table if exists temp_values;
create temporary table temp_values(int val, index idx_val(val));
set @i = 0;
store_values: repeat
set @i = @i + 1;
insert into temp_values values(@i);
until @i >= n end repeat;
end $$
delimtier ;

然后简单地执行程序并执行select:

call myProc(6);
select replace('Unit XXX,  14751 - 125 STREET NW, To construct 6 Unit Dwellings of Row Housings (Units 1-6; Building 10)', 'XXX', v.val)
from temp_values as v
order by v.val;

这将为您提供所需的输出。