如何在SQL Developer中让用户选择IN子句列表以简化多版本脚本？

问题背景 #

我负责给一群频繁运行脚本的用户编写SQL脚本，日常的变量输入提示操作已熟练掌握。但最近脚本衍生出多个版本，仅IN子句的取值列表存在差异（比如脚本1为EXISTS IN (x, y, z)，脚本2为EXISTS IN (a,b,c)）。希望通过让用户选择目标列表的方式合并这些脚本，避免维护多份重复代码。

现有基础脚本（隐去敏感信息）：

SELECT firstname, lastname
FROM tablename
WHERE somecolumn = &CodeNumber
    AND EXISTS (
        SELECT 1
        FROM table2 
        WHERE anothercolumn = somecolumn
        AND anotherCode IN ('A','B','C') 
        )

目前脚本已有CodeNumber的输入提示，我尝试用以下语句获取用户选择：

accept runOption char format A1 prompt 'Type 1 for (a,b,c) or 2 for (x,y,z) '

但不知道如何将runOption转换为对应的IN列表，需要可行的解决方案。

使用环境：SQL Developer 22.2.0.173；Oracle 19c

解决方案 #

方法1：SQL*Plus替换变量+DECODE赋值 #

利用define和decode函数根据用户选择动态定义IN列表变量，实现简单直观：

-- 获取用户选择
accept runOption char format A1 prompt '输入1选择列表(a,b,c)，输入2选择列表(x,y,z): '

-- 根据选择映射到对应IN列表
define in_list = decode('&runOption', '1', "'A','B','C'", '2', "'X','Y','Z'", "'A','B','C'")

-- 执行查询
SELECT firstname, lastname
FROM tablename
WHERE somecolumn = &CodeNumber
    AND EXISTS (
        SELECT 1
        FROM table2 
        WHERE anothercolumn = somecolumn
        AND anotherCode IN (&in_list) 
        )

说明：decode会根据runOption的取值返回对应字符串，最后一个参数为默认列表，可设置为最常用的选项，避免用户输入错误。

方法2：绑定变量+CASE表达式嵌入逻辑 #

把选择逻辑直接嵌入SQL的WHERE子句中，无需额外定义变量，适合列表规则较复杂的场景：

-- 获取用户输入
accept runOption char format A1 prompt '输入1选择列表(a,b,c)，输入2选择列表(x,y,z): '
accept CodeNumber number prompt '输入CodeNumber: '

-- 带分支判断的查询
SELECT firstname, lastname
FROM tablename
WHERE somecolumn = &CodeNumber
    AND EXISTS (
        SELECT 1
        FROM table2 
        WHERE anothercolumn = somecolumn
        AND (
            CASE '&runOption'
                WHEN '1' THEN anotherCode IN ('A','B','C')
                WHEN '2' THEN anotherCode IN ('X','Y','Z')
                ELSE anotherCode IN ('A','B','C') -- 默认选项
            END
        )
    )

说明：CASE表达式会根据用户选择匹配对应的IN条件，逻辑清晰，无需额外变量定义。

方法3：PL/SQL动态拼接SQL（适合大量列表场景） #

如果存在大量IN列表选项，用PL/SQL块动态拼接SQL更易维护，修改时只需调整CASE分支即可：

accept runOption char format A1 prompt '输入1选择列表(a,b,c)，输入2选择列表(x,y,z): '
accept CodeNumber number prompt '输入CodeNumber: '

DECLARE
    v_sql VARCHAR2(4000);
    v_in_list VARCHAR2(100);
BEGIN
    -- 根据用户选择赋值对应列表
    CASE '&runOption'
        WHEN '1' THEN v_in_list := '''A'',''B'',''C''';
        WHEN '2' THEN v_in_list := '''X'',''Y'',''Z''';
        ELSE v_in_list := '''A'',''B'',''C''';
    END CASE;

    -- 拼接完整SQL语句
    v_sql := 'SELECT firstname, lastname
              FROM tablename
              WHERE somecolumn = ' || &CodeNumber || '
                  AND EXISTS (
                      SELECT 1
                      FROM table2 
                      WHERE anothercolumn = somecolumn
                      AND anotherCode IN (' || v_in_list || ') 
                  )';

    -- 执行并输出结果（SQL Developer中通过DBMS_OUTPUT展示）
    DECLARE
        v_result SYS_REFCURSOR;
        v_firstname tablename.firstname%TYPE;
        v_lastname tablename.lastname%TYPE;
    BEGIN
        OPEN v_result FOR v_sql;
        DBMS_OUTPUT.PUT_LINE('Firstname | Lastname');
        DBMS_OUTPUT.PUT_LINE('---------------------');
        LOOP
            FETCH v_result INTO v_firstname, v_lastname;
            EXIT WHEN v_result%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(v_firstname || ' | ' || v_lastname);
        END LOOP;
        CLOSE v_result;
    END;
END;
/

说明：动态SQL方式扩展性强，新增列表只需在CASE中添加分支，无需修改主查询结构，适合长期维护的场景。

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