Stored Procedure Nedir, Ne Zaman Kullanılır

Uygulamanız her sipariş kaydı için veritabanına sırayla beş sorgu gönderiyorsa, bu beş sorgunun her biri ağ üzerinden gidip geliyor, her biri ayrı ayrı ayrıştırılıyor ve aralarında bir hata olursa yarım kalmış bir tabloyla baş başa kalıyorsunuz. Stored procedure, yani saklı yordam, tam olarak bu tabloyu değiştirmek için var: bir dizi SQL işlemini veritabanının içinde adlandırılmış tek bir birim hâline getirir, tek çağrıyla çalıştırır ve tamamının ya hep ya hiç mantığıyla bitmesini sağlar.

Ama stored procedure her derde deva değil. Yıllar içinde hem "her şeyi procedure'e taşıyalım" diye başlayıp bakımı imkânsız hâle gelmiş sistemler gördüm, hem de basit bir toplu güncellemeyi uygulama katmanında satır satır döndürüp veritabanını gereksiz yere yoran ekipler. Bu rehberde stored procedure'ün ne olduğunu, fonksiyondan farkını, PostgreSQL ve MySQL'de nasıl yazıldığını, gerçekten kazanç sağladığı senaryoları ve sizi ileride pişman edecek kullanım biçimlerini anlatacağım.

Stored Procedure Tam Olarak Nedir#

Stored procedure, veritabanı sunucusunda derlenmiş ve saklanmış bir kod bloğudur. İçinde SQL ifadeleri, koşullar, döngüler, değişkenler ve hata yakalama blokları bulunabilir. Uygulamanız bu bloğu bir isimle çağırır, parametre gönderir ve sonucu alır. Kod veritabanının içinde durduğu için, çağrı ile veri arasındaki mesafe sıfıra iner.

Klasik bir örnekle bakalım. Bir e-ticaret sisteminde sipariş oluşturmak üç işlem gerektiriyor: stoktan düşmek, sipariş satırı yazmak ve müşteri bakiyesini güncellemek. Uygulama katmanından bunu yaparsanız üç ayrı sorgu, üç ayrı gidiş-dönüş demektir. Aynı işi PostgreSQL'de tek bir procedure'e taşıyalım:

-- PostgreSQL 11 ve sonrası: CALL ile çağrılan gerçek procedure
CREATE OR REPLACE PROCEDURE siparis_olustur(
    p_musteri_id  bigint,
    p_urun_id     bigint,
    p_adet        integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stok   integer;
    v_fiyat  numeric(12,2);
BEGIN
    -- Stok satırını kilitleyerek oku (eşzamanlı sipariş yarışını engeller)
    SELECT stok, fiyat INTO v_stok, v_fiyat
    FROM urunler
    WHERE id = p_urun_id
    FOR UPDATE;

    IF v_stok IS NULL THEN
        RAISE EXCEPTION 'Ürün bulunamadı: %', p_urun_id;
    END IF;

    IF v_stok < p_adet THEN
        RAISE EXCEPTION 'Yetersiz stok (mevcut: %, istenen: %)', v_stok, p_adet;
    END IF;

    UPDATE urunler SET stok = stok - p_adet WHERE id = p_urun_id;

    INSERT INTO siparisler (musteri_id, urun_id, adet, birim_fiyat)
    VALUES (p_musteri_id, p_urun_id, p_adet, v_fiyat);

    UPDATE musteriler
    SET bakiye = bakiye - (v_fiyat * p_adet)
    WHERE id = p_musteri_id;
END;
$$;

Çağırmak tek satır:

CALL siparis_olustur(1042, 88, 3);

Buradaki kazanç sadece "tek çağrı" değil. FOR UPDATE ile satırı kilitlemek, stok kontrolü ile stok düşümü arasına başka bir oturumun girmesini engelliyor. Aynı mantığı uygulama katmanında yazsaydınız, kilit süresi ağ gecikmesi kadar uzayacaktı. Veritabanı içinde bu süre mikrosaniyelerle ölçülür.

Procedure ve Function Arasındaki Fark#

Türkçede ikisine de sıklıkla "saklı yordam" denir ama veritabanı motorları açısından aralarında somut farklar vardır ve bu farkları bilmemek doğrudan hataya yol açar. PostgreSQL 11 öncesinde yalnızca FUNCTION vardı; procedure kavramı sonradan eklendi ve asıl ayrım işlem (transaction) kontrolü üzerinedir.

En kritik satır sonuncudan bir öncekidir. Bir fonksiyon, kendisini çağıran işlemin içinde çalışır ve o işlemi bitiremez. Procedure ise kendi içinde COMMIT çağırabilir. Bu, milyonlarca satırlık toplu işleri parça parça işlerken hayat kurtarır:

CREATE OR REPLACE PROCEDURE eski_loglari_temizle(p_gun integer)
LANGUAGE plpgsql
AS $$
DECLARE
    v_silinen integer;
BEGIN
    LOOP
        DELETE FROM erisim_loglari
        WHERE id IN (
            SELECT id FROM erisim_loglari
            WHERE olusturulma < now() - (p_gun || ' days')::interval
            LIMIT 5000
        );
        GET DIAGNOSTICS v_silinen = ROW_COUNT;
        EXIT WHEN v_silinen = 0;
        -- Her 5000 satırda bir commit: WAL şişmez, kilit uzun sürmez
        COMMIT;
    END LOOP;
END;
$$;

Bu bloğu fonksiyon olarak yazsaydınız tek dev bir işlem olurdu; on milyon satırlık bir temizlik sırasında WAL diski doldurur, replikasyon gecikir ve tablo üzerindeki kilit saatlerce sürerdi. MySQL tarafında ayrım daha bulanıktır: MySQL'de de PROCEDURE ve FUNCTION vardır, fonksiyon değer döndürmek zorundadır, ancak MySQL fonksiyonları varsayılan olarak deterministik olmadığını beyan etmenizi ister ve replikasyon güvenliği açısından ek kısıtlar getirir.

MySQL Tarafında Stored Procedure Yazmak#

MySQL ve MariaDB'de söz dizimi PostgreSQL'den belirgin biçimde farklıdır. En büyük tuzak, ; karakterinin hem SQL ifadelerini hem de procedure gövdesini bitirmesidir; bu yüzden geçici olarak sınırlayıcıyı (delimiter) değiştirmeniz gerekir.

DELIMITER //

CREATE PROCEDURE musteri_ozeti(IN p_musteri_id BIGINT, OUT p_toplam DECIMAL(14,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT COALESCE(SUM(adet * birim_fiyat), 0)
      INTO p_toplam
      FROM siparisler
     WHERE musteri_id = p_musteri_id;

    UPDATE musteriler
       SET son_hesaplama = NOW(), toplam_harcama = p_toplam
     WHERE id = p_musteri_id;

    COMMIT;
END //

DELIMITER ;

Çağırmak ve çıktı parametresini okumak:

CALL musteri_ozeti(1042, @toplam);
SELECT @toplam;

DELIMITER // satırını unutmak, MySQL istemcisinde en sık karşılaşılan sözdizimi hatasıdır: MySQL ilk ; gördüğü yerde procedure'ü kestiği için "You have an error in your SQL syntax" alırsınız ve hatanın nerede olduğunu bulmak zaman alır. Ayrıca MySQL'de procedure'ler mysql.proc yerine artık veri sözlüğünde tutulur; SHOW CREATE PROCEDURE musteri_ozeti ile içeriğini geri okuyabilirsiniz. MySQL yönetimine yeniyseniz MySQL veritabanı yönetimi yazısı temel işletim komutlarını toparlıyor.

Ne Zaman Gerçekten İşe Yarar#

Stored procedure'ü savunulabilir kılan senaryolar bellidir ve hepsinin ortak noktası "veriye yakın olmanın ölçülebilir bir kazanç sağlaması"dır.

1. Çok adımlı, bütünlüğü kritik işlemler. Para transferi, stok düşümü, kredi limiti kontrolü gibi işlerde adımlar arasına başka bir oturumun girmemesi gerekir. Kilit süresini kısaltmak doğrudan eşzamanlılığı artırır.
2. Büyük hacimli toplu işler. Yüz binlerce satırı uygulamaya çekip işleyip geri yazmak, ağ üzerinde gereksiz veri taşır. Aynı işi veritabanı içinde yapmak çoğu zaman on kat hızlıdır.
3. Birden fazla uygulamanın aynı kuralı paylaşması. Bir Python servisi, bir PHP paneli ve bir raporlama aracı aynı hesaplamayı yapıyorsa, kuralı üç kez yazmak yerine tek bir procedure'de tutmak tutarlılık sağlar.
4. Ayrıcalık sınırlama. SECURITY DEFINER ile bir procedure, çağıranın değil sahibinin yetkileriyle çalışır. Böylece uygulama kullanıcısına tabloya doğrudan UPDATE yetkisi vermeden, sadece kontrollü bir işlemi çalıştırma hakkı verebilirsiniz.

Dördüncü madde çoğu ekibin gözden kaçırdığı en değerli kullanımdır. Örnek:

CREATE OR REPLACE PROCEDURE parola_sifirla(p_kullanici_id bigint, p_yeni_hash text)
LANGUAGE plpgsql
SECURITY DEFINER
-- search_path sabitlenmezse SECURITY DEFINER bir güvenlik açığına dönüşür
SET search_path = public, pg_temp
AS $$
BEGIN
    UPDATE kullanicilar
       SET parola_hash = p_yeni_hash, parola_degisim = now()
     WHERE id = p_kullanici_id;
END;
$$;

REVOKE ALL ON kullanicilar FROM uygulama_kullanici;
GRANT EXECUTE ON PROCEDURE parola_sifirla(bigint, text) TO uygulama_kullanici;

Bu yapıda uygulama kullanıcısı kullanicilar tablosunu okuyamaz ve keyfî güncelleyemez; sadece parola alanını değiştirebilir. SQL injection ile bile ele geçirilse hasar sınırlıdır. SET search_path satırını atlarsanız, saldırgan kendi şemasında sahte bir kullanicilar tablosu oluşturup procedure'ü kandırabilir — bu, PostgreSQL'de belgelenmiş klasik bir yükseltme tekniğidir.

Ne Zaman Kullanmamalısınız#

Stored procedure'ün maliyeti kod satırında değil, süreçte ortaya çıkar. En büyük sorun sürüm kontrolüdür. Uygulama kodunuz Git'te durur, kod incelemesinden geçer, CI'da test edilir. Procedure ise veritabanının içindedir; biri gecenin üçünde production'da CREATE OR REPLACE çalıştırdığında hiçbir yerde iz kalmaz. Bunu çözmenin tek yolu, procedure kaynaklarını da depoya koyup göç (migration) dosyalarıyla uygulamaktır.

İkinci sorun test edilebilirliktir. plpgsql içinde yazılmış iki yüz satırlık iş mantığını birim testle kapsamak, aynı mantığı Python'da test etmekten kat kat zordur. Hata ayıklama araçları sınırlıdır; çoğu zaman RAISE NOTICE ile log basmaktan öteye geçemezsiniz.

Üçüncüsü taşınabilirlik. plpgsql kodu MySQL'de çalışmaz, MySQL procedure'leri PostgreSQL'de çalışmaz. Veritabanı değiştirme ihtimaliniz varsa iş mantığını veritabanına gömmek, göçü tek başına imkânsız hâle getirebilir. İki motor arasındaki farkları merak ediyorsanız MySQL ve PostgreSQL karşılaştırması yazısında bu konuya ayrıntılı değinilmiş.

Dördüncüsü ölçekleme yönü. Uygulama sunucusu eklemek kolaydır, veritabanı sunucusunu büyütmek pahalıdır. İş mantığını procedure'lere yığdıkça CPU yükü kolay ölçeklenen katmandan zor ölçeklenen katmana kayar. Yoğun döngü içeren, satır satır işleyen procedure'ler bu açıdan özellikle risklidir.

Performans Gerçekten Artıyor mu#

"Stored procedure daha hızlıdır" cümlesi eskiden doğruydu, bugün kısmen doğrudur. Eski veritabanı motorlarında procedure'ler önceden derlenip planları saklanırdı; modern PostgreSQL ve MySQL sürümlerinde hazırlanmış ifadeler (prepared statements) benzer kazancı zaten sağlar. Bugün gerçek kazanç şuradan gelir:

Ölçmeden karar vermeyin. PostgreSQL'de bir procedure'ün içindeki sorguların planını görmek için auto_explain eklentisini kullanabilirsiniz:

# postgresql.conf içinde
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_nested_statements = on

log_nested_statements = on kritik satırdır: bu açık değilse procedure içindeki sorgular loglara düşmez, sadece CALL satırını görürsünüz ve yavaşlığın nereden geldiğini bulamazsınız. Genel ayar dosyası düzenlemesi için postgresql.conf optimizasyonu yazısına bakabilirsiniz.

Sık Yapılan Hatalar#

Döngü içinde tek satırlık işlem yapmak. En yaygın performans hatası budur. FOR r IN SELECT ... LOOP UPDATE ... END LOOP yapısı, küme tabanlı çalışan bir motoru satır satır çalışmaya zorlar. Aynı işi tek bir UPDATE ... FROM ile yapabiliyorsanız mutlaka öyle yapın; fark genelde 20-50 kat civarındadır.

Hata yakalamayı unutmak. PostgreSQL'de EXCEPTION bloğu olmayan bir procedure, hata anında tüm işlemi geri alır. Bu bazen istediğiniz şeydir, bazen değildir. MySQL'de ise DECLARE EXIT HANDLER tanımlamazsanız yarım kalmış bir işlem bırakabilirsiniz.

Parametreleri metin birleştirerek sorguya gömmek. EXECUTE 'SELECT * FROM t WHERE ad = ''' || p_ad || '''' yazmak, procedure'ün içine SQL injection açığı taşımaktır. Dinamik SQL yazmanız gerekiyorsa format() ve %I / %L yer tutucularını kullanın:

EXECUTE format('SELECT count(*) FROM %I WHERE ad = %L', p_tablo, p_ad) INTO v_adet;

Aşırı büyütmek. Beş yüz satırlık, on beş dallı bir procedure bakımı imkânsızdır. Bir procedure tek bir iş yapmalı ve tek ekranda okunabilmelidir. Daha büyük mantığı uygulama katmanına taşıyın.

Yedeklerde procedure'leri unutmak. pg_dump şema dökümüne procedure'leri dahil eder ama yalnızca veri yedeği alıyorsanız (--data-only) kodlarınız yedekte olmaz. Yedekleme stratejinizi kurarken pg_dump ile PostgreSQL yedekleme yazısındaki tam şema dökümü yaklaşımını benimseyin.

Sıkça Sorulan Sorular#

Stored procedure ile fonksiyon arasındaki temel fark nedir#

En temel fark işlem kontrolüdür. Fonksiyon kendisini çağıran işlemin içinde çalışır ve içinde COMMIT veya ROLLBACK kullanamaz; procedure ise kendi işlem sınırlarını yönetebilir. Ayrıca fonksiyon bir değer döndürmek zorundadır ve SELECT ifadesinin içinde kullanılabilir, procedure ise CALL ile çağrılır ve sorgu içinde kullanılamaz. Trigger'a yalnızca fonksiyon bağlanabilir.

Stored procedure performansı gerçekten artırır mı#

Artırır ama beklediğiniz sebepten değil. Modern motorlarda önceden derleme avantajı büyük ölçüde hazırlanmış ifadelerle zaten sağlanıyor. Gerçek kazanç ağ gidiş-dönüşlerinin azalması, kilitlerin daha kısa süre açık kalması ve büyük veri kümelerinin ağa hiç çıkmamasıdır. Tek bir basit sorguyu procedure'e sarmak ölçülebilir bir hız kazandırmaz.

İş mantığını veritabanına taşımak doğru mu#

Duruma bağlı. Veri bütünlüğünü koruyan kurallar, para ve stok gibi kritik alanların tutarlılığı veritabanında olmalıdır; çünkü orası tüm uygulamaların ortak kapısıdır. Ancak fiyatlandırma stratejisi, kampanya kuralları gibi sık değişen iş mantığını procedure'e gömmek bakımı zorlaştırır. Genel kural şudur: değişmeyen bütünlük kuralları aşağıda, sık değişen iş kuralları yukarıda dursun.

Stored procedure'leri nasıl sürüm kontrolüne alırım#

Procedure kaynak kodunu depoda .sql dosyaları olarak tutun ve her değişikliği bir göç dosyasıyla uygulayın. CREATE OR REPLACE kullanmak bu yaklaşımı kolaylaştırır çünkü aynı dosyayı yeniden çalıştırmak güvenlidir. Production'da doğrudan düzenleme yapmayı kesin bir kuralla yasaklayın; aksi halde depodaki kod ile çalışan kod arasındaki fark bir gün sizi çok yorar.

Stored procedure hata ayıklaması nasıl yapılır#

PostgreSQL'de en pratik yöntem RAISE NOTICE ile ara değerleri loglara basmaktır; auto_explain eklentisini log_nested_statements = on ile açarsanız procedure içindeki yavaş sorguları da görürsünüz. MySQL'de SELECT ile ara sonuçları döndürmek veya geçici bir log tablosuna yazmak yaygın yöntemdir. Adım adım hata ayıklama için pgAdmin'in debugger eklentisi vardır ama üretim sunucusunda kullanılması önerilmez.

Stored procedure yerine ne kullanmalıyım#

Basit tek adımlı işler için hazırlanmış ifadeler (prepared statements) ve uygulama katmanı yeterlidir. Sadece hesaplanmış bir sonuç istiyorsanız view veya materialized view daha temiz bir çözümdür. Veri değişikliğine otomatik tepki vermek istiyorsanız trigger uygun olabilir, ancak onun da kendi tuzakları vardır. Karar verirken "bu mantık gerçekten veriye yakın olmak zorunda mı" sorusunu sorun.

Kapanış#

Stored procedure, doğru yerde kullanıldığında ağ trafiğini düşüren, kilit sürelerini kısaltan ve yetki sınırlarını daraltan güçlü bir araçtır; yanlış yerde kullanıldığında ise test edilemeyen, sürümlenemeyen ve taşınamayan bir borç yığınıdır. Aklınızda kalması gereken dört alışkanlık şunlar: procedure'leri küçük ve tek işli tutun, kaynaklarını mutlaka depoda saklayıp göç dosyalarıyla uygulayın, SECURITY DEFINER kullandığınızda search_path sabitlemeyi asla atlamayın ve döngü yazmadan önce aynı işi tek bir küme sorgusuyla yapıp yapamayacağınızı sorun.

Bu tür yapıları kurarken altınızdaki sunucunun tutarlı kaynak vermesi de en az kodun kendisi kadar önemlidir; kilit çekişmesi ve I/O beklemesi çoğu zaman kodun değil altyapının sonucudur. Tam root erişimiyle kendi PostgreSQL kurulumunuzu yönetmek isterseniz VDS ve bulut sunucu paketlerimiz esnek bir başlangıç sağlar; kurulum, ayar ve izleme işini devretmek isterseniz sunucu yönetimi hizmetimiz bu yükü sizin yerinize üstlenir. Düzenli döküm ve geri dönüş provası için yedekleme çözümlerimize de göz atabilirsiniz.