Oracle动态查询实现：基于字段内嵌SQL的循环执行需求

核心思路 #

利用数据库的动态SQL执行能力，结合参数的结构化存储（如JSON）规避宽表依赖，步骤如下：

1. 从request_table提取请求参数，转为JSON/键值对格式（适配任意数量的扩展列）
2. 根据请求的product_type筛选pricing_table中对应的内嵌SQL逻辑（measures字段）
3. 循环执行每条内嵌SQL，通过结构化参数传递匹配条件，收集所有符合的价格行

PostgreSQL 实现示例 #

通过PL/pgSQL函数实现循环执行与参数传递：

CREATE OR REPLACE FUNCTION get_matching_prices()
RETURNS TABLE (price_id INT, price NUMERIC, product_type VARCHAR) AS $$
DECLARE
    req RECORD;
    measure_sql TEXT;
BEGIN
    -- 读取当前请求参数（若request_table有多行，需外层加循环遍历）
    SELECT * INTO req FROM request_table;

    -- 循环遍历对应产品的所有匹配规则
    FOR measure_sql IN
        SELECT measures FROM pricing_table WHERE product_type = req.product_type
    LOOP
        -- 动态执行内嵌SQL，将请求参数转为JSONB传递，避免硬编码列名
        RETURN QUERY EXECUTE format(
            'SELECT p.price_id, p.price, p.product_type FROM pricing_table p WHERE %s',
            measure_sql
        ) USING req::JSONB;
        -- 注意：measures字段的SQL需统一参数引用方式，例如：p.term = $1->>'Term'
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM get_matching_prices();

MySQL 实现示例 #

通过存储过程结合游标与动态SQL实现：

DELIMITER //
CREATE PROCEDURE get_matching_prices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE measure_sql TEXT;
    DECLARE req_json JSON;
    DECLARE rule_cursor CURSOR FOR 
        SELECT measures FROM pricing_table 
        WHERE product_type = JSON_UNQUOTE(JSON_EXTRACT(req_json, '$.product_type'));
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 将请求参数转为JSON格式（适配任意扩展列）
    SELECT JSON_OBJECTAGG(col, val) INTO req_json
    FROM (
        SELECT column_name AS col, column_value AS val 
        FROM request_table
    ) AS req_params;

    -- 循环执行每条匹配规则
    OPEN rule_cursor;
    rule_loop: LOOP
        FETCH rule_cursor INTO measure_sql;
        IF done THEN
            LEAVE rule_loop;
        END IF;
        -- 构造并执行动态SQL
        SET @dynamic_sql = CONCAT('SELECT price_id, price, product_type FROM pricing_table WHERE ', measure_sql);
        PREPARE stmt FROM @dynamic_sql;
        EXECUTE stmt USING req_json; -- 传递JSON参数，measures中需用JSON_EXTRACT引用，例如：term = JSON_UNQUOTE(JSON_EXTRACT(?->>'Term'))
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE rule_cursor;
END //
DELIMITER ;

-- 调用存储过程
CALL get_matching_prices();

关键注意事项 #

• SQL注入防护：必须使用参数化传递（如PostgreSQL的USING、MySQL的EXECUTE ... USING），禁止直接拼接参数值到动态SQL中
• 内嵌SQL规范：pricing_table.measures中的SQL逻辑需统一参数引用方式（基于JSON键），确保兼容任意数量的扩展参数
• 多请求场景：若request_table存在多行请求数据，需在外层增加循环，逐个处理每条请求的参数与匹配规则

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