带参数的存储过程

Question

如何在过滤SELECT结果前增加对参数的IF NOT NULL检查？我尝试了在SE上找到的各种变体，但似乎都不适合我，IF() - THEN - END IF ...使用MariaDB，是否有不同的符号？

DELIMITER //
CREATE PROCEDURE IzvjestajRacuna (
    IN p_DatumOd DATETIME,
    IN p_DatumDo DATETIME,
    in p_ArtiklID INTEGER,
    IN p_PoslovnicaID INTEGER,
    IN p_KupacID INTEGER,
    IN p_RadnikID INTEGER
)
BEGIN
    SELECT Racun.* FROM Racun
    INNER JOIN RacunStavka ON Racun.RacunID=RacunStavka.RacunID
        AND RacunStavka.ArtiklID=p_ArtiklID
        AND Racun.KupacID=p_KupacID
        AND Racun.RadnikID=p_RadnikID
    INNER JOIN Radnik on Racun.RadnikID = Radnik.RadnikID
        AND Radnik.PoslovnicaID=p_PoslovnicaID
    WHERE CAST(Racun.DatumVrijeme AS date) >= p_DatumOd
      AND CAST(Racun.DatumVrijeme AS date) <= p_DatumDo;
END //

DELIMITER ;


call IzvjestajRacuna('2020-02-22','2020-02-25', 15, 25, 9, 35);
call IzvjestajRacuna(null, null, 15, 25, 9, 35);

Answer 1

您可以在过程中使用预准备语句。

这是一个更新的SQL。

DELIMITER //
CREATE PROCEDURE IzvjestajRacuna (
    IN p_DatumOd DATETIME,
    IN p_DatumDo DATETIME,
    IN p_ArtiklID INTEGER,
    IN p_PoslovnicaID INTEGER,
    IN p_KupacID INTEGER,
    IN p_RadnikID INTEGER
)
BEGIN
    -- Assumed that if a parameter is null, we would find rows with null value on date column.
    SET @where1 = IF(p_DatumOd IS NULL, " AND CAST(Racun.DatumVrijeme AS date) IS NULL", CONCAT(" AND CAST(Racun.DatumVrijeme AS date) >= ", p_DatumOd));
    SET @where2 = IF(p_DatumDo IS NULL, " AND CAST(Racun.DatumVrijeme AS date) IS NULL", CONCAT(" AND CAST(Racun.DatumVrijeme AS date) <= ", p_DatumDo));
    
    SET @sql_query = CONCAT(
        "
            SELECT Racun.* FROM Racun
            INNER JOIN RacunStavka ON Racun.RacunID=RacunStavka.RacunID
                AND RacunStavka.ArtiklID=p_ArtiklID
                AND Racun.KupacID=p_KupacID
                AND Racun.RadnikID=p_RadnikID
            INNER JOIN Radnik ON Racun.RadnikID = Radnik.RadnikID
                AND Radnik.PoslovnicaID=p_PoslovnicaID
            WHERE TRUE
        ", @where1, @where2        
    ) ;
    PREPARE stmt FROM @sql_query ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt ;
END //

DELIMITER ;

Answer 2

如果您的列确实有值，则可以使用ifnull。

CREATE PROCEDURE IzvjestajRacuna (
p_DatumOd DATETIME,
p_DatumDo DATETIME,
p_ArtiklID INTEGER,
p_PoslovnicaID INTEGER,
p_KupacID INTEGER,
p_RadnikID INTEGER
)
BEGIN

SELECT Racun.* 
FROM Racun
  INNER JOIN RacunStavka ON Racun.RacunID=RacunStavka.RacunID
    AND RacunStavka.ArtiklID=ifnull(p_ArtiklID, RacunStavka.ArtiklID)
    AND Racun.KupacID=ifnull(p_KupacID, Racun.KupacID)
    AND Racun.RadnikID=ifnull(p_RadnikID, Racun.RadnikID)
  INNER JOIN Radnik on Racun.RadnikID = Radnik.RadnikID
    AND Radnik.PoslovnicaID=ifnull(p_PoslovnicaID, Radnik.PoslovnicaID)
WHERE CAST(Racun.DatumVrijeme AS date) >= ifnull(p_DatumOd, Racun.DatumVrijeme)
  AND CAST(Racun.DatumVrijeme AS date) <= ifnull(p_DatumDo, Racun.DatumVrijeme);

END //

Answer 3

COALESCE(might_be_null, fallback_value)