PHPhulp.nl
35k+ leden Over PHPhulp Offerte opdracht Contact
S

Vreemd probleem met stored function

Door Sneeuwvlok op 11-07-2007 20:50
1.960 views
Ik heb een probleem met een stored function in MySQL. Het vreemde is dat ik een werkende query toevoeg aan een werkende functie en het geheel een NIET werkende functie is.

De volgende functie werkt:
DROP FUNCTION `get_wage`//
CREATE FUNCTION `get_wage`(
    mPid INT,
    score_count INT,
    work_date DATE,
    score_set VARCHAR(31),
    personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
    DECLARE prev_start_value, start_value INT(11);
    DECLARE prev_fee, fee DECIMAL(6, 2);
    DECLARE prev_repeat_every, repeat_every TINYINT(4);
    DECLARE bonus_value DECIMAL(6, 2);
    DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
    DECLARE EOF BOOL DEFAULT FALSE;
    DECLARE bonus_cursor
    CURSOR FOR
        SELECT
            amount,
            value,
            recursive
        FROM
            mBonus
        WHERE
            fk_mPid     =   mPid        AND
            startdate   <=  work_date   AND
            (
                enddate     >=  work_date   OR
                enddate     =   '0000-00-00'
            )                           AND
            amount      <=  score_count AND
            setMember   =   score_set
        ORDER BY
            amount;

    DECLARE extra_bonus_cursor
    CURSOR FOR
        SELECT
            amount,
            value,
            recursive
        FROM
            mExtraBonus
        WHERE
            fk_mPid     =   mPid        AND
            startdate   <=  work_date   AND
            (
                enddate     >=  work_date   OR
                enddate     =   '0000-00-00'
            )                           AND
            amount      <=  score_count AND
            setMember   =   score_set
        ORDER BY
            amount;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET EOF := TRUE;

    DECLARE EXIT HANDLER
        FOR SQLEXCEPTION
        sql_exception: BEGIN
            CLOSE bonus_cursor;
            CLOSE extra_bonus_cursor;
        END;

    OPEN bonus_cursor;
    OPEN extra_bonus_cursor;

    FETCH bonus_cursor INTO
        start_value,
        fee,
        repeat_every;

    IF (NOT EOF) THEN
        bonus_loop: LOOP
            SET prev_start_value    =   start_value;
            SET prev_fee            =   fee;
            SET prev_repeat_every   =   repeat_every;

            FETCH bonus_cursor INTO
                start_value,
                fee,
                repeat_every;

            IF (EOF) THEN
                IF (prev_repeat_every > 0) THEN
                    SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
                ELSE
                    SET bonus_value         =   prev_fee;
                END IF;
                SET bonus_total         =   bonus_total + bonus_value;
                LEAVE bonus_loop;
            END IF;

            IF (prev_repeat_every > 0 && score_count >= start_value) THEN
                SET bonus_value         =   CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
            ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
                SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
            ELSE
                SET bonus_value         =   prev_fee;
            END IF;

            SET bonus_total         =   bonus_total + bonus_value;
        END LOOP;
    END IF;

    SET EOF := FALSE;

    FETCH extra_bonus_cursor INTO
        start_value,
        fee,
        repeat_every;

    IF (NOT EOF) THEN
        extra_bonus_loop: LOOP
            SET prev_start_value    =   start_value;
            SET prev_fee            =   fee;
            SET prev_repeat_every   =   repeat_every;

            FETCH extra_bonus_cursor INTO
                start_value,
                fee,
                repeat_every;

            IF (EOF) THEN
                IF (prev_repeat_every > 0) THEN
                    SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
                ELSE
                    SET bonus_value         =   prev_fee;
                END IF;
                SET bonus_total         =   bonus_total + bonus_value;
                LEAVE extra_bonus_loop;
            END IF;

            IF (prev_repeat_every > 0 && score_count >= start_value) THEN
                SET bonus_value         =   CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
            ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
                SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
            ELSE
                SET bonus_value         =   prev_fee;
            END IF;

            SET bonus_total         =   bonus_total + bonus_value;
        END LOOP;
    END IF;

    CLOSE bonus_cursor;
    CLOSE extra_bonus_cursor;

    RETURN bonus_total;
END//


Nu wil ik een query toevoegen en de som van bonus_total en deze waarde teruggeven. Het enige wat ik verander is dat ik een extra variabele declareer om dit in op te slaan, een query draai (nadat alle cursors gesloten zijn) en het totaal teruggeef ipv enkel bonus_total. De functie wordt dan totaal het volgende:

DROP FUNCTION `get_wage`//
CREATE FUNCTION `get_wage`(
    mPid INT,
    score_count INT,
    work_date DATE,
    score_set VARCHAR(31),
    personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
    DECLARE prev_start_value, start_value INT(11);
    DECLARE prev_fee, fee DECIMAL(6, 2);
    DECLARE prev_repeat_every, repeat_every TINYINT(4);
    DECLARE base_wage DECIMAL(6, 2);
    DECLARE bonus_value DECIMAL(6, 2);
    DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
    DECLARE EOF BOOL DEFAULT FALSE;
    DECLARE bonus_cursor
    CURSOR FOR
        SELECT
            amount,
            value,
            recursive
        FROM
            mBonus
        WHERE
            fk_mPid     =   mPid        AND
            startdate   <=  work_date   AND
            (
                enddate     >=  work_date   OR
                enddate     =   '0000-00-00'
            )                           AND
            amount      <=  score_count AND
            setMember   =   score_set
        ORDER BY
            amount;

    DECLARE extra_bonus_cursor
    CURSOR FOR
        SELECT
            amount,
            value,
            recursive
        FROM
            mExtraBonus
        WHERE
            fk_mPid     =   mPid        AND
            startdate   <=  work_date   AND
            (
                enddate     >=  work_date   OR
                enddate     =   '0000-00-00'
            )                           AND
            amount      <=  score_count AND
            setMember   =   score_set
        ORDER BY
            amount;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET EOF := TRUE;

    DECLARE EXIT HANDLER
        FOR SQLEXCEPTION
        sql_exception: BEGIN
            CLOSE bonus_cursor;
            CLOSE extra_bonus_cursor;
        END;

    OPEN bonus_cursor;
    OPEN extra_bonus_cursor;

    FETCH bonus_cursor INTO
        start_value,
        fee,
        repeat_every;

    IF (NOT EOF) THEN
        bonus_loop: LOOP
            SET prev_start_value    =   start_value;
            SET prev_fee            =   fee;
            SET prev_repeat_every   =   repeat_every;

            FETCH bonus_cursor INTO
                start_value,
                fee,
                repeat_every;

            IF (EOF) THEN
                IF (prev_repeat_every > 0) THEN
                    SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
                ELSE
                    SET bonus_value         =   prev_fee;
                END IF;
                SET bonus_total         =   bonus_total + bonus_value;
                LEAVE bonus_loop;
            END IF;

            IF (prev_repeat_every > 0 && score_count >= start_value) THEN
                SET bonus_value         =   CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
            ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
                SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
            ELSE
                SET bonus_value         =   prev_fee;
            END IF;

            SET bonus_total         =   bonus_total + bonus_value;
        END LOOP;
    END IF;

    SET EOF := FALSE;

    FETCH extra_bonus_cursor INTO
        start_value,
        fee,
        repeat_every;

    IF (NOT EOF) THEN
        extra_bonus_loop: LOOP
            SET prev_start_value    =   start_value;
            SET prev_fee            =   fee;
            SET prev_repeat_every   =   repeat_every;

            FETCH extra_bonus_cursor INTO
                start_value,
                fee,
                repeat_every;

            IF (EOF) THEN
                IF (prev_repeat_every > 0) THEN
                    SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
                ELSE
                    SET bonus_value         =   prev_fee;
                END IF;
                SET bonus_total         =   bonus_total + bonus_value;
                LEAVE extra_bonus_loop;
            END IF;

            IF (prev_repeat_every > 0 && score_count >= start_value) THEN
                SET bonus_value         =   CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
            ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
                SET bonus_value         =   CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
            ELSE
                SET bonus_value         =   prev_fee;
            END IF;

            SET bonus_total         =   bonus_total + bonus_value;
        END LOOP;
    END IF;

    CLOSE bonus_cursor;
    CLOSE extra_bonus_cursor;

    SELECT
        `mProject`.`stdHours`           *
        `wages`.`wage`
    INTO
        base_wage
    FROM
        `wages`
    JOIN
        `person`
    ON
        `wages`.`age`                   =   years(person.birthdate, work_date)  AND
        `person`.`pid`                  =   personid
    JOIN
    (
        `mWageTable2project`,
        `mProject`
    )
    ON
        `wages`.`group_id`              =   `mWageTable2project`.`fk_mWTid`     AND
        `mWageTable2project`.`fk_mPid`  =   `mProject`.`mPid`                   AND
        `mProject`.`mPid`               =   project_id;

    RETURN base_wage + bonus_total;
END//


De fout die ik krijg is 'ERROR 1326 (24000): Cursor is not open'. Dit probleem treedt op in MySQL #5.0.27-standard-log en #5.0.42-standard-log.

Wat doe ik hier verkeerd?
b bart van der veen
11-07-2007 20:57
uw heeft meer als 20 regels code geplaats, dat kunnen we hier niet aan... dit is ontspanning geen werk...
C Citroen Anoniem Graag
11-07-2007 22:10
Sneeuwvlok, wat is jouw probleem? Het maakt toch niet uit dat bart in een ánder topic, wat hier niets mee te maken heeft meer dan 20 regels code post.
Hij vraag alleen of jij hier een regel of 20 wil posten omdat hij geen zin heeft om 300 regels door te lezen. Dan kan je dat ook gewoon doen in plaats van een excuus zoeken?
S Sneeuwvlok
11-07-2007 22:31
Freek schreef op 11.07.2007 22:10
Sneeuwvlok, wat is jouw probleem? Het maakt toch niet uit dat bart in een ánder topic, wat hier niets mee te maken heeft meer dan 20 regels code post.
Hij vraag alleen of jij hier een regel of 20 wil posten omdat hij geen zin heeft om 300 regels door te lezen. Dan kan je dat ook gewoon doen in plaats van een excuus zoeken?


Nee, dat kan ik niet, aangezien er bij zulke sql's geen line nummer gegeven worden waar er een error plaatsvindt, Was dat maar zo :{ . Dat is ook wat het debuggen van functies & procedures zo vervelend maakt.

K Klaasjan Boven
11-07-2007 22:43
@Sneeuwvlok ik doe dit niet snel maar voor ingewikkelde SQL en daar valt jouw vraag onder:
klik
b bart van der veen
11-07-2007 22:56
@sneeuwvlok, punt dat waren er meer dan 20, maar nog lang geen 300.

@freek, bedankt...

@sneeuwvlok, verder vindt ik het sterk staaltje (my)sql, ik blijf altijd hangen bij select enz...

edit:
ps heb nu nog hulp nodig hier staat al opgelost:
http://gathering.tweakers.net/forum/list_messages/1231456

edit2:
ik zag net het aantal foutcodes van 4.1 naar 5.0 met 147 verhoogt is...

Reageren

Inloggen om te reageren