A pedidos segue como fazer uma Stored Procedure recursiva no Mysql.
É aconselhável ler estes tópicos antes:
- Cursores
- Stored Procedure
O nosso exemplo consiste em cadastrar vários elementos e fazer um relacionamento entre pais e filhos, a recursividade é usada para formar uma hierarquia, mostrando a geração de cada indivíduo relacionado.
Uma função é considerada recursiva quando ela chama a si mesma diversas vezes.
Exemplo:
| Geração | ID | Elemento | Pai |
| 1 | 1 | A | 0 |
| 2 | 2 | AB | 1 |
| 2 | 3 | AC | 1 |
| 2 | 4 | AD | 1 |
| 3 | 5 | ADA | 4 |
| 1 | 6 | B | 0 |
| 2 | 7 | BA | 6 |
A tabela tb_recursive precisa ter apenas 3 campos, o campo id_filho irá ser igual a 0 quando o elemento não tiver um pai.
CREATE TABLE `test`.`tb_recursive` (
`id` int(11) NOT NULL auto_increment,
`nome` varchar(255) NOT NULL,
`id_pai` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `tb_recursive` VALUES (1, 'A', 0);
INSERT INTO `tb_recursive` VALUES (2, 'AB', 1);
INSERT INTO `tb_recursive` VALUES (3, 'AC', 1);
INSERT INTO `tb_recursive` VALUES (4, 'AD', 1);
INSERT INTO `tb_recursive` VALUES (5, 'B', 0);
INSERT INTO `tb_recursive` VALUES (6, 'BA', 5);
INSERT INTO `tb_recursive` VALUES (7, 'BB', 5);
INSERT INTO `tb_recursive` VALUES (8, 'C', 0);
INSERT INTO `tb_recursive` VALUES (9, 'ADA', 4);
INSERT INTO `tb_recursive` VALUES (10, 'ADB', 4);
INSERT INTO `tb_recursive` VALUES (11, 'ADBA', 10);
A pergunta básica que se baseia o algoritmo da SP recursiva é: Sendo esse pai X, quem são os filhos dele?
Esta pergunta é feita para cada filho desse pai, e assim por adiante, até que não tenhamos mais descendentes. É importante identificar a geração desse pai para que se possa organizar a hierarquia.
Para facilitar, iniciamos a criação de uma SP que iniciará a busca dos filhos:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_recursive_start`$$
CREATE PROCEDURE `test`.`sp_recursive_start` (IN in_id INT)
BEGIN
DECLARE v_geracao INT;
SET v_geracao = 1;
SET @@max_sp_recursion_depth = 10;
DROP TEMPORARY TABLE IF EXISTS tmp_recursive;
CREATE TEMPORARY TABLE tmp_recursive (
geracao INT,
id int,
nome VARCHAR(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO tmp_recursive (geracao,id,nome)
SELECT v_geracao, id, nome FROM tb_recursive WHERE id = in_id;
CALL sp_recursive(in_id, v_geracao);
SELECT * FROM tmp_recursive;
END$$
DELIMITER ;
Iremos criar uma tabela temporária para armazenar os resultados da hierarquia dos elementos, e exibi-los depois da pesquisa.
Segue abaixo o código da SP recursiva:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_recursive`$$
CREATE PROCEDURE `test`.`sp_recursive` (IN in_id INT, IN in_geracao INT )
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE v_nome VARCHAR(255);
DECLARE v_id_filho INT;
DECLARE v_geracao INT;
DECLARE cursor_a CURSOR FOR
SELECT id,nome FROM tb_recursive WHERE id_pai = in_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
OPEN cursor_a;
REPEAT FETCH cursor_a INTO v_id_filho,v_nome;
IF NOT done THEN
INSERT INTO tmp_recursive (geracao,id,nome) VALUE(in_geracao+1, v_id_filho, v_nome);
CALL sp_recursive (v_id_filho, in_geracao+1);
END IF;
UNTIL done END REPEAT;
CLOSE cursor_a;
END$$
DELIMITER ;
Caso o elemento não contenha filhos a SP é encerrada e volta para quem a chamou.
Para executar a SP use:
CALL sp_recursive_start(1);
Arquivo contendo todos os comandos recursive.zip
