Mục tiêu: Triển khai giải pháp làm mới tăng dần với bảng tạm

• Sử dụng một bảng vật lý (T_BOM_MAPPING) để lưu trữ kết quả.
• Sử dụng một bảng tạm làm việc (T_BOM_MAPPING_TMP) để xây dựng dữ liệu mới.
• Tận dụng ALTER TABLE ... EXCHANGE PARTITION hoặc RENAME để thực hiện làm mới nguyên tử.
• Hỗ trợ phát hiện cập nhật tăng dần (dựa trên timestamp hoặc trường phiên bản).
• Làm mới định kỳ qua stored procedure hoặc tác vụ lập lịch.

I. Giả định trước khi triển khai

1. Bảng bmb_file và bma_file có trường thời gian cập nhật (như last_update_time) để xác định cần làm mới.

• Nếu không, nên thêm trường này hoặc sử dụng cơ chế CDC/trigger của cơ sở dữ liệu.

2. Sử dụng cơ sở dữ liệu Oracle (do sử dụng cú pháp CONNECT BY).
3. Cho phép làm mới định kỳ (không thời gian thực).

II. Các bước triển khai hoàn chỉnh

Bước 1: Tạo bảng vật lý (bảng chính)

CREATE TABLE T_BOM_MAPPING (
    VATTU_CHINH    VARCHAR2(30) NOT NULL,
    VATTU_DON_LE   VARCHAR2(30) NOT NULL,
    THOI_GIAN_TAO  DATE DEFAULT SYSDATE
);
-- Tùy chọn: tạo chỉ mục
CREATE INDEX IDX_BOM_MAP_CHINH ON T_BOM_MAPPING(VATTU_CHINH);
CREATE INDEX IDX_BOM_MAP_DON_LE ON T_BOM_MAPPING(VATTU_DON_LE);

Bước 2: Tạo bảng tạm làm việc (cấu trúc giống bảng chính)

CREATE TABLE T_BOM_MAPPING_TMP (
    VATTU_CHINH    VARCHAR2(30) NOT NULL,
    VATTU_DON_LE   VARCHAR2(30) NOT NULL,
    THOI_GIAN_TAO  DATE DEFAULT SYSDATE
);
-- Chỉ mục tùy chọn, có thể xây dựng lại sau khi trao đổi

️ Lưu ý: Bảng tạm phải có cấu trúc hoàn toàn giống bảng chính (bao gồm ràng buộc, chỉ mục), nếu không EXCHANGE sẽ thất bại.

Bước 3: Tạo stored procedure làm mới (logic cốt lõi)

CREATE OR REPLACE PROCEDURE P_CAP_NHAP_BOM_MAPPING AS
BEGIN
    -- Bước 1: Xóa sạch bảng tạm
    EXECUTE IMMEDIATE 'TRUNCATE TABLE T_BOM_MAPPING_TMP';

    -- Bước 2: Chèn kết quả tính toán mới vào bảng tạm
    INSERT INTO T_BOM_MAPPING_TMP (VATTU_CHINH, VATTU_DON_LE)
    WITH bom_de_quy AS (
        SELECT
            CONNECT_BY_ROOT bmb01 AS vattu_chinh,
            bmb03 AS vattu_don_le,
            LEVEL AS muc_do_bom
        FROM bmb_file
        WHERE (bmb05 IS NULL OR bmb04 > bmb05)
          AND bmb01 IS NOT NULL
        START WITH bmb01 IN (
            SELECT bma01
            FROM bma_file
            WHERE bma01 IS NOT NULL
        )
        CONNECT BY PRIOR bmb03 = bmb01
    ),
    bom_goc AS (
        SELECT vattu_chinh, vattu_don_le FROM bom_de_quy
        UNION ALL
        SELECT bma01 AS vattu_chinh, bma01 AS vattu_don_le
        FROM bma_file
        WHERE bma01 IN (SELECT DISTINCT vattu_chinh FROM bom_de_quy)
    )
    SELECT DISTINCT
        vattu_chinh,
        vattu_don_le
    FROM bom_goc
    WHERE vattu_chinh IS NOT NULL
      AND vattu_don_le IS NOT NULL;

    -- Bước 3: Commit giao dịch
    COMMIT;

    -- Bước 4: Trao đổi bảng nguyên tử (bước quan trọng)
    -- Sử dụng RENAME để trao đổi nguyên tử (khuyến nghị, đơn giản và đáng tin cậy)
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';
    EXCEPTION
        WHEN OTHERS THEN NULL; -- Bỏ qua lỗi bảng không tồn tại
    END;

    -- Đổi tên bảng chính hiện tại thành bảng cũ, bảng tạm thành bảng chính mới
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING TO T_BOM_MAPPING_OLD';
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_TMP TO T_BOM_MAPPING';

    -- Bước 5: Tùy chọn: xóa bảng cũ
    EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';

    -- Hoặc giữ lại bảng cũ để hoàn tác
    -- EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING_TMP'; -- Dùng để hoàn tác

    DBMS_OUTPUT.PUT_LINE('Làm mới BOM Mapping hoàn tất: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Làm mới thất bại: ' || SQLERRM);
        RAISE;
END P_CAP_NHAP_BOM_MAPPING;
/

Bước 4: Tạo view (trỏ đến bảng vật lý)

CREATE OR REPLACE VIEW V_BOM_MAPPING AS
SELECT VATTU_CHINH, VATTU_DON_LE
FROM T_BOM_MAPPING;

View này không còn chứa logic phức tạp, chỉ truy vấn bảng vật lý nên có hiệu năng rất cao.

Bước 5: Lập tác vụ làm mới định kỳ (ví dụ mỗi ngày lúc 2 giờ sáng)

Sử dụng Oracle Job Scheduler:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_CAP_NHAP_BOM_MAPPING',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN P_CAP_NHAP_BOM_MAPPING; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- Mỗi ngày lúc 2 giờ sáng
        enabled         => TRUE,
        comments        => 'Làm mới hàng ngày bảng ánh xạ BOM vật tư'
    );
END;
/

Bước 6 (Tùy chọn): Tối ưu hóa làm mới tăng dần (khi dữ liệu rất lớn)

Nếu bmb_file hoặc bma_file cập nhật với tần suất thấp, có thể thêm logic phát hiện thay đổi để tránh làm mới không cần thiết:

-- Ví dụ: kiểm tra có thay đổi trong 24 giờ qua không
SELECT COUNT(*) INTO v_cnt
FROM (
    SELECT thoi_gian_cap_nhat FROM bmb_file WHERE thoi_gian_cap_nhat > SYSDATE - 1
    UNION ALL
    SELECT thoi_gian_cap_nhat FROM bma_file WHERE thoi_gian_cap_nhat > SYSDATE - 1
);
IF v_cnt = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Không có thay đổi, bỏ qua làm mới');
    RETURN;
END IF;

III. Tóm tắt ưu điểm

IV. Lưu ý quan trọng

1. Loại bỏ trùng khóa chính: DISTINCT đã xử lý, nhưng có thể thêm ràng buộc khóa chính:

ALTER TABLE T_BOM_MAPPING ADD CONSTRAINT PK_BOM_MAP PRIMARY KEY (VATTU_CHINH, VATTU_DON_LE);

Quy ước đặt tên bảng tạm: Nên sử dụng hậu tố TMP_ hoặc _STG. Quản lý quyền: Đảm bảo người dùng thực thi stored procedure có quyền tạo bảng, đổi tên. Lưu trữ và giám sát: Ghi nhật ký mỗi lần làm mới (ví dụ chèn vào bảng nhật ký).

V. Phương án hoàn tác (khi xảy ra lỗi)

-- Nếu làm mới thất bại, có thể khôi phục thủ công
RENAME T_BOM_MAPPING TO T_BOM_MAPPING_HONG;
RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING;

Phiên bản tối ưu hóa cuối cùng của stored procedure (bao gồm: phát hiện tăng dần + cơ chế hoàn tác an toàn)

CREATE OR REPLACE PROCEDURE P_CAP_NHAP_BOM_MAPPING AS
    v_dem        NUMBER;
    v_thoi_gian_bd DATE := SYSDATE;
BEGIN
    -- ========================
    -- ✅ 1. 【Phát hiện tăng dần】Xác định có thay đổi dữ liệu không, quyết định bỏ qua làm mới
    -- ========================
    SELECT COUNT(*) INTO v_dem
    FROM (
        SELECT 1 FROM bmb_file WHERE thoi_gian_cap_nhat > SYSDATE - 1 AND ROWNUM = 1
        UNION ALL
        SELECT 1 FROM bma_file WHERE thoi_gian_cap_nhat > SYSDATE - 1 AND ROWNUM = 1
    );

    IF v_dem = 0 THEN
        DBMS_OUTPUT.PUT_LINE('【Phát hiện tăng dần】Không có thay đổi trong 24 giờ qua, bỏ qua làm mới.');
        RETURN; -- Không có thay đổi, thoát sớm
    END IF;

    DBMS_OUTPUT.PUT_LINE('【Bắt đầu làm mới】Phát hiện thay đổi dữ liệu BOM, khởi động quy trình làm mới...');

    -- ========================
    -- ✅ 2. Xóa sạch bảng tạm
    -- ========================
    EXECUTE IMMEDIATE 'TRUNCATE TABLE T_BOM_MAPPING_TMP';

    -- ========================
    -- ✅ 3. Chèn kết quả tính toán mới vào bảng tạm
    -- ========================
    INSERT INTO T_BOM_MAPPING_TMP (VATTU_CHINH, VATTU_DON_LE)
    WITH bom_de_quy AS (
        SELECT
            CONNECT_BY_ROOT bmb01 AS vattu_chinh,
            bmb03 AS vattu_don_le,
            LEVEL AS muc_do_bom
        FROM bmb_file
        WHERE (bmb05 IS NULL OR bmb04 > bmb05)
          AND bmb01 IS NOT NULL
        START WITH bmb01 IN (
            SELECT bma01
            FROM bma_file
            WHERE bma01 IS NOT NULL
        )
        CONNECT BY PRIOR bmb03 = bmb01
    ),
    bom_goc AS (
        SELECT vattu_chinh, vattu_don_le FROM bom_de_quy
        UNION ALL
        SELECT bma01 AS vattu_chinh, bma01 AS vattu_don_le
        FROM bma_file
        WHERE bma01 IN (SELECT DISTINCT vattu_chinh FROM bom_de_quy)
    )
    SELECT DISTINCT
        vattu_chinh,
        vattu_don_le
    FROM bom_goc
    WHERE vattu_chinh IS NOT NULL
      AND vattu_don_le IS NOT NULL;

    COMMIT;

    -- ========================
    -- ✅ 4. Trao đổi bảng nguyên tử (thực hiện bằng RENAME)
    -- ========================
    -- Thử xóa bảng cũ từ lần trước (tránh xung đột)
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    -- Thực hiện trao đổi: bảng chính → bảng cũ, bảng tạm → bảng chính mới
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING TO T_BOM_MAPPING_OLD';
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_TMP TO T_BOM_MAPPING';

    DBMS_OUTPUT.PUT_LINE('【Làm mới hoàn tất】Bảng ánh xạ BOM đã cập nhật thành công.');

    -- Tùy chọn: ghi nhật ký hoặc gửi thông báo
    -- INSERT INTO lich_su_lam_moi VALUES ('BOM_MAPPING', v_thoi_gian_bd, SYSDATE, 'THANH_CONG');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

        -- ========================
        -- ❌ 5. 【Xử lý ngoại lệ & Cơ chế hoàn tác】
        -- ========================
        DECLARE
            e_table_exists EXCEPTION;
            PRAGMA EXCEPTION_INIT(e_table_exists, -955); -- ORA-00955: tên đã được sử dụng bởi đối tượng tồn tại
        BEGIN
            -- Thử khôi phục: nếu bảng chính hiện tại không tồn tại hoặc hỏng, thử dùng bảng cũ
            BEGIN
                -- Kiểm tra bảng chính hiện tại có tồn tại không
                EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DUAL WHERE EXISTS (SELECT 1 FROM USER_TABLES WHERE TABLE_NAME = ''T_BOM_MAPPING'')';
            EXCEPTION
                WHEN OTHERS THEN
                    -- Bảng chính có thể không tồn tại, thử khôi phục
                    BEGIN
                        EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING';
                        DBMS_OUTPUT.PUT_LINE('【Hoàn tác thành công】Đã khôi phục phiên bản bảng cũ.');
                        RAISE_APPLICATION_ERROR(-20001, 'Làm mới thất bại và bảng chính bị mất, đã tự động hoàn tác: ' || SQLERRM);
                    EXCEPTION
                        WHEN e_table_exists THEN
                            DBMS_OUTPUT.PUT_LINE('【Hoàn tác thất bại】T_BOM_MAPPING đã tồn tại, không thể khôi phục bảng cũ.');
                        WHEN OTHERS THEN
                            DBMS_OUTPUT.PUT_LINE('【Hoàn tác thất bại】Không thể khôi phục T_BOM_MAPPING_OLD: ' || SQLERRM);
                    END;
            END;

            -- Ghi nhật ký lỗi
            DBMS_OUTPUT.PUT_LINE('【Làm mới thất bại】Thông báo lỗi: ' || SQLERRM);
            RAISE_APPLICATION_ERROR(-20000, 'Làm mới ánh xạ BOM thất bại: ' || SQLERRM);

        END;
END P_CAP_NHAP_BOM_MAPPING;
/

Thực hiện lần đầu stored procedure:

- Bước 1: Tạo bảng (nếu chưa tạo)
-- (Bỏ qua, tham khảo câu lệnh trước đó)

-- Bước 2: Tạm sửa stored procedure: comment phát hiện tăng dần (chỉ lần đầu)
-- (Sửa sau đó biên dịch lại stored procedure)

-- Bước 3: Thực hiện stored procedure  Lần đầu thực hiện stored procedure
BEGIN
    P_CAP_NHAP_BOM_MAPPING;
END;
/

-- Bước 4: Xác minh dữ liệu
SELECT COUNT(*) FROM T_BOM_MAPPING;
SELECT * FROM V_BOM_MAPPING WHERE ROWNUM <= 10;

-- Bước 5: Khôi phục stored procedure: kích hoạt lại phát hiện tăng dần (sau khi thêm trường)
-- Sau đó biên dịch lại stored procedure

Tóm tắt: Bạn nên hiểu quy trình như sau

Phiên bản tối ưu``` Bắt đầu │ ├─▶ Phát hiện tăng dần → Không thay đổi?→ Kết thúc │ ├─▶ Có thay đổi → Xóa sạch bảng tạm → Tính toán dữ liệu mới → Chèn vào bảng tạm │ ├─▶ Trao đổi bảng (chính ←→ tạm) │ ├─▶ Thành công: Hoàn thành │ └─▶ Thất bại: Thử khôi phục bảng chính bằng bảng cũ → Ném lỗi

Tóm tắt

Bạn đã chuyển đổi thành công một view đệ quy phức tạp thành:

"Cấu trúc bảng vật lý + bảng tạm + làm mới bằng trao đổi" có hiệu năng cao, dễ bảo trì.

Cách truy cập cuối cùng không thay đổi: Ứng dụng vẫn truy vấn V_BOM_MAPPING, nhưng dưới đáy đã là cấu trúc tối ưu.