MariaDB ve MySQL’de Parametreli Stored Procedure Örnekleri

Tarafındankomut
0 Yorum

Veritabanı yönetiminde tekrar eden işlemleri otomatize etmek, hem zaman kazandırır hem de hata riskini azaltır. Stored procedure’ler (saklı yordamlar) bu noktada devreye girer ve özellikle parametreli versiyonları, uygulamalarınızla veritabanınız arasında güçlü bir köprü kurar. Bu yazıda MariaDB ve MySQL üzerinde gerçek dünya senaryolarına dayanan parametreli stored procedure örneklerini inceleyeceğiz.

Stored Procedure Nedir ve Neden Parametreli Kullanmalısınız?

Stored procedure, veritabanı sunucusunda saklanan ve ihtiyaç duyulduğunda çağrılabilen SQL kod bloklarıdır. Parametreli hale getirdiğinizde ise bu kod blokları dinamik bir yapıya kavuşur. Uygulama katmanından veri gönderir, procedure içinde işlersiniz ve sonucu geri alırsınız.

Parametreli stored procedure kullanmanın avantajları şunlardır:

  • Kod tekrarını önler: Aynı mantığı defalarca yazmak yerine bir kez yazıp defalarca çağırırsınız
  • SQL injection’a karşı koruma: Parametreler doğrudan sorguya eklenmediğinden injection saldırıları engellenir
  • Performans artışı: Procedure’ler derlenerek önbelleğe alınır, her seferinde parse edilmez
  • Merkezi iş mantığı: Uygulama kodunu değiştirmeden veritabanı katmanında güncelleme yapabilirsiniz
  • Yetki yönetimi: Tablolara direkt erişim vermek yerine sadece procedure’e çağırma yetkisi verebilirsiniz

Temel Syntax ve Parametre Tipleri

MariaDB/MySQL’de üç tür parametre vardır:

  • IN: Dışarıdan procedure’e değer gönderir, en yaygın kullanılan tiptir
  • OUT: Procedure’den dışarıya değer döndürür
  • INOUT: Hem giriş hem çıkış olarak kullanılır, içeride değiştirilebilir

Temel bir stored procedure oluşturmadan önce delimiter’ı değiştirmeniz gerekir. Çünkü procedure gövdesi noktalı virgül içerdiğinden MySQL istemcisi karışabilir.

-- Delimiter'ı değiştir
DELIMITER $$

CREATE PROCEDURE procedure_adi(
    IN parametre1 INT,
    IN parametre2 VARCHAR(100),
    OUT sonuc INT
)
BEGIN
    -- SQL kodları buraya
    SELECT COUNT(*) INTO sonuc
    FROM tablo
    WHERE id = parametre1;
END$$

DELIMITER ;

Gerçek Dünya Senaryosu 1: Kullanıcı Kayıt İşlemi

E-ticaret uygulamanızda yeni kullanıcı kaydı sırasında birden fazla tabloya veri yazmak gerekebilir. Bunu tek bir procedure ile yönetebilirsiniz.

DELIMITER $$

CREATE PROCEDURE sp_kullanici_kayit(
    IN p_ad VARCHAR(50),
    IN p_soyad VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_sifre_hash VARCHAR(255),
    IN p_telefon VARCHAR(15),
    OUT p_kullanici_id INT,
    OUT p_hata_mesaji VARCHAR(255)
)
BEGIN
    DECLARE v_mevcut_email INT DEFAULT 0;
    
    -- Hata yakalama
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_hata_mesaji = 'Kayıt sırasında bir hata oluştu';
        SET p_kullanici_id = -1;
        ROLLBACK;
    END;
    
    -- Email kontrolü
    SELECT COUNT(*) INTO v_mevcut_email
    FROM kullanicilar
    WHERE email = p_email;
    
    IF v_mevcut_email > 0 THEN
        SET p_hata_mesaji = 'Bu email adresi zaten kayıtlı';
        SET p_kullanici_id = 0;
    ELSE
        START TRANSACTION;
        
        INSERT INTO kullanicilar (ad, soyad, email, sifre_hash, kayit_tarihi)
        VALUES (p_ad, p_soyad, p_email, p_sifre_hash, NOW());
        
        SET p_kullanici_id = LAST_INSERT_ID();
        
        -- Kullanıcı profil tablosuna da ekle
        INSERT INTO kullanici_profiller (kullanici_id, telefon, durum)
        VALUES (p_kullanici_id, p_telefon, 'aktif');
        
        -- Hoşgeldin bildirimi kuyruğuna ekle
        INSERT INTO bildirim_kuyrugu (kullanici_id, tip, mesaj, olusturma_tarihi)
        VALUES (p_kullanici_id, 'hosgeldin', 'Aramıza hoş geldiniz!', NOW());
        
        SET p_hata_mesaji = '';
        COMMIT;
    END IF;
END$$

DELIMITER ;

-- Çağırma şekli:
SET @yeni_id = 0;
SET @hata = '';
CALL sp_kullanici_kayit('Ahmet', 'Yılmaz', '[email protected]', 
                         'hash_deger', '05551234567', @yeni_id, @hata);
SELECT @yeni_id, @hata;

Gerçek Dünya Senaryosu 2: Sayfalandırmalı Arama

Uygulama geliştirirken sıkça ihtiyaç duyulan sayfalandırmalı ve filtrelenmiş arama işlemleri için güzel bir örnek:

DELIMITER $$

CREATE PROCEDURE sp_urun_ara(
    IN p_arama_terimi VARCHAR(100),
    IN p_kategori_id INT,
    IN p_min_fiyat DECIMAL(10,2),
    IN p_max_fiyat DECIMAL(10,2),
    IN p_sayfa INT,
    IN p_sayfa_boyutu INT,
    OUT p_toplam_kayit INT
)
BEGIN
    DECLARE v_offset INT;
    
    -- Offset hesapla
    SET v_offset = (p_sayfa - 1) * p_sayfa_boyutu;
    
    -- Toplam kayıt sayısını öğren
    SELECT COUNT(*) INTO p_toplam_kayit
    FROM urunler u
    INNER JOIN kategoriler k ON u.kategori_id = k.id
    WHERE u.durum = 'aktif'
      AND (p_arama_terimi IS NULL OR u.urun_adi LIKE CONCAT('%', p_arama_terimi, '%'))
      AND (p_kategori_id IS NULL OR u.kategori_id = p_kategori_id)
      AND (p_min_fiyat IS NULL OR u.fiyat >= p_min_fiyat)
      AND (p_max_fiyat IS NULL OR u.fiyat <= p_max_fiyat);
    
    -- Sayfalandırılmış sonucu getir
    SELECT 
        u.id,
        u.urun_adi,
        u.fiyat,
        u.stok_adedi,
        k.kategori_adi,
        u.guncelleme_tarihi
    FROM urunler u
    INNER JOIN kategoriler k ON u.kategori_id = k.id
    WHERE u.durum = 'aktif'
      AND (p_arama_terimi IS NULL OR u.urun_adi LIKE CONCAT('%', p_arama_terimi, '%'))
      AND (p_kategori_id IS NULL OR u.kategori_id = p_kategori_id)
      AND (p_min_fiyat IS NULL OR u.fiyat >= p_min_fiyat)
      AND (p_max_fiyat IS NULL OR u.fiyat <= p_max_fiyat)
    ORDER BY u.urun_adi
    LIMIT p_sayfa_boyutu OFFSET v_offset;
END$$

DELIMITER ;

-- Kullanım: 2. sayfada, elektronik kategorisinde, 
-- 100-500 TL arası "laptop" araması
SET @toplam = 0;
CALL sp_urun_ara('laptop', 3, 100.00, 500.00, 2, 10, @toplam);
SELECT @toplam AS toplam_sonuc;

Gerçek Dünya Senaryosu 3: Stok Güncelleme ve Loglama

Muhasebe veya stok takip sistemlerinde her değişikliğin loglanması kritik öneme sahiptir. INOUT parametresi kullanımını da bu örnekte göreceksiniz:

DELIMITER $$

CREATE PROCEDURE sp_stok_guncelle(
    IN p_urun_id INT,
    IN p_islem_tipi ENUM('giris', 'cikis', 'duzeltme'),
    INOUT p_miktar INT,
    IN p_aciklama VARCHAR(500),
    IN p_islem_yapan_id INT,
    OUT p_sonuc_mesaji VARCHAR(255)
)
BEGIN
    DECLARE v_mevcut_stok INT DEFAULT 0;
    DECLARE v_yeni_stok INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_sonuc_mesaji = CONCAT('Hata: Stok güncellenemedi, ürün ID: ', p_urun_id);
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Mevcut stok miktarını oku (FOR UPDATE ile kilitle)
    SELECT stok_adedi INTO v_mevcut_stok
    FROM urunler
    WHERE id = p_urun_id
    FOR UPDATE;
    
    -- Ürün bulunamadıysa
    IF v_mevcut_stok IS NULL THEN
        SET p_sonuc_mesaji = 'Ürün bulunamadı';
        SET p_miktar = -1;
        ROLLBACK;
    ELSE
        -- Yeni stok hesapla
        CASE p_islem_tipi
            WHEN 'giris' THEN
                SET v_yeni_stok = v_mevcut_stok + p_miktar;
            WHEN 'cikis' THEN
                IF v_mevcut_stok < p_miktar THEN
                    SET p_sonuc_mesaji = CONCAT('Yetersiz stok. Mevcut: ', v_mevcut_stok);
                    SET p_miktar = v_mevcut_stok;
                    ROLLBACK;
                    LEAVE sp_stok_guncelle;
                END IF;
                SET v_yeni_stok = v_mevcut_stok - p_miktar;
            WHEN 'duzeltme' THEN
                SET v_yeni_stok = p_miktar;
                SET p_miktar = v_yeni_stok - v_mevcut_stok;
        END CASE;
        
        -- Stok güncelle
        UPDATE urunler
        SET stok_adedi = v_yeni_stok,
            son_stok_guncelleme = NOW()
        WHERE id = p_urun_id;
        
        -- Log kaydı ekle
        INSERT INTO stok_hareketleri (
            urun_id, islem_tipi, miktar, 
            onceki_stok, sonraki_stok,
            aciklama, islem_yapan_id, islem_tarihi
        )
        VALUES (
            p_urun_id, p_islem_tipi, p_miktar,
            v_mevcut_stok, v_yeni_stok,
            p_aciklama, p_islem_yapan_id, NOW()
        );
        
        SET p_sonuc_mesaji = CONCAT('Stok güncellendi. Yeni stok: ', v_yeni_stok);
        COMMIT;
    END IF;
END$$

DELIMITER ;

-- Stok çıkışı örneği
SET @miktar = 5;
SET @sonuc = '';
CALL sp_stok_guncelle(42, 'cikis', @miktar, 'Online sipariş #1234', 7, @sonuc);
SELECT @miktar AS gerceklesen_miktar, @sonuc AS mesaj;

Gerçek Dünya Senaryosu 4: Rapor Üretme Procedure’ü

Yöneticilerin sık istediği satış raporlarını dinamik tarih aralıklarıyla üretmek için:

DELIMITER $$

CREATE PROCEDURE sp_satis_raporu(
    IN p_baslangic_tarihi DATE,
    IN p_bitis_tarihi DATE,
    IN p_kategori_id INT,
    IN p_para_birimi VARCHAR(3),
    OUT p_toplam_satis DECIMAL(15,2),
    OUT p_siparis_sayisi INT
)
BEGIN
    DECLARE v_kur_carpani DECIMAL(10,4) DEFAULT 1.0000;
    
    -- Para birimi dönüşümü
    IF p_para_birimi = 'USD' THEN
        SELECT kur INTO v_kur_carpani
        FROM doviz_kurlari
        WHERE para_birimi = 'USD'
          AND tarih = CURDATE();
    ELSEIF p_para_birimi = 'EUR' THEN
        SELECT kur INTO v_kur_carpani
        FROM doviz_kurlari
        WHERE para_birimi = 'EUR'
          AND tarih = CURDATE();
    END IF;
    
    -- Özet bilgileri OUT parametrelerine yaz
    SELECT 
        COALESCE(SUM(s.toplam_tutar * v_kur_carpani), 0),
        COUNT(DISTINCT s.id)
    INTO p_toplam_satis, p_siparis_sayisi
    FROM siparisler s
    INNER JOIN siparis_detaylari sd ON s.id = sd.siparis_id
    INNER JOIN urunler u ON sd.urun_id = u.id
    WHERE s.siparis_tarihi BETWEEN p_baslangic_tarihi AND p_bitis_tarihi
      AND s.durum IN ('tamamlandi', 'kargolandi')
      AND (p_kategori_id IS NULL OR u.kategori_id = p_kategori_id);
    
    -- Detaylı raporu result set olarak döndür
    SELECT 
        DATE_FORMAT(s.siparis_tarihi, '%Y-%m-%d') AS tarih,
        k.kategori_adi,
        u.urun_adi,
        SUM(sd.adet) AS toplam_adet,
        SUM(sd.birim_fiyat * sd.adet * v_kur_carpani) AS toplam_gelir,
        COUNT(DISTINCT s.id) AS siparis_sayisi
    FROM siparisler s
    INNER JOIN siparis_detaylari sd ON s.id = sd.siparis_id
    INNER JOIN urunler u ON sd.urun_id = u.id
    INNER JOIN kategoriler k ON u.kategori_id = k.id
    WHERE s.siparis_tarihi BETWEEN p_baslangic_tarihi AND p_bitis_tarihi
      AND s.durum IN ('tamamlandi', 'kargolandi')
      AND (p_kategori_id IS NULL OR u.kategori_id = p_kategori_id)
    GROUP BY DATE_FORMAT(s.siparis_tarihi, '%Y-%m-%d'), k.id, u.id
    ORDER BY tarih, toplam_gelir DESC;
END$$

DELIMITER ;

-- Tüm kategoriler için Ocak 2024 TL raporu
SET @toplam = 0;
SET @adet = 0;
CALL sp_satis_raporu('2024-01-01', '2024-01-31', NULL, 'TRY', @toplam, @adet);
SELECT @toplam AS toplam_satis_tl, @adet AS siparis_adedi;

Procedure Yönetimi: Listeleme, Değiştirme ve Silme

Procedure’leri yönetmek için sık kullandığım komutlar:

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

-- Belirli bir procedure'ün kodunu görüntüle
SHOW CREATE PROCEDURE sp_kullanici_kayit;

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

-- Procedure'ü güncelle (silip yeniden oluşturmak gerekir)
DROP PROCEDURE IF EXISTS sp_urun_ara;

DELIMITER $$
CREATE PROCEDURE sp_urun_ara(
    -- güncellenmiş parametreler ve kod
)
BEGIN
    -- yeni kod
END$$
DELIMITER ;

-- Procedure'e yetki ver
GRANT EXECUTE ON PROCEDURE veritabani_adi.sp_kullanici_kayit TO 'uygulama_kullanici'@'%';

-- information_schema üzerinden detaylı bilgi al
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED,
    LAST_ALTERED,
    SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'veritabani_adi'
  AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

Hata Yönetimi ve HANDLER Kullanımı

Production ortamında hata yönetimi olmayan stored procedure kullanmak ciddi sorunlara yol açabilir. Kapsamlı bir hata yönetimi örneği:

DELIMITER $$

CREATE PROCEDURE sp_toplu_fiyat_guncelle(
    IN p_kategori_id INT,
    IN p_artis_orani DECIMAL(5,2),
    IN p_min_fiyat_siniri DECIMAL(10,2),
    OUT p_guncellenen_adet INT,
    OUT p_hata_kodu INT,
    OUT p_hata_detay VARCHAR(500)
)
BEGIN
    -- Değişken tanımlamaları en başta olmalı
    DECLARE v_sqlstate VARCHAR(5);
    DECLARE v_mysql_errno INT;
    DECLARE v_mesaj TEXT;
    DECLARE v_etkilenen_satir INT DEFAULT 0;
    
    -- Genel SQL hatası yakalayıcı
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_sqlstate = RETURNED_SQLSTATE,
            v_mysql_errno = MYSQL_ERRNO,
            v_mesaj = MESSAGE_TEXT;
        
        SET p_hata_kodu = v_mysql_errno;
        SET p_hata_detay = CONCAT('SQLSTATE: ', v_sqlstate, ' | Mesaj: ', v_mesaj);
        SET p_guncellenen_adet = -1;
        ROLLBACK;
    END;
    
    -- Parametre doğrulama
    IF p_artis_orani <= -100 THEN
        SET p_hata_kodu = 1001;
        SET p_hata_detay = 'Artış oranı -100 den küçük olamaz';
        SET p_guncellenen_adet = 0;
    ELSEIF p_min_fiyat_siniri < 0 THEN
        SET p_hata_kodu = 1002;
        SET p_hata_detay = 'Minimum fiyat sınırı negatif olamaz';
        SET p_guncellenen_adet = 0;
    ELSE
        START TRANSACTION;
        
        -- Fiyat güncellemesini yap
        UPDATE urunler
        SET fiyat = fiyat * (1 + p_artis_orani / 100),
            guncelleme_tarihi = NOW()
        WHERE kategori_id = p_kategori_id
          AND fiyat >= p_min_fiyat_siniri
          AND durum = 'aktif';
        
        SET v_etkilenen_satir = ROW_COUNT();
        
        -- Değişiklik logu
        INSERT INTO fiyat_degisim_logu (
            kategori_id, artis_orani, 
            etkilenen_urun_sayisi, islem_tarihi
        )
        VALUES (
            p_kategori_id, p_artis_orani,
            v_etkilenen_satir, NOW()
        );
        
        SET p_guncellenen_adet = v_etkilenen_satir;
        SET p_hata_kodu = 0;
        SET p_hata_detay = '';
        
        COMMIT;
    END IF;
END$$

DELIMITER ;

-- Çağırım
SET @guncellenen = 0;
SET @hata_kod = 0;
SET @hata_detay = '';

CALL sp_toplu_fiyat_guncelle(5, 10.00, 50.00, @guncellenen, @hata_kod, @hata_detay);

SELECT 
    @guncellenen AS guncellenen_urun,
    @hata_kod AS hata_kodu,
    @hata_detay AS hata_detayi;

Procedure’leri PHP ve Python’dan Çağırmak

Gerçek uygulamalarda procedure’leri uygulama katmanından çağırmanız gerekecek. İki yaygın örnek:

# PHP PDO ile stored procedure çağırımı
# Bu örnekte PHP kodu yorum satırı olarak gösterilmiştir

# PHP kodunu test.php dosyasına yazıp çalıştırabilirsiniz:
# <?php
# $pdo = new PDO('mysql:host=localhost;dbname=veritabani', 'kullanici', 'sifre');
# $stmt = $pdo->prepare("CALL sp_kullanici_kayit(?, ?, ?, ?, ?, @kullanici_id, @hata)");
# $stmt->execute(['Ahmet', 'Yılmaz', '[email protected]', password_hash('sifre123', PASSWORD_BCRYPT), '05551234567']);
# $result = $pdo->query("SELECT @kullanici_id, @hata")->fetch();
# echo "Kullanıcı ID: " . $result['@kullanici_id'];
# ?>

# Python mysql-connector ile çağırım örneği
# python3 -c "
# import mysql.connector
# conn = mysql.connector.connect(host='localhost', database='veritabani', user='kullanici', password='sifre')
# cursor = conn.cursor()
# args = ('Ayse', 'Kaya', '[email protected]', 'hash_deger', '05559876543', 0, '')
# result_args = cursor.callproc('sp_kullanici_kayit', args)
# print('Kullanici ID:', result_args[5])
# print('Hata:', result_args[6])
# conn.close()
# "

Performans İpuçları

Production ortamında stored procedure kullanırken dikkat etmeniz gereken noktalar:

  • DETERMINISTIC bildirimi: Aynı parametrelerle her zaman aynı sonucu döndüren procedure’lere DETERMINISTIC ekleyin, replikasyon için önemlidir
  • SQL SECURITY: Varsayılan DEFINER yerine INVOKER kullanmayı değerlendirin, güvenlik açısından daha temizdir
  • Büyük result set’lerden kaçının: Procedure içinden çok büyük veri döndürmek yerine geçici tablolar veya cursor kullanmayı düşünün
  • Transaction sınırlarını küçük tutun: Uzun transaction’lar tablo kilitlemelerine yol açar
  • Procedure içinde procedure çağırmayı sınırlayın: Derin iç içe çağrılar performansı olumsuz etkiler ve debug’ı zorlaştırır
  • EXPLAIN ile test edin: Procedure içindeki SELECT’leri önce tek tek EXPLAIN ile analiz edin
-- Procedure içindeki bir sorguyu analiz etmek için
EXPLAIN
SELECT u.id, u.urun_adi, u.fiyat
FROM urunler u
INNER JOIN kategoriler k ON u.kategori_id = k.id
WHERE u.durum = 'aktif'
  AND u.kategori_id = 3
  AND u.fiyat BETWEEN 100.00 AND 500.00;

-- Procedure execution planını profile ile incele
SET profiling = 1;
CALL sp_urun_ara('laptop', 3, 100.00, 500.00, 1, 10, @toplam);
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SET profiling = 0;

Sonuç

Parametreli stored procedure’ler, MariaDB ve MySQL ortamlarında veritabanı yönetimini ciddi ölçüde kolaylaştıran araçlardır. Özellikle IN, OUT ve INOUT parametre tiplerini doğru kombinasyonlarda kullanmak, karmaşık iş mantığını tek bir çağrıyla yönetmenizi sağlar.

Bu yazıda incelediğimiz senaryolar, gerçek dünya uygulamalarında karşılaşacağınız durumların büyük bölümünü kapsıyor: kullanıcı kaydı, sayfalandırmalı arama, stok yönetimi, rapor üretimi ve toplu güncelleme işlemleri. Hata yönetimi kısmını asla atlamayın, production’da yakalanmayan bir exception transaction’ı yarıda bırakarak tutarsız veriye yol açabilir.

Son olarak, stored procedure’lerin her derde deva olmadığını belirtmek gerekir. Çok sık değişen iş mantığını uygulama katmanında tutmak, veritabanı bağımsız mimari açısından daha sağlıklı olabilir. Ama tekrarlayan, performans kritik ve çok adımlı işlemler için stored procedure hala en iyi arkadaşınız olmaya devam ediyor.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir