Stored Procedures

Procedure vs Function

Basit Procedure

CREATE OR REPLACE PROCEDURE update_user_status(
    user_id_param INTEGER,
    new_status VARCHAR
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE users 
    SET status = new_status, updated_at = NOW()
    WHERE id = user_id_param;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % not found', user_id_param;
    END IF;
END;
$$;

-- Kullanım
CALL update_user_status(5, 'active');

Transaction ile Para Transferi

CREATE OR REPLACE PROCEDURE transfer_money(
    sender_id INTEGER,
    receiver_id INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql AS $$
DECLARE
    sender_balance NUMERIC;
BEGIN
    -- Gönderenin bakiyesini kontrol et
    SELECT balance INTO sender_balance 
    FROM accounts WHERE user_id = sender_id FOR UPDATE;
    
    IF sender_balance IS NULL THEN
        RAISE EXCEPTION 'Sender account not found';
    END IF;
    
    IF sender_balance < amount THEN
        RAISE EXCEPTION 'Insufficient balance. Available: %, Required: %', sender_balance, amount;
    END IF;
    
    -- Transfer işlemi
    UPDATE accounts SET balance = balance - amount WHERE user_id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE user_id = receiver_id;
    
    -- Log kaydı
    INSERT INTO transfer_log (from_user, to_user, amount, created_at)
    VALUES (sender_id, receiver_id, amount, NOW());
    
    COMMIT;
    RAISE NOTICE 'Transfer of % completed successfully', amount;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- Kullanım
CALL transfer_money(1, 2, 500.00);

Toplu İşlem Procedure

CREATE OR REPLACE PROCEDURE archive_old_orders(
    days_old INTEGER DEFAULT 365
)
LANGUAGE plpgsql AS $$
DECLARE
    archived_count INTEGER;
BEGIN
    -- Eski siparişleri arşive taşı
    INSERT INTO archived_orders
    SELECT * FROM orders
    WHERE created_at < NOW() - (days_old || ' days')::INTERVAL
    AND status IN ('completed', 'cancelled');
    
    GET DIAGNOSTICS archived_count = ROW_COUNT;
    
    -- Orijinal tablodan sil
    DELETE FROM orders
    WHERE created_at < NOW() - (days_old || ' days')::INTERVAL
    AND status IN ('completed', 'cancelled');
    
    COMMIT;
    RAISE NOTICE 'Archived % orders', archived_count;
END;
$$;

CALL archive_old_orders(180);

OUT Parametreli Procedure

CREATE OR REPLACE PROCEDURE process_batch(
    batch_size INTEGER,
    OUT processed INTEGER,
    OUT failed INTEGER
)
LANGUAGE plpgsql AS $$
BEGIN
    processed := 0;
    failed := 0;
    
    -- İşlem mantığı...
    -- Her başarılı işlem: processed := processed + 1;
    -- Her hata: failed := failed + 1;
END;
$$;

-- Kullanım
DO $$
DECLARE
    p INT; f INT;
BEGIN
    CALL process_batch(100, p, f);
    RAISE NOTICE 'Processed: %, Failed: %', p, f;
END $$;