Select from cursor

Question

I am trying to make a trial with the following mysql block:

DECLARE done         INT DEFAULT FALSE;
DECLARE v_id         INT;
DECLARE R CURSOR FOR 
                            SELECT  id                                     
                                   ,nome
                              FROM pessoa d                               

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN R;
 read_loop: LOOP
    FETCH R INTO v_id;
      IF done THEN
        LEAVE read_loop;
      END IF;

      SELECT DATE_FORMAT(previsao,'%d/%m/%Y') as doba_dt_previsao
                             ,usuario_id
                         FROM documentos 
                        WHERE doc_pess_id = (

                                        SELECT MAX(doc_ID) ID
                                         FROM DOCUMENTOS
                                        WHERE doc_pess_id = v_id
                       )

                       AND docu_quitr IS NULL
                       AND previsao < DATE(NOW()) 
    END LOOP;
  CLOSE R;  

I wonder if you have to show only select values from within the loop, in a single select.

Because I understand that every time he runs the loop he will do the select, I wonder if he has how to store these values of the loop in a variable so then I show?

I don’t know if it would be right to give one select from a cursor

Create another cursor inside the loop to show?

It’s because I researched, I don’t know if I researched properly, but I didn’t find

Answer 1

DELIMITER $$
CREATE OR REPLACE PROCEDURE email_list_temp_table (in w_uf varchar(2))
BEGIN
   DECLARE v_finished INTEGER DEFAULT 0;
   DECLARE v_email varchar(100) DEFAULT ""; 
   DECLARE v_telefone varchar(100) DEFAULT ""; 
   DEClARE clientes_cursor CURSOR FOR
       SELECT email,telefone FROM clientes where uf=w_uf order by nome; 
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
   create temporary table temp_table (email varchar(100),telefone varchar(100));   
   OPEN clientes_cursor;
   get_dados: LOOP
       FETCH clientes_cursor INTO v_email,v_telefone;
       IF v_finished = 1 THEN 
           LEAVE get_dados;
       END IF;
       insert into temp_table (email,telefone) values (v_email,v_telefone);
   END LOOP get_dados;
   CLOSE clientes_cursor;
   select * from temp_table;
   drop temporary table temp_table;
END$$

-- para rodar a procedure:
call email_list_temp_table('PR');

Answer 2

Cursor example.

DELIMITER $$

CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
        DECLARE v_email varchar(100) DEFAULT "";

 -- declare cursor for employee email
 DEClARE email_cursor CURSOR FOR 
 SELECT email FROM employees;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

 OPEN email_cursor;

 get_email: LOOP

 FETCH email_cursor INTO v_email;

 IF v_finished = 1 THEN 
 LEAVE get_email;
 END IF;

 -- build email list
 SET email_list = CONCAT(v_email,";",email_list);

 END LOOP get_email;

 CLOSE email_cursor;

END$$

DELIMITER ;