MySQL FUNCTION with cursor

Question

I have to create a function which given three parameters of type integer, where the first represents one hour, the second one minute and the third one second, return the number of records in a table where, a column of type DATETIME, that the hours is equal to the first parameter, or that the minutes are equal to the second parameter or that the seconds are equal to the third parameter. I need to check that the parameters have valid values ​​for the column that they are compared to, otherwise it returns a value of -1. I've done this but it's not working properly.

DELIMITER //
CREATE FUNCTION orders_placed(par_hour INT , par_minute INT , par_second INT )
RETURNS INT
BEGIN
DECLARE var_date DATETIME;
DECLARE var_counter INT;
DECLARE c1 CURSOR FOR SELECT date FROM orders;
            IF (par_hour NOT BETWEEN 0 AND 23 OR par_minute NOT BETWEEN 0 AND 59 OR par_second NOT BETWEEN 0 AND 59) THEN 
                    SET var_counter = -1;
            END IF; 
    OPEN c1;

        bucle: LOOP 

            FETCH c1 into var_date;

                IF (par_hour = hour(var_date) OR par_minute = minute(var_date) OR par_second = second(var_date)) THEN

                    SET var_counter= var_counter + 1;

                END IF;

        END LOOP bucle;

    CLOSE c1;
RETURNS var_counter;
END //
DELIMITER ;

Answer 1

There a re some Problems wioth your query.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `orders_placed`(par_hour INT , par_minute INT , par_second INT ) RETURNS int
    DETERMINISTIC
BEGIN
DECLARE var_date DATETIME;
DECLARE var_counter INT DEFAULT 0;
DECLARE finished INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR SELECT `date` FROM orders;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;

    IF (par_hour NOT BETWEEN 0 AND 23 OR par_minute NOT BETWEEN 0 AND 59 OR par_second NOT BETWEEN 0 AND 59) THEN 
            SET var_counter = -1;
    ELSE 
        OPEN c1;
            bucle: LOOP 
                FETCH c1 into var_date;
                IF finished = 1 THEN 
                    LEAVE bucle;
                END IF;
                    IF (par_hour = hour(var_date) OR par_minute = minute(var_date) OR par_second = second(var_date)) THEN
                        SET var_counter= var_counter + 1;
                    END IF;
            END LOOP bucle;
        CLOSE c1;
    END IF;
RETURN var_counter;
END$$
DELIMITER ;

And are you sure that iy don't want to check, if all numbers hour minute and second are equal to your date, because you check only if one of them are eual.