Pc31jf1e

1SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE 
2   A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
3	
4SELECT A.DATEI, A.DIAI,A.HORAI  INTO VDATEI, VDIAI,VHORAI FROM
5 STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1;
6	
7CREATE PROCEDURE P_GETHORARIOS(
8    IN `MED` BIGINT,
9    IN `DATAREF` DATE,
10    IN `ESPEC` BIGINT,
11    IN `CONVE` BIGINT
12)
13BEGIN
14  DECLARE BDONE,BTEMP, BOCUP INT;
15  DECLARE DIA;
16  DECLARE VDIA,OK TINYINT;  
17  DECLARE VDURACAO TINYINT;
18  DECLARE VDATEI DATE;
19  DECLARE VDIAI TINYINT;
20  DECLARE VHORAI TIME; 
21  DECLARE VHORA, VHORAI,VHORAF TIME;
22
23  DECLARE CURS CURSOR FOR  SELECT DIA,COALESCE(A.DURACAO,30) AS DURACAO, A.HINI   FROM STAFF_ESCALA A  
24    WHERE A.DIA=DIA;
25  DECLARE CONTINUE HANDLER FOR NOT FOUND SET BDONE = 1;
26
27  DROP TEMPORARY TABLE IF EXISTS TBLRESULTS;
28  CREATE TEMPORARY TABLE IF NOT EXISTS TBLRESULTS  (
29    DATA_AG DATE,
30    DIA TINYINT,
31    HORA TIME  );
32
33  SET DIA=DAYOFWEEK(DATAREF);
34  OPEN CURS;
35
36  SET BDONE = 0;
37  REPEAT
38      SET OK=TRUE;
39      FETCH CURS INTO VDIA,VDURACAO,VHORAI;
40
41      -- SET VDATEI=(SELECT A.DATEI FROM STAFF_CONVENIOS A WHERE A.CONV_ID=CONVE AND A.STAFF_ID=MED LIMIT 1);
42      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;
43      SET BTEMP=IF(VDATEI IS NULL,TRUE,FALSE);
44      SET OK=OK AND BTEMP ;
45      IF (OK) THEN
46            INSERT INTO TBLRESULTS VALUES (DATAREF,VDIA,VHORA);
47      ELSE INSERT INTO TBLRESULTS VALUES (VDATEI,VDIAI,VHORAI);
48    END IF;
49  UNTIL BDONE END REPEAT;
50
51  CLOSE CURS;
52
53  SELECT A.* FROM TBLRESULTS;
54
55END