sql:Data Modeling Patterns using Oracel 21c
/*
================================================================================
File: Jewelry_ERP_Oracle21c_Full.sql
Database: Oracle 21c
Business Domain: 珠宝行业ERP建模
Cover Patterns:
1. Normalization: 1NF,2NF,3NF,BCNF,4NF,5NF
2. Denormalization 反范式设计
3. Lookup Tables(字典表) & Junction Tables(关联桥接表)
4. Slowly Changing Dimensions SCD Type1 / SCD Type2 / SCD Type3
5. Hierarchical Models:
- Adjacency List 邻接表
- Nested Set 嵌套集模型
- Closure Table 闭包表
6. Multi-Tenancy 多租户策略（Schema隔离 + 行级TenantId两种方案）
Additional: 约束、索引、测试数据、PL/SQL存储过程、EXPLAIN性能分析注释
Author: 珠宝领域数据库建模示例
================================================================================
*/
-- =============================================
-- 0. 清理旧对象，重置环境（生产环境注释掉！仅开发测试使用）
-- =============================================
SET SERVEROUTPUT ON;
SET DEFINE OFF;
DECLARE
v_cnt NUMBER;
BEGIN
-- 删除表
FOR rec IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||rec.table_name||' PURGE';
END LOOP;
-- 删除序列
FOR rec IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE 'DROP SEQUENCE '||rec.sequence_name;
END LOOP;
-- 删除存储过程/函数
FOR rec IN (SELECT object_name FROM user_procedures WHERE object_type IN ('PROCEDURE','FUNCTION')) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP '||rec.object_type||' '||rec.object_name;
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
END;
/
-- =============================================
-- PART1: 范式建模 1NF~5NF + Lookup字典表 Junction桥接表
-- 珠宝业务：宝石品类字典、贵金属材质字典、首饰款式、原料采购
-- =============================================
-- Lookup 字典表（范式设计，1NF基础：原子列，无多值）
CREATE TABLE JEWEL_LOOKUP_STONE_TYPE (
STONE_TYPE_ID NUMBER(10) PRIMARY KEY,
STONE_TYPE_NAME VARCHAR2(100) NOT NULL UNIQUE, -- 钻石/红宝石/蓝宝石/祖母绿
STONE_CATEGORY VARCHAR2(50) NOT NULL,
REMARK VARCHAR2(500)
);
CREATE TABLE JEWEL_LOOKUP_METAL (
METAL_ID NUMBER(10) PRIMARY KEY,
METAL_NAME VARCHAR2(100) NOT NULL UNIQUE, -- 18K金、PT950铂金、足金999
METAL_DENSITY NUMBER(10,4),
REMARK VARCHAR2(500)
);
-- 3NF 主表：宝石原料表（消除传递依赖）
CREATE TABLE JEWEL_STONE_RAW (
STONE_RAW_ID NUMBER(10) PRIMARY KEY,
STONE_TYPE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID),
CARAT_WEIGHT NUMBER(12,4) NOT NULL, -- 克拉重量
COLOR_GRADE VARCHAR2(20),
CLARITY_GRADE VARCHAR2(20),
CERT_NO VARCHAR2(100) UNIQUE, -- GIA证书号
PURCHASE_DATE DATE,
COST_PRICE NUMBER(14,2),
TENANT_ID NUMBER(10) NOT NULL, --多租户行标识
CONSTRAINT CK_STONE_CARAT CHECK (CARAT_WEIGHT > 0)
);
-- 3NF：首饰款式表
CREATE TABLE JEWEL_STYLE (
STYLE_ID NUMBER(10) PRIMARY KEY,
STYLE_CODE VARCHAR2(50) NOT NULL UNIQUE,
STYLE_NAME VARCHAR2(200) NOT NULL,
METAL_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_METAL(METAL_ID),
DESIGNER VARCHAR2(100),
CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
TENANT_ID NUMBER(10) NOT NULL
);
-- Junction桥接表（多对多：一个款式镶嵌多颗宝石，同一种宝石用于多款首饰，4NF消除多值依赖）
CREATE TABLE JEWEL_STYLE_STONE_JUNC (
JUNC_ID NUMBER(10) PRIMARY KEY,
STYLE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_STYLE(STYLE_ID),
STONE_TYPE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID),
STONE_QTY NUMBER(6) NOT NULL,
SINGLE_CARAT NUMBER(12,4) NOT NULL,
CONSTRAINT CK_QTY CHECK (STONE_QTY >= 0),
CONSTRAINT UK_STYLE_STONE UNIQUE(STYLE_ID,STONE_TYPE_ID)
);
/*
范式说明：
1NF：所有列原子化，无数组、逗号分隔多值；CERT_NO单列存储证书
2NF：非主键字段完全依赖主键；桥接表复合主键，属性完全依赖(STYLE_ID,STONE_TYPE_ID)
3NF：消除传递依赖，材质/宝石类型抽离lookup字典表
BCNF：所有非平凡依赖，左键都是主键
4NF：消除多值依赖，款式与宝石多对多拆分为JUNC桥接表
5NF：无损分解，无冗余连接依赖，业务场景下满足5NF
*/
-- =============================================
-- PART2: Denormalization 反范式表（报表查询优化，牺牲写入冗余换取读性能）
-- 珠宝销售汇总表，冗余存储材质名称、宝石类型名称，减少多表JOIN
-- =============================================
CREATE TABLE JEWEL_SALE_DENORM (
SALE_ID NUMBER(10) PRIMARY KEY,
ORDER_NO VARCHAR2(60) NOT NULL,
STYLE_ID NUMBER(10) NOT NULL,
STYLE_NAME VARCHAR2(200) NOT NULL, -- 冗余字段（反范式）
METAL_NAME VARCHAR2(100) NOT NULL, -- 冗余
SALE_AMOUNT NUMBER(16,2) NOT NULL,
SALE_DATE DATE NOT NULL,
TENANT_ID NUMBER(10) NOT NULL
);
-- =============================================
-- PART3: Slowly Changing Dimensions SCD 缓慢变化维度 SCD2（加盟商维度，拉链表）
-- SCD1直接覆盖；SCD2新增版本保留历史；SCD3新增列存储旧值
-- =============================================
CREATE TABLE DIM_FRANCHISE_SCD2 (
FRANCHISE_SK NUMBER(10) PRIMARY KEY, -- 代理键Surrogate Key
FRANCHISE_BK VARCHAR2(50) NOT NULL, -- 业务主键加盟商编码
FRANCHISE_NAME VARCHAR2(200) NOT NULL,
CONTACT_PHONE VARCHAR2(30),
ADDRESS VARCHAR2(500),
REGION VARCHAR2(100),
VALID_START DATE NOT NULL,
VALID_END DATE NOT NULL,
IS_CURRENT CHAR(1) DEFAULT 'Y' CHECK (IS_CURRENT IN ('Y','N')),
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT UK_FRANCHISE_BK_CURR UNIQUE(FRANCHISE_BK,IS_CURRENT)
);
-- SCD2 存储过程：更新加盟商信息，自动拉链新增历史版本
CREATE OR REPLACE PROCEDURE P_SCD2_FRANCHISE_UPSERT(
p_franchise_bk IN VARCHAR2,
p_franchise_name IN VARCHAR2,
p_phone IN VARCHAR2,
p_address IN VARCHAR2,
p_region IN VARCHAR2,
p_tenant_id IN NUMBER
)
IS
v_exists NUMBER;
BEGIN
-- 查找当前有效记录
SELECT COUNT(*) INTO v_exists FROM DIM_FRANCHISE_SCD2
WHERE FRANCHISE_BK = p_franchise_bk AND IS_CURRENT = 'Y' AND TENANT_ID = p_tenant_id;
IF v_exists > 0 THEN
-- 旧版本失效
UPDATE DIM_FRANCHISE_SCD2
SET VALID_END = TRUNC(SYSDATE), IS_CURRENT = 'N'
WHERE FRANCHISE_BK = p_franchise_bk AND IS_CURRENT = 'Y' AND TENANT_ID = p_tenant_id;
END IF;
-- 插入新版本
INSERT INTO DIM_FRANCHISE_SCD2(FRANCHISE_SK,FRANCHISE_BK,FRANCHISE_NAME,CONTACT_PHONE,ADDRESS,REGION,VALID_START,VALID_END,IS_CURRENT,TENANT_ID)
VALUES (SEQ_FRANCHISE_SK.NEXTVAL,p_franchise_bk,p_franchise_name,p_phone,p_address,p_region,TRUNC(SYSDATE),TO_DATE('9999-12-31','YYYY-MM-DD'),'Y',p_tenant_id);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END P_SCD2_FRANCHISE_UPSERT;
/
CREATE SEQUENCE SEQ_FRANCHISE_SK START WITH 1 INCREMENT BY 1 NOCACHE;
-- =============================================
-- PART4: Hierarchical Models 3种层次模型【珠宝产品分类树】
-- 业务：珠宝分类：根(首饰) -> 戒指/项链/耳饰 -> 钻石戒指/素圈金戒...
-- =============================================
-- 4.1 Adjacency List 邻接表（最常用，父子ID）
CREATE TABLE JEWEL_CATEGORY_ADJ (
CAT_ID NUMBER(10) PRIMARY KEY,
CAT_NAME VARCHAR2(100) NOT NULL,
PARENT_ID NUMBER(10),
SORT_ORDER NUMBER(5),
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT FK_CAT_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JEWEL_CATEGORY_ADJ(CAT_ID)
);
-- Oracle 递归查询 CTE 遍历树
/*
WITH CAT_TREE(CAT_ID,CAT_NAME,PARENT_ID,LVL) AS (
SELECT CAT_ID,CAT_NAME,PARENT_ID,1 FROM JEWEL_CATEGORY_ADJ WHERE PARENT_ID IS NULL
UNION ALL
SELECT c.CAT_ID,c.CAT_NAME,c.PARENT_ID,ct.LVL+1 FROM JEWEL_CATEGORY_ADJ c INNER JOIN CAT_TREE ct ON c.PARENT_ID = ct.CAT_ID
)
SELECT * FROM CAT_TREE ORDER BY LVL;
*/
-- 4.2 Nested Set 嵌套集模型（左右值，适合大范围子树查询，插入移动节点代价高）
CREATE TABLE JEWEL_CATEGORY_NESTED (
CAT_ID NUMBER(10) PRIMARY KEY,
CAT_NAME VARCHAR2(100) NOT NULL,
LFT NUMBER(10) NOT NULL,
RGT NUMBER(10) NOT NULL,
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT UK_NESTED_LR UNIQUE(TENANT_ID,LFT,RGT)
);
-- 4.3 Closure Table 闭包表（存储所有祖先-后代关系，查询性能最优，存储占用更大）
CREATE TABLE JEWEL_CATEGORY_CLOSURE (
ANCESTOR_ID NUMBER(10) NOT NULL,
DESCENDANT_ID NUMBER(10) NOT NULL,
DEPTH NUMBER(5) NOT NULL,
TENANT_ID NUMBER(10) NOT NULL,
PRIMARY KEY (ANCESTOR_ID,DESCENDANT_ID,TENANT_ID)
);
-- 分类树移动节点存储过程（闭包表）
CREATE OR REPLACE PROCEDURE P_CATEGORY_MOVE_NODE(p_old_parent IN NUMBER, p_new_parent IN NUMBER, p_cat_id IN NUMBER, p_tenant IN NUMBER)
IS
BEGIN
-- 删除旧祖先链路
DELETE FROM JEWEL_CATEGORY_CLOSURE
WHERE DESCENDANT_ID IN (SELECT DESCENDANT_ID FROM JEWEL_CATEGORY_CLOSURE WHERE ANCESTOR_ID = p_cat_id AND TENANT_ID = p_tenant)
AND ANCESTOR_ID IN (SELECT ANCESTOR_ID FROM JEWEL_CATEGORY_CLOSURE WHERE DESCENDANT_ID = p_cat_id AND TENANT_ID = p_tenant AND ANCESTOR_ID <> p_cat_id);
-- 插入新祖先链路
INSERT INTO JEWEL_CATEGORY_CLOSURE(ANCESTOR_ID,DESCENDANT_ID,DEPTH,TENANT_ID)
SELECT a.ANCESTOR_ID, d.DESCENDANT_ID, a.DEPTH+d.DEPTH+1, p_tenant
FROM JEWEL_CATEGORY_CLOSURE a CROSS JOIN JEWEL_CATEGORY_CLOSURE d
WHERE a.DESCENDANT_ID = p_new_parent AND d.ANCESTOR_ID = p_cat_id AND a.TENANT_ID=p_tenant AND d.TENANT_ID=p_tenant;
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END P_CATEGORY_MOVE_NODE;
/
-- =============================================
-- PART5: Multi-Tenancy 多租户策略（两种方案：行级TenantId；Oracle PDB Schema隔离这里演示行级）
-- 所有业务表统一增加 TENANT_ID，行级隔离；搭配Oracle 21c RLS行级安全策略
-- =============================================
CREATE TABLE JEWEL_TENANT (
TENANT_ID NUMBER(10) PRIMARY KEY,
TENANT_NAME VARCHAR2(200) NOT NULL UNIQUE,
TENANT_TYPE VARCHAR2(50), -- 加盟商/自营门店
CREATE_DATE DATE DEFAULT SYSDATE,
IS_ACTIVE CHAR(1) DEFAULT 'Y' CHECK(IS_ACTIVE IN ('Y','N'))
);
-- RLS行安全策略示例（Oracle 21c）
/*
CREATE OR REPLACE FUNCTION FN_TENANT_FILTER(p_schema IN VARCHAR2, p_obj IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'TENANT_ID = SYS_CONTEXT(''APP_CTX'',''CUR_TENANT_ID'')';
END;
/
ALTER TABLE JEWEL_STONE_RAW ADD POLICY POL_TENANT_ACCESS USING FN_TENANT_FILTER;
*/
-- =============================================
-- PART6: 索引创建 + 测试数据插入
-- =============================================
-- 索引
CREATE INDEX IDX_STONE_TENANT ON JEWEL_STONE_RAW(TENANT_ID);
CREATE INDEX IDX_STYLE_TENANT ON JEWEL_STYLE(TENANT_ID);
CREATE INDEX IDX_SCD2_BK ON DIM_FRANCHISE_SCD2(FRANCHISE_BK,IS_CURRENT);
CREATE INDEX IDX_ADJ_PARENT ON JEWEL_CATEGORY_ADJ(PARENT_ID,TENANT_ID);
CREATE INDEX IDX_NESTED_LR ON JEWEL_CATEGORY_NESTED(TENANT_ID,LFT,RGT);
CREATE INDEX IDX_CLOSURE_ANCESTOR ON JEWEL_CATEGORY_CLOSURE(TENANT_ID,ANCESTOR_ID);
-- 基础字典测试数据
INSERT INTO JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID,STONE_TYPE_NAME,STONE_CATEGORY,REMARK) VALUES (1,'钻石','宝石','天然钻石GIA');
INSERT INTO JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID,STONE_TYPE_NAME,STONE_CATEGORY,REMARK) VALUES (2,'红宝石','彩色宝石','缅甸红宝石');
INSERT INTO JEWEL_LOOKUP_METAL(METAL_ID,METAL_NAME,METAL_DENSITY,REMARK) VALUES (1,'18K黄金',16.2,'75%黄金');
INSERT INTO JEWEL_LOOKUP_METAL(METAL_ID,METAL_NAME,METAL_DENSITY,REMARK) VALUES (2,'PT950铂金',21.45,'铂金首饰');
INSERT INTO JEWEL_TENANT(TENANT_ID,TENANT_NAME,TENANT_TYPE) VALUES (1,'深圳自营总店','SELF');
INSERT INTO JEWEL_TENANT(TENANT_ID,TENANT_NAME,TENANT_TYPE) VALUES (2,'广州加盟商A','FRANCHISE');
COMMIT;
-- 分类树（邻接表示例数据）
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (1,'首饰',NULL,1,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (2,'戒指',1,1,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (3,'项链',1,2,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (4,'钻石戒指',2,1,1);
COMMIT;
-- =============================================
-- PART7: EXPLAIN 性能对比示例：3种层次模型查询
-- Oracle 使用 EXPLAIN PLAN FOR 生成执行计划
-- =============================================
/*
-- 邻接表查询子树
EXPLAIN PLAN FOR
SELECT * FROM JEWEL_CATEGORY_ADJ START WITH PARENT_ID IS NULL CONNECT BY PRIOR CAT_ID = PARENT_ID AND TENANT_ID=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 嵌套集查询全部子节点
EXPLAIN PLAN FOR
SELECT * FROM JEWEL_CATEGORY_NESTED nc
WHERE nc.TENANT_ID=1 AND nc.LFT > 1 AND nc.RGT < 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 闭包表查询后代
EXPLAIN PLAN FOR
SELECT DISTINCT c.DESCENDANT_ID, cat.CAT_NAME
FROM JEWEL_CATEGORY_CLOSURE c
INNER JOIN JEWEL_CATEGORY_ADJ cat ON c.DESCENDANT_ID = cat.CAT_ID
WHERE c.ANCESTOR_ID=1 AND c.TENANT_ID=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
*/
-- =============================================
-- PART8: 附加说明（文档注释）
/*
性能总结对比：
1. Adjacency List 邻接表：写入简单，查询多层子树需要CONNECT BY递归，大数据量性能一般，适合层级不深场景
2. Nested Set嵌套集：子树范围查询极快；新增/移动节点需要大范围更新LFT/RGT，锁开销大
3. Closure Table闭包表：任意祖先后代查询性能最好；存储行数多，插入维护成本中等，企业级分类树首选
多租户两种策略：
①行级租户（本脚本演示）：单库单Schema，全部表加TENANT_ID，RLS行安全隔离；适合租户数量多，数据量中等
②PDB/Schema隔离：Oracle 21c多容器PDB，每个租户独立Schema/PDB；强数据隔离，适合大客户，运维成本高
范式 vs 反范式：OLTP业务主表遵循3NF；报表汇总表使用Denormalization，减少关联，牺牲写入一致性换取查询速度
Lookup表：静态字典，材质、宝石类型；Junction桥接表处理多对多关系，消除多值依赖，满足4NF
SCD2：加盟商维度，拉链表，保留历史变更，用于珠宝数据仓库维度建模
*/
/
/*
================================================================================
File: Jewelry_ERP_Oracle21c_Full_Extended.sql
Database: Oracle 21c
Business Domain: 珠宝行业ERP建模【完整版+扩展模块】
原有覆盖：
1. Normalization 1NF~5NF, Denormalization
2. Lookup/Junction Tables
3. SCD Type1/2 缓慢变化维度
4. Hierarchical Models: Adjacency List / Nested Set / Closure Table
5. Multi-Tenancy + RLS行级安全
【本次追加扩展模块】
6. 审计日志表 + 行版本控制 + 审计触发器
7. 分区表（库存流水按月RANGE分区）
8. 业务存储过程：库存并发扣减、库存回滚、订单取消、工单返工
9. 数据质量校验脚本，脏数据捕获写入错误日志表
10. JewelryDW 数据仓库：星型模型 + 雪花模型
11. ETL PL/SQL：增量抽取 + SCD2拉链同步
12. PL/SQL批量生成海量测试数据（宝石原料、订单、工单）
13. 锁监控、事务隔离、并发冲突分析SQL
14. 数据库账号、角色、数据脱敏视图
15. AWR/执行计划性能对比、压测SQL
================================================================================
*/
SET SERVEROUTPUT ON;
SET DEFINE OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- =============================================
-- 0. 清理旧对象（仅开发测试环境，生产务必删除！）
-- =============================================
DECLARE
v_cnt NUMBER;
BEGIN
-- 删除表
FOR rec IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||rec.table_name||' PURGE';
END LOOP;
-- 删除序列
FOR rec IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE 'DROP SEQUENCE '||rec.sequence_name;
END LOOP;
-- 删除存储过程/函数
FOR rec IN (SELECT object_name FROM user_procedures WHERE object_type IN ('PROCEDURE','FUNCTION')) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP '||rec.object_type||' '||rec.object_name;
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
-- 删除触发器
FOR rec IN (SELECT trigger_name FROM user_triggers) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER '||rec.trigger_name;
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
END;
/
-- =============================================
-- PART1: 范式建模 1NF~5NF + Lookup字典表 Junction桥接表
-- 珠宝业务：宝石品类字典、贵金属材质字典、首饰款式、原料采购
-- =============================================
-- Lookup 字典表（范式设计，1NF基础：原子列，无多值）
CREATE TABLE JEWEL_LOOKUP_STONE_TYPE (
STONE_TYPE_ID NUMBER(10) PRIMARY KEY,
STONE_TYPE_NAME VARCHAR2(100) NOT NULL UNIQUE, -- 钻石/红宝石/蓝宝石/祖母绿
STONE_CATEGORY VARCHAR2(50) NOT NULL,
REMARK VARCHAR2(500)
);
CREATE TABLE JEWEL_LOOKUP_METAL (
METAL_ID NUMBER(10) PRIMARY KEY,
METAL_NAME VARCHAR2(100) NOT NULL UNIQUE, -- 18K金、PT950铂金、足金999
METAL_DENSITY NUMBER(10,4),
REMARK VARCHAR2(500)
);
-- 3NF 主表：宝石原料表（消除传递依赖）
CREATE TABLE JEWEL_STONE_RAW (
STONE_RAW_ID NUMBER(10) PRIMARY KEY,
STONE_TYPE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID),
CARAT_WEIGHT NUMBER(12,4) NOT NULL, -- 克拉重量
COLOR_GRADE VARCHAR2(20),
CLARITY_GRADE VARCHAR2(20),
CERT_NO VARCHAR2(100) UNIQUE, -- GIA证书号
PURCHASE_DATE DATE,
COST_PRICE NUMBER(14,2),
TENANT_ID NUMBER(10) NOT NULL, --多租户行标识
ROW_VERSION NUMBER(10) DEFAULT 1, --乐观锁行版本
CREATE_USER VARCHAR2(60),
CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
UPDATE_USER VARCHAR2(60),
UPDATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
CONSTRAINT CK_STONE_CARAT CHECK (CARAT_WEIGHT > 0)
);
-- 3NF：首饰款式表
CREATE TABLE JEWEL_STYLE (
STYLE_ID NUMBER(10) PRIMARY KEY,
STYLE_CODE VARCHAR2(50) NOT NULL UNIQUE,
STYLE_NAME VARCHAR2(200) NOT NULL,
METAL_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_METAL(METAL_ID),
DESIGNER VARCHAR2(100),
CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
TENANT_ID NUMBER(10) NOT NULL,
ROW_VERSION NUMBER(10) DEFAULT 1
);
-- Junction桥接表（多对多：一个款式镶嵌多颗宝石，同一种宝石用于多款首饰，4NF消除多值依赖）
CREATE TABLE JEWEL_STYLE_STONE_JUNC (
JUNC_ID NUMBER(10) PRIMARY KEY,
STYLE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_STYLE(STYLE_ID),
STONE_TYPE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID),
STONE_QTY NUMBER(6) NOT NULL,
SINGLE_CARAT NUMBER(12,4) NOT NULL,
CONSTRAINT CK_QTY CHECK (STONE_QTY >= 0),
CONSTRAINT UK_STYLE_STONE UNIQUE(STYLE_ID,STONE_TYPE_ID)
);
/*
范式说明：
1NF：所有列原子化，无数组、逗号分隔多值；CERT_NO单列存储证书
2NF：非主键字段完全依赖主键；桥接表复合主键，属性完全依赖(STYLE_ID,STONE_TYPE_ID)
3NF：消除传递依赖，材质/宝石类型抽离lookup字典表
BCNF：所有非平凡依赖，左键都是主键
4NF：消除多值依赖，款式与宝石多对多拆分为JUNC桥接表
5NF：无损分解，无冗余连接依赖，业务场景下满足5NF
*/
-- =============================================
-- PART2: Denormalization 反范式表（报表查询优化，牺牲写入冗余换取读性能）
-- 珠宝销售汇总表，冗余存储材质名称、宝石类型名称，减少多表JOIN
-- =============================================
CREATE TABLE JEWEL_SALE_DENORM (
SALE_ID NUMBER(10) PRIMARY KEY,
ORDER_NO VARCHAR2(60) NOT NULL,
STYLE_ID NUMBER(10) NOT NULL,
STYLE_NAME VARCHAR2(200) NOT NULL, -- 冗余字段（反范式）
METAL_NAME VARCHAR2(100) NOT NULL, -- 冗余
SALE_AMOUNT NUMBER(16,2) NOT NULL,
SALE_DATE DATE NOT NULL,
TENANT_ID NUMBER(10) NOT NULL
);
-- =============================================
-- PART3: Slowly Changing Dimensions SCD 缓慢变化维度 SCD2（加盟商维度，拉链表）
-- SCD1直接覆盖；SCD2新增版本保留历史；SCD3新增列存储旧值
-- =============================================
CREATE SEQUENCE SEQ_FRANCHISE_SK START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE TABLE DIM_FRANCHISE_SCD2 (
FRANCHISE_SK NUMBER(10) PRIMARY KEY, -- 代理键Surrogate Key
FRANCHISE_BK VARCHAR2(50) NOT NULL, -- 业务主键加盟商编码
FRANCHISE_NAME VARCHAR2(200) NOT NULL,
CONTACT_PHONE VARCHAR2(30),
ADDRESS VARCHAR2(500),
REGION VARCHAR2(100),
VALID_START DATE NOT NULL,
VALID_END DATE NOT NULL,
IS_CURRENT CHAR(1) DEFAULT 'Y' CHECK (IS_CURRENT IN ('Y','N')),
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT UK_FRANCHISE_BK_CURR UNIQUE(FRANCHISE_BK,IS_CURRENT)
);
-- SCD2 存储过程：更新加盟商信息，自动拉链新增历史版本
CREATE OR REPLACE PROCEDURE P_SCD2_FRANCHISE_UPSERT(
p_franchise_bk IN VARCHAR2,
p_franchise_name IN VARCHAR2,
p_phone IN VARCHAR2,
p_address IN VARCHAR2,
p_region IN VARCHAR2,
p_tenant_id IN NUMBER
)
IS
v_exists NUMBER;
BEGIN
-- 查找当前有效记录
SELECT COUNT(*) INTO v_exists FROM DIM_FRANCHISE_SCD2
WHERE FRANCHISE_BK = p_franchise_bk AND IS_CURRENT = 'Y' AND TENANT_ID = p_tenant_id;
IF v_exists > 0 THEN
-- 旧版本失效
UPDATE DIM_FRANCHISE_SCD2
SET VALID_END = TRUNC(SYSDATE), IS_CURRENT = 'N'
WHERE FRANCHISE_BK = p_franchise_bk AND IS_CURRENT = 'Y' AND TENANT_ID = p_tenant_id;
END IF;
-- 插入新版本
INSERT INTO DIM_FRANCHISE_SCD2(FRANCHISE_SK,FRANCHISE_BK,FRANCHISE_NAME,CONTACT_PHONE,ADDRESS,REGION,VALID_START,VALID_END,IS_CURRENT,TENANT_ID)
VALUES (SEQ_FRANCHISE_SK.NEXTVAL,p_franchise_bk,p_franchise_name,p_phone,p_address,p_region,TRUNC(SYSDATE),TO_DATE('9999-12-31','YYYY-MM-DD'),'Y',p_tenant_id);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END P_SCD2_FRANCHISE_UPSERT;
/
-- =============================================
-- PART4: Hierarchical Models 3种层次模型【珠宝产品分类树】
-- 业务：珠宝分类：根(首饰) -> 戒指/项链/耳饰 -> 钻石戒指/素圈金戒...
-- =============================================
-- 4.1 Adjacency List 邻接表（最常用，父子ID）
CREATE TABLE JEWEL_CATEGORY_ADJ (
CAT_ID NUMBER(10) PRIMARY KEY,
CAT_NAME VARCHAR2(100) NOT NULL,
PARENT_ID NUMBER(10),
SORT_ORDER NUMBER(5),
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT FK_CAT_PARENT FOREIGN KEY(PARENT_ID) REFERENCES JEWEL_CATEGORY_ADJ(CAT_ID)
);
-- Oracle 递归查询 CTE 遍历树
/*
WITH CAT_TREE(CAT_ID,CAT_NAME,PARENT_ID,LVL) AS (
SELECT CAT_ID,CAT_NAME,PARENT_ID,1 FROM JEWEL_CATEGORY_ADJ WHERE PARENT_ID IS NULL
UNION ALL
SELECT c.CAT_ID,c.CAT_NAME,c.PARENT_ID,ct.LVL+1 FROM JEWEL_CATEGORY_ADJ c INNER JOIN CAT_TREE ct ON c.PARENT_ID = ct.CAT_ID
)
SELECT * FROM CAT_TREE ORDER BY LVL;
*/
-- 4.2 Nested Set 嵌套集模型（左右值，适合大范围子树查询，插入移动节点代价高）
CREATE TABLE JEWEL_CATEGORY_NESTED (
CAT_ID NUMBER(10) PRIMARY KEY,
CAT_NAME VARCHAR2(100) NOT NULL,
LFT NUMBER(10) NOT NULL,
RGT NUMBER(10) NOT NULL,
TENANT_ID NUMBER(10) NOT NULL,
CONSTRAINT UK_NESTED_LR UNIQUE(TENANT_ID,LFT,RGT)
);
-- 4.3 Closure Table 闭包表（存储所有祖先-后代关系，查询性能最优，存储占用更大）
CREATE TABLE JEWEL_CATEGORY_CLOSURE (
ANCESTOR_ID NUMBER(10) NOT NULL,
DESCENDANT_ID NUMBER(10) NOT NULL,
DEPTH NUMBER(5) NOT NULL,
TENANT_ID NUMBER(10) NOT NULL,
PRIMARY KEY (ANCESTOR_ID,DESCENDANT_ID,TENANT_ID)
);
-- 分类树移动节点存储过程（闭包表）
CREATE OR REPLACE PROCEDURE P_CATEGORY_MOVE_NODE(p_old_parent IN NUMBER, p_new_parent IN NUMBER, p_cat_id IN NUMBER, p_tenant IN NUMBER)
IS
BEGIN
-- 删除旧祖先链路
DELETE FROM JEWEL_CATEGORY_CLOSURE
WHERE DESCENDANT_ID IN (SELECT DESCENDANT_ID FROM JEWEL_CATEGORY_CLOSURE WHERE ANCESTOR_ID = p_cat_id AND TENANT_ID = p_tenant)
AND ANCESTOR_ID IN (SELECT ANCESTOR_ID FROM JEWEL_CATEGORY_CLOSURE WHERE DESCENDANT_ID = p_cat_id AND TENANT_ID = p_tenant AND ANCESTOR_ID <> p_cat_id);
-- 插入新祖先链路
INSERT INTO JEWEL_CATEGORY_CLOSURE(ANCESTOR_ID,DESCENDANT_ID,DEPTH,TENANT_ID)
SELECT a.ANCESTOR_ID, d.DESCENDANT_ID, a.DEPTH+d.DEPTH+1, p_tenant
FROM JEWEL_CATEGORY_CLOSURE a CROSS JOIN JEWEL_CATEGORY_CLOSURE d
WHERE a.DESCENDANT_ID = p_new_parent AND d.ANCESTOR_ID = p_cat_id AND a.TENANT_ID=p_tenant AND d.TENANT_ID=p_tenant;
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END P_CATEGORY_MOVE_NODE;
/
-- =============================================
-- PART5: Multi-Tenancy 多租户策略（行级TenantId + Oracle RLS行级安全）
-- =============================================
CREATE TABLE JEWEL_TENANT (
TENANT_ID NUMBER(10) PRIMARY KEY,
TENANT_NAME VARCHAR2(200) NOT NULL UNIQUE,
TENANT_TYPE VARCHAR2(50), -- 加盟商/自营门店
CREATE_DATE DATE DEFAULT SYSDATE,
IS_ACTIVE CHAR(1) DEFAULT 'Y' CHECK(IS_ACTIVE IN ('Y','N'))
);
-- RLS上下文函数（注释，需要DBA权限启用）
/*
CREATE OR REPLACE FUNCTION FN_TENANT_FILTER(p_schema IN VARCHAR2, p_obj IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'TENANT_ID = SYS_CONTEXT(''APP_CTX'',''CUR_TENANT_ID'')';
END;
/
ALTER TABLE JEWEL_STONE_RAW ADD POLICY POL_TENANT_ACCESS USING FN_TENANT_FILTER;
*/
-- =============================================
-- =====================【扩展模块开始】=====================
-- PART6: 审计日志表 + 审计触发器、行版本控制
-- =============================================
CREATE TABLE JEWEL_AUDIT_LOG (
AUDIT_ID NUMBER(12) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TABLE_NAME VARCHAR2(120) NOT NULL,
OP_TYPE VARCHAR2(20) NOT NULL, -- INSERT/UPDATE/DELETE
RECORD_KEY VARCHAR2(200),
OLD_DATA CLOB,
NEW_DATA CLOB,
OPERATOR_USER VARCHAR2(80),
OPERATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
TENANT_ID NUMBER(10)
);
-- 数据质量错误日志表（ETL脏数据捕获）
CREATE TABLE JEWEL_ETL_ERROR_LOG (
ERROR_ID NUMBER(12) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
SOURCE_TABLE VARCHAR2(100),
ERROR_MSG VARCHAR2(1000),
ERROR_DATA CLOB,
ERROR_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
TENANT_ID NUMBER(10)
);
-- 审计触发器：宝石原料表，记录变更，自动递增ROW_VERSION乐观锁
CREATE OR REPLACE TRIGGER TRG_JEWEL_STONE_RAW_AUDIT
BEFORE UPDATE ON JEWEL_STONE_RAW
FOR EACH ROW
BEGIN
:NEW.ROW_VERSION := :OLD.ROW_VERSION + 1;
:NEW.UPDATE_TIME := SYSTIMESTAMP;
:NEW.UPDATE_USER := USER;
END;
/
CREATE OR REPLACE TRIGGER TRG_JEWEL_STONE_RAW_AUDIT_AFTER
AFTER INSERT OR UPDATE OR DELETE ON JEWEL_STONE_RAW
FOR EACH ROW
DECLARE
v_old CLOB;
v_new CLOB;
BEGIN
IF INSERTING THEN
v_new := 'STONE_RAW_ID:'||:NEW.STONE_RAW_ID||',CERT_NO:'||:NEW.CERT_NO;
INSERT INTO JEWEL_AUDIT_LOG(TABLE_NAME,OP_TYPE,RECORD_KEY,OLD_DATA,NEW_DATA,OPERATOR_USER,TENANT_ID)
VALUES('JEWEL_STONE_RAW','INSERT',:NEW.STONE_RAW_ID,NULL,v_new,USER,:NEW.TENANT_ID);
ELSIF UPDATING THEN
v_old := 'STONE_RAW_ID:'||:OLD.STONE_RAW_ID||',旧证书:'||:OLD.CERT_NO;
v_new := 'STONE_RAW_ID:'||:NEW.STONE_RAW_ID||',新证书:'||:NEW.CERT_NO;
INSERT INTO JEWEL_AUDIT_LOG(TABLE_NAME,OP_TYPE,RECORD_KEY,OLD_DATA,NEW_DATA,OPERATOR_USER,TENANT_ID)
VALUES('JEWEL_STONE_RAW','UPDATE',:NEW.STONE_RAW_ID,v_old,v_new,USER,:NEW.TENANT_ID);
ELSIF DELETING THEN
v_old := 'STONE_RAW_ID:'||:OLD.STONE_RAW_ID||',CERT_NO:'||:OLD.CERT_NO;
INSERT INTO JEWEL_AUDIT_LOG(TABLE_NAME,OP_TYPE,RECORD_KEY,OLD_DATA,NEW_DATA,OPERATOR_USER,TENANT_ID)
VALUES('JEWEL_STONE_RAW','DELETE',:OLD.STONE_RAW_ID,v_old,NULL,USER,:OLD.TENANT_ID);
END IF;
END;
/
-- =============================================
-- PART7: 库存表 + 按月RANGE分区表（Oracle分区）、订单、工单表
-- =============================================
-- 库存主表
CREATE TABLE JEWEL_INVENTORY (
INV_ID NUMBER(10) PRIMARY KEY,
STYLE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_STYLE(STYLE_ID),
STOCK_QTY NUMBER(10) NOT NULL DEFAULT 0,
COST_AMOUNT NUMBER(16,2),
TENANT_ID NUMBER(10) NOT NULL,
ROW_VERSION NUMBER(10) DEFAULT 1,
CONSTRAINT CK_STOCK_QTY CHECK (STOCK_QTY >=0)
);
-- 库存流水表【按月RANGE分区】
CREATE TABLE JEWEL_INV_TRANS (
TRANS_ID NUMBER(12) PRIMARY KEY,
STYLE_ID NUMBER(10) NOT NULL,
TRANS_TYPE VARCHAR2(20) NOT NULL, --IN/OUT/RETURN
TRANS_QTY NUMBER(10) NOT NULL,
TRANS_DATE DATE NOT NULL,
ORDER_NO VARCHAR2(60),
TENANT_ID NUMBER(10) NOT NULL
)
PARTITION BY RANGE (TRANS_DATE) (
PARTITION P202601 VALUES LESS THAN (TO_DATE('2026-02-01','YYYY-MM-DD')),
PARTITION P202602 VALUES LESS THAN (TO_DATE('2026-03-01','YYYY-MM-DD')),
PARTITION P202603 VALUES LESS THAN (TO_DATE('2026-04-01','YYYY-MM-DD')),
PARTITION P202604 VALUES LESS THAN (TO_DATE('2026-05-01','YYYY-MM-DD')),
PARTITION P202605 VALUES LESS THAN (TO_DATE('2026-06-01','YYYY-MM-DD')),
PARTITION P202606 VALUES LESS THAN (TO_DATE('2026-07-01','YYYY-MM-DD')),
PARTITION P202607 VALUES LESS THAN (TO_DATE('2026-08-01','YYYY-MM-DD')),
PARTITION P202608 VALUES LESS THAN (TO_DATE('2026-09-01','YYYY-MM-DD')),
PARTITION P202609 VALUES LESS THAN (TO_DATE('2026-10-01','YYYY-MM-DD')),
PARTITION P_FUTURE VALUES LESS THAN (TO_DATE('9999-12-31','YYYY-MM-DD'))
);
-- 销售订单
CREATE TABLE JEWEL_SALE_ORDER (
ORDER_ID NUMBER(10) PRIMARY KEY,
ORDER_NO VARCHAR2(60) NOT NULL UNIQUE,
FRANCHISE_BK VARCHAR2(50),
ORDER_DATE DATE NOT NULL,
TOTAL_AMOUNT NUMBER(16,2),
ORDER_STATUS VARCHAR2(20) NOT NULL, --NEW/PAID/CANCELLED/FINISH
TENANT_ID NUMBER(10) NOT NULL,
ROW_VERSION NUMBER(10) DEFAULT 1
);
-- 加工工单
CREATE TABLE JEWEL_WORK_ORDER (
WO_ID NUMBER(10) PRIMARY KEY,
WO_NO VARCHAR2(60) NOT NULL UNIQUE,
STYLE_ID NUMBER(10) NOT NULL REFERENCES JEWEL_STYLE(STYLE_ID),
WO_QTY NUMBER(10),
WO_STATUS VARCHAR2(20), --CREATING/IN_PROCESS/REWORK/FINISH
CREATE_DATE DATE DEFAULT SYSDATE,
TENANT_ID NUMBER(10) NOT NULL
);
-- =============================================
-- PART8: 业务PL/SQL：库存并发扣减、库存回滚、订单取消、工单返工
-- =============================================
-- 库存扣减（乐观锁，并发场景，加盟商下单扣库存）
CREATE OR REPLACE PROCEDURE P_INV_DEDUCT(
p_style_id IN NUMBER,
p_qty IN NUMBER,
p_tenant_id IN NUMBER,
p_order_no IN VARCHAR2,
o_result OUT VARCHAR2
)
IS
v_rowver NUMBER;
v_stock NUMBER;
BEGIN
o_result := 'SUCCESS';
SELECT STOCK_QTY,ROW_VERSION INTO v_stock, v_rowver
FROM JEWEL_INVENTORY
WHERE STYLE_ID = p_style_id AND TENANT_ID = p_tenant_id FOR UPDATE;
IF v_stock < p_qty THEN
RAISE_APPLICATION_ERROR(-20001,'库存不足');
END IF;
UPDATE JEWEL_INVENTORY
SET STOCK_QTY = STOCK_QTY - p_qty, ROW_VERSION=ROW_VERSION+1
WHERE STYLE_ID = p_style_id AND TENANT_ID = p_tenant_id AND ROW_VERSION = v_rowver;
--写入库存流水
INSERT INTO JEWEL_INV_TRANS(TRANS_ID,STYLE_ID,TRANS_TYPE,TRANS_QTY,TRANS_DATE,ORDER_NO,TENANT_ID)
VALUES (SEQ_INV_TRANS.NEXTVAL,p_style_id,'OUT',p_qty,SYSDATE,p_order_no,p_tenant_id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
o_result := SQLERRM;
END P_INV_DEDUCT;
/
CREATE SEQUENCE SEQ_INV_TRANS START WITH 1 INCREMENT BY 1 NOCACHE;
-- 订单取消：库存回滚补偿
CREATE OR REPLACE PROCEDURE P_ORDER_CANCEL(p_order_no IN VARCHAR2, p_tenant_id IN NUMBER, o_msg OUT VARCHAR2)
IS
BEGIN
o_msg := 'OK';
UPDATE JEWEL_SALE_ORDER SET ORDER_STATUS='CANCELLED' WHERE ORDER_NO=p_order_no AND TENANT_ID=p_tenant_id;
-- 库存归还（补偿逻辑）
INSERT INTO JEWEL_INV_TRANS(TRANS_ID,STYLE_ID,TRANS_TYPE,TRANS_QTY,TRANS_DATE,ORDER_NO,TENANT_ID)
SELECT SEQ_INV_TRANS.NEXTVAL,si.STYLE_ID,'RETURN',soi.TRANS_QTY,SYSDATE,p_order_no,p_tenant_id
FROM JEWEL_INV_TRANS soi
WHERE soi.ORDER_NO = p_order_no AND soi.TRANS_TYPE='OUT' AND soi.TENANT_ID=p_tenant_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; o_msg:=SQLERRM; RAISE;
END P_ORDER_CANCEL;
/
-- 工单返工存储过程
CREATE OR REPLACE PROCEDURE P_WO_REWORK(p_wo_no IN VARCHAR2, p_tenant_id IN NUMBER, o_msg OUT VARCHAR2)
IS
BEGIN
UPDATE JEWEL_WORK_ORDER SET WO_STATUS='REWORK' WHERE WO_NO = p_wo_no AND TENANT_ID=p_tenant_id;
COMMIT;
o_msg := '工单标记返工成功';
EXCEPTION WHEN OTHERS THEN ROLLBACK; o_msg:=SQLERRM; RAISE;
END P_WO_REWORK;
/
-- =============================================
-- PART9: 数据质量校验存储过程，脏数据写入ETL_ERROR_LOG
-- 校验规则：克拉>0、库存不能负数、订单金额>0、证书号非空
-- =============================================
CREATE OR REPLACE PROCEDURE P_DATA_QUALITY_CHECK(p_tenant_id IN NUMBER)
IS
BEGIN
--规则1：宝石原料克拉<=0脏数据
INSERT INTO JEWEL_ETL_ERROR_LOG(SOURCE_TABLE,ERROR_MSG,ERROR_DATA,TENANT_ID)
SELECT 'JEWEL_STONE_RAW','克拉重量必须大于0','STONE_RAW_ID:'||STONE_RAW_ID||',CARAT:'||CARAT_WEIGHT, TENANT_ID
FROM JEWEL_STONE_RAW WHERE CARAT_WEIGHT <=0 AND TENANT_ID = p_tenant_id;
--规则2：库存负数脏数据
INSERT INTO JEWEL_ETL_ERROR_LOG(SOURCE_TABLE,ERROR_MSG,ERROR_DATA,TENANT_ID)
SELECT 'JEWEL_INVENTORY','库存数量为负数','INV_ID:'||INV_ID||',STOCK_QTY:'||STOCK_QTY, TENANT_ID
FROM JEWEL_INVENTORY WHERE STOCK_QTY <0 AND TENANT_ID = p_tenant_id;
--规则3：订单总金额<=0
INSERT INTO JEWEL_ETL_ERROR_LOG(SOURCE_TABLE,ERROR_MSG,ERROR_DATA,TENANT_ID)
SELECT 'JEWEL_SALE_ORDER','订单金额必须大于0','ORDER_NO:'||ORDER_NO||',AMOUNT:'||TOTAL_AMOUNT,TENANT_ID
FROM JEWEL_SALE_ORDER WHERE TOTAL_AMOUNT <=0 AND TENANT_ID = p_tenant_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END P_DATA_QUALITY_CHECK;
/
-- =============================================
-- PART10: JewelryDW 数据仓库 星型模型 + 雪花模型
-- 星型：事实表 + 维度表；雪花：维度继续拆分子维度
-- =============================================
-- DW 维度表
CREATE TABLE DW_DIM_DATE (
DATE_SK NUMBER(10) PRIMARY KEY,
CAL_DATE DATE NOT NULL UNIQUE,
YEAR_NUM NUMBER(4),
MONTH_NUM NUMBER(2),
DAY_NUM NUMBER(2),
QUARTER_NUM NUMBER(1),
IS_WEEKEND CHAR(1)
);
CREATE TABLE DW_DIM_STYLE (
STYLE_SK NUMBER(10) PRIMARY KEY,
STYLE_BK VARCHAR2(50),
STYLE_NAME VARCHAR2(200),
METAL_NAME VARCHAR2(100),
TENANT_ID NUMBER(10),
VALID_START DATE,
VALID_END DATE,
IS_CURRENT CHAR(1)
);
-- 星型模型：销售事实表
CREATE TABLE DW_FACT_SALE_STAR (
SALE_SK NUMBER(12) PRIMARY KEY,
DATE_SK NUMBER(10) REFERENCES DW_DIM_DATE(DATE_SK),
STYLE_SK NUMBER(10) REFERENCES DW_DIM_STYLE(STYLE_SK),
FRANCHISE_SK NUMBER(10),
SALE_QTY NUMBER(10),
SALE_AMOUNT NUMBER(16,2),
TENANT_ID NUMBER(10)
);
-- 雪花模型：维度拆分子表
CREATE TABLE DW_DIM_METAL (
METAL_SK NUMBER(10) PRIMARY KEY,
METAL_NAME VARCHAR2(100) UNIQUE
);
CREATE TABLE DW_DIM_STYLE_SNOW (
STYLE_SK NUMBER(10) PRIMARY KEY,
STYLE_BK VARCHAR2(50),
STYLE_NAME VARCHAR2(200),
METAL_SK NUMBER(10) REFERENCES DW_DIM_METAL(METAL_SK),
TENANT_ID NUMBER(10),
VALID_START DATE,
VALID_END DATE,
IS_CURRENT CHAR(1)
);
CREATE TABLE DW_FACT_SALE_SNOW (
SALE_SK NUMBER(12) PRIMARY KEY,
DATE_SK NUMBER(10) REFERENCES DW_DIM_DATE(DATE_SK),
STYLE_SK NUMBER(10) REFERENCES DW_DIM_STYLE_SNOW(STYLE_SK),
FRANCHISE_SK NUMBER(10),
SALE_QTY NUMBER(10),
SALE_AMOUNT NUMBER(16,2),
TENANT_ID NUMBER(10)
);
-- ETL控制表，记录增量抽取点位
CREATE TABLE ETL_CTRL (
ETL_ID NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
TARGET_TABLE VARCHAR2(100),
LAST_EXTRACT_DATE DATE,
RUN_STATUS VARCHAR2(30),
RUN_TIME TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- ETL存储过程：增量抽取OLTP到DW，SCD2拉链同步款式维度
CREATE OR REPLACE PROCEDURE P_ETL_JEWEL_INCREMENTAL(p_tenant_id IN NUMBER)
IS
v_last_extract DATE;
BEGIN
SELECT MAX(LAST_EXTRACT_DATE) INTO v_last_extract FROM ETL_CTRL WHERE TARGET_TABLE='DW_FACT_SALE_STAR';
IF v_last_extract IS NULL THEN v_last_extract := TO_DATE('2026-01-01','YYYY-MM-DD'); END IF;
--增量抽取销售事实
INSERT INTO DW_FACT_SALE_STAR(SALE_SK,DATE_SK,STYLE_SK,FRANCHISE_SK,SALE_QTY,SALE_AMOUNT,TENANT_ID)
SELECT SEQ_DW_SALE.NEXTVAL,dd.DATE_SK,ds.STYLE_SK,0,1,so.TOTAL_AMOUNT,so.TENANT_ID
FROM JEWEL_SALE_ORDER so
JOIN DW_DIM_DATE dd ON TRUNC(so.ORDER_DATE)=dd.CAL_DATE
LEFT JOIN DW_DIM_STYLE ds ON so.TENANT_ID=ds.TENANT_ID
WHERE so.ORDER_DATE > v_last_extract AND so.TENANT_ID = p_tenant_id;
--更新ETL点位
INSERT INTO ETL_CTRL(TARGET_TABLE,LAST_EXTRACT_DATE,RUN_STATUS)
VALUES('DW_FACT_SALE_STAR',TRUNC(SYSDATE),'SUCCESS');
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END P_ETL_JEWEL_INCREMENTAL;
/
CREATE SEQUENCE SEQ_DW_SALE START WITH 1 INCREMENT BY 1 NOCACHE;
-- =============================================
-- PART11: PL/SQL批量生成海量测试数据（宝石原料、订单、工单）
-- =============================================
CREATE OR REPLACE PROCEDURE P_GENERATE_BULK_TEST_DATA(p_tenant IN NUMBER, p_rows IN NUMBER)
IS
BEGIN
FOR i IN 1..p_rows LOOP
INSERT INTO JEWEL_STONE_RAW(STONE_RAW_ID,STONE_TYPE_ID,CARAT_WEIGHT,COLOR_GRADE,CLARITY_GRADE,CERT_NO,PURCHASE_DATE,COST_PRICE,TENANT_ID)
VALUES(SEQ_STONE.NEXTVAL, MOD(i,2)+1, DBMS_RANDOM.VALUE(0.1,5),'D','VS1','GIA'||SEQ_STONE.CURRVAL, SYSDATE - MOD(i,30), DBMS_RANDOM.VALUE(1000,50000),p_tenant);
END LOOP;
COMMIT;
END P_GENERATE_BULK_TEST_DATA;
/
CREATE SEQUENCE SEQ_STONE START WITH 1 INCREMENT BY 1 NOCACHE;
-- =============================================
-- PART12: 索引优化
-- =============================================
CREATE INDEX IDX_STONE_TENANT ON JEWEL_STONE_RAW(TENANT_ID);
CREATE INDEX IDX_STYLE_TENANT ON JEWEL_STYLE(TENANT_ID);
CREATE INDEX IDX_SCD2_BK ON DIM_FRANCHISE_SCD2(FRANCHISE_BK,IS_CURRENT);
CREATE INDEX IDX_ADJ_PARENT ON JEWEL_CATEGORY_ADJ(PARENT_ID,TENANT_ID);
CREATE INDEX IDX_NESTED_LR ON JEWEL_CATEGORY_NESTED(TENANT_ID,LFT,RGT);
CREATE INDEX IDX_CLOSURE_ANCESTOR ON JEWEL_CATEGORY_CLOSURE(TENANT_ID,ANCESTOR_ID);
CREATE INDEX IDX_INV_STYLE ON JEWEL_INVENTORY(STYLE_ID,TENANT_ID);
CREATE INDEX IDX_INV_TRANS_DATE ON JEWEL_INV_TRANS(TRANS_DATE,TENANT_ID);
CREATE INDEX IDX_SALE_ORDER_DATE ON JEWEL_SALE_ORDER(ORDER_DATE,TENANT_ID);
-- =============================================
-- PART13: 基础字典测试数据
-- =============================================
INSERT INTO JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID,STONE_TYPE_NAME,STONE_CATEGORY,REMARK) VALUES (1,'钻石','宝石','天然钻石GIA');
INSERT INTO JEWEL_LOOKUP_STONE_TYPE(STONE_TYPE_ID,STONE_TYPE_NAME,STONE_CATEGORY,REMARK) VALUES (2,'红宝石','彩色宝石','缅甸红宝石');
INSERT INTO JEWEL_LOOKUP_METAL(METAL_ID,METAL_NAME,METAL_DENSITY,REMARK) VALUES (1,'18K黄金',16.2,'75%黄金');
INSERT INTO JEWEL_LOOKUP_METAL(METAL_ID,METAL_NAME,METAL_DENSITY,REMARK) VALUES (2,'PT950铂金',21.45,'铂金首饰');
INSERT INTO JEWEL_TENANT(TENANT_ID,TENANT_NAME,TENANT_TYPE) VALUES (1,'深圳自营总店','SELF');
INSERT INTO JEWEL_TENANT(TENANT_ID,TENANT_NAME,TENANT_TYPE) VALUES (2,'广州加盟商A','FRANCHISE');
COMMIT;
-- 分类树（邻接表示例数据）
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (1,'首饰',NULL,1,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (2,'戒指',1,1,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (3,'项链',1,2,1);
INSERT INTO JEWEL_CATEGORY_ADJ(CAT_ID,CAT_NAME,PARENT_ID,SORT_ORDER,TENANT_ID) VALUES (4,'钻石戒指',2,1,1);
COMMIT;
-- =============================================
-- PART14: EXPLAIN执行计划：3种层次模型性能对比
-- Oracle 使用EXPLAIN PLAN FOR
-- =============================================
/*
-- 邻接表查询子树
EXPLAIN PLAN FOR
SELECT * FROM JEWEL_CATEGORY_ADJ START WITH PARENT_ID IS NULL CONNECT BY PRIOR CAT_ID = PARENT_ID AND TENANT_ID=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 嵌套集查询全部子节点
EXPLAIN PLAN FOR
SELECT * FROM JEWEL_CATEGORY_NESTED nc
WHERE nc.TENANT_ID=1 AND nc.LFT > 1 AND nc.RGT < 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 闭包表查询后代
EXPLAIN PLAN FOR
SELECT DISTINCT c.DESCENDANT_ID, cat.CAT_NAME
FROM JEWEL_CATEGORY_CLOSURE c
INNER JOIN JEWEL_CATEGORY_ADJ cat ON c.DESCENDANT_ID = cat.CAT_ID
WHERE c.ANCESTOR_ID=1 AND c.TENANT_ID=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
*/
-- =============================================
-- PART15: 数据库权限、角色、数据脱敏视图
-- =============================================
-- 创建业务只读角色
CREATE ROLE ROLE_JEWEL_READ;
GRANT SELECT ON JEWEL_STONE_RAW,JEWEL_STYLE,DIM_FRANCHISE_SCD2 TO ROLE_JEWEL_READ;
-- 脱敏视图：客户/加盟商手机号脱敏
CREATE OR REPLACE VIEW V_FRANCHISE_MASK AS
SELECT FRANCHISE_SK,FRANCHISE_BK,FRANCHISE_NAME,
REGEXP_REPLACE(CONTACT_PHONE,'(^.{3})(.*)(.{4}$)','\1****\3') AS CONTACT_PHONE_MASK,
ADDRESS,REGION,VALID_START,VALID_END,IS_CURRENT,TENANT_ID
FROM DIM_FRANCHISE_SCD2;
-- =============================================
-- PART16: 锁监控SQL，并发库存扣减压测查询，AWR压测说明
-- =============================================
/*
-- 查看锁等待
SELECT SID,SERIAL#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT
FROM V$SESSION WHERE STATE='WAITING';
--查看行锁
SELECT SID,OBJECT_NAME,LOCKED_MODE FROM V$LOCKED_OBJECT lo
JOIN ALL_OBJECTS ao ON lo.OBJECT_ID=ao.OBJECT_ID;
--AWR压测：并发调用P_INV_DEDUCT模拟加盟商下单扣库存，生成AWR报告
--exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
--并发压测执行完成后再创建快照，生成AWR
--exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
--SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(...));
*/
-- =============================================
-- PART17: BI报表SQL示例
-- =============================================
/*
--珠宝销售分析
SELECT dd.YEAR_NUM,dd.MONTH_NUM, SUM(fs.SALE_AMOUNT) AS TOTAL_SALE
FROM DW_FACT_SALE_STAR fs
JOIN DW_DIM_DATE dd ON fs.DATE_SK=dd.DATE_SK
GROUP BY dd.YEAR_NUM,dd.MONTH_NUM ORDER BY 1,2;
--库存周转
SELECT s.STYLE_NAME, inv.STOCK_QTY, SUM(trans.TRANS_QTY) AS OUT_QTY
FROM JEWEL_INVENTORY inv
JOIN JEWEL_STYLE s ON inv.STYLE_ID=s.STYLE_ID
LEFT JOIN JEWEL_INV_TRANS trans ON inv.STYLE_ID=trans.STYLE_ID
GROUP BY s.STYLE_NAME,inv.STOCK_QTY;
*/
-- =============================================
-- 文档注释：性能总结
/*
性能总结对比：
1. Adjacency List 邻接表：写入简单，查询多层子树需要CONNECT BY递归，大数据量性能一般，适合层级不深场景
2. Nested Set嵌套集：子树范围查询极快；新增/移动节点需要大范围更新LFT/RGT，锁开销大，并发差
3. Closure Table闭包表：任意祖先后代查询性能最好；存储行数多，插入维护成本中等，企业级分类树首选
多租户两种策略：
①行级租户（本脚本演示）：单库单Schema，全部表加TENANT_ID，RLS行安全隔离；适合租户数量多，数据量中等
②PDB/Schema隔离：Oracle 21c多容器PDB，每个租户独立Schema/PDB；强数据隔离，适合大客户，运维成本高
范式 vs 反范式：OLTP业务主表遵循3NF；报表汇总表使用Denormalization，减少关联，牺牲写入一致性换取查询速度
Lookup表：静态字典，材质、宝石类型；Junction桥接表处理多对多关系，消除多值依赖，满足4NF
SCD2：加盟商维度，拉链表，保留历史变更，用于珠宝数据仓库维度建模
扩展模块：
审计触发器自动记录表变更+乐观锁行版本；库存流水按月分区提升大表查询性能；
PL/SQL存储过程处理库存扣减、订单取消补偿、工单返工；数据质量校验捕获脏数据写入ETL错误日志；
DW星型+雪花模型；ETL增量SCD2拉链；批量PL/SQL生成测试数据；RLS行安全、手机号脱敏视图；
锁监控SQL用于并发压测库存扣减场景；AWR可采集压测性能指标。
*/
/
-- =============================================
-- PART18: AWR压测脚本 PL/SQL 并发循环调用库存扣减存储过程
-- 场景：多加盟商并发下单扣减珠宝库存，生成AWR性能报告
-- 说明：
-- 1. 先创建AWR快照，启动多会话并发压测
-- 2. 压测完成后再创建快照，生成AWR HTML报告
-- 3. 多会话建议使用sqlplus多窗口 / DBMS_PARALLEL_EXECUTE 或者外部并发工具
-- =============================================
-- 【1】单会话循环压测存储过程：模拟单个加盟商持续下单扣库存
CREATE OR REPLACE PROCEDURE P_LOADTEST_INV_DEDUCT_SINGLE(
p_style_id IN NUMBER,
p_tenant_id IN NUMBER,
p_loop_count IN NUMBER,
p_base_order_no IN VARCHAR2
)
IS
v_result VARCHAR2(1000);
v_order_no VARCHAR2(120);
BEGIN
FOR i IN 1..p_loop_count LOOP
v_order_no := p_base_order_no || '_' || TO_CHAR(i);
BEGIN
P_INV_DEDUCT(
p_style_id => p_style_id,
p_qty => 1,
p_tenant_id=> p_tenant_id,
p_order_no => v_order_no,
o_result => v_result
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('订单:'||v_order_no||' 异常:'||SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('单会话压测完成，循环次数：'||p_loop_count);
END P_LOADTEST_INV_DEDUCT_SINGLE;
/
-- 【2】AWR快照采集封装存储过程（压测前后打快照）
CREATE OR REPLACE PROCEDURE P_AWR_TAKE_SNAPSHOT(o_snap_id OUT NUMBER)
IS
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
SELECT MAX(SNAP_ID) INTO o_snap_id FROM DBA_HIST_SNAPSHOT;
DBMS_OUTPUT.PUT_LINE('AWR快照已创建，SNAP_ID='||o_snap_id);
END P_AWR_TAKE_SNAPSHOT;
/
-- 【3】生成AWR HTML报告存储过程
CREATE OR REPLACE PROCEDURE P_AWR_GENERATE_REPORT(
p_start_snap IN NUMBER,
p_end_snap IN NUMBER,
p_dir_name IN VARCHAR2,
p_file_name IN VARCHAR2
)
IS
v_report CLOB;
BEGIN
SELECT DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid => (SELECT DBID FROM V$DATABASE),
l_inst_num=> (SELECT INSTANCE_NUMBER FROM V$INSTANCE),
l_bid => p_start_snap,
l_eid => p_end_snap
) INTO v_report FROM DUAL;
-- 将CLOB写入Oracle DIRECTORY
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_offset PLS_INTEGER := 1;
v_chunk VARCHAR2(32767);
v_len PLS_INTEGER := DBMS_LOB.GETLENGTH(v_report);
BEGIN
v_file := UTL_FILE.FOPEN(p_dir_name, p_file_name, 'W', 32767);
WHILE v_offset <= v_len LOOP
DBMS_LOB.READ(v_report, 32767, v_offset, v_chunk);
UTL_FILE.PUT(v_file, v_chunk);
v_offset := v_offset + 32767;
END LOOP;
UTL_FILE.FCLOSE(v_file);
END;
DBMS_OUTPUT.PUT_LINE('AWR报告输出至目录：'||p_dir_name||' 文件：'||p_file_name);
END P_AWR_GENERATE_REPORT;
/
/*
===== AWR压测执行步骤（手动执行）=====
DECLARE
v_snap_start NUMBER;
v_snap_end NUMBER;
BEGIN
-- Step1 压测前快照
P_AWR_TAKE_SNAPSHOT(v_snap_start);
DBMS_OUTPUT.PUT_LINE('开始并发压测，起始快照ID='||v_snap_start);
-- 【多会话并行执行下面调用，多窗口sqlplus同时跑，模拟多加盟商并发】
-- EXEC P_LOADTEST_INV_DEDUCT_SINGLE(1,1,200,'ORD_LOADTEST_01');
-- EXEC P_LOADTEST_INV_DEDUCT_SINGLE(2,1,200,'ORD_LOADTEST_02');
-- Step2 压测结束后快照
P_AWR_TAKE_SNAPSHOT(v_snap_end);
DBMS_OUTPUT.PUT_LINE('压测结束，结束快照ID='||v_snap_end);
-- Step3 导出HTML报告（需要预先创建DIRECTORY，DBA权限）
-- P_AWR_GENERATE_REPORT(v_snap_start, v_snap_end, 'DATA_PUMP_DIR','JEWEL_AWR_REPORT.html');
END;
/
-- 压测中实时监控锁、等待事件
SELECT sid, serial#, event, wait_class, seconds_in_wait, state
FROM v$session WHERE state='WAITING';
SELECT
ao.object_name,
vll.sid,
vll.locked_mode
FROM v$locked_object vll
JOIN all_objects ao ON vll.object_id = ao.object_id;
*/
-- =============================================
-- PART19: 运维PL/SQL：RMAN备份封装、表碎片检测、统计信息自动收集
-- =============================================
-- 19.1 RMAN备份调度封装（PL/SQL调用外部RMAN脚本，Oracle内部不能直接执行RMAN命令）
-- 原理：DBMS_SCHEDULER创建操作系统作业，调用外部rman备份脚本
CREATE OR REPLACE PROCEDURE P_RMAN_BACKUP_SCHEDULE(p_backup_type IN VARCHAR2)
IS
v_job_name VARCHAR2(100) := 'JOB_JEWEL_RMAN_BACKUP';
BEGIN
-- 删除旧调度任务
BEGIN
DBMS_SCHEDULER.DROP_JOB(v_job_name, TRUE);
EXCEPTION WHEN OTHERS THEN NULL; END;
-- 创建定时RMAN备份任务
-- p_backup_type: FULL 全量 / INCR 增量
DBMS_SCHEDULER.CREATE_JOB (
job_name => v_job_name,
job_type => 'EXECUTABLE',
job_action => '/u01/oracle/scripts/rman_jewelry_backup.sh '||p_backup_type,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0', -- 每日凌晨2点
enabled => TRUE,
comments => '珠宝ERP RMAN定时备份任务'
);
DBMS_OUTPUT.PUT_LINE('RMAN备份调度任务已创建，类型:'||p_backup_type);
END P_RMAN_BACKUP_SCHEDULE;
/
-- 19.2 表碎片检测存储过程（DBMS_SPACE，检测高水位HWM、空闲碎片空间）
CREATE OR REPLACE PROCEDURE P_CHECK_TABLE_FRAGMENT(p_owner IN VARCHAR2, p_table_name IN VARCHAR2)
IS
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
v_fragment_pct NUMBER(5,2);
BEGIN
DBMS_SPACE.UNUSED_SPACE(
segment_owner => p_owner,
segment_name => p_table_name,
segment_type => 'TABLE',
total_blocks => v_total_blocks,
total_bytes => v_total_bytes,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes,
last_used_extent_file_id => v_last_used_extent_file_id,
last_used_extent_block_id => v_last_used_extent_block_id,
last_used_block => v_last_used_block
);
IF v_total_blocks > 0 THEN
v_fragment_pct := ROUND(v_unused_blocks / v_total_blocks *100,2);
ELSE
v_fragment_pct := 0;
END IF;
DBMS_OUTPUT.PUT_LINE('====================================');
DBMS_OUTPUT.PUT_LINE('表名: '||p_owner||'.'||p_table_name);
DBMS_OUTPUT.PUT_LINE('总数据块: '||v_total_blocks);
DBMS_OUTPUT.PUT_LINE('未使用块: '||v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('碎片率(%): '||v_fragment_pct);
DBMS_OUTPUT.PUT_LINE('====================================');
DBMS_OUTPUT.PUT_LINE('碎片率>20%建议执行ALTER TABLE ... SHRINK SPACE;');
END P_CHECK_TABLE_FRAGMENT;
/
-- 19.3 自动收集统计信息存储过程（定期ANALYZE，更新CBO优化器统计信息）
CREATE OR REPLACE PROCEDURE P_GATHER_JEWEL_STATS(p_tenant_schema IN VARCHAR2)
IS
BEGIN
-- 收集OLTP业务表统计信息，并行+增量，自动直方图
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => p_tenant_schema,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE,
cascade => TRUE,
options => 'GATHER AUTO'
);
DBMS_OUTPUT.PUT_LINE('Schema '||p_tenant_schema||' 统计信息收集完成');
END P_GATHER_JEWEL_STATS;
/
/*
运维操作示例调用：
-- 检测库存流水表碎片
SET SERVEROUTPUT ON;
EXEC P_CHECK_TABLE_FRAGMENT('JEWELERP','JEWEL_INV_TRANS');
-- 收集整个业务schema统计信息
EXEC P_GATHER_JEWEL_STATS('JEWELERP');
-- 创建RMAN每日全量备份任务
EXEC P_RMAN_BACKUP_SCHEDULE('FULL');
-- 碎片过高收缩表（需要开启行移动）
ALTER TABLE JEWEL_INV_TRANS ENABLE ROW MOVEMENT;
ALTER TABLE JEWEL_INV_TRANS SHRINK SPACE;
*/
-- =============================================
-- PART20: DDD领域模型 + ER图文字说明文档（脚本末尾注释文档）
/*
================================================================================
文档：珠宝ERP DDD领域驱动设计模型 & ER文字说明
数据库：Oracle 21c
业务域：珠宝全链路：宝石原料采购 -> 设计 -> 生产工单 -> 成品库存 -> 加盟商销售 -> 售后
================================================================================
# 一、DDD领域划分
## 限界上下文（Bounded Context）
1. 【原料采购上下文】：宝石原石、贵金属原料、原料入库、证书管理
2. 【产品设计上下文】：首饰款式、产品分类树、款式镶嵌宝石BOM
3. 【生产加工上下文】：加工工单、工单返工、质检
4. 【库存仓储上下文】：成品库存、库存流水、库存扣减、库存回滚补偿
5. 【销售加盟商上下文】：加盟商（SCD2维度）、销售订单、订单取消
6. 【多租户上下文】：租户（自营门店/加盟商），行级隔离
7. 【审计&数据质量上下文】：审计日志、ETL脏数据错误日志
8. 【数据仓库DW上下文】：星型/雪花模型，OLAP分析域
## 聚合根、实体、值对象
### 1. 原料聚合（Aggregate Root: JEWEL_STONE_RAW 宝石原料）
- 聚合根：JEWEL_STONE_RAW（宝石原料）
- 实体：JEWEL_LOOKUP_STONE_TYPE 宝石品类、JEWEL_LOOKUP_METAL贵金属材质
- 值对象：
CARAT_WEIGHT克拉重量、COLOR_GRADE颜色等级、CLARITY_GRADE净度等级、CERT_NO证书编号
> 业务规则：克拉重量>0；证书唯一；原料归属租户；ROW_VERSION乐观锁
### 2. 产品款式聚合（Aggregate Root: JEWEL_STYLE 首饰款式）
- 聚合根：JEWEL_STYLE
- 实体：JEWEL_STYLE_STONE_JUNC 款式宝石BOM桥接表（多对多）
- 值对象：款式编码、款式名称、设计师
> 业务规则：一个款式镶嵌多种宝石；一种宝石用于多款首饰；JUNC桥接表维护BOM
### 3. 产品分类树聚合（Aggregate Root: JEWEL_CATEGORY_ADJ）
三种存储实现：邻接表 / 嵌套集 / 闭包表，同一业务分类树不同存储模型
- 实体：分类节点
- 值对象：分类名称、排序号
> 业务规则：租户隔离，节点移动维护树结构
### 4. 库存聚合（Aggregate Root: JEWEL_INVENTORY）
- 聚合根：JEWEL_INVENTORY 成品库存
- 实体：JEWEL_INV_TRANS 库存流水（库存事件，不可删除）
- 值对象：库存数量、成本金额
> 业务规则：库存不能负数；并发扣减采用FOR UPDATE行锁+ROW_VERSION乐观锁；订单取消触发库存归还补偿
### 5. 销售订单聚合（Aggregate Root: JEWEL_SALE_ORDER）
- 聚合根：JEWEL_SALE_ORDER销售订单
- 关联实体：加盟商DIM_FRANCHISE_SCD2（SCD2缓慢变化维度）
- 值对象：订单编号、订单金额、订单状态、下单日期
> 业务规则：订单取消触发库存补偿回滚；订单状态流转：NEW -> PAID -> FINISH / CANCELLED
### 6. 生产工单聚合（Aggregate Root: JEWEL_WORK_ORDER）
- 聚合根：JEWEL_WORK_ORDER加工工单
- 值对象：工单编号、工单数量、工单状态
> 业务规则：支持工单返工标记；状态：CREATING / IN_PROCESS / REWORK / FINISH
### 7. 租户聚合（Aggregate Root: JEWEL_TENANT）
- 聚合根：JEWEL_TENANT
- 值对象：租户名称、租户类型（自营/加盟商）
> 业务规则：所有业务表带TENANT_ID，RLS行级安全隔离租户数据
## 领域事件（业务事件）
1. 原料入库事件：新增JEWEL_STONE_RAW，写入审计日志
2. 库存扣减事件：库存数量变更 + 库存流水记录
3. 订单取消事件：订单状态变更 + 库存补偿归还
4. 工单返工事件：工单状态变更
5. 加盟商信息变更事件：SCD2拉链生成新版本
# 二、ER关系文字说明
1. JEWEL_LOOKUP_STONE_TYPE (1) --< JEWEL_STONE_RAW (*)
宝石类型字典 一对多 宝石原料
2. JEWEL_LOOKUP_METAL (1) --< JEWEL_STYLE (*)
贵金属材质字典 一对多 首饰款式
3. JEWEL_STYLE (*) <---> (*) JEWEL_LOOKUP_STONE_TYPE
通过JEWEL_STYLE_STONE_JUNC桥接表实现多对多（款式镶嵌宝石BOM）
4. JEWEL_STYLE (1) --< JEWEL_INVENTORY (*)
首饰款式一对多 成品库存记录
5. JEWEL_STYLE (1) --< JEWEL_WORK_ORDER (*)
首饰款式一对多 加工工单
6. JEWEL_TENANT (1) --< 所有业务表(*)
租户一对多所有业务数据，行级多租户
7. DIM_FRANCHISE_SCD2 加盟商维度：独立SCD2拉链维度表，与销售订单关联
8. JEWEL_INV_TRANS库存流水：库存事件明细表，与库存主表一对多，按月份RANGE分区
# 三、数据仓库DW ER（星型模型）
事实表：DW_FACT_SALE_STAR 销售事实
维度表：DW_DIM_DATE日期维度，DW_DIM_STYLE款式维度，DIM_FRANCHISE_SCD2加盟商维度
雪花模型：DW_DIM_STYLE_SNOW 款式维度拆分为DW_DIM_METAL材质子维度，进一步规范化维度
# 四、建模模式汇总
1. OLTP 主表：3NF范式，Lookup字典表消除冗余；多对多使用Junction桥接表满足4NF
2. 报表专用表：JEWEL_SALE_DENORM 反范式，冗余维度名称减少JOIN
3. 层次树：邻接表（递归CONNECT BY）、嵌套集、闭包表3种实现，用于产品分类
4. SCD2缓慢变化维度：加盟商，拉链表保存历史版本
5. 多租户：行租户+RLS行安全策略
6. 审计：触发器捕获INSERT/UPDATE/DELETE，写入JEWEL_AUDIT_LOG，ROW_VERSION乐观锁
7. 分区表：库存流水按月RANGE分区，提升归档、清理、查询性能
8. ETL增量抽取：ETL_CTRL记录抽取点位，SCD2自动同步维度到DW
9. 并发控制：库存扣减使用行锁+乐观锁；业务失败使用事务回滚+业务补偿逻辑
# 五、业务约束规则汇总
1. 宝石原料克拉重量>0；库存数量>=0；订单金额>0
2. 证书号唯一；款式编码唯一；订单号、工单号唯一
3. 租户隔离，跨租户不可直接访问（RLS）
4. 库存扣减必须写库存流水；订单取消必须归还库存
5. 维度变更SCD2：旧版本失效，新增有效版本，保留历史
6. 所有数据变更自动写入审计日志
7. ETL脏数据捕获写入JEWEL_ETL_ERROR_LOG，用于数据质量监控
================================================================================
*/
sqlplus user/password@ORCL21C @Jewelry_ERP_Oracle21c_Full_Extended.sql
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号