55 lines
1.4 KiB
MySQL
55 lines
1.4 KiB
MySQL
|
DROP PROCEDURE IF EXISTS `vn`.`zone_getPostalCode`;
|
||
|
|
|
||
|
|
DELIMITER $$
|
||
|
|
$$
|
||
|
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `vn`.`zone_getPostalCode`(vSelf INT)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Devuelve los códigos postales incluidos en una zona
|
||
|
|
*/
|
||
|
|
DECLARE vGeoFk INT DEFAULT NULL;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.zoneNodes;
|
||
|
|
CREATE TEMPORARY TABLE tmp.zoneNodes (
|
||
|
|
geoFk INT,
|
||
|
|
name VARCHAR(100),
|
||
|
|
parentFk INT,
|
||
|
|
sons INT,
|
||
|
|
isChecked BOOL DEFAULT 0,
|
||
|
|
zoneFk INT,
|
||
|
|
PRIMARY KEY zoneNodesPk (zoneFk, geoFk),
|
||
|
|
INDEX(geoFk))
|
||
|
|
ENGINE = MEMORY;
|
||
|
|
|
||
|
|
CALL zone_getLeaves2(vSelf, NULL , NULL);
|
||
|
|
|
||
|
|
UPDATE tmp.zoneNodes zn
|
||
|
|
SET isChecked = 0
|
||
|
|
WHERE parentFk IS NULL;
|
||
|
|
|
||
|
|
myLoop: LOOP
|
||
|
|
SET vGeoFk = NULL;
|
||
|
|
SELECT geoFk INTO vGeoFk
|
||
|
|
FROM tmp.zoneNodes zn
|
||
|
|
WHERE NOT isChecked
|
||
|
|
LIMIT 1;
|
||
|
|
|
||
|
|
CALL zone_getLeaves2(vSelf, vGeoFk, NULL);
|
||
|
|
UPDATE tmp.zoneNodes
|
||
|
|
SET isChecked = TRUE
|
||
|
|
WHERE geoFk = vGeoFk;
|
||
|
|
|
||
|
|
IF vGeoFk IS NULL THEN
|
||
|
|
LEAVE myLoop;
|
||
|
|
END IF;
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
DELETE FROM tmp.zoneNodes
|
||
|
|
WHERE sons > 0;
|
||
|
|
|
||
|
|
SELECT zn.geoFk, zn.name
|
||
|
|
FROM tmp.zoneNodes zn
|
||
|
|
JOIN zone z ON z.id = zn.zoneFk;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|