PL/SQL拼接VARCHAR查询时如何保留日期的小时信息？

编写存储函数时，通过拼接VARCHAR生成查询语句，遇到Date变量小时信息丢失的问题：

静态查询正常返回带时间的记录 #

静态SQL语句如下：

SELECT
    RESERVATIONS.NUMERO,
    RESERVATIONS.DATE_DEBUT_PRECIS,
    RESERVATIONS.DATE_FIN_PRECIS
FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
WHERE
    LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
    LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
    LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
    OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
    OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
    OBJETS.SOCIETES_ID = 5 AND
    RESERVATIONS.SOCIETES_ID = 5 AND
    RESERVATIONS.DEMANDE = 0 AND
    RESERVATIONS.ANNULER = 0 AND
    LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
    LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
    CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
    CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
    CLIENTS.SOCIETES_ID = 5 AND
    (TO_DATE('03.10.2022 23:00', 'dd.mm.YYYY hh24:mi') > RESERVATIONS.DATE_DEBUT_PRECIS AND TO_DATE('03.10.2022 07:00', 'dd.mm.YYYY hh24:mi') < RESERVATIONS.DATE_FIN_PRECIS)

返回结果：

NUMERO  DATE_DEBUT  DATE_FIN
94065   03.10.22    03.10.22
93995   03.10.22    03.10.22

动态拼接时丢失小时信息 #

当日期参数来自变量时，拼接后的SQL丢失了小时部分：

SELECT
    RESERVATIONS.NUMERO,
    RESERVATIONS.DATE_DEBUT_PRECIS,
    RESERVATIONS.DATE_FIN_PRECIS
FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
WHERE
    LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
    LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
    LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
    OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
    OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
    OBJETS.SOCIETES_ID = 5 AND
    RESERVATIONS.SOCIETES_ID = 5 AND
    RESERVATIONS.DEMANDE = 0 AND
    RESERVATIONS.ANNULER = 0 AND
    LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
    LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
    CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
    CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
    CLIENTS.SOCIETES_ID = 5 AND
    03.10.2022 > RESERVATIONS.DATE_DEBUT_PRECIS AND 03.10.2022 < RESERVATIONS.DATE_FIN_PRECIS

尝试过用TO_CHAR(P_DATE_FIN, 'dd.mm.YYYY hh24:mi')强制保留时间但无结果，拼接TO_DATE时写法错误导致数据库崩溃，寻求正确解决方法。

方法1：正确拼接带时间格式的TO_DATE语句 #

在拼接动态SQL时，需要把日期变量格式化为带小时分钟的字符串，并且完整拼接TO_DATE函数，确保语法正确。示例（以PL/SQL为例）：

-- 假设P_DATE_DEBUT和P_DATE_FIN是输入的DATE类型变量
v_sql := 'SELECT
            RESERVATIONS.NUMERO,
            RESERVATIONS.DATE_DEBUT_PRECIS,
            RESERVATIONS.DATE_FIN_PRECIS
          FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
          WHERE
            LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
            LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
            LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
            OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
            OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
            OBJETS.SOCIETES_ID = 5 AND
            RESERVATIONS.SOCIETES_ID = 5 AND
            RESERVATIONS.DEMANDE = 0 AND
            RESERVATIONS.ANNULER = 0 AND
            LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
            LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
            CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
            CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
            CLIENTS.SOCIETES_ID = 5 AND
            (TO_DATE(''' || TO_CHAR(P_DATE_DEBUT, 'dd.mm.YYYY hh24:mi') || ''', ''dd.mm.YYYY hh24:mi'') > RESERVATIONS.DATE_DEBUT_PRECIS 
             AND TO_DATE(''' || TO_CHAR(P_DATE_FIN, 'dd.mm.YYYY hh24:mi') || ''', ''dd.mm.YYYY hh24:mi'') < RESERVATIONS.DATE_FIN_PRECIS)';

关键注意点：

• 字符串中的单引号需要用两个单引号转义（''），避免语法错误
• 使用TO_CHAR将DATE变量转换为带小时分钟的格式化字符串，确保时间信息不丢失
• 完整保留TO_DATE函数的调用，和静态SQL的结构完全一致

方法2：优先使用绑定变量（更安全高效） #

如果业务允许，建议使用绑定变量替代字符串拼接，既能避免SQL注入风险，又能自动保留日期的完整信息，无需手动格式化。示例：

v_sql := 'SELECT
            RESERVATIONS.NUMERO,
            RESERVATIONS.DATE_DEBUT_PRECIS,
            RESERVATIONS.DATE_FIN_PRECIS
          FROM RESERVATIONS, LIGNES_RESERVATIONS, OBJETS, CLIENTS
          WHERE
            LIGNES_RESERVATIONS.OBJ_NUMERO = 261 AND
            LIGNES_RESERVATIONS.OBJ_SOCIETES_ID = 5 AND
            LIGNES_RESERVATIONS.SOCIETES_ID = 5 AND
            OBJETS.NUMERO = LIGNES_RESERVATIONS.OBJ_NUMERO AND
            OBJETS.SOCIETES_ID = LIGNES_RESERVATIONS.OBJ_SOCIETES_ID AND
            OBJETS.SOCIETES_ID = 5 AND
            RESERVATIONS.SOCIETES_ID = 5 AND
            RESERVATIONS.DEMANDE = 0 AND
            RESERVATIONS.ANNULER = 0 AND
            LIGNES_RESERVATIONS.RES_NUMERO = RESERVATIONS.NUMERO AND
            LIGNES_RESERVATIONS.RES_SOCIETES_ID = RESERVATIONS.SOCIETES_ID AND
            CLIENTS.NUMERO = RESERVATIONS.CLI_NUMERO AND
            CLIENTS.SOCIETES_ID = RESERVATIONS.CLI_SOCIETES_ID AND
            CLIENTS.SOCIETES_ID = 5 AND
            (:p_debut > RESERVATIONS.DATE_DEBUT_PRECIS AND :p_fin < RESERVATIONS.DATE_FIN_PRECIS)';

-- 执行时绑定变量
EXECUTE IMMEDIATE v_sql INTO v_result USING P_DATE_DEBUT, P_DATE_FIN;

这种方式下，数据库会自动处理变量的类型转换，无需手动格式化日期，同时避免了SQL注入的风险，执行性能也更优。

内容的提问来源于stack exchange，提问作者DanisOnTheFlux