Oracle里多条件分页查询存储到底怎么搞才顺手点呢？

-- 假设我们传入参数有：p_name, p_status, p_start_date, p_end_date, page_start, page_end
v_sql := 'SELECT * FROM (';
v_sql := v_sql || ' SELECT u.*, ROW_NUMBER() OVER (ORDER BY u.CREATE_TIME DESC) AS rn ';
v_sql := v_sql || ' FROM USERS u ';
v_sql := v_sql || ' WHERE 1=1 ';
-- 动态拼接条件
IF p_name IS NOT NULL THEN
    v_sql := v_sql || ' AND u.NAME LIKE ''%' || p_name || '%'' ';
END IF;
IF p_status IS NOT NULL THEN
    v_sql := v_sql || ' AND u.STATUS = ''' || p_status || ''' ';
END IF;
IF p_start_date IS NOT NULL THEN
    v_sql := v_sql || ' AND u.CREATE_TIME >= TO_DATE(''' || TO_CHAR(p_start_date, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD'') ';
END IF;
IF p_end_date IS NOT NULL THEN
    -- 注意结束日期一般要包含当天，所以用 < 第二天
    v_sql := v_sql || ' AND u.CREATE_TIME < TO_DATE(''' || TO_CHAR(p_end_date + 1, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD'') ';
END IF;
v_sql := v_sql || ') ';
v_sql := v_sql || ' WHERE rn BETWEEN ' || page_start || ' AND ' || page_end;
-- 然后使用EXECUTE IMMEDIATE执行v_sql

这就是最核心的逻辑,看到没？WHERE 1=1是个小技巧，就是为了后面能无脑地拼接AND条件，每个条件都判断一下传入的参数是否为空，不为空才拼接到SQL里。

别忘了查询总条数！

分页不仅要返回当前页的数据,还要返回总记录数，以便前端计算总页数，这个通常需要另一个查询，你可以在同一个存储过程里做两件事：

-- 拼接计算总条数的SQL
v_count_sql := 'SELECT COUNT(*) FROM USERS u WHERE 1=1 ';
-- 把上面拼接条件的IF语句段在这里重复利用一遍（这是痛点，会导致代码重复）
IF p_name IS NOT NULL THEN
    v_count_sql := v_count_sql || ' AND u.NAME LIKE ''%' || p_name || '%'' ';
END IF;
... -- 其他条件同样拼接
-- 执行v_count_sql into某个变量v_total_count

然后把你查询到的数据（可能是用游标返回）和总条数v_total_count一起返回给应用。

让人“顺手”的关键点

• 避免重复代码： 上面看到，拼接条件的逻辑在分页SQL和求总数SQL里写了两遍，很不爽，这是这种写法的主要缺点，为了解决它，有些人会把动态条件部分也封装成一个函数或过程，返回一个条件字符串，供两者调用，或者在应用层用MyBatis等ORM框架，可以定义一段<sql>片段，在查询数据和查询总数时分别引用，这样就只写一次条件。
• 性能考量： 一定要为作为查询条件的字段建立索引，比如NAME, STATUS, CREATE_TIME，如果条件组合多变，可以考虑建立组合索引，但需要根据实际查询频率来设计，否则维护索引的代价也高。
• 排序稳定性： 用ORDER BY u.CREATE_TIME DESC这样的字段来保证分页每页的顺序是固定的，如果只用ROW_NUMBER()而没有ORDER BY，顺序是不确定的。
• 参数化查询与SQL注入： 我上面用拼接字符串的写法是不安全的，有SQL注入风险，在实际应用中，应该使用绑定变量，在PL/SQL中可以用EXECUTE IMMEDIATE ... USING ...子句，在MyBatis中，就是占位符，MyBatis会帮你处理成绑定变量，既安全又利于Oracle共享SQL，提升性能。这是非常重要的最佳实践。

一个更“顺手”的完整存储过程框架

CREATE OR REPLACE PROCEDURE page_users(
    p_name IN VARCHAR2,
    p_status IN VARCHAR2,
    p_start_date IN DATE,
    p_end_date IN DATE,
    p_page_no IN NUMBER, -- 页码，从1开始
    p_page_size IN NUMBER, -- 每页大小
    p_cur OUT SYS_REFCURSOR, -- 返回数据的游标
    p_total OUT NUMBER -- 返回总记录数
) IS
    v_sql VARCHAR2(4000);
    v_count_sql VARCHAR2(4000);
    v_where VARCHAR2(1000) := ' WHERE 1=1 ';
    v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
    v_end NUMBER := p_page_no * p_page_size;
BEGIN
    -- 1. 构建公共的WHERE条件片段
    IF p_name IS NOT NULL THEN
        v_where := v_where || ' AND u.NAME LIKE ''%' || p_name || '%'' ';
    END IF;
    IF p_status IS NOT NULL THEN
        v_where := v_where || ' AND u.STATUS = ''' || p_status || ''' ';
    END IF;
    -- ... 其他条件
    -- 2. 查询总条数
    v_count_sql := 'SELECT COUNT(*) FROM USERS u ' || v_where;
    EXECUTE IMMEDIATE v_count_sql INTO p_total; -- 注意：这里为了简化仍用拼接，实际应用应用绑定变量
    -- 3. 查询分页数据
    v_sql := 'SELECT * FROM (';
    v_sql := v_sql || ' SELECT u.*, ROW_NUMBER() OVER (ORDER BY u.CREATE_TIME DESC) as rn ';
    v_sql := v_sql || ' FROM USERS u ';
    v_sql := v_sql || v_where;
    v_sql := v_sql || ') WHERE rn BETWEEN :start AND :end';
    -- 打开游标，使用绑定变量
    OPEN p_cur FOR v_sql USING v_start, v_end;
END;
/

想让Oracle多条件分页顺手,就抓住几点：用ROW_NUMBER()做分页骨架，用动态SQL（判断参数非空）处理多条件，用绑定变量保证安全和性能，想办法封装公共条件避免代码重复，虽然看起来步骤多一点，但一旦把这个模板搭好，以后各种分页查询就是套用和微调了。