SQLPlus/SQLcl无法识别带WITH FUNCTION的SQL语句问题求助

问题描述 #

在SQL Developer中可正常运行的以下SQL语句，在SQL*Plus或SQLcl中执行时，WITH子句无法被识别为SELECT语句的一部分，导致执行中断：

WITH FUNCTION AMS_SUBTRACT_WORKDAY(VDATE IN DATE, VDAYS IN NUMBER)
    RETURN DATE
    IS
    VENDD DATE;
    VDYWR NUMBER := 5;
    VDYCL NUMBER := 7;
    VDAYD NUMBER;
    VDAY2 NUMBER := VDAYS;
    VWEKS NUMBER;
    BEGIN
        SELECT TO_NUMBER(VDATE - TRUNC(VDATE, 'IW')) INTO VDAYD FROM DUAL;
        IF VDAYD > VDAY2 THEN 
            VENDD := VDATE - VDAY2;
        ELSE 
            VDAY2 := VDAY2 - VDAYD;
            VENDD := TRUNC(VDATE, 'IW');
            VENDD := VENDD - (TRUNC(VDAY2 / 5) * 7) - 2;
            VDAY2 := VDAY2 - (TRUNC(VDAY2 / 5) * 5);
            VENDD := VENDD - VDAY2;
        END IF;
        RETURN VENDD;
    END AMS_SUBTRACT_WORKDAY;
    SELECT AMS_SUBTRACT_WORKDAY(DATE '2024-09-28', 5) RES FROM DUAL;

原因分析 #

SQL*Plus和SQLcl默认不允许SQL语句中包含空行，当解析到WITH FUNCTION块内的空行时，会错误地将其判定为当前语句结束，导致后续的SELECT语句无法和前面的WITH子句关联，最终执行失败。而SQL Developer对空行的处理更宽松，支持这种写法。

解决方案 #

方法1：开启SQLBLANKLINES选项 #

在执行目标SQL前，先运行以下命令开启允许空行的配置：

SET SQLBLANKLINES ON

之后再执行你的完整SQL语句即可。这个配置会让SQL*Plus/SQLcl忽略语句中的空行，正确解析整个WITH子句和后续的SELECT关联。

方法2：移除语句中的所有空行 #

将原SQL中的所有空行删除，把代码合并为连续的块，示例如下：

WITH FUNCTION AMS_SUBTRACT_WORKDAY(VDATE IN DATE, VDAYS IN NUMBER) RETURN DATE IS VENDD DATE; VDYWR NUMBER := 5; VDYCL NUMBER := 7; VDAYD NUMBER; VDAY2 NUMBER := VDAYS; VWEKS NUMBER; BEGIN SELECT TO_NUMBER(VDATE - TRUNC(VDATE, 'IW')) INTO VDAYD FROM DUAL; IF VDAYD > VDAY2 THEN VENDD := VDATE - VDAY2; ELSE VDAY2 := VDAY2 - VDAYD; VENDD := TRUNC(VDATE, 'IW'); VENDD := VENDD - (TRUNC(VDAY2 / 5) * 7) - 2; VDAY2 := VDAY2 - (TRUNC(VDAY2 / 5) * 5); VENDD := VENDD - VDAY2; END IF; RETURN VENDD; END AMS_SUBTRACT_WORKDAY; SELECT AMS_SUBTRACT_WORKDAY(DATE '2024-09-28', 5) RES FROM DUAL;

这种方式无需修改配置，直接执行即可，但代码可读性会下降。

方法3：使用PL/SQL块包裹执行 #

如果上述方法仍有问题，可以将整个逻辑放在PL/SQL块中执行，通过DBMS_OUTPUT输出结果：

SET SERVEROUTPUT ON
DECLARE
    FUNCTION AMS_SUBTRACT_WORKDAY(VDATE IN DATE, VDAYS IN NUMBER)
        RETURN DATE
        IS
        VENDD DATE;
        VDYWR NUMBER := 5;
        VDYCL NUMBER := 7;
        VDAYD NUMBER;
        VDAY2 NUMBER := VDAYS;
        VWEKS NUMBER;
        BEGIN
            SELECT TO_NUMBER(VDATE - TRUNC(VDATE, 'IW')) INTO VDAYD FROM DUAL;
            IF VDAYD > VDAY2 THEN 
                VENDD := VDATE - VDAY2;
            ELSE 
                VDAY2 := VDAY2 - VDAYD;
                VENDD := TRUNC(VDATE, 'IW');
                VENDD := VENDD - (TRUNC(VDAY2 / 5) * 7) - 2;
                VDAY2 := VDAY2 - (TRUNC(VDAY2 / 5) * 5);
                VENDD := VENDD - VDAY2;
            END IF;
            RETURN VENDD;
        END AMS_SUBTRACT_WORKDAY;
BEGIN
    DBMS_OUTPUT.PUT_LINE('RES: ' || AMS_SUBTRACT_WORKDAY(DATE '2024-09-28', 5));
END;
/

执行前需要先开启SERVEROUTPUT，执行后会在控制台输出结果。

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