Stored Procedures ve Functions

Giriş — SQL'i Programlama Dili Gibi Kullanmak

Şimdiye kadar SQL'i tek seferlik sorgular için kullandın: bir SELECT yaz, sonuç al. Bir UPDATE yaz, veri güncelle. Ama gerçek dünyada aynı iş mantığını tekrar tekrar çalıştırman gerekir: "yeni sipariş oluştur ve stoku güncelle", "aylık fatura hesapla", "eski kayıtları arşivle."

Bu iş mantıklarını her seferinde uygulama kodundan SQL olarak göndermek yerine, veritabanının içinde saklayabilirsin. İşte stored procedure ve function tam olarak bunu yapar: SQL komutlarını bir program gibi paketler, isim verir ve tekrar tekrar çağırılabilir hale getirir.

🎯 Analoji: Stored procedure, bir yemek tarifinin mutfağa asılı hali gibi. Her seferinde "önce soğanı doğra, sonra kızart, sonra domatesi ekle..." diye düşünmek yerine, tarifin adını söylersin: "Menemen yap!" Adımlar zaten tarif kağıdında yazılı — mutfak (veritabanı) ne yapacağını bilir.

Stored Procedure Nedir?

Stored procedure, veritabanında saklanan, isimlendirilmiş bir SQL program bloğudur. Parametre alabilir, birden fazla SQL komutu çalıştırabilir, koşullu mantık (IF/ELSE) ve döngüler (WHILE/LOOP) kullanabilir.

İlk Stored Procedure

-- DELIMITER değiştir (procedure içinde ; kullanacağız)
DELIMITER //

CREATE PROCEDURE get_customer_orders(IN p_customer_id INT)
BEGIN
    SELECT 
        o.order_id,
        o.order_date,
        o.total_amount,
        o.status
    FROM orders o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.order_date DESC;
END //

DELIMITER ;

Çağırma:

CALL get_customer_orders(101);
-- customer_id = 101 olan müşterinin siparişlerini döndürür

DELIMITER Neden Gerekli?

Procedure içinde birden fazla SQL komutu var ve her biri ; ile bitiyor. Ama MySQL ; gördüğünde komutu çalıştırmaya çalışır. DELIMITER'ı // veya $$ gibi farklı bir karakter dizisine değiştirerek, MySQL'e "procedure bitene kadar bekle" diyorsun.

Parametre Türleri: IN, OUT, INOUT

DELIMITER //

CREATE PROCEDURE get_customer_stats(
    IN p_customer_id INT,          -- Girdi parametresi
    OUT p_total_spent DECIMAL(10,2),  -- Çıktı parametresi
    OUT p_order_count INT,
    INOUT p_message VARCHAR(200)   -- Hem girdi hem çıktı
)
BEGIN
    SELECT 
        SUM(total_amount),
        COUNT(*)
    INTO p_total_spent, p_order_count
    FROM orders
    WHERE customer_id = p_customer_id;
    
    SET p_message = CONCAT(p_message, ' - Toplam: ', p_total_spent, ' TL');
END //

DELIMITER ;

Kullanım:

SET @msg = 'Müşteri Raporu';
CALL get_customer_stats(101, @total, @count, @msg);
SELECT @total, @count, @msg;
-- @total: 25000.00
-- @count: 12
-- @msg: 'Müşteri Raporu - Toplam: 25000.00 TL'

Kontrol Yapıları — IF, CASE, LOOP

IF / ELSEIF / ELSE

DELIMITER //

CREATE PROCEDURE apply_discount(
    IN p_order_id INT,
    OUT p_discount_applied VARCHAR(50)
)
BEGIN
    DECLARE v_total DECIMAL(10,2);
    
    SELECT total_amount INTO v_total 
    FROM orders WHERE order_id = p_order_id;
    
    IF v_total >= 10000 THEN
        UPDATE orders SET total_amount = v_total * 0.90 WHERE order_id = p_order_id;
        SET p_discount_applied = '%10 indirim uygulandı';
    ELSEIF v_total >= 5000 THEN
        UPDATE orders SET total_amount = v_total * 0.95 WHERE order_id = p_order_id;
        SET p_discount_applied = '%5 indirim uygulandı';
    ELSE
        SET p_discount_applied = 'İndirim uygulanmadı (minimum 5000 TL)';
    END IF;
END //

DELIMITER ;

CASE

DELIMITER //

CREATE PROCEDURE get_shipping_cost(
    IN p_city VARCHAR(100),
    OUT p_cost DECIMAL(8,2)
)
BEGIN
    CASE p_city
        WHEN 'İstanbul' THEN SET p_cost = 0;      -- Ücretsiz
        WHEN 'Ankara' THEN SET p_cost = 25.00;
        WHEN 'İzmir' THEN SET p_cost = 30.00;
        ELSE SET p_cost = 50.00;                   -- Diğer şehirler
    END CASE;
END //

DELIMITER ;

WHILE Loop

DELIMITER //

CREATE PROCEDURE archive_old_orders(IN p_batch_size INT)
BEGIN
    DECLARE v_affected INT DEFAULT 1;
    DECLARE v_total_archived INT DEFAULT 0;
    
    -- Batch halinde eski siparişleri arşivle
    WHILE v_affected > 0 DO
        INSERT INTO orders_archive
        SELECT * FROM orders 
        WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
          AND status = 'completed'
        LIMIT p_batch_size;
        
        SET v_affected = ROW_COUNT();
        
        DELETE FROM orders 
        WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
          AND status = 'completed'
        LIMIT p_batch_size;
        
        SET v_total_archived = v_total_archived + v_affected;
        
        -- Her batch arasında kısa bekleme (lock'ları serbest bırak)
        -- DO SLEEP(0.1); -- İsteğe bağlı
    END WHILE;
    
    SELECT CONCAT(v_total_archived, ' sipariş arşivlendi') AS result;
END //

DELIMITER ;

-- Kullanım
CALL archive_old_orders(5000);  -- 5000'er batch halinde arşivle

Error Handling — Hata Yönetimi

DELIMITER //

CREATE PROCEDURE create_order(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;
    
    -- Hata yakalayıcı (error handler)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = -1;  -- Hata durumunda -1 döndür
    END;
    
    START TRANSACTION;
    
    -- Stok kontrolü
    SELECT price, stock_quantity INTO v_price, v_stock
    FROM products 
    WHERE product_id = p_product_id
    FOR UPDATE;  -- Satır kilidi
    
    IF v_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Yetersiz stok';
    END IF;
    
    -- Sipariş oluştur
    INSERT INTO orders (customer_id, order_date, total_amount, status)
    VALUES (p_customer_id, NOW(), v_price * p_quantity, 'pending');
    
    SET p_order_id = LAST_INSERT_ID();
    
    -- Sipariş detayı ekle
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_quantity, v_price);
    
    -- Stoku güncelle
    UPDATE products 
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;
    
    COMMIT;
END //

DELIMITER ;

SIGNAL ile özel hata fırlatabilirsin, DECLARE HANDLER ile hataları yakalayabilirsin. Bu, uygulama seviyesinde try/catch yapısının veritabanı karşılığı.

Stored Function — Değer Döndüren Alt Programlar

Function, procedure'dan farklı olarak tek bir değer döndürür ve SQL ifadeleri içinde kullanılabilir:

DELIMITER //

CREATE FUNCTION calculate_tax(p_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- Aynı girdi her zaman aynı çıktıyı verir
BEGIN
    RETURN p_amount * 0.18;  -- %18 KDV
END //

DELIMITER ;

Kullanım — tablo gibi değil, ifade içinde:

-- Function'ı SELECT'te kullan
SELECT 
    order_id, 
    total_amount,
    calculate_tax(total_amount) AS tax,
    total_amount + calculate_tax(total_amount) AS total_with_tax
FROM orders;

-- WHERE'de kullan
SELECT * FROM orders 
WHERE total_amount + calculate_tax(total_amount) > 10000;

Procedure vs Function

Daha Fazla Function Örneği

-- Müşteri segmentini döndüren function
DELIMITER //

CREATE FUNCTION get_customer_segment(p_customer_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(12,2);
    
    SELECT COALESCE(SUM(total_amount), 0) INTO v_total
    FROM orders
    WHERE customer_id = p_customer_id;
    
    IF v_total >= 50000 THEN RETURN 'VIP';
    ELSEIF v_total >= 20000 THEN RETURN 'Premium';
    ELSEIF v_total >= 5000 THEN RETURN 'Standart';
    ELSE RETURN 'Yeni';
    END IF;
END //

DELIMITER ;

-- Kullanım
SELECT 
    customer_id, 
    first_name,
    get_customer_segment(customer_id) AS segment
FROM customers;

⚠️ Dikkat: Function'ı SELECT'te her satır için çağırmak, N+1 problemine benzer bir performans sorunu yaratabilir. 10.000 satırlık tabloda function 10.000 kez çalışır. Mümkünse JOIN veya window function tercih et.

Cursor — Satır Satır İşleme

Cursor, bir sorgu sonucunu satır satır işlemeni sağlar. Genellikle set-based (küme tabanlı) çözüm bulunamadığında son çare olarak kullanılır:

DELIMITER //

CREATE PROCEDURE send_payment_reminders()
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_customer_id INT;
    DECLARE v_email VARCHAR(200);
    DECLARE v_amount DECIMAL(10,2);
    
    -- Cursor tanımla
    DECLARE overdue_cursor CURSOR FOR
        SELECT c.customer_id, c.email, o.total_amount
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
        WHERE o.status = 'pending'
          AND o.order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);
    
    -- Son satır kontrolü
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    OPEN overdue_cursor;
    
    read_loop: LOOP
        FETCH overdue_cursor INTO v_customer_id, v_email, v_amount;
        IF v_done THEN LEAVE read_loop; END IF;
        
        -- Her müşteri için hatırlatma kaydı oluştur
        INSERT INTO payment_reminders (customer_id, email, amount, reminder_date)
        VALUES (v_customer_id, v_email, v_amount, NOW());
    END LOOP;
    
    CLOSE overdue_cursor;
END //

DELIMITER ;

💡 İpucu: Cursor'lar yavaştır — set-based çözüm (INSERT...SELECT, UPDATE...JOIN) her zaman tercih edilmeli. Cursor, sadece satır bazlı karmaşık iş mantığı gerektiğinde kullanılmalı.

Procedure Yönetimi

-- Mevcut procedure'ları listele
SHOW PROCEDURE STATUS WHERE Db = 'ecommerce';

-- Procedure tanımını göster
SHOW CREATE PROCEDURE create_order;

-- Procedure'ı sil
DROP PROCEDURE IF EXISTS create_order;

-- Function'ı sil
DROP FUNCTION IF EXISTS calculate_tax;

-- Procedure'ı değiştirmek için: sil + yeniden oluştur
-- MySQL'de ALTER PROCEDURE sadece meta bilgileri değiştirir (COMMENT, SQL SECURITY)

Gerçek Dünya Örneği — Sipariş İşleme Pipeline

DELIMITER //

CREATE PROCEDURE process_order(
    IN p_customer_id INT,
    IN p_items JSON,  -- [{"product_id": 1, "quantity": 2}, ...]
    OUT p_order_id INT,
    OUT p_status VARCHAR(100)
)
BEGIN
    DECLARE v_total DECIMAL(12,2) DEFAULT 0;
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_item_count INT;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = NULL;
        SET p_status = 'HATA: İşlem geri alındı';
    END;
    
    SET v_item_count = JSON_LENGTH(p_items);
    
    IF v_item_count = 0 THEN
        SET p_status = 'HATA: Sepet boş';
        SET p_order_id = NULL;
        LEAVE proc_label;
    END IF;
    
    START TRANSACTION;
    
    -- Sipariş oluştur
    INSERT INTO orders (customer_id, order_date, total_amount, status)
    VALUES (p_customer_id, NOW(), 0, 'processing');
    SET p_order_id = LAST_INSERT_ID();
    
    -- Her ürünü ekle
    WHILE v_i < v_item_count DO
        SET v_product_id = JSON_EXTRACT(p_items, CONCAT('$[', v_i, '].product_id'));
        SET v_quantity = JSON_EXTRACT(p_items, CONCAT('$[', v_i, '].quantity'));
        
        -- Stok ve fiyat kontrolü
        SELECT price, stock_quantity INTO v_price, v_stock
        FROM products WHERE product_id = v_product_id FOR UPDATE;
        
        IF v_stock < v_quantity THEN
            ROLLBACK;
            SET p_order_id = NULL;
            SET p_status = CONCAT('HATA: Yetersiz stok - Ürün ID: ', v_product_id);
            LEAVE proc_label;
        END IF;
        
        -- Sipariş detayı ekle
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, v_product_id, v_quantity, v_price);
        
        -- Stok düş
        UPDATE products SET stock_quantity = stock_quantity - v_quantity
        WHERE product_id = v_product_id;
        
        SET v_total = v_total + (v_price * v_quantity);
        SET v_i = v_i + 1;
    END WHILE;
    
    -- Toplam tutarı güncelle
    UPDATE orders SET total_amount = v_total, status = 'confirmed'
    WHERE order_id = p_order_id;
    
    COMMIT;
    SET p_status = CONCAT('BAŞARILI: Sipariş #', p_order_id, ' oluşturuldu. Toplam: ', v_total, ' TL');
    
END //

DELIMITER ;

-- Kullanım
CALL process_order(
    101, 
    '[{"product_id": 1, "quantity": 2}, {"product_id": 5, "quantity": 1}]',
    @oid, @status
);
SELECT @oid, @status;

PostgreSQL Farklılıkları

-- PostgreSQL: PL/pgSQL kullanır, DELIMITER gerektirmez
CREATE OR REPLACE FUNCTION calculate_tax(p_amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_amount * 0.18;
END;
$$ LANGUAGE plpgsql;

-- PostgreSQL: Procedure (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE archive_orders(p_days INT)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO orders_archive SELECT * FROM orders 
    WHERE order_date < CURRENT_DATE - p_days;
    DELETE FROM orders WHERE order_date < CURRENT_DATE - p_days;
    COMMIT;
END;
$$;

-- PostgreSQL: Birden fazla dil destekler (PL/pgSQL, PL/Python, PL/Perl)

Özet

• Stored Procedure veritabanında saklanan SQL program bloğudur — CALL ile çağrılır

• IN/OUT/INOUT parametreleri ile girdi alır, çıktı verir

• IF/CASE/WHILE/LOOP kontrol yapıları ile prosedürel mantık yazılır

• Error handling: DECLARE HANDLER + SIGNAL ile hata yakalama ve fırlatma

• Stored Function tek değer döndürür ve SQL ifadelerinde kullanılır

• Cursor satır satır işleme yapar — son çare, set-based çözüm tercih et

• Procedure iş mantığını veritabanına taşır — avantaj: tek yerde bakım, dezavantaj: veritabanı bağımlılığı

Sıkça Yapılan Hatalar

1. DELIMITER'ı geri değiştirmeyi unutmak — Procedure sonunda DELIMITER ; yazmazsan, sonraki komutlar çalışmaz.

2. Function'ı her satırda çağırıp performans kaybı — SELECT'te function çağırmak N+1 benzeri etki yapar. Büyük tablolarda JOIN veya window function tercih et.

3. Cursor'ı set-based çözüm varken kullanmak — INSERT...SELECT, UPDATE...JOIN her zaman cursor'dan hızlıdır. Cursor sadece gerçekten satır bazlı mantık gerektiğinde.

4. Transaction'ı procedure içinde yönetmemek — Birden fazla tablo güncelleniyorsa, hata durumunda ROLLBACK gerekir. DECLARE EXIT HANDLER ile otomatik rollback ekle.

5. Stored procedure'lara aşırı bağımlılık — Tüm iş mantığını procedure'lara koymak, uygulamanın test edilmesini, debug edilmesini ve farklı veritabanına taşınmasını zorlaştırır. Dengeli kullan.