Использование функции внутри Select ORACLE SQL

У меня есть такая функция:

CREATE FUNCTION ISROOMAVAILABLE(P_ID_ROOM INT, P_DATE DATE) RETURN BOOLEAN
    IS
    V_COUNT INT;
BEGIN
    SELECT count(*)
    INTO V_COUNT
    FROM CHECKIN A
             LEFT JOIN CHECKOUT B ON (A.ID_RESERVATION = B.ID_RESERVATION)
    WHERE A.ID_ROOM = P_ID_ROOM
      AND ((A.DATE >= trunc(P_DATE) AND B.DATE IS NULL)
        OR
           (A.DATE <= trunc(P_DATE) AND B.DATE >= trunc(P_DATE)));
    RETURN (V_COUNT > 0);
END;

Это возвращает истину, если комната (идентифицируемая по ее идентификатору) доступна в определенную дату.

И я хочу проверить несколько параметров, используя указанную выше функцию в качестве определяющего фактора в моем выборе:

    SELECT Q.ID, COUNT(R.DATE_OUT - R.DATE_IN) AS DAYS
    FROM QUARTO Q
             JOIN CHECKIN CI ON Q.ID = CI.ID_ROOM
             JOIN RESERVATION R ON CI.ID_RESERVATION = R.ID
    WHERE (EXTRACT(YEAR FROM R.DATE_IN) = EXTRACT(YEAR FROM SYSDATE))
      AND (ISROOMAVAILABLE(Q.ID, SYSDATE))
    GROUP BY Q.ID
    ORDER BY DAYS;

Я использую sysdate в функции, потому что мне все еще не хватает некоторых данных в базе данных, но я все равно получаю ошибку Relational Operator Invalid.

Как можно использовать функцию, указанную в предложении WHERE?

Прошу прощения за плохой перевод с португальского, я не знаю точных терминов на английском языке.


sql oracle where-clause plsql sql-function
person Community    schedule 11.12.2020    source источник
comment
@OldProgrammer - это не совсем тот же вопрос. Тема, на которую вы указываете, связана с более сложным вопросом, когда функция требует логического ввода. В этом потоке вопрос проще - проблемой является только тип возвращаемых данных, и это легче решить. На SO есть много других подобных вопросов, я найду один и закрою эту ветку.   -  person mathguy    schedule 11.12.2020

Ответы (1)


arrow_upward
1
arrow_downward

Я думаю, что Oracle не может оценить логическое значение PL / SQL в предложении WHERE запроса SQL.

Самый простой подход - изменить функцию, чтобы она возвращала то, что можно было бы оценить. Мы могли бы, например, вернуть 1 для истины и 0 для false:

CREATE FUNCTION ISROOMAVAILABLE(P_ID_ROOM INT, P_DATE DATE) RETURN INT
    IS
    V_COUNT INT;
BEGIN
    SELECT count(*)
    INTO V_COUNT
    FROM CHECKIN A
             LEFT JOIN CHECKOUT B ON (A.ID_RESERVATION = B.ID_RESERVATION)
    WHERE A.ID_ROOM = P_ID_ROOM
      AND ((A.DATE >= trunc(P_DATE) AND B.DATE IS NULL)
        OR
           (A.DATE <= trunc(P_DATE) AND B.DATE >= trunc(P_DATE)));
    RETURN LEAST(V_COUNT, 1);
END;

А потом:

WHERE ...
  AND ISROOMAVAILABLE(Q.ID, SYSDATE) = 1

Если вы не можете изменить функцию, вам нужно будет обернуть ее в другую функцию, которая возвращает тип данных, который может быть оценен в SQL, и вызывать функцию-оболочку вместо оригинала.

Примечания, не относящиеся к вашему запросу:

  • EXTRACT(YEAR FROM R.DATE_IN) = EXTRACT(YEAR FROM SYSDATE) неоптимально. Это было бы более эффективно выразить с помощью прямой фильтрации:
    R.DATE_IN >= TRUNC(SYSDATE, 'YEAR') 
AND R.DATE_IN < ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12)
  • Интересно, COUNT(R.DATE_OUT - R.DATE_IN) делает ли то, что вы хотите. Он подсчитывает все строки, в которых обе даты не равны NULL. Может ты имел ввиду: SUM(R.DATE_OUT - R.DATE_IN)?
person GMB    schedule 11.12.2020
comment
Boolean - правильный тип данных; проблема в том, что Oracle SQL его не поддерживает. Более распространенный обходной путь (вместо того, чтобы возвращать счетчик, как вы предлагаете), - это вернуть 0 для false, 1 для true (вернуть тип данных NUMBER) или, возможно, вернуть строки, такие как 'Y' / 'N' или 'true' / 'false ' - person mathguy; 11.12.2020
comment
@mathguy: вот что я имел в виду ... Я адаптировал формулировку. Я также меняю возвращаемое значение на 0/1 (хотя счетчик здесь отлично справился бы с условием неравенства в предложении WHERE). - person GMB; 11.12.2020
comment
Спасибо за подсказку о дате. Я решил проблему, изменив возврат функции, как вы предложили. - person ; 12.12.2020