I have a much more complex procedure than the one I'm going to post here, I use it to export to a excel sheet all my product information, but the information comes from messed up because I attach a lot of foreign keys.
for summary purpose I have following pseudo code:
SET vetor_campos_extras = ?
Loop:
...
CALL buscar_campo_extra_produto(id_do_produto, vetor_campos_extras);
CALL organizar_valores_extra(vetor_campos_extras, valores_campos_extra); ...
INSERT INTO saida(codigo, template, tupla, campo_extra) SELECT codigo, template, tupla, valores_campos_extra FROM produto ...;
fim do loop;
I have a table called extra_field, which has the name of my extra field and some information, I have another table that relates the value of an extra field with its respective one, and a third table that relates the value of the extra field with the product.
My question is how to create a vector of keys through a select in my table field_extra, something like SELECT nome INTO vetor_campos_extras FROM campo_extra;
so that I can use it in my CALL to be able to make a following manipulation:
CREATE PROCEDURE buscar_campo_extra_produto(IN id_produto INT, OUT vetor_campo_extra ARRAY())
BEGIN
DECLARE t_nome varchar(255);
DECLARE t_valor varchar(255);
DECLARE v_finished INTEGER DEFAULT 0;
DEClARE campos_extras CURSOR FOR
SELECT ce.nome, cev.valor
FROM produto_campo_extra_valor AS pv
JOIN campo_extra_valor AS cev ON (pv.campo_extra_valor_id = cev.id)
JOIN campo_extra AS ce ON (cev.campo_extra_id = ce.id)
WHERE pv.produto_campos_extra_id = id_produto;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
SET t_campos_extras = "";
OPEN campos_extras;
interno_get: LOOP
FETCH campos_extras INTO t_nome, t_valor;
IF v_finished = 1 THEN
LEAVE interno_get;
END IF;
SET vetor_campo_extra[T_nome] = IFNULL(t_valor,"sem valor");
END LOOP interno_get;
CLOSE campos_extras;
END |
Because what happens not all my products have correlations with the extra fields, but I need to generate an output, when the field does not have the field empty.