Oracle存储过程实现参数可选忽略过滤的方法咨询

针对你需要实现「参数为空时不参与过滤」的需求，提供两种可行方案：

方案一：静态SQL条件判断（简单直接） #

无需拼接SQL，直接在WHERE子句中为每个参数添加「参数为空则跳过过滤，否则匹配字段」的逻辑，实现起来快速简洁。

修改后的代码： #

CREATE OR REPLACE PROCEDURE FETCH_CONS_REPORT_NEW(
    P_STATENAME IN VARCHAR2,
    P_CITYNAME  IN VARCHAR2,
    P_FROMDATE  IN DATE,
    P_TODATE    IN DATE,
    TBLOUT OUT SYS_REFCURSOR )
AS
BEGIN
    OPEN TBLOUT FOR 
        SELECT 
            a.changerequestid, a.changerequestnumber, a.networktype, 
            a.statename, a.cityname, a.description, a.createdon, 
            a.lastmodifiedon, a.lastmodifiedby, a.band, 
            b.sap_id, b.site_type, b.cr_category, b.latitude, 
            b.longitude, b.approve_reject, b.CREATION_SAPID, 
            b.APPROVED_BY, b.BACKHAUL, b.CR_JUSTIFICATIONS, b.CREATED_DATE 
        FROM changerequests a 
        INNER JOIN tbl_pre_post_hoto b ON a.changerequestid = b.CHANGEREQUEST_ID 
        WHERE 
            -- 州名过滤：参数为空则不限制，否则匹配参数值
            (P_STATENAME IS NULL OR a.statename = P_STATENAME)
            -- 城市名过滤：同理
            AND (P_CITYNAME IS NULL OR a.cityname = P_CITYNAME)
            -- 起始日期过滤：参数为空则不限制，否则大于等于该日期
            AND (P_FROMDATE IS NULL OR a.createdon >= P_FROMDATE)
            -- 结束日期过滤：同理
            AND (P_TODATE IS NULL OR a.lastmodifiedon <= P_TODATE);
END;

关键说明： #

• 移除了原代码中硬编码的a.statename = 'Mumbai'，替换为参数动态判断逻辑
• 原代码中TO_DATE(P_FROMDATE,'DD-MM-YY')是错误写法：P_FROMDATE本身就是DATE类型，直接比较即可，无需二次转换
• 缺点：参数较多时，Oracle优化器可能无法生成最优执行计划，适合参数数量少的场景

方案二：动态SQL拼接（性能更优） #

通过动态拼接WHERE条件，仅当参数不为空时才添加对应过滤规则，生成的SQL更简洁，性能更优，同时避免SQL注入风险。

修改后的代码： #

CREATE OR REPLACE PROCEDURE FETCH_CONS_REPORT_NEW(
    P_STATENAME IN VARCHAR2,
    P_CITYNAME  IN VARCHAR2,
    P_FROMDATE  IN DATE,
    P_TODATE    IN DATE,
    TBLOUT OUT SYS_REFCURSOR )
AS
    v_query_string    VARCHAR2 (9000);
    v_where_condition VARCHAR2 (9000);
BEGIN
    -- 基础查询语句
    v_query_string := '
        SELECT 
            a.changerequestid, a.changerequestnumber, a.networktype, 
            a.statename, a.cityname, a.description, a.createdon, 
            a.lastmodifiedon, a.lastmodifiedby, a.band, 
            b.sap_id, b.site_type, b.cr_category, b.latitude, 
            b.longitude, b.approve_reject, b.CREATION_SAPID, 
            b.APPROVED_BY, b.BACKHAUL, b.CR_JUSTIFICATIONS, b.CREATED_DATE 
        FROM changerequests a 
        INNER JOIN tbl_pre_post_hoto b ON a.changerequestid = b.CHANGEREQUEST_ID ';

    -- 拼接WHERE条件
    v_where_condition := '';

    -- 处理州名参数
    IF P_STATENAME IS NOT NULL THEN
        v_where_condition := v_where_condition || 'a.statename = :p_statename';
    END IF;

    -- 处理城市名参数
    IF P_CITYNAME IS NOT NULL THEN
        IF v_where_condition IS NOT NULL THEN
            v_where_condition := v_where_condition || ' AND ';
        END IF;
        v_where_condition := v_where_condition || 'a.cityname = :p_cityname';
    END IF;

    -- 处理起始日期参数
    IF P_FROMDATE IS NOT NULL THEN
        IF v_where_condition IS NOT NULL THEN
            v_where_condition := v_where_condition || ' AND ';
        END IF;
        v_where_condition := v_where_condition || 'a.createdon >= :p_fromdate';
    END IF;

    -- 处理结束日期参数
    IF P_TODATE IS NOT NULL THEN
        IF v_where_condition IS NOT NULL THEN
            v_where_condition := v_where_condition || ' AND ';
        END IF;
        v_where_condition := v_where_condition || 'a.lastmodifiedon <= :p_todate';
    END IF;

    -- 如果有WHERE条件，拼接到主查询
    IF v_where_condition IS NOT NULL THEN
        v_query_string := v_query_string || ' WHERE ' || v_where_condition;
    END IF;

    -- 打开游标，使用绑定变量传递参数
    OPEN TBLOUT FOR v_query_string USING 
        CASE WHEN P_STATENAME IS NOT NULL THEN P_STATENAME ELSE NULL END,
        CASE WHEN P_CITYNAME IS NOT NULL THEN P_CITYNAME ELSE NULL END,
        CASE WHEN P_FROMDATE IS NOT NULL THEN P_FROMDATE ELSE NULL END,
        CASE WHEN P_TODATE IS NOT NULL THEN P_TODATE ELSE NULL END;
END;

关键说明： #

• 使用绑定变量（:p_statename等）避免SQL注入，同时让Oracle可以复用执行计划
• 拼接条件时注意判断是否需要添加AND分隔符，避免语法错误
• 同样移除了原代码中错误的TO_DATE转换
• 优点：生成的SQL贴合实际过滤需求，性能优于静态SQL方案，适合参数数量多的场景

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