매개 변수가 있는 저장 프로시저

매개 변수가 있는 저장 프로시저

"를 추가하는 방법NULL이 아닐 경우 SELECT 결과를 필터링하기 전에 매개 변수를 확인하십시오.SE에서 발견된 다양한 변형을 시도해봤지만, 저에게 맞는 것은 없는 것 같습니다. MariaDB를 사용하는 IF() - THEN - ENDIF..., 다른 표기법이 있을까요?

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);

절차에서 준비된 문을 사용할 수 있습니다.

여기 업데이트된 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 ;

열에 값이 있으면 다음을 사용할 수 있습니다.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 //

COALESCE(might_be_null, fallback_value)

언급URL : https://stackoverflow.com/questions/63078381/stored-procedure-with-parameters