PL/SQL实现条件式输入查询客户信息的方案咨询

我正尝试将团队用于信息核查的几个查询整合在一起。现有查询可返回指定客户的相关信息，返回列固定为cust_id、name、zip_cd，但用户的查询条件会变化——有时可直接通过cust_id查询，有时仅能通过name和zip_cd查询。

请问能否创建单个查询，先提示用户选择查询方式（按cust_id或按name+zip_cd），再根据用户选择仅提示输入对应查询条件？

此外，我希望查询结果能像普通查询一样显示在结果面板中，目前想到的最优方案是使用游标，但仍需相关建议。

我曾尝试以下几种方案，但均存在问题：

方案一：全部预提示输入 #

该方案会提示所有输入而非仅所需条件：

SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: '
ACCEPT cust_name_prompt PROMPT 'Enter customer name: '
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: '
BEGIN
    IF &choice_prompt = '1' THEN 
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE a.cust_id = &cust_id_prompt
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSIF &choice_prompt = '2' THEN
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSE
        dbms_output.put_line('There were errors.');
    END IF;
END;

方案二：将ACCEPT嵌入条件分支 #

尝试将ACCEPT PROMPT嵌入对应条件分支，但无法正常运行：

SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
BEGIN
    IF &choice_prompt = '1' THEN 
        ACCEPT cust_id_prompt PROMPT 'Enter cust_id: '
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE a.cust_id = &cust_id_prompt
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSIF &choice_prompt = '2' THEN
        ACCEPT cust_name_prompt PROMPT 'Enter customer name: '
        ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: '
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSE
        dbms_output.put_line('There were errors.');
    END IF;
END;

方案三：使用输入变量 #

虽可执行，但仍会提示所有输入：

SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
DECLARE
    enter_cust_id number;
    enter_cust_name varchar2(20);
    enter_cust_zip_cd varchar2(5)
BEGIN
    IF &choice_prompt = '1' THEN
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE a.cust_id = &enter_cust_id
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSIF &choice_prompt = '2' THEN
        dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
        FOR r_product IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE b.cust_zip = &enter_cust_zip_cd AND c.cust_nm = &enter_cust_name
        )
        LOOP
            dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
        END LOOP;
    ELSE
        dbms_output.put_line('There were errors.');
    END IF;
END;

方法一：绑定变量+动态SQL实现结果面板输出（推荐） #

利用SQL Developer绑定变量特性，结合动态SQL实现按需提示，结果直接显示在常规查询结果面板：

DECLARE
    v_choice NUMBER := :choice; -- 先选择查询方式
    v_cust_id NUMBER;
    v_name VARCHAR2(20);
    v_zip VARCHAR2(5);
    v_sql VARCHAR2(1000);
    v_cursor SYS_REFCURSOR;
BEGIN
    IF v_choice = 1 THEN
        v_cust_id := :cust_id; -- 仅提示输入cust_id
        v_sql := 'SELECT a.cust_id, c.cust_nm, b.cust_zip 
                  FROM customers.cust_id a 
                  JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
                  JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
                  WHERE a.cust_id = :p_cust_id';
        OPEN v_cursor FOR v_sql USING v_cust_id;
    ELSIF v_choice = 2 THEN
        v_name := :cust_name; -- 提示输入姓名
        v_zip := :cust_zip; -- 提示输入邮编
        v_sql := 'SELECT a.cust_id, c.cust_nm, b.cust_zip 
                  FROM customers.cust_id a 
                  JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
                  JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
                  WHERE c.cust_nm = :p_name AND b.cust_zip = :p_zip';
        OPEN v_cursor FOR v_sql USING v_name, v_zip;
    ELSE
        RAISE_APPLICATION_ERROR(-20001, '无效选择，请输入1或2');
    END IF;

    -- 将游标结果输出到查询结果面板
    DBMS_SQL.RETURN_RESULT(v_cursor);
END;
/

使用说明：

1. 执行时先弹出输入框选择choice（1或2）
2. 根据选择仅弹出对应条件的输入框
3. 结果直接显示在SQL Developer的“查询结果”面板，支持导出、排序等常规操作
4. 需Oracle 12c及以上版本支持DBMS_SQL.RETURN_RESULT

方法二：ACCEPT条件提示+DBMS_OUTPUT输出（兼容低版本） #

通过WHEN子句实现按需提示输入，优化输出格式：

SET SERVEROUTPUT ON FORMAT WRAPPED
ACCEPT choice_prompt PROMPT '选择查询方式: (1) 按cust_id查询 (2) 按姓名+邮编查询: '

-- 动态标记查询类型
COLUMN dummy NEW_VALUE query_type
SELECT DECODE(&choice_prompt, 1, 'id', 2, 'name_zip') dummy FROM dual;

-- 按需提示输入
ACCEPT cust_id_prompt PROMPT '输入cust_id: ' WHEN query_type = 'id'
ACCEPT cust_name_prompt PROMPT '输入客户姓名: ' WHEN query_type = 'name_zip'
ACCEPT cust_zip_prompt PROMPT '输入客户邮编: ' WHEN query_type = 'name_zip'

BEGIN
    IF &choice_prompt = 1 THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('cust_id',10) || RPAD('cust_nm',20) || 'cust_zip');
        DBMS_OUTPUT.PUT_LINE('----------------------------------------');
        FOR r IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE a.cust_id = &cust_id_prompt
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(RPAD(r.cust_id,10) || RPAD(r.cust_nm,20) || r.cust_zip);
        END LOOP;
    ELSIF &choice_prompt = 2 THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('cust_id',10) || RPAD('cust_nm',20) || 'cust_zip');
        DBMS_OUTPUT.PUT_LINE('----------------------------------------');
        FOR r IN (
            SELECT a.cust_id, c.cust_nm, b.cust_zip 
            FROM customers.cust_id a 
            JOIN customers.cust_addr b ON a.xref_id = b.xref_id 
            JOIN customers.cust_nm c ON a.xref_id = c.xref_id  
            WHERE c.cust_nm = '&cust_name_prompt' AND b.cust_zip = '&cust_zip_prompt'
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(RPAD(r.cust_id,10) || RPAD(r.cust_nm,20) || r.cust_zip);
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('无效选择，请输入1或2');
    END IF;
END;
/

说明：

• 用WHEN子句控制ACCEPT仅在符合条件时触发提示
• 用RPAD格式化输出，让列对齐更美观
• 结果显示在DBMS_OUTPUT窗口，适合低版本Oracle环境

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