Tạo procedure nhiều bước: kiểm tra dữ liệu, insert và ghi log

Trong các tình huống thực tế của business, không chỉ đơn giản là thực hiện một thao tác, mà thường phải xây dựng chuỗi hành động: ví dụ khi nhận đơn hàng — kiểm tra dữ liệu khách hàng, lưu đơn hàng, ghi log để audit. Procedure nhiều bước giúp gom các bước này thành một logic thống nhất và đảm bảo tính toàn vẹn nhờ transaction: nếu có lỗi ở bất kỳ bước nào — mọi thay đổi sẽ bị rollback.

Kể từ khi PostgreSQL ra mắt các phiên bản mới, đặc biệt là khi có procedure riêng biệt (CREATE PROCEDURE) và mở rộng khả năng transaction, bạn cần hiểu rõ sự khác biệt giữa function và procedure trong PL/pgSQL, cũng như cách làm việc đúng với savepoint (SAVEPOINT), rollback, block xử lý lỗi.

Cơ bản về cấu trúc procedure nhiều bước

Một procedure business điển hình gồm các bước:

1. Kiểm tra dữ liệu — validate tham số đầu vào, kiểm tra khách hàng/sản phẩm tồn tại, v.v.
2. Insert dữ liệu — thực hiện thêm mới (hoặc update) bản ghi.
3. Ghi log hoặc audit — lưu thông tin về thao tác thành công hoặc thất bại.

Mỗi bước có thể thực hiện trong cùng một transaction (atomic), hoặc nếu process "dài" hay cần xử lý lỗi từng phần, thì tạo savepoint (SAVEPOINT) và dùng block xử lý exception để rollback cục bộ.

Ví dụ: thêm đơn hàng với kiểm soát toàn vẹn

Xét tình huống sau — có ba bảng:

• customers — khách hàng
• orders — đơn hàng
• order_log — log đơn hàng

Chuẩn bị schema:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

Tạo procedure nhiều bước: FUNCTION hay PROCEDURE?

Lưu ý!

• Nếu bạn cần kiểm soát hoàn toàn transaction (savepoint, COMMIT/ROLLBACK rõ ràng) — dùng CREATE PROCEDURE.
• Nếu procedure logic là atomic ("tất cả hoặc không gì cả") và được gọi từ các truy vấn SQL khác — dùng function.

Phiên bản function (logic atomic):

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. Kiểm tra khách hàng
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Khách hàng với ID % không tồn tại', p_customer_id;
    END IF;

    -- 2. Thêm đơn hàng
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Ghi log
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Đơn hàng đã được tạo thành công.');

    RAISE NOTICE 'Đơn hàng % cho khách hàng % đã thêm thành công', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Đặc điểm: function trong PostgreSQL luôn chạy trong một transaction bên ngoài. Không thể dùng transaction control (COMMIT, ROLLBACK, SAVEPOINT) bên trong function. Rollback hoặc commit sẽ được thực hiện bên ngoài.

Phiên bản có xử lý lỗi và ghi log lỗi:

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- Kiểm tra khách hàng
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Khách hàng với ID % không tồn tại', p_customer_id;
        END IF;

        -- Thêm đơn hàng
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Ghi log
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Đơn hàng đã được tạo thành công.');

        RAISE NOTICE 'Đơn hàng % cho khách hàng % đã thêm thành công', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Lỗi: %', SQLERRM));
            RAISE; -- Rollback toàn bộ transaction của function
    END;
END;
$$ LANGUAGE plpgsql;

Block BEGIN ... EXCEPTION ... END: Trong PL/pgSQL, bên trong function và procedure, block này tạo savepoint ảo. Mọi thay đổi trong block sẽ bị rollback nếu có lỗi xảy ra.

Commit từng phần và xử lý từng bước: tại sao cần procedure

Nếu cần commit từng bước (commit thực sự từng phần) — hãy dùng PROCEDURE!

Từ PostgreSQL version 11, bạn có thể viết procedure riêng (CREATE PROCEDURE), có thể kiểm soát transaction và savepoint ở phía server. Chỉ trong PROCEDURE (không phải function!) mới được phép dùng COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT rõ ràng. Nhưng: lệnh ROLLBACK TO SAVEPOINT trong procedure PL/pgSQL bị cấm — hãy dùng exception handler.

Ví dụ procedure xử lý từng bước và xử lý lỗi

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- Block đầu tiên: kiểm tra khách hàng
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Khách hàng với ID % không tồn tại', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Lỗi (validate): %', SQLERRM));
            RETURN;
    END;

    -- Block thứ hai: thêm đơn hàng
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Lỗi (order): %', SQLERRM));
            RETURN;
    END;

    -- Block thứ ba: ghi log thao tác thành công
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Đơn hàng đã được tạo thành công.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Ở đây không quan trọng, kể cả log không ghi được
            RAISE NOTICE 'Không ghi được log cho đơn hàng %', v_order_id;
    END;

    RAISE NOTICE 'Đơn hàng % cho khách hàng % đã thêm thành công (procedure)', v_order_id, p_customer_id;
END;
$$;

Gọi procedure:

CALL add_order_step_by_step(1, 150.50);

Best practice khi làm việc với transaction và procedure

• Dùng function cho các thao tác business atomic — khi cần nguyên tắc "tất cả hoặc không gì cả".
• Để commit từng bước hoặc rollback từng phần — dùng procedure và gọi ngoài transaction rõ ràng (chế độ autocommit).
• Để "rollback từng phần" hãy dùng block BEGIN ... EXCEPTION ... END — bên trong PL/pgSQL sẽ tự tạo savepoint và rollback block nếu có lỗi.
• Ghi log lỗi — cách tốt nhất để biết vì sao có gì đó không insert được hoặc không chạy.
• Không dùng ROLLBACK TO SAVEPOINT trong procedure PL/pgSQL — sẽ bị lỗi cú pháp (giới hạn của PostgreSQL 17+).

Test: kịch bản thành công và lỗi

-- Thêm khách hàng
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Gọi function (nên thành công)
SELECT add_order(1, 300.00);

-- Gọi function với khách hàng không tồn tại (sẽ lỗi)
SELECT add_order(999, 100.00);

-- Kiểm tra log
SELECT * FROM order_log;