Erilyn Erilyn - 5 months ago 15
MySQL Question

How to create a cursor loop making it loop X amount of times, concatenating with data from a column

Hello again and back for something that may be impossible or just simple and I have over complicated this exponentially.

What I am attempting to do and from what I understand is that I will require to put together a loop and are not very educated on cursors and loops in MySQL.

What I am trying to do is:

This is the data.

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


The first line is what happened, so to construct 6 unit dwellings, I want it to loop 6 times (6 dwellings)
then concat 'Unit', 'house_num' <-- which is from the details in the brackets, Units 1-6), 'street'

So what I want is an output that looks like this:

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)


This is what I have so far... and I just can't seem to find the next steps. Please kindly, help... an explanation would be wonderful as well as I would very very very much like to not only learn how this works, but to understand this as well. Thank you!

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 ;


After this... I am lost...

i get this back....

> 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


My unit numbers aren't increasing by 1, and it is not looping the requested, 6 times....

permits_prep

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;


And here is a sample of the data (the main focal point anyways):

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

I haven't tested it, but I'm fairly sure this will do what you want:

DROP PROCEDURE IF EXISTS list_permits;
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;
      SET count = 0;
      WHILE count < v_units_add DO
        SELECT CONCAT('Unit ', (v_house_num + count), ', ', v_street_address, ', ', v_job_descr);
        SET count = count + 1;
      END WHILE;
    END LOOP;

    CLOSE permits;
  END $$
DELIMITER ;

Note that the DELIMITER clause is not SQL but rather is specific to the mysql command-line client and MySQL Workbench; other clients may not understand it.

HOW IT WORKS: We begin with the declarations. The order of these is important: you declare all variables first, followed by conditions (we have none of those here), followed by cursors, followed by condition handlers.

count is used to control the WHILE loop (explained in a moment), the v_* variables will hold valued read from the cursor, and done will flag when we've run out of data to process.

The basic idiom for cursors is to DECLARE the cursor (this associates the cursor name with a SELECT statement), OPEN the cursor (this executes the SELECT and creates a result set), FETCH and process one record at a time from the result set until no more records exist, and then CLOSE the cursor to free up resources.

In this application we need to read permits_prep one row at a time, and for each row generate X rows of output; for this we use nested loops. The outer LOOP will execute "forever" until we run out of rows to fetch, at which point we break out of the loop with a LEAVE statement. We FETCH each row and then set up the inner WHILE loop to produce the required number of output rows. (You could also use a FOR here, but IMHO a WHILE looks cleaner.)

Calling SELECT without an INTO clause causes the procedure to build up a result set that will be returned to the calling code.

After we've FETCHed the last row from the cursor, the next call to FETCH will cause the NOT FOUND handler to be executed, setting done to TRUE, and in turn causing the LEAVE statement to break out of the outer loop.

Hope that helps.

Comments