Stored Procedure Nedir? MySQL ve MariaDB Örnekleri

Veritabanı yönetiminde bir noktada “bu sorguyu her seferinde tekrar yazmak zorunda mıyım?” diye kendinize sordunuz mu? Ya da aynı mantığı hem uygulama kodunda hem de başka bir script’te tekrarladığınızı fark ettiniz mi? İşte stored procedure’ler tam bu sorunu çözmek için var. MariaDB ve MySQL’de stored procedure’ler, veritabanı sunucusu üzerinde saklanan, isim verilen ve tekrar tekrar çağrılabilen SQL kod bloklarıdır. Bugün bu yapıyı sıfırdan öğrenecek, gerçek dünya senaryolarıyla pekiştirecek ve production ortamında nasıl kullanacağınızı göreceğiz.

Stored Procedure Neden Kullanmalısınız?

Önce motivasyonu netleştirelim. Uygulamanızda bir e-ticaret sistemi var diyelim. Sipariş tamamlandığında şunlar olması gerekiyor: stok düşülecek, sipariş kaydı oluşturulacak, müşteri puanı güncellenecek, log tablosuna kayıt atılacak. Bu dört işlemi her uygulama katmanında tekrar yazmak yerine bir stored procedure içine koyarsanız hem tutarlılık sağlarsınız hem de network trafiğini azaltırsınız çünkü tek bir CALL komutu gönderirsiniz, dört ayrı sorgu değil.

Stored procedure kullanmanın başlıca avantajları şunlar:

Performans: Procedure ilk çalıştırıldığında derlenir ve execution plan cache’lenir. Sonraki çağrılarda bu plan doğrudan kullanılır.
Güvenlik: Kullanıcılara tablo üzerinde doğrudan erişim vermek yerine sadece procedure’ü çalıştırma yetkisi verebilirsiniz.
Bakım kolaylığı: İş mantığı tek bir yerde. Değişiklik gerektiğinde sadece procedure’ü güncelliyorsunuz.
Network yükü azaltma: Özellikle döngü içeren işlemlerde onlarca sorgu yerine tek bir çağrı yapılır.
Kod tekrarını önleme: Aynı mantığı PHP, Python, Java ve başka uygulamalar aynı procedure’ü çağırır.

Dezavantajlarını da bilmek gerekir tabii:

Hata ayıklamak uygulama koduna göre daha zor
Stored logic veritabanına bağımlılık yaratır, migration’larda dikkat gerekir
Çok karmaşık iş mantıkları için uygulama katmanı daha uygun olabilir

Temel Syntax ve İlk Procedure

MariaDB ve MySQL’de stored procedure yazmadan önce DELIMITER kavramını anlamanız gerekiyor. Normalde MySQL istemcisi noktalı virgül görünce komutu çalıştırır. Ama procedure içinde birden fazla statement var ve hepsinin noktalı virgülü var. Bunu çözmek için delimiter’ı geçici olarak değiştiriyoruz.

mysql -u root -p veritabani_adi

-- Delimiter'ı değiştiriyoruz
DELIMITER //

CREATE PROCEDURE ilk_procedure()
BEGIN
    SELECT 'Merhaba, Stored Procedure!' AS mesaj;
    SELECT NOW() AS sunucu_zamani;
END //

DELIMITER ;

Procedure’ü çağırmak için:

CALL ilk_procedure();

Bu basit örnek çalışıyor, güzel. Şimdi parametreler konusuna geçelim çünkü gerçek işler parametrelerle yapılır.

Parametreler: IN, OUT ve INOUT

Stored procedure’lerde üç tür parametre vardır:

IN: Prosedüre değer gönderirsiniz, procedure içinde kullanılır ama dışarıda değişmez
OUT: Procedure dışına değer döndürmek için kullanılır, başlangıç değeri NULL’dır
INOUT: Hem içeri değer gönderir hem de değiştirilmiş haliyle dışarı döner

Gerçek bir örnek görelim. Bir müşteri arama procedure’ü yazalım:

DELIMITER //

CREATE PROCEDURE musteri_ara(
    IN p_sehir VARCHAR(100),
    IN p_aktif TINYINT,
    OUT p_toplam_musteri INT
)
BEGIN
    -- Şehre ve aktiflik durumuna göre müşterileri listele
    SELECT 
        musteri_id,
        ad,
        soyad,
        email,
        kayit_tarihi
    FROM musteriler
    WHERE sehir = p_sehir 
      AND aktif = p_aktif
    ORDER BY kayit_tarihi DESC;
    
    -- Toplam sayıyı OUT parametresine ata
    SELECT COUNT(*) INTO p_toplam_musteri
    FROM musteriler
    WHERE sehir = p_sehir 
      AND aktif = p_aktif;
END //

DELIMITER ;

Bu procedure’ü çağırırken:

-- Önce session variable tanımlıyoruz OUT parametresi için
CALL musteri_ara('Istanbul', 1, @toplam);

-- OUT parametresinin değerini okuyoruz
SELECT @toplam AS istanbul_aktif_musteri_sayisi;

Değişkenler ve Kontrol Akışı

Gerçek dünya senaryolarında sadece SELECT sorgusu yazmakla kalmaz, değişkenler kullanır, koşullara göre farklı işlemler yaparsınız. MariaDB/MySQL’de procedure içinde değişken tanımlamak için DECLARE kullanılır ve bu satırlar BEGIN’den hemen sonra gelmelidir.

DELIMITER //

CREATE PROCEDURE siparis_ozeti(IN p_musteri_id INT)
BEGIN
    -- Değişken tanımlamaları en üstte olmalı
    DECLARE v_toplam_siparis INT DEFAULT 0;
    DECLARE v_toplam_tutar DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_musteri_adi VARCHAR(200);
    DECLARE v_kategori VARCHAR(50);
    
    -- Müşteri adını al
    SELECT CONCAT(ad, ' ', soyad) INTO v_musteri_adi
    FROM musteriler
    WHERE musteri_id = p_musteri_id;
    
    -- Sipariş istatistiklerini hesapla
    SELECT 
        COUNT(*),
        COALESCE(SUM(toplam_tutar), 0)
    INTO v_toplam_siparis, v_toplam_tutar
    FROM siparisler
    WHERE musteri_id = p_musteri_id
      AND durum != 'iptal';
    
    -- IF/ELSEIF/ELSE ile müşteri kategorisi belirle
    IF v_toplam_tutar >= 10000 THEN
        SET v_kategori = 'VIP Müşteri';
    ELSEIF v_toplam_tutar >= 5000 THEN
        SET v_kategori = 'Premium Müşteri';
    ELSEIF v_toplam_tutar >= 1000 THEN
        SET v_kategori = 'Standart Müşteri';
    ELSE
        SET v_kategori = 'Yeni Müşteri';
    END IF;
    
    -- Sonuçları döndür
    SELECT 
        v_musteri_adi AS musteri,
        v_toplam_siparis AS siparis_sayisi,
        v_toplam_tutar AS toplam_harcama,
        v_kategori AS musteri_kategorisi;
END //

DELIMITER ;

Döngüler: WHILE, REPEAT ve LOOP

Bazen toplu işlemler yapmanız gerekir. Örneğin belirli bir tarih aralığında günlük rapor oluşturmak ya da toplu veri güncellemesi yapmak. Bu durumlarda döngüler devreye girer.

Gerçek bir senaryo: Her ay sonu çalışan prim hesaplama sistemi. Satış ekibindeki her çalışan için hedef tutturup tutturmadığını kontrol edip prim tablosuna kayıt atalım.

DELIMITER //

CREATE PROCEDURE aylik_prim_hesapla(IN p_yil INT, IN p_ay INT)
BEGIN
    DECLARE v_bitti INT DEFAULT 0;
    DECLARE v_personel_id INT;
    DECLARE v_gerceklesen DECIMAL(10,2);
    DECLARE v_hedef DECIMAL(10,2);
    DECLARE v_prim DECIMAL(10,2);
    DECLARE v_prim_orani DECIMAL(5,2);
    
    -- Cursor tanımı
    DECLARE personel_cursor CURSOR FOR
        SELECT p.personel_id, p.aylik_hedef
        FROM personeller p
        WHERE p.departman = 'Satis'
          AND p.aktif = 1;
    
    -- Cursor bittiğinde v_bitti = 1 yap
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_bitti = 1;
    
    -- Önceki ay kaydı varsa temizle (idempotent çalışma için)
    DELETE FROM prim_kayitlari 
    WHERE yil = p_yil AND ay = p_ay;
    
    OPEN personel_cursor;
    
    prim_dongusu: LOOP
        FETCH personel_cursor INTO v_personel_id, v_hedef;
        
        IF v_bitti = 1 THEN
            LEAVE prim_dongusu;
        END IF;
        
        -- O personelin o ayki satışlarını topla
        SELECT COALESCE(SUM(satis_tutari), 0) INTO v_gerceklesen
        FROM satislar
        WHERE personel_id = v_personel_id
          AND YEAR(satis_tarihi) = p_yil
          AND MONTH(satis_tarihi) = p_ay;
        
        -- Prim oranını belirle
        IF v_gerceklesen >= v_hedef * 1.2 THEN
            SET v_prim_orani = 0.15; -- Hedefi %20 aştı, %15 prim
        ELSEIF v_gerceklesen >= v_hedef THEN
            SET v_prim_orani = 0.10; -- Hedefi tutturdu, %10 prim
        ELSEIF v_gerceklesen >= v_hedef * 0.8 THEN
            SET v_prim_orani = 0.05; -- %80 ulaştı, %5 prim
        ELSE
            SET v_prim_orani = 0.00; -- Prim yok
        END IF;
        
        SET v_prim = v_gerceklesen * v_prim_orani;
        
        -- Prim kaydını ekle
        INSERT INTO prim_kayitlari(personel_id, yil, ay, satis_tutari, prim_tutari, hesaplama_tarihi)
        VALUES(v_personel_id, p_yil, p_ay, v_gerceklesen, v_prim, NOW());
        
    END LOOP;
    
    CLOSE personel_cursor;
    
    -- Özet bilgi döndür
    SELECT 
        COUNT(*) AS hesaplanan_personel,
        SUM(prim_tutari) AS toplam_prim,
        AVG(prim_tutari) AS ortalama_prim
    FROM prim_kayitlari
    WHERE yil = p_yil AND ay = p_ay;
    
END //

DELIMITER ;

Bu procedure’ü her ay sonunda bir cron job ile çağırabilirsiniz:

# /etc/cron.d/aylik_prim dosyasına ekleyin
# Her ayın son günü saat 23:30'da çalışır
30 23 28-31 * * root [ "$(date +%d)" = "$(cal | awk '/^[ 0-9]/{print $NF}' | tail -1)" ] && mysql -u prim_user -pSIFRE veritabani -e "CALL aylik_prim_hesapla(YEAR(NOW()), MONTH(NOW()));"

Hata Yönetimi: DECLARE HANDLER

Production ortamında hata yönetimi olmayan stored procedure yazmak felakete davetiye çıkarmaktır. Özellikle transaction içeren işlemlerde bir şey ters giderse rollback yapabilmeniz gerekir.

DELIMITER //

CREATE PROCEDURE stok_guncelle_ve_siparis_olustur(
    IN p_urun_id INT,
    IN p_musteri_id INT,
    IN p_miktar INT,
    IN p_birim_fiyat DECIMAL(10,2),
    OUT p_siparis_id INT,
    OUT p_hata_mesaji VARCHAR(500)
)
BEGIN
    DECLARE v_mevcut_stok INT;
    DECLARE v_hata_kodu INT DEFAULT 0;
    
    -- Hata handler tanımı
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Hata bilgilerini al
        GET DIAGNOSTICS CONDITION 1
            v_hata_kodu = MYSQL_ERRNO;
        
        ROLLBACK;
        SET p_siparis_id = -1;
        SET p_hata_mesaji = CONCAT('Hata kodu: ', v_hata_kodu, ' - İşlem geri alındı');
    END;
    
    -- Transaction başlat
    START TRANSACTION;
    
    -- Stok kontrolü (FOR UPDATE ile satırı kilitle)
    SELECT stok_miktari INTO v_mevcut_stok
    FROM urunler
    WHERE urun_id = p_urun_id
    FOR UPDATE;
    
    -- Yeterli stok var mı?
    IF v_mevcut_stok IS NULL THEN
        SET p_hata_mesaji = 'Ürün bulunamadı';
        SET p_siparis_id = -1;
        ROLLBACK;
    ELSEIF v_mevcut_stok < p_miktar THEN
        SET p_hata_mesaji = CONCAT('Yetersiz stok. Mevcut: ', v_mevcut_stok, ', Talep: ', p_miktar);
        SET p_siparis_id = -1;
        ROLLBACK;
    ELSE
        -- Stok düş
        UPDATE urunler 
        SET stok_miktari = stok_miktari - p_miktar,
            son_guncelleme = NOW()
        WHERE urun_id = p_urun_id;
        
        -- Siparişi oluştur
        INSERT INTO siparisler(musteri_id, urun_id, miktar, birim_fiyat, toplam_tutar, siparis_tarihi, durum)
        VALUES(p_musteri_id, p_urun_id, p_miktar, p_birim_fiyat, p_miktar * p_birim_fiyat, NOW(), 'beklemede');
        
        SET p_siparis_id = LAST_INSERT_ID();
        
        -- Log kaydı
        INSERT INTO islem_loglari(tablo_adi, islem_turu, kayit_id, aciklama, tarih)
        VALUES('siparisler', 'INSERT', p_siparis_id, 
               CONCAT('Müşteri ', p_musteri_id, ' için sipariş oluşturuldu'), NOW());
        
        COMMIT;
        SET p_hata_mesaji = 'Başarılı';
    END IF;
    
END //

DELIMITER ;

Kullanımı:

CALL stok_guncelle_ve_siparis_olustur(42, 101, 3, 299.99, @siparis_id, @hata);
SELECT @siparis_id, @hata;

Procedure Yönetimi: Listeleme, Görüntüleme ve Silme

Günlük yönetim işlemleri için bilmeniz gereken komutlar:

-- Veritabanındaki tüm procedure'leri listele
SHOW PROCEDURE STATUS WHERE Db = 'veritabani_adi';

-- Procedure kodunu görüntüle
SHOW CREATE PROCEDURE musteri_ara;

-- Belirli bir procedure hakkında bilgi
SELECT 
    ROUTINE_NAME,
    CREATED,
    LAST_ALTERED,
    DEFINER,
    SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'veritabani_adi'
  AND ROUTINE_TYPE = 'PROCEDURE';

-- Procedure'ü sil
DROP PROCEDURE IF EXISTS eski_procedure;

-- Mevcut procedure'ü güncelle (MySQL'de ALTER yok, drop-create yapıyoruz)
DROP PROCEDURE IF EXISTS musteri_ara;

DELIMITER //
CREATE PROCEDURE musteri_ara(...)
BEGIN
    -- Yeni kod buraya
END //
DELIMITER ;

Yetki Yönetimi

Stored procedure’lerin güvenlik avantajından yararlanmak için yetki yönetimini doğru kurgulamanız gerekir. Örneğin uygulama kullanıcısına tablolara doğrudan erişim vermek yerine sadece ilgili procedure’leri çağırma yetkisi verin:

-- Uygulama kullanıcısı oluştur
CREATE USER 'uygulama_user'@'10.0.1.%' IDENTIFIED BY 'guclu_sifre_buraya';

-- Tablolara doğrudan erişim verme
-- Sadece procedure çalıştırma yetkisi ver
GRANT EXECUTE ON PROCEDURE veritabani.musteri_ara TO 'uygulama_user'@'10.0.1.%';
GRANT EXECUTE ON PROCEDURE veritabani.stok_guncelle_ve_siparis_olustur TO 'uygulama_user'@'10.0.1.%';
GRANT EXECUTE ON PROCEDURE veritabani.aylik_prim_hesapla TO 'uygulama_user'@'10.0.1.%';

FLUSH PRIVILEGES;

-- Yetkileri kontrol et
SHOW GRANTS FOR 'uygulama_user'@'10.0.1.%';

SQL SECURITY kavramına da dikkat etmek gerekir. Procedure oluştururken iki seçeneğiniz var:

DEFINER: Procedure, onu oluşturan kullanıcının yetkileriyle çalışır. Yani çağıran kullanıcının tablolara doğrudan erişimi olmasa bile procedure çalışır.
INVOKER: Procedure, çağıran kullanıcının yetkileriyle çalışır.

Çoğu durumda DEFINER daha uygun ve güvenlidir:

CREATE DEFINER = 'db_admin'@'localhost' 
PROCEDURE veritabani.hassas_rapor()
SQL SECURITY DEFINER
BEGIN
    SELECT * FROM hassas_musteri_verileri;
END;

Performans İpuçları

Production ortamında stored procedure yazarken dikkat etmeniz gereken birkaç kritik nokta var.

Procedure’ün execution plan’ını görmek için:

-- Procedure'ün kullandığı sorguları test et
EXPLAIN SELECT musteri_id, ad, soyad 
FROM musteriler 
WHERE sehir = 'Istanbul' AND aktif = 1;

-- Procedure çalışma süresini ölç
SET profiling = 1;
CALL musteri_ara('Istanbul', 1, @toplam);
SHOW PROFILES;
SET profiling = 0;

Dikkat etmeniz gereken performans konuları:

Index kullanımı: Procedure içindeki WHERE koşullarında kullanılan sütunlarda index olduğundan emin olun. Aksi halde her CALL’da full table scan yapılır.
Cursor’lardan kaçının: Mümkün olduğunca set-based işlemler yapın. Cursor döngüsü satır satır işlem yaptığından yavaştır. Tek bir UPDATE ile yapabildiğinizi cursor’la yapmayın.
Transaction boyutu: Uzun transaction’lar lock sorunlarına yol açar. Mümkünse transaction’ları küçük tutun.
Temporary table: Karmaşık hesaplamalarda temporary table kullanmak bazen daha verimlidir.

DELIMITER //

CREATE PROCEDURE toplu_guncelleme_verimli(IN p_kategori_id INT, IN p_artis_orani DECIMAL(5,2))
BEGIN
    -- Cursor yerine set-based güncelleme - çok daha hızlı
    UPDATE urunler u
    JOIN kategoriler k ON u.kategori_id = k.kategori_id
    SET u.fiyat = u.fiyat * (1 + p_artis_orani / 100),
        u.son_guncelleme = NOW()
    WHERE k.kategori_id = p_kategori_id
      AND u.aktif = 1;
    
    SELECT ROW_COUNT() AS guncellenen_urun_sayisi;
END //

DELIMITER ;

Procedure’leri Komut Satırından Çağırmak

Cron job’lar veya shell script’lerden procedure çağırmak için:

#!/bin/bash
# /opt/scripts/aylik_rapor.sh

DB_HOST="localhost"
DB_USER="rapor_user"
DB_PASS="sifre"
DB_NAME="veritabani"
LOG_FILE="/var/log/aylik_rapor.log"

YIL=$(date +%Y)
AY=$(date +%m)

echo "[$(date)] Aylık prim hesaplama başladı - $YIL/$AY" >> $LOG_FILE

mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME 
  --skip-column-names 
  -e "CALL aylik_prim_hesapla($YIL, $AY);" >> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
    echo "[$(date)] İşlem başarıyla tamamlandı" >> $LOG_FILE
else
    echo "[$(date)] HATA: İşlem başarısız" >> $LOG_FILE
    # Alarm mail gönder
    echo "Aylık prim hesaplama hatası - $YIL/$AY" | mail -s "DB Alert" [email protected]
fi

Procedure Debugging

Stored procedure hata ayıklamak gerçekten can sıkıcı olabiliyor. Birkaç pratik yöntem:

DELIMITER //

CREATE PROCEDURE debug_ornegi(IN p_deger INT)
BEGIN
    DECLARE v_adim VARCHAR(100);
    
    SET v_adim = 'Başlangıç';
    
    -- Debug tablosuna log at
    INSERT INTO debug_log(procedure_adi, adim, deger, zaman)
    VALUES('debug_ornegi', v_adim, p_deger, NOW());
    
    -- İşlem yap
    SET v_adim = 'Hesaplama';
    SET p_deger = p_deger * 2;
    
    INSERT INTO debug_log(procedure_adi, adim, deger, zaman)
    VALUES('debug_ornegi', v_adim, p_deger, NOW());
    
    -- SELECT ile ara değerleri görüntüle (development ortamında)
    SELECT v_adim AS mevcut_adim, p_deger AS mevcut_deger;
    
END //

DELIMITER ;

Development ortamında SELECT’leri debug için kullanabilirsiniz ama production’a almadan önce temizleyin ya da bir debug flag parametresi ekleyin.

Sonuç

Stored procedure’ler doğru kullanıldığında veritabanı mimarinize ciddi katkı sağlar. Tekrarlayan iş mantığını tek bir yere toplar, güvenlik katmanı oluşturur ve uygulama ile veritabanı arasındaki iletişimi optimize eder. Özellikle birden fazla uygulamanın aynı veritabanını kullandığı ortamlarda iş mantığını procedure’lerde tutmak tutarlılığı garanti eder.

Bununla birlikte her şeyi stored procedure’e dökmek de doğru değil. Basit CRUD operasyonları için ORM veya uygulama katmanı yeterlidir. Stored procedure’ler özellikle karmaşık iş kuralları, toplu veri işleme, birden fazla tabloyu etkileyen atomik işlemler ve güvenlik gerektiren kritik operasyonlar için değer yaratır.

Bu yazıdaki örnekleri kendi test ortamınızda deneyimleyin, cursor’lar yerine set-based işlemler yazmayı alışkanlık haline getirin ve her zaman hata yönetimini ihmal etmeyin. Production’da “beklenmedik şeyler her zaman olur” zihniyetiyle yazan kod, uzun vadede sizi sabah 3’te uyandırmaktan kurtarır.

Stored Procedure Nedir ve Nasıl Yazılır?