在TOAD中为WITH语句使用SQL日期变量的技术问询

方法1：使用替代变量（最适合纯SQL查询） #

直接用TOAD支持的SQL*Plus风格替代变量，无需PL/SQL块，适合重复引用同一日期的场景：

DEFINE TODAY = SYSDATE
WITH SUBQUERY1 AS (
    SELECT *
    FROM TABLE
    WHERE TABLE.DATE = &TODAY
),
SUBQUERY2 AS (
    SELECT *
    FROM TABLE2
    WHERE TABLE2.DATE = &TODAY
)
SELECT *
FROM TABLE3 
JOIN SUBQUERY1 ON TABLE3.PRIMARYKEY = SUBQUERY1.PRIMARYKEY
JOIN SUBQUERY2 ON TABLE3.PRIMARYKEY = SUBQUERY2.PRIMARYKEY;

• 若需要固定日期而非当前日期，可修改为：DEFINE TODAY = TO_DATE('2024-05-20','YYYY-MM-DD')
• 执行时TOAD会自动将&TODAY替换为定义的值，直接运行普通SQL即可。

方法2：PL/SQL块结合游标（需处理查询结果时使用） #

如果必须用PL/SQL块做后续逻辑处理，需通过游标接收WITH查询的多行结果：

DECLARE
    v_today DATE := SYSDATE; -- 定义PL/SQL变量，无需&符号
    -- 定义游标存储WITH查询的结果
    CURSOR c_join_result IS
        WITH SUBQUERY1 AS (
            SELECT *
            FROM TABLE
            WHERE TABLE.DATE = v_today
        ),
        SUBQUERY2 AS (
            SELECT *
            FROM TABLE2
            WHERE TABLE2.DATE = v_today
        )
        SELECT *
        FROM TABLE3 
        JOIN SUBQUERY1 ON TABLE3.PRIMARYKEY = SUBQUERY1.PRIMARYKEY
        JOIN SUBQUERY2 ON TABLE3.PRIMARYKEY = SUBQUERY2.PRIMARYKEY;
    v_result c_join_result%ROWTYPE; -- 定义与游标行结构匹配的变量
BEGIN
    -- 遍历游标输出结果（TOAD中可通过DBMS_OUTPUT查看）
    OPEN c_join_result;
    LOOP
        FETCH c_join_result INTO v_result;
        EXIT WHEN c_join_result%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('主键值: ' || v_result.PRIMARYKEY);
        -- 这里可添加自定义处理逻辑
    END LOOP;
    CLOSE c_join_result;
END;
/

原脚本失败的原因 #

1. PL/SQL块中不能直接执行裸SELECT，必须通过INTO或游标接收结果，否则会报错。
2. DECLARE定义的PL/SQL变量直接用变量名引用即可，不需要加&符号（&是替代变量的语法，和PL/SQL变量不兼容）。
3. Oracle中SYSDATE是内置函数，无需加括号，写成SYSDATE()会触发语法错误。

关于INTO语句的补充 #

INTO确实用于将查询结果赋值给变量，但仅适用于单行结果的场景，比如：

DECLARE
    v_date DATE;
BEGIN
    SELECT SYSDATE INTO v_date FROM DUAL;
END;
/

如果WITH查询返回多行，使用INTO会抛出TOO_MANY_ROWS异常，此时需用游标或BULK COLLECT INTO批量赋值给集合变量。

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