基于动态PL-SQL的CONCAT_REL规则匹配用户定价体系实现咨询

若客户匹配CONCAT_REL表中1个或多个定价关联条件，需为其绑定对应的定价体系。

• 用户Rahul选择空运模式、寄送包裹且需要回执，需绑定PRICE_SYSTEM1与PRICE_SYSTEM3
• 用户Rajkumar选择铁路运输、寄送集装箱、送货上门且不要求电话配送通知，需绑定PRICE_SYSTEM1
• 用户Ashok寄送易碎品、送货上门且配送通知为短信或配送地址为办公地址，需绑定PRICE_SYSTEM2

-- CONCAT_REL：定价匹配规则配置表
CREATE TABLE CONCAT_REL (
    UNIQUE_MET VARCHAR2(50),
    UNIQUE_EXPRESION VARCHAR2(500)
);
INSERT INTO CONCAT_REL VALUES ('METHOD1', 'SHIPMENT_MODE = ''AIRWAY'' AND TYP = ''BOX'' OR TYP = ''ENVELOPE''');
INSERT INTO CONCAT_REL VALUES ('METHOD2', 'SHIPMENT_MODE = ''RAIL_ROAD'' AND TYP = ''CONTAINER'' AND DELIVERY_PH <> ''CALL'' AND DELIVERY_TYP = ''HOUSE''');
INSERT INTO CONCAT_REL VALUES ('METHOD3', 'TYP = ''FRAGILE'' AND DELIVERY_PH = ''TEXT'' OR DELIVERY_TYP = ''OFFICE''');
INSERT INTO CONCAT_REL VALUES ('METHOD4', 'ACKNOWLDGE = ''YES''');

-- CON_CHK：规则与定价体系映射表
CREATE TABLE CON_CHK (
    PRICING VARCHAR2(50),
    UNIQUE_MET VARCHAR2(50)
);
INSERT INTO CON_CHK VALUES ('PRICE_SYSTEM1', 'METHOD1');
INSERT INTO CON_CHK VALUES ('PRICE_SYSTEM1', 'METHOD2');
INSERT INTO CON_CHK VALUES ('PRICE_SYSTEM2', 'METHOD3');
INSERT INTO CON_CHK VALUES ('PRICE_SYSTEM3', 'METHOD4');

-- USER_PRICNG：用户定价体系绑定表
CREATE TABLE USER_PRICNG (
    USERS VARCHAR2(50),
    PRICING VARCHAR2(50)
);

实现思路 #

1. 接收用户基本信息及配送属性作为入参
2. 遍历CONCAT_REL配置的所有匹配规则，通过动态SQL判断当前用户属性是否满足规则
3. 关联CON_CHK表获取满足规则对应的定价体系，去重后写入USER_PRICNG表，写入前先清理该用户历史绑定数据避免重复

存储过程代码 #

CREATE OR REPLACE PROCEDURE P_BIND_USER_PRICING(
    P_USER_NAME         IN VARCHAR2,
    P_SHIPMENT_MODE     IN VARCHAR2,
    P_TYP               IN VARCHAR2,
    P_DELIVERY_PH       IN VARCHAR2,
    P_DELIVERY_TYP      IN VARCHAR2,
    P_ACKNOWLDGE        IN VARCHAR2
) AS
    -- 临时变量存储动态SQL、匹配标识
    V_SQL VARCHAR2(1000);
    V_MATCH_FLAG NUMBER;
    -- 定义游标遍历所有规则
    CURSOR CUR_RULE IS
        SELECT UNIQUE_MET, UNIQUE_EXPRESION FROM CONCAT_REL;
BEGIN
    -- 先删除当前用户已绑定的定价体系，按全量覆盖逻辑处理
    DELETE FROM USER_PRICNG WHERE USERS = P_USER_NAME;
    
    -- 遍历所有规则判断是否匹配
    FOR REC_RULE IN CUR_RULE LOOP
        -- 构造动态SQL，将入参代入规则表达式
        V_SQL := 'SELECT 1 FROM DUAL WHERE ' || REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REC_RULE.UNIQUE_EXPRESION,
            'SHIPMENT_MODE', '''' || P_SHIPMENT_MODE || ''''),
            'TYP', '''' || P_TYP || ''''),
            'DELIVERY_PH', '''' || P_DELIVERY_PH || ''''),
            'DELIVERY_TYP', '''' || P_DELIVERY_TYP || ''''),
            'ACKNOWLDGE', '''' || P_ACKNOWLDGE || ''''
        );
        
        -- 执行动态SQL判断是否匹配
        BEGIN
            EXECUTE IMMEDIATE V_SQL INTO V_MATCH_FLAG;
            -- 匹配成功则插入对应定价体系，去重处理
            INSERT INTO USER_PRICNG (USERS, PRICING)
            SELECT DISTINCT P_USER_NAME, PRICING 
            FROM CON_CHK 
            WHERE UNIQUE_MET = REC_RULE.UNIQUE_MET
            AND NOT EXISTS (
                SELECT 1 FROM USER_PRICNG 
                WHERE USERS = P_USER_NAME AND PRICING = CON_CHK.PRICING
            );
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 不匹配规则直接跳过
                NULL;
            WHEN OTHERS THEN
                -- 规则表达式异常可按需打日志，这里跳过
                NULL;
        END;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END P_BIND_USER_PRICING;
/

测试验证 #

-- 测试Rahul场景
BEGIN
    P_BIND_USER_PRICING(
        P_USER_NAME => 'Rahul',
        P_SHIPMENT_MODE => 'AIRWAY',
        P_TYP => 'BOX',
        P_DELIVERY_PH => NULL,
        P_DELIVERY_TYP => NULL,
        P_ACKNOWLDGE => 'YES'
    );
END;
/
-- 查询结果：Rahul绑定PRICE_SYSTEM1、PRICE_SYSTEM3，符合预期

-- 测试Rajkumar场景
BEGIN
    P_BIND_USER_PRICING(
        P_USER_NAME => 'Rajkumar',
        P_SHIPMENT_MODE => 'RAIL_ROAD',
        P_TYP => 'CONTAINER',
        P_DELIVERY_PH => 'TEXT',
        P_DELIVERY_TYP => 'HOUSE',
        P_ACKNOWLDGE => 'NO'
    );
END;
/
-- 查询结果：Rajkumar绑定PRICE_SYSTEM1，符合预期

-- 测试Ashok场景
BEGIN
    P_BIND_USER_PRICING(
        P_USER_NAME => 'Ashok',
        P_SHIPMENT_MODE => NULL,
        P_TYP => 'FRAGILE',
        P_DELIVERY_PH => 'TEXT',
        P_DELIVERY_TYP => 'OFFICE',
        P_ACKNOWLDGE => 'NO'
    );
END;
/
-- 查询结果：Ashok绑定PRICE_SYSTEM2，符合预期

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