Oracle配置表设计咨询：主键设置与默认值实现需求

这个需求在配置类场景里太常见了——既要灵活支持特定组合的配置，又要避免新增产品时重复造轮子。我给你几个在Oracle里可行的最优方案，你可以根据自己的业务场景选择：

核心思路是把配置分成三级优先级，用占位符替代“所有”的逻辑，避免空值带来的查询困扰：

1.1 表结构设计 #

用*作为“所有国家”或“所有产品”的占位符，同时给COUNTRY和PRODUCT加联合主键，保证配置不重复：

CREATE TABLE PRODUCT_PARAMS (
    COUNTRY VARCHAR2(2) NOT NULL,
    PRODUCT VARCHAR2(50) NOT NULL,
    PARAM1 NUMBER NOT NULL,
    PARAM2 NUMBER NOT NULL,
    PARAM3 NUMBER NOT NULL,
    CONSTRAINT PK_PRODUCT_PARAMS PRIMARY KEY (COUNTRY, PRODUCT)
);

1.2 插入初始配置 #

只需要维护特殊规则的记录，不用给每个产品都加行：

-- 全局默认：所有国家+所有产品
INSERT INTO PRODUCT_PARAMS VALUES ('*', '*', 3, 3, 3);
-- 法国默认：法国+所有产品
INSERT INTO PRODUCT_PARAMS VALUES ('FR', '*', 2, 2, 2);
-- US+Product A 特殊配置
INSERT INTO PRODUCT_PARAMS VALUES ('US', 'Product A', 1, 1, 1);

1.3 查询逻辑（自动匹配优先级） #

用ROW_NUMBER()给配置排序优先级，精确匹配>国家默认>全局默认，取最高优先级的记录：

SELECT PARAM1, PARAM2, PARAM3
FROM (
    SELECT 
        PARAM1, PARAM2, PARAM3,
        ROW_NUMBER() OVER (
            ORDER BY 
                CASE 
                    WHEN COUNTRY = :p_country AND PRODUCT = :p_product THEN 1
                    WHEN COUNTRY = :p_country AND PRODUCT = '*' THEN 2
                    WHEN COUNTRY = '*' AND PRODUCT = '*' THEN 3
                END
        ) AS priority_rank
    FROM PRODUCT_PARAMS
    WHERE (COUNTRY = :p_country OR COUNTRY = '*')
      AND (PRODUCT = :p_product OR PRODUCT = '*')
)
WHERE priority_rank = 1;

方案优点 #

• 新增产品完全不用动表，自动继承对应层级的默认值
• 配置规则清晰，维护成本极低，新增规则只要插一行就行
• 避免空值带来的查询陷阱，逻辑更直观

如果业务层不想写复杂的SQL，可以把匹配逻辑封装成PL/SQL函数，对外提供简单的调用接口：

2.1 先定义参数记录类型 #

CREATE TYPE PRODUCT_PARAM_REC AS OBJECT (
    PARAM1 NUMBER,
    PARAM2 NUMBER,
    PARAM3 NUMBER
);
/

2.2 实现查询函数 #

CREATE OR REPLACE FUNCTION GET_PRODUCT_PARAMS(
    p_country IN VARCHAR2,
    p_product IN VARCHAR2
) RETURN PRODUCT_PARAM_REC IS
    v_result PRODUCT_PARAM_REC;
BEGIN
    -- 1. 先查精确匹配
    SELECT PARAM1, PARAM2, PARAM3 INTO v_result
    FROM PRODUCT_PARAMS
    WHERE COUNTRY = p_country AND PRODUCT = p_product;
    RETURN v_result;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 2. 没找到的话查国家默认
        BEGIN
            SELECT PARAM1, PARAM2, PARAM3 INTO v_result
            FROM PRODUCT_PARAMS
            WHERE COUNTRY = p_country AND PRODUCT = '*';
            RETURN v_result;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 3. 最后返回全局默认
                SELECT PARAM1, PARAM2, PARAM3 INTO v_result
                FROM PRODUCT_PARAMS
                WHERE COUNTRY = '*' AND PRODUCT = '*';
                RETURN v_result;
        END;
END;
/

调用方式 #

业务层直接调用函数即可：

SELECT GET_PRODUCT_PARAMS('FR', 'Product B') FROM DUAL;

方案优点 #

• 业务层无需关心底层逻辑，调用简单
• 逻辑集中在函数里，后续修改规则只要改函数就行

如果想让业务层像查普通表一样用配置，可以把优先级查询逻辑做成视图：

CREATE VIEW VW_PRODUCT_PARAMS AS
SELECT 
    target_country AS COUNTRY,
    target_product AS PRODUCT,
    COALESCE(p.PARAM1, g.PARAM1, global.PARAM1) AS PARAM1,
    COALESCE(p.PARAM2, g.PARAM2, global.PARAM2) AS PARAM2,
    COALESCE(p.PARAM3, g.PARAM3, global.PARAM3) AS PARAM3
FROM (
    -- 这里可以替换成你需要查询的国家/产品列表，或者用笛卡尔积生成所有组合
    SELECT DISTINCT COUNTRY AS target_country FROM SOME_COUNTRY_TABLE
    CROSS JOIN DISTINCT PRODUCT AS target_product FROM SOME_PRODUCT_TABLE
) target
LEFT JOIN PRODUCT_PARAMS p
    ON target.target_country = p.COUNTRY AND target.target_product = p.PRODUCT
LEFT JOIN PRODUCT_PARAMS g
    ON target.target_country = g.COUNTRY AND g.PRODUCT = '*'
LEFT JOIN PRODUCT_PARAMS global
    ON global.COUNTRY = '*' AND global.PRODUCT = '*';

方案优点 #

• 业务层直接查视图，完全感知不到默认逻辑的存在
• 适合需要批量查询所有配置组合的场景

1. 优先用占位符代替NULL：Oracle中NULL参与主键/唯一约束时会有异常（主键不允许全空，唯一约束允许多个NULL），用*更安全直观
2. 确保全局默认记录存在：不管用哪个方案，一定要保证('*', '*')的全局默认记录存在，避免查询返回空值
3. 索引优化：如果配置表数据量很大，给COUNTRY和PRODUCT加联合索引，能大幅提升查询速度

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