1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| USE expertimo;
DELIMITER //
CREATE FUNCTION f_lot_description(id_lot INT(11)) RETURNS TEXT DETERMINISTIC
BEGIN
#on récupère le numéro de dossier concernant le lot
#SELECT COUNT(*) INTO @cnt FROM egroupware.egw_cal WHERE cal_id=id;
SELECT dossier into @id_dossier from expertimo.lot where id = id_lot;
#on va récupérer tous les lots du dossier
DECLARE done INT DEFAULT 0;
DECLARE retour TEXT;
DECLARE lot_type VARCHAR(100);
DECLARE lot_nbpp SMALLINT(6);
DECLARE lot_surface SMALLINT(6);
DECLARE cur1 CURSOR FOR SELECT id from type,nbpp,surface where dossier = @id_dossier;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
retour = CONCAT(retour,"\n");
REPEAT
FETCH cur1 INTO lot_type, lot_nbpp, lot_surface;
retour = CONCAT(retour, "----------------------------------------------------------------","\n");
retour = CONCAT(retour, "Type du bien : ", lot_type,"\n");
if (lot_nbpp != 0) THEN
retour = CONCAT(retour, "Nb pièces : ",lot_nbpp,"\n");
END IF;
if (lot_surface != 0) THEN
retour = CONCAT(retour, "Surface :", lot_surface,"\n");
END IF;
UNTIL done END REPEAT;
RETURN retour;
END //
DELIMITER; |
Partager