时间戳

首页 > 解决方案 > 在 Select ORACLE SQL 中使用函数

sql - 在 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;

如果房间（由其 ID 标识）在特定日期可用，则返回 true。

我想使用上面的函数作为我选择的决定因素来检查多个参数：

    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，因为我仍然缺少数据库中的一些数据，但我得到的错误仍然是“关系运算符无效”。

有什么方法可以使用我在“WHERE”子句中的功能吗？

请原谅我的葡萄牙语翻译不好，我不知道英文的确切术语。

标签： sqloracleplsqlwhere-clausesql-function

解决方案

我认为 Oracle 无法在WHERESQL 查询的子句中评估 PL/SQL 布尔值。

最简单的方法是修改函数，使其返回可以评估的内容。例如，我们可以返回1true 和0false：

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

推荐阅读