PL/SQL存储过程基于配置表实现动态WHERE条件查询的问题求助

需求说明 #

现有3张配置表存储查询规则，需根据传入的SWITCH参数自动关联配置生成查询条件，对CUR_ALL_CONTENTS执行动态查询，无需硬编码SQL条件。
3张配置表说明：

• Mains_Control：存储开关类型与一级规则ID的映射关系
• Control_Mapping：存储一级规则ID到二级规则ID的映射关系
• Control_Details：存储二级规则对应的查询字段、操作符、匹配值

存储过程代码 #

CREATE OR REPLACE PROCEDURE DYNAMIC_QUERY_CONTENTS(
    P_SWITCH IN VARCHAR2,
    P_RESULT OUT SYS_REFCURSOR
) AS
    V_SQL VARCHAR2(32767);
    V_WHERE_COND VARCHAR2(32767);
BEGIN
    -- 关联配置表拼接查询条件
    SELECT LISTAGG(ATTRIB_COLUMNS || ' ' || OPERAND || ' ''' || REPLACE(ATTRIB_VAL, '''', '''''') || '''', ' OR ') 
    INTO V_WHERE_COND
    FROM MAINS_CONTROL MC
    JOIN CONTROL_MAPPING CM ON MC.RULE_MAP_1 = CM.RULE_MAP_1
    JOIN CONTROL_DETAILS CD ON CM.RULE_MAP_2 = CD.RULE_MAP_2
    WHERE MC.SWITCH = P_SWITCH;

    -- 拼接完整动态SQL
    V_SQL := 'SELECT * FROM CUR_ALL_CONTENTS WHERE MAINS_CONTROL_SWITCH = :1 AND (' || V_WHERE_COND || ')';

    -- 执行SQL返回结果集
    OPEN P_RESULT FOR V_SQL USING P_SWITCH;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 无匹配规则时返回空结果
        OPEN P_RESULT FOR 'SELECT * FROM CUR_ALL_CONTENTS WHERE 1=2';
    WHEN OTHERS THEN
        RAISE;
END DYNAMIC_QUERY_CONTENTS;
/

调用示例 #

DECLARE
    V_RES SYS_REFCURSOR;
    -- 此处可定义和CUR_ALL_CONTENTS结构一致的变量接收结果，示例省略
BEGIN
    DYNAMIC_QUERY_CONTENTS(P_SWITCH => 'TYPE', P_RESULT => V_RES);
    -- 后续处理结果集逻辑，示例省略
END;
/

注意事项 #

1. 上述代码默认多个条件之间用OR拼接，如果需要支持AND等其他逻辑连接符，可以在Control_Details表新增LOGIC_OPERATOR字段存储连接规则，修改LISTAGG的拼接逻辑即可
2. 已对匹配值的单引号做转义处理，避免SQL语法错误
3. 若需要防范SQL注入风险，可新增校验逻辑：查询ALL_TAB_COLUMNS校验ATTRIB_COLUMNS字段确实属于CUR_ALL_CONTENTS，同时限制OPERAND字段只允许存储预设的合法操作符（如=/<>/LIKE/IN等）

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