CODEWITHSUNDEEP
mysqlstored-procedures
Luiz Alves
Luiz Alves

Reputation: 2645

Select into multiple varibales with MySQL

I need to write a stored procedure using mysql.

In the repeat loop showing below, if I use something as: 

 SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE 
   A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);

the stored procedure works well and I get a result set.

But, I need select into multiple variables. Something as: 

SELECT 
    A.DATEI, A.DIAI, A.HORAI  
INTO 
    VDATEI, VDIAI, VHORAI 
FROM
    STAFF_CONVENIOS A 
WHERE 
    A.CONV_ID = CONVE AND A.STAFF_ID = MED 
LIMIT 1;

In this case my stored procedure is broken I get no result set.

How to use select into to multiple variables into stored procedure with temporary tables?

My stored procedure is similar to:

CREATE PROCEDURE P_GETHORARIOS(
    IN `MED` BIGINT,
    IN `DATAREF` DATE,
    IN `ESPEC` BIGINT,
    IN `CONVE` BIGINT
)
BEGIN
  DECLARE BDONE,BTEMP, BOCUP INT;
  DECLARE DIA;
  DECLARE VDIA,OK TINYINT;  
  DECLARE VDURACAO TINYINT;
  DECLARE VDATEI DATE;
  DECLARE VDIAI TINYINT;
  DECLARE VHORAI TIME; 
  DECLARE VHORA, VHORAI,VHORAF TIME;

  DECLARE CURS CURSOR FOR  SELECT DIA,COALESCE(A.DURACAO,30) AS DURACAO, A.HINI   FROM STAFF_ESCALA A  
    WHERE A.DIA=DIA;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET BDONE = 1;

  DROP TEMPORARY TABLE IF EXISTS TBLRESULTS;
  CREATE TEMPORARY TABLE IF NOT EXISTS TBLRESULTS  (
    DATA_AG DATE,
    DIA TINYINT,
    HORA TIME  );

  SET DIA=DAYOFWEEK(DATAREF);
  OPEN CURS;

  SET BDONE = 0;
  REPEAT
      SET OK=TRUE;
      FETCH CURS INTO VDIA,VDURACAO,VHORAI;

      -- SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
      SELECT A.DATEI, A.DIAI,A.HORAI  INTO VDATEI, VDIAI,VHORAI FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;
      SET BTEMP=IF(VDATEI IS NULL,TRUE,FALSE);
      SET OK=OK AND BTEMP ;
      IF (OK) THEN
            INSERT INTO TBLRESULTS VALUES (DATAREF,VDIA,VHORA);
      ELSE INSERT INTO TBLRESULTS VALUES (VDATEI,VDIAI,VHORAI);
    END IF;
  UNTIL BDONE END REPEAT;

  CLOSE CURS;

  SELECT A.* FROM TBLRESULTS;

END

I have posted a sample of my problem. Here is the real stored procedure:

CREATE DEFINER=`SYSDBA`@`%` PROCEDURE `P_GETHORARIOS`(
    IN `MED` BIGINT,
    IN `DATAREF` DATE,
    IN `ESPEC` BIGINT,
    IN `CONVE` BIGINT
)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
  DECLARE BDONE INT;
  DECLARE BTEMP TINYINT;
  DECLARE BOCUP TINYINT;
  DECLARE DIA BIGINT;
  DECLARE VDIA TINYINT;  
  DECLARE VDURACAO TINYINT;  
  DECLARE VHORA, VHORAI,VHORAF TIME;
  DECLARE VMED_ID BIGINT;
  DECLARE VESPEC_ID BIGINT;
  DECLARE VCONV_ID BIGINT;
  DECLARE OK TINYINT;
  DECLARE VNOMEESPEC,VNOMEMED VARCHAR(100);

  DECLARE CURS CURSOR FOR  SELECT DIA,COALESCE(A.DURACAO,30) AS DURACAO, A.HINI, A.HFIM, A.STAFF_ID,B.NOME AS NOMESTAFF, A.ESPEC_ID,C.NOME AS NOMEESPEC 
    FROM STAFF_ESCALA A  
     LEFT JOIN STAFF B ON B.ID=A.STAFF_ID 
    LEFT JOIN ESPECIALIDADES C ON C.ID=A.ESPEC_ID 
    WHERE A.DIA=DIA;
  -- DECLARE CURS CURSOR FOR  SELECT HINI, HFIM FROM STAFF_ESCALA;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET BDONE = 1;

  DROP TEMPORARY TABLE IF EXISTS TBLRESULTS;
  CREATE TEMPORARY TABLE IF NOT EXISTS TBLRESULTS  (
    DATA_AG DATE,
     DIA TINYINT,
    HORA TIME,
    MED_ID BIGINT,
    NOME_MED VARCHAR(100),
    ESPEC_ID BIGINT,
    NOME_ESPEC VARCHAR(100)
  );



  SET DIA=DAYOFWEEK(DATAREF);
  OPEN CURS;

  SET BDONE = 0;
  REPEAT
    SET OK=TRUE;

     FETCH CURS INTO VDIA,VDURACAO,VHORAI,VHORAF,VMED_ID,VNOMEMED,VESPEC_ID,VNOMEESPEC;
    IF (MED IS NOT NULL) THEN
     SET OK=IF(MED=VMED_ID,TRUE,FALSE) ;

    END IF;
    IF (ESPEC IS NOT NULL) THEN
     SET OK=OK AND IF(ESPEC=VESPEC_ID,TRUE,FALSE) ;

    END IF;
    IF (CONVE IS NOT NULL) THEN
      -- SET @EOK=(SELECT 1 FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
      SELECT 1 INTO @EOK FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;
      SET BTEMP=IF(@EOK IS NULL,TRUE,FALSE);
      SET OK= OK AND BTEMP ;
    END IF;

    IF (OK) THEN
       SET VHORA=VHORAI;
       IF (VDURACAO IS NULL) THEN
           SET VDURACAO=30;
         END IF;
         WHILE (VHORA <= VHORAF) DO
             SET BOCUP=(SELECT 1 FROM AGENDA_STAFF A WHERE A.DATA_MARCADA=DATAREF AND A.HORA_MARCADA=VHORA);
             IF (BOCUP IS NULL) THEN 
               INSERT INTO TBLRESULTS VALUES (DATAREF,VDIA,VHORA,VMED_ID,VNOMEMED,VESPEC_ID,VNOMEESPEC);
             END IF;
             SET VHORA=ADDTIME(VHORA, VDURACAO * 100);
         END  WHILE;
         SET VHORA=ADDTIME(VHORA, VDURACAO * (-100));
       IF (VHORA < VHORAF) THEN
            SET VHORA=VHORAF;
             SET BOCUP=(SELECT 1 FROM AGENDA_STAFF A WHERE A.DATA_MARCADA=DATAREF AND A.HORA_MARCADA=VHORA);
             IF (BOCUP IS NULL) THEN 
            INSERT INTO TBLRESULTS VALUES (DATAREF,VDIA,VHORA,VMED_ID,VNOMEMED,VESPEC_ID,VNOMEESPEC);
            END IF;
         END IF;

    END IF;
  UNTIL BDONE END REPEAT;

  CLOSE CURS;

  SELECT A.* FROM TBLRESULTS A LEFT JOIN AGENDA_STAFF B ON B.STAFF_ID=A.MED_ID AND B.HORA_MARCADA=A.HORA AND B.DATA_MARCADA=A.DATA_AG;

END

// The problem is with the lines:

 -- SET @EOK=(SELECT 1 FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
      SELECT 1 INTO @EOK FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;

If I use "SET @EOK..." all works well.

If I use SELECT 1 INTO @EOK.. the problem appears.

More info with data

Expected result from call p_gethorarios(7,'2017-11-20',47,21)

"data_ag";"dia";"hora";"med_id";"nome_med";"espec_id";"nome_espec"
"2017-11-20";"2";"08:30:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"09:00:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"09:30:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"10:00:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"08:30:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"09:00:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"09:30:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"
"2017-11-20";"2";"10:00:00";"7";"Paulo Renato Scofano";"47";"Pediatria/Neonatologia"

STAFF-ESCALA TABLE

ID;STAFF_ID;DIA;HINI;HFIM;ESPEC_ID;DURACAO;CREATION_TIME;MODIFICATION_TIME;LAST_USER_MODIF

    25;7;2;08:00:00;10:00:00;50;;18/11/2017 21:44:02;;1
    26;7;6;08:00:00;10:00:00;50;;18/11/2017 21:55:27;;1
    27;7;4;08:00:00;10:00:00;50;;18/11/2017 21:55:27;;1
    28;7;5;08:00:00;10:00:00;50;;18/11/2017 21:56:36;;1
    35;7;3;08:00:00;10:00:00;47;;19/11/2017 19:10:29;;1
    36;7;5;08:00:00;10:00:00;47;;19/11/2017 19:10:29;;1
    38;7;2;08:00:00;10:00:00;47;;19/11/2017 19:10:29;;1

Upvotes: 0

Views: 51

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

You may have to use user/session variables (the @xyz kind). The documentation does not explicitly state such, but it only shows examples with them. https://dev.mysql.com/doc/refman/5.7/en/select-into.html

If it is an issue with the NOT FOUND handler getting triggered from something other than the cursor, you could try structuring your code something like this (very pseudocode):

FETCH     
WHILE( NOT DONE )
BEGIN
   DO STUFF
   SET DONE = FALSE
   FETCH
END

Upvotes: 1

Related Questions