Select from cursor

Question

Select from cursor

Navigation

#1 by (0 votes)

0

I'm trying to do a procedure 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 would like to know if you have only select values from within the loop, in a single select.

Because I understand that, every time it runs the loop it will do the select, would you like to know if it has to save those values from the loop in a variable so afterwards I show?

I do not know if it would be correct to give a select of a cursor

Create another cursor within the loop to display?

It's because I searched, I do not know if I researched correctly, but I did not find it.

mysql loop cursor

asked by anonymous 29.06.2018 / 16:21

1 answer

error message Can not find a different supporting object '[object Object]' of type 'object'. NgFor only Problems developing a bricks-breaking game in tkinter (collisions with objects)

score 0 · Answer 1

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 ;