Manipulate array in mysql with procedures

0

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.

    
asked by anonymous 08.08.2018 / 20:31

0 answers