salix/db/routines/vn/procedures/subordinateGetList.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`subordinateGetList`(vBossFk INT)
BEGIN

	-- deprecated usar vn.worker_GetHierarch
	DECLARE vBossId INT;
    DECLARE vDone BOOL;
	DECLARE workerCur CURSOR FOR
		SELECT workerFk 
			FROM tmp.subordinate 
			WHERE NOT isChecked;
            

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
    DECLARE EXIT HANDLER FOR 1062 BEGIN
		CALL util.throw('INFINITE_LOOP');
    END;

	DROP TEMPORARY TABLE IF EXISTS tmp.subordinate;
    
    CREATE TEMPORARY TABLE tmp.subordinate
        (PRIMARY KEY (workerFk))
        ENGINE = MEMORY
		SELECT vBossFk AS workerFk, 0 AS isChecked;
        
    WHILE (SELECT COUNT(*) FROM tmp.subordinate WHERE NOT isChecked) > 0 DO
		OPEN workerCur;
		workerLoop: LOOP
			SET vDone = FALSE;

			FETCH workerCur INTO vBossId;
			
			IF vDone THEN
				LEAVE workerLoop;
			END IF;
		
			INSERT INTO tmp.subordinate 
				SELECT id, 0 
					FROM worker 
					WHERE bossFk = vBossId;
					
			UPDATE tmp.subordinate 
				SET isChecked = 1
				WHERE workerFk = vBossId;
		END LOOP;

		CLOSE workerCur;
    END WHILE;
END$$
DELIMITER ;