Bir neçə mərhələli prosedurların yaradılması: məlumatların yoxlanması, insert və loglama

Real biznes-case-lərdə adətən tək bir əməliyyat yox, bir neçə addımdan ibarət zəncir qurmaq lazım olur: məsələn, sifariş gələndə — müştəri məlumatlarını yoxlamaq, sifarişi saxlamaq, audit üçün log yazmaq. Çoxmərhələli prosedur bu addımları bir məntiqdə birləşdirir və transaction-lar sayəsində bütövlüyü təmin edir: əgər hansısa addımda nəsə səhv getsə — dəyişikliklər geri qaytarılır.

PostgreSQL-in yeni versiyalarında, xüsusilə ayrıca prosedurlar (CREATE PROCEDURE) və transaction-larla işləmənin genişlənməsindən sonra, PL/pgSQL-də funksiya ilə prosedur arasındakı fərqi və savepoint-lərlə (SAVEPOINT), rollback-larla, error blokları ilə düzgün işləməyi başa düşmək vacibdir.

Çoxmərhələli prosedurun əsas strukturu

Tipik biznes-prosedur aşağıdakı mərhələlərdən ibarətdir:

1. Məlumatların yoxlanması — daxil olan argumentlərin, müştəri/məhsulun olub-olmamasının və s. validasiyası.
2. Məlumatların insert edilməsi — faktiki olaraq bir və ya bir neçə sətrin əlavə olunması (və ya yenilənməsi).
3. Loglama və ya audit — uğurlu və ya uğursuz əməliyyat barədə məlumatın yazılması.

Hər mərhələni bir transaction daxilində (atomik) yerinə yetirmək olar, ya da proses "uzun" və ya error-ların hissə-hissə işlənməsi lazımdırsa — savepoint-lər (SAVEPOINT) yaradıb, exception handling blokları ilə lokal rollback etmək olar.

Nümunə: bütövlüyə nəzarətlə sifariş əlavə etmək

Belə bir situasiyanı nəzərdən keçirək — üç cədvəlimiz var:

• customers — müştərilər
• orders — sifarişlər
• order_log — sifarişlərin logu

Schema-nı hazırlayaq:

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()
);

Çoxmərhələli prosedurun yaradılması: FUNKSİYA yoxsa PROSEDUR?

Vacib!

• Əgər sənə transaction-lara tam nəzarət lazımdırsa (savepoint-lər, explicit COMMIT/ROLLBACK) — CREATE PROCEDURE istifadə et.
• Əgər prosedur məntiqcə atomikdirsə ("ya hamısı, ya heç nə") və başqa SQL sorğularından çağırılırsa — funksiya istifadə et.

Funksiya şəklində versiya (atomik məntiq):

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. Müştərinin yoxlanması
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'ID-si % olan müştəri mövcud deyil', p_customer_id;
    END IF;

    -- 2. Sifarişin insert edilməsi
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Loglama
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Sifariş uğurla yaradıldı.');

    RAISE NOTICE 'Sifariş % müştəri % üçün uğurla əlavə olundu', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Xüsusiyyət: PostgreSQL-də funksiyalar həmişə bir xarici transaction daxilində işləyir. Funksiya daxilində transaction idarəetməsi (COMMIT, ROLLBACK, SAVEPOINT) istifadə etmək olmaz. Rollback və ya commit xaricdən olur.

Error handling və error loglama ilə versiya:

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
        -- Müştərinin yoxlanması
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID-si % olan müştəri mövcud deyil', p_customer_id;
        END IF;

        -- Sifarişin insert edilməsi
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Loglama
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Sifariş uğurla yaradıldı.');

        RAISE NOTICE 'Sifariş % müştəri % üçün uğurla əlavə olundu', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Xəta: %', SQLERRM));
            RAISE; -- Funksiyanın bütün transaction-ı rollback edir
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... END bloku: PL/pgSQL-də, funksiyalar və prosedurlar daxilində, bu blok virtual savepoint yaradır. Blok daxilində error çıxsa, bütün dəyişikliklər geri qaytarılır.

Hissəvi commit və addım-addım işləmə: prosedurlar niyə lazımdır

Əgər addım-addım commit (həqiqi hissəvi təsdiq) lazımdırsa — PROSEDURLARDAN istifadə et!

PostgreSQL 11-dən başlayaraq ayrıca prosedurlar (CREATE PROCEDURE) yazmaq olur, bunlar transaction-ları və savepoint-ləri server tərəfində idarə edə bilir. Yalnız PROSEDURLARDA (funksiyalarda yox!) explicit COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT etmək olar. Amma: PL/pgSQL prosedurunda ROLLBACK TO SAVEPOINT qadağandır — exception handler-lərdən istifadə et.

Addım-addım işləmə və error handling ilə prosedur nümunəsi

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
    -- Birinci blok: müştərinin yoxlanması
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID-si % olan müştəri mövcud deyil', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Xəta (validate): %', SQLERRM));
            RETURN;
    END;

    -- İkinci blok: sifarişin insert edilməsi
    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('Xəta (order): %', SQLERRM));
            RETURN;
    END;

    -- Üçüncü blok: uğurlu əməliyyatın loglanması
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Sifariş uğurla yaradıldı.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Burda fərq etmir, log yazılmasa da olar
            RAISE NOTICE 'Sifariş % üçün log yazmaq alınmadı', v_order_id;
    END;

    RAISE NOTICE 'Sifariş % müştəri % üçün uğurla əlavə olundu (prosedur)', v_order_id, p_customer_id;
END;
$$;

Prosedurun çağırılması:

CALL add_order_step_by_step(1, 150.50);

Transaction-lar və prosedurlarla işləyərkən yaxşı praktikalar

• Atomik biznes əməliyyatları üçün funksiyalardan istifadə et — "hamısı və ya heç nə" prinsipi lazımdırsa.
• Addım-addım commit və ya mərhələvi rollback üçün — prosedurlardan istifadə et və onları explicit transaction olmadan çağır (autocommit rejimi).
• "Hissəvi rollback" üçün BEGIN ... EXCEPTION ... END bloklarından istifadə et — PL/pgSQL blok daxilində özü savepoint yaradır və error olanda həmin bloku rollback edir.
• Xətaları logla — nəyin niyə yüklənmədiyini və ya işləmədiyini başa düşməyin ən yaxşı yolu budur.
• PL/pgSQL prosedurlarında ROLLBACK TO SAVEPOINT istifadə etmə — bu, syntax error verəcək (PostgreSQL 17+ məhdudiyyəti).

Test: uğurlu və xətalı ssenari

-- Müştəri əlavə edək
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Funksiyanı çağıraq (uğurlu olmalıdır)
SELECT add_order(1, 300.00);

-- Mövcud olmayan müştəri ilə funksiyanı çağıraq (xəta olacaq)
SELECT add_order(999, 100.00);

-- Log jurnalını yoxlayaq
SELECT * FROM order_log;