MySql tour of a CURSOR


How a CURSOR is run on MySql, that is, having the following code:

SELECT pedi_prod_clave, prod_descripcion, SUM(vaca_toneladas) vaca_toneladas
FROM   scc_vales_carga, scc_pedidos, scc_productos
WHERE  vaca_tavi_numero = pw_tavi_numero
AND    vaca_eqpr_fecha  = lw_eqpr_fecha
AND    vaca_status     != 'X'
AND    pedi_serie       = vaca_pedi_serie
AND    pedi_numero      = vaca_pedi_numero
AND    prod_clave       = pedi_prod_clave
GROUP BY pedi_prod_clave, prod_descripcion;

I know that this query can return me more than one record, I also know that the tour can be done in the following way:

OPEN lc_prod;
get_lc_prod: LOOP
FETCH lc_prod INTO lr_prod_pedi_prod_clave, lr_prod_prod_descripcion, lr_prod_vaca_toneladas;
  IF not_found = 1 THEN
     LEAVE get_lc_prod;
  INSERT INTO scc_t_auxiliar_prod_basc (aupb_prod_clave, aupb_prod_desc, aupb_toneladas)
  VALUES                               (lr_prod_pedi_prod_clave, lr_prod_prod_descripcion, lr_prod_vaca_toneladas); 
CLOSE lc_prod;

The question is can it be done in any other way besides using FETCH ?, this doubt arises because what happens when in the SELECT of the CURSOR go more than 10-15 fields? In this case in the FECTH ... INTO would declare the 10-15 fields, besides that if I am not mistaken the fields defined in the SELECT to be consulted are the same that can be put in the INTO no?

asked by Osiel Candido Onofre 04.10.2016 в 18:53

1 answer


According to the documentation 14.6.6 Cursors there seems to be no other way to go through a cursor, only FETCH Syntax Cursor .

In addition, the cursor query, that is, the SELECT, should only include those columns (or fields) that will then actually be used in the FETCH, therefore, if 10 or 15 columns will be used or needed, then the FETCH must include 10 or 15 columns.

Another point to reflect on and keep in mind is: when to actually use a cursor. Personally, in the case presented, I would not use a cursor, I would directly execute an INSERT-SELECT.

answered by 06.10.2016 / 12:27