stored procedures -> reuckgabewert

[rolf z.]

hi,
ich mache schon den ganzen tag an einer billigen procedure (mysql 5.0) rum, aber es will mir einfach nicht gelingen selbige ans laufen zu bekommen.

ziel ist es, dass in der variable @oee eine liste von id's zu finden ist wie z.b. so: "1,2,3,4" ich bekomme jedoch immer nur NULL zurueck.

aufruf:

call ein_test(@oee);  
select @oee;

procedure:

BEGIN  
	DECLARE done BOOLEAN DEFAULT FALSE;  
	DECLARE res INT(10);  
	DECLARE cur CURSOR FOR SELECT `s`.`id` FROM `season` `s`;  
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;  
  
	OPEN cur;  
		  
	WHILE NOT done DO  
		FETCH cur INTO res;  
		set `out` = concat(`out`,',',`res`);  
	END WHILE;  
  
	CLOSE cur;  
END

ich hoffe es kann mir jemand auf die spruenge helfen.
danke
rolf z.

[dedlfix]

Hi!

ziel ist es, dass in der variable @oee eine liste von id's zu finden ist wie z.b. so: "1,2,3,4" ich bekomme jedoch immer nur NULL zurueck.
aufruf:

call ein_test(@oee);

select @oee;

  
Warum nimmst du eine Procedure und keine Function?  
  

> procedure:  
> [code lang=sql]BEGIN  
  
Vor dem BEGIN fehlt der Procedure-Kopf (zumindest in deinem Posting).  
  
  
Lo!

[rolf z.]

warum ich keine funktion nehme, kann ich dir ehrlich gesagt nicht sagen. ich nehme an, dass dies dort auch nicht besser zu loesen ist, oder? hier nochmal der komplette code:

CREATE DEFINER=`user`@`%` PROCEDURE `ein_test`(OUT `out` text)  
SQL SECURITY INVOKER  
BEGIN  
	DECLARE done BOOLEAN DEFAULT FALSE;  
	DECLARE res INT(10);  
	DECLARE hup VARCHAR(255);  
	DECLARE cur CURSOR FOR SELECT `s`.`id` FROM `elv_season` `s`;  
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;  
  
	OPEN cur;  
		  
	WHILE NOT done DO  
		FETCH cur INTO res;  
		set `out` = concat(`out`,',',`res`);  
	END WHILE;  
  
	CLOSE cur;  
END

rolf z.

[Vinzenz Mai]

Hallo,

warum ich keine funktion nehme, kann ich dir ehrlich gesagt nicht sagen. ich nehme an, dass dies dort auch nicht besser zu loesen ist, oder?

sowohl mit einer Funktion als auch mit einer Procedure kannst Du es richtig lösen. Da Du einen Skalar zurückgibst, reicht Dir eine Function aus.

CREATE DEFINER=user@% PROCEDURE ein_test(OUT out text)

SQL SECURITY INVOKER
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE res INT(10);
DECLARE hup VARCHAR(255);
DECLARE cur CURSOR FOR SELECT s.id FROM elv_season s;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

OPEN cur;

WHILE NOT done DO
FETCH cur INTO res;

-- Hier passiert der Fehler!
-- Du hast einen CONTINUE-Handler definiert.
-- Wenn FETCH keinen Datensatz mehr liefern kann, wird done auf TRUE gesetzt.
-- Die nächste Anweisung wird aber selbstverständlich ausgeführt, was Du aber
-- nicht willst.
-- res bekommt anscheinend den Wert NULL (nicht getestet) und
-- CONCAT(irgendwas, NULL) ergibt NULL. So erklärt sich Dein NULL-Wert.

  set `out` = concat(`out`,',',`res`);  

-- Die unschöne und ineffiziente Lösung wäre es, das SET-Statement in eine
-- IF-Anweisung zu packen:

--     IF NOT done
--         SET out = CONCAT(out, ',', res);
--     END IF

-- Die bessere Variante ist es, statt einer strukturierten Schleife wie im
-- Handbuch die unstrukturierte Schleife mit LOOP zu verwenden.

END WHILE;

CLOSE cur;
END

  
Siehe dazu das Tutorial von Roland Bouman:  
[Why REPEAT and WHILE are usually not handy to handle MySQL CURSORs](http://rpbouman.blogspot.com/2005/09/why-repeat-and-while-are-usually-not.html)  
  
  
Freundliche Grüße  
  
Vinzenz

[Vinzenz Mai]

Hallo Ingrid,

vieles stimmte, aber leider nicht alles. Jetzt getestet:

warum ich keine funktion nehme, kann ich dir ehrlich gesagt nicht sagen. ich nehme an, dass dies dort auch nicht besser zu loesen ist, oder?

wie dedlfix bereits schrieb, ist GROUP_CONCAT() die einfachere, elegantere und vermutlich schnellere Lösung :-) Du hast bei Deiner Funktion noch einiges nicht beachtet:

  
-- idList gefällt mir besser, es braucht auch nicht maskiert zu werden  
CREATE PROCEDURE ein_test(OUT idList text)  
BEGIN  
    DECLARE done BOOLEAN DEFAULT FALSE;  
    DECLARE res INT;  
    DECLARE cur CURSOR FOR SELECT s.id FROM elv_season s;  
    -- NOT FOUND ist lesbarer als SQLSTATE '02000'  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
  
    --  Welchen Wert hat die Variable idList?  
    --  Antwort: da nicht initialisiert, hat sie den Wert NULL  
    SET idList = '';  
  
    OPEN cur;  
    WHILE NOT done DO  
        FETCH cur INTO res;  
        -- Zweiter Fehler:  
        -- Wenn done wahr wird, darf die nächste Anweisung nicht  
        -- ausgeführt werden.  
        [link:http://dev.mysql.com/doc/refman/5.1/en/if-statement.html@title=IF] NOT done THEN  
            -- CONCAT(irgendwas, NULL) ergibt NULL.  
            -- Wegen der fehlenden Initialisierung gibt es Deinen NULL-Wert.  
            -- Dritter Fehler:  
            -- Im ersten Schleifendurchlauf ist idList der Leerstring,  
            -- diesem wird zunächst ein Komma, dann der erste Wert angefügt  
            -- Um dies zu verhindern, muss geprüft werden, ob es sich um  
            -- den ersten Durchlauf handelt.  
            IF idList = '' THEN  
                SET idList = res;  
            ELSE  
                SET idList = CONCAT(idList, ',', res);  
            END IF;  
        END IF  
    END WHILE;  
  
    CLOSE cur;  
END

  
CREATE PROCEDURE ein_test(OUT idList text)  
BEGIN  
    DECLARE done BOOLEAN DEFAULT FALSE;  
    DECLARE res INT;  
    DECLARE cur CURSOR FOR SELECT s.id FROM elv_season s;  
    -- NOT FOUND ist lesbarer als SQLSTATE '02000'  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
  
    --  Welchen Wert hat die Variable idList?  
    --  Antwort: da nicht initialisiert, hat sie den Wert NULL  
    --  Dies können wir auch nutzen, um den ersten Durchlauf zu erkennen:  
  
    OPEN cur;  
    WHILE NOT done DO  
        FETCH cur INTO res;  
        IF NOT done THEN  
            IF idList IS NULL THEN  
                SET idList = res;  
            ELSE  
                SET idList = CONCAT(idList, ',', res);  
            END IF;  
        END IF  
    END WHILE;  
  
    CLOSE cur;  
END

Schreiben wir es um mit LOOP und nutzen die Funktion COALESCE(), um den ersten Durchlauf abzufangen:

CREATE PROCEDURE ein_test(OUT idList text)  
BEGIN  
    DECLARE done BOOLEAN DEFAULT FALSE;  
    DECLARE res INT;  
    DECLARE cur CURSOR FOR SELECT s.id FROM elv_season s;  
    DECLARE CONTINUE HANDLER FOR [link:http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html@title=NOT FOUND] SET done = TRUE;  
  
    OPEN cur;  
    myLoop: [link:http://dev.mysql.com/doc/refman/5.1/en/loop-statement.html@title=LOOP]  
        FETCH cur INTO res;  
        [link:http://dev.mysql.com/doc/refman/5.1/en/if-statement.html@title=IF] done THEN  
            CLOSE cur;  
            [link:http://dev.mysql.com/doc/refman/5.0/en/leave-statement.html@title=LEAVE] myLoop;  
        END IF  
  
        SET idList = [link:http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce@title=COALESCE](CONCAT(idList, ',', res), res);  
  
    END LOOP;  
  
    CLOSE cur;  
END

Und alles in allem liefert dies nichts anderes als ein einfaches

SELECT [link:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat@title=GROUP_CONCAT](id) FROM elv_season

Freundliche Grüße

Vinzenz

[dedlfix]

Hi!

warum ich keine funktion nehme, kann ich dir ehrlich gesagt nicht sagen. ich nehme an, dass dies dort auch nicht besser zu loesen ist, oder? hier nochmal der komplette code:

Vinzenz hat ja schon gelöst, aber hier noch zwei Dinge von mir:

• Kennst du die Funktion GROUP_CONCAT()? Mit der kannst du dir vielleicht das ganze Konstrukt sparen.
• Das Debugging in einer SP ist recht mühsam aber nicht unmöglich. Man kann Werte, die man kontrollieren will, in eine Tabelle einfügen, die man extra für den Debug-Fall schnell man anlegt. SP laufen lassen -> Debug-Tabelle ausgeben -> nächster Versuch. Schön wäre es, wenn man eigene Warnungen produzieren können, was aber meines Wissens nach mit MySQL nicht geht.

Lo!