如何在SQL存储过程的WHERE子句中动态添加可选参数筛选条件

你之前的写法错误在于：PL/SQL的IF是流程控制语句，不能直接嵌在SQL的WHERE子句里；另外Oracle引用存储过程参数不用@，直接用参数名即可，注意避免和表列名重名，建议给参数加前缀比如p_避免冲突。

下面给你两种可行的实现方式：

方式一：静态SQL（简单场景推荐） #

利用逻辑表达式实现条件筛选，当p_store_id为NULL时，(p_store_id IS NULL OR store_id = p_store_id)这个条件永远为真，相当于不做额外筛选；当传入非NULL值时，就会触发store_id = p_store_id的校验。

CREATE OR REPLACE EDITIONABLE PROCEDURE MY_STORED_PROCEDURE (
    p_my_id IN VARCHAR2, 
    p_store_id IN NUMBER DEFAULT NULL
) AS
    -- 定义游标接收查询结果，存储过程中不能直接裸写SELECT，需结合游标或INTO赋值
    CURSOR c_result IS
        SELECT *
        FROM MYTABLE
        WHERE my_id = p_my_id
          AND (p_store_id IS NULL OR store_id = p_store_id);
    v_rec MYTABLE%ROWTYPE;
BEGIN
    -- 示例：遍历查询结果，可根据实际需求修改处理逻辑
    OPEN c_result;
    LOOP
        FETCH c_result INTO v_rec;
        EXIT WHEN c_result%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('my_id: ' || v_rec.my_id || ', store_id: ' || v_rec.store_id);
    END LOOP;
    CLOSE c_result;
END;
/

方式二：动态SQL（复杂多可选参数场景适用） #

通过拼接SQL语句动态添加筛选条件，灵活性更高：

CREATE OR REPLACE EDITIONABLE PROCEDURE MY_STORED_PROCEDURE (
    p_my_id IN VARCHAR2, 
    p_store_id IN NUMBER DEFAULT NULL
) AS
    v_sql VARCHAR2(1000);
    v_rec MYTABLE%ROWTYPE;
BEGIN
    -- 基础SQL语句
    v_sql := 'SELECT * FROM MYTABLE WHERE my_id = :1';
    
    -- 动态追加store_id筛选条件
    IF p_store_id IS NOT NULL THEN
        v_sql := v_sql || ' AND store_id = :2';
    END IF;
    
    -- 执行动态SQL并处理结果
    IF p_store_id IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql INTO v_rec USING p_my_id, p_store_id;
        DBMS_OUTPUT.PUT_LINE('my_id: ' || v_rec.my_id || ', store_id: ' || v_rec.store_id);
    ELSE
        EXECUTE IMMEDIATE v_sql INTO v_rec USING p_my_id;
        DBMS_OUTPUT.PUT_LINE('my_id: ' || v_rec.my_id);
    END IF;
END;
/

调用示例 #

• 仅传入my_id时：

CALL MY_STORED_PROCEDURE('1234');

实际执行逻辑等价于：

SELECT * FROM MYTABLE WHERE my_id = '1234';

• 同时传入my_id和store_id时：

CALL MY_STORED_PROCEDURE('1234', 987);

实际执行逻辑等价于：

SELECT * FROM MYTABLE WHERE my_id = '1234' AND store_id = 987;

补充：如果你的需求是直接返回查询结果集，建议改用函数或带REF CURSOR的存储过程，普通存储过程更适合执行数据操作而非直接返回结果。

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