Saklı Prosedürler: Nasıl Çalışırlar ve Nasıl İyi Kullanılırlar

Veritabanı tabanlı her uygulama, uygulama koduna dağılmış SQL sorgularının bir noktada bakım sorunu haline geldiği bir noktaya ulaşır. Aynı karmaşık birleştirme işlemi üç farklı serviste görünür. Veritabanı katmanında olması gereken iş mantığı uygulamaya sızar. Veri katmanında uygulanması gereken güvenlik politikaları, bunun yerine atlatılabilen uygulama kodu tarafından tutarsız bir şekilde uygulanır. Saklı prosedürler, yeniden kullanılabilir, güvenliğe duyarlı ve performans açısından kritik SQL mantığını, onu çağıran uygulamalardan bağımsız olarak yönetilebilen, sürümlendirilebilen, güvenli hale getirilebilen ve optimize edilebilen veritabanına taşıyarak bu tür sorunları ele alır.

Saklı prosedür, veritabanında depolanan ve bir bütün olarak yürütülen, adlandırılmış, önceden derlenmiş bir SQL ifadeleri kümesidir. Parametreleri kabul eder, mantık içerir ve sonuçlar, çıktı parametreleri veya durum kodları döndürebilir. Uygulama kodu tarafından gönderilen geçici sorguların aksine, saklı prosedür bir kez ayrıştırılır ve derlenir, yürütme planı önbelleğe alınır ve sonraki her çağrıda yeniden kullanılır; bu da tekrarlanan dinamik SQL'in neden olduğu derleme yükünü ortadan kaldırır. Bu kılavuz, saklı prosedürlerin ne olduğunu, ne zaman kullanılacağını, güvenliği nasıl sağladığını, performansı nasıl etkilediğini ve tüm veritabanı ortamını kapsayan bağımlılıklara dönüştükçe nasıl yönetileceğini ele almaktadır.

Saklı Prosedür Nedir?

Saklı prosedür, ilişkisel bir veritabanında depolanan ve isteğe bağlı parametrelerle birlikte adı belirtilerek çağrılan önceden derlenmiş bir rutindir. Veritabanı motoru bunu bir kez derler, yürütme planını önbelleğe alır ve sonraki her çağrıda bu planı yeniden kullanır; böylece, her çalıştırmada geçici SQL sorgularının gerektirdiği ayrıştırma-derleme-optimizasyon döngüsünden kaçınılır.

sql

-- SQL Server: basic stored procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
END;
GO

-- Calling the procedure
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

Uygulama hiçbir zaman doğrudan SQL yazmaz. Bunun yerine çağrı yapar. GetCustomerOrders Parametrelerle birlikte. Veritabanı gerisini halleder.

Saklı Prosedür, Görünüm ve Fonksiyon Karşılaştırması

Veritabanında sıklıkla karıştırılan üç nesne vardır. Aşağıdaki tabloda bunlar birbirinden ayırt edilmektedir:

nesne	Geri dönüşler	Parametreleri kabul eder	Verileri Değiştirebilirsiniz	Uygulama Planı Önbelleğe Alındı	En
Saklı yordam	Sonuç kümeleri, çıktı parametreleri, dönüş kodları	Evet	Evet	Evet	Karmaşık mantık, DML işlemleri, güvenlik uygulaması
Görüntüle	Tek sonuç kümesi (tablo gibi)	Yok hayır	Hayır (normalde)	Kısmi	SELECT sorgularını basitleştirme, sütun düzeyinde güvenlik
Skalar Fonksiyon	Tek değer	Evet	Yok hayır	Yok hayır	SELECT listelerinde yeniden kullanılan hesaplamalar
Tablo Değerli Fonksiyon	Sonuç kümesi	Evet	Yok hayır	Kısmi	Parametreli görünümler, küme döndüren hesaplamalar

Temel ayrım: Yeniden kullanılabilir bir SELECT soyutlaması istediğinizde görünüm kullanın. Parametrelere, koşullu mantığa, veri değişikliğine veya güvenlik uygulamasına ihtiyaç duyduğunuzda saklı prosedür kullanın. Değer veya tablo döndüren ve diğer SQL sorgularıyla birleştirilmesi gereken bir hesaplamaya ihtiyaç duyduğunuzda fonksiyon kullanın.

Dört Temel Fayda

Performans: Ön Derleme ve Plan Önbellekleme

SQL Server, PostgreSQL veya Oracle, saklı prosedür çağrısı aldığında, bu prosedür için önbelleğe alınmış bir yürütme planının olup olmadığını kontrol eder. Eğer varsa, hemen yürütülür. Yoksa, prosedür derlenir, bir yürütme planı oluşturulur, önbelleğe alınır ve yürütülür. Sonraki tüm çağrılarda, önbelleğe alınmış plan yeniden kullanılır.

Uygulama kodundan gönderilen, dize birleştirme yöntemiyle oluşturulmuş geçici SQL sorguları, veritabanına ve sorgu yapısına bağlı olarak her çağrıda yeniden derlenebilir. Saniyede binlerce kez çağrılan yüksek frekanslı sorgular için bu derleme yükü oldukça önemlidir.

sql

-- Demonstrating plan reuse in SQL Server
-- Check if a plan exists for a procedure
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, 1, 100) AS procedure_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.execution_count DESC;

İkinci performans avantajı ise ağ trafiğinin azalmasıdır . Uygulama, her çağrıda veritabanına karmaşık 30 satırlık bir SQL sorgusu göndermek yerine, kısa bir prosedür çağrısı gönderir. Ağ yükü minimum düzeydedir. Veritabanı, ağır hesaplamaları sunucu tarafında yapar ve yalnızca sonuç kümesini döndürür.

Güvenlik: Doğrudan Masa Erişimini Kısıtlama

SC verilerinin özellikle aradığı fayda da budur: "Doğrudan veri erişimini kısıtlamak ve veritabanı güvenliğini artırmak için saklı prosedürlerin nasıl kullanılacağı". Mekanizma basit ve güçlüdür.

Uygulama kullanıcılarına saklı prosedürler üzerinde yürütme izni verin. Temel tablolara doğrudan erişimi engelleyin. Kullanıcı prosedürü çağırabilir ancak tablodan doğrudan sorgulama, ekleme, güncelleme veya silme işlemi yapamaz.

sql

-- Create a role for application users
CREATE ROLE AppReadRole;

-- Grant execute on the procedure
GRANT EXECUTE ON GetCustomerOrders TO AppReadRole;

-- Deny direct table access
DENY SELECT ON Orders TO AppReadRole;
DENY SELECT ON Customers TO AppReadRole;

-- The user can now:
--   EXEC GetCustomerOrders @CustomerId=123, ... -> works
--   SELECT * FROM Orders WHERE ... -> ACCESS DENIED

SQL enjeksiyonuna karşı koruma, ikinci güvenlik avantajıdır. Dinamik SQL yapısı yerine parametreli girdiler kullanan saklı prosedürler, SQL enjeksiyonuna karşı doğal olarak korunmaktadır. Parametre değeri, SQL kodu olarak değil, değişmez bir değer olarak ele alınır.

sql

-- VULNERABLE: dynamic SQL built from user input
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Customers WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- An attacker can inject: '; DROP TABLE Customers; --

-- SAFE: parameterized stored procedure
CREATE PROCEDURE GetCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SELECT CustomerId, Name, Email
    FROM Customers
    WHERE Name = @CustomerName;  -- @CustomerName is a literal, not SQL
END;

Dikkat et: Dinamik SQL sorgularını dahili olarak oluşturan bir saklı prosedür. EXEC() or sp_executesql Kullanıcı girdilerinin dize birleştirmesi, uygulama düzeyindeki dinamik SQL kadar savunmasızdır. Parametrelendirme, prosedür içinde oluşturulan herhangi bir dinamik SQL'e de uygulanmalıdır.

Bakım Kolaylığı: Tek Bir Değişiklik, Tüm Uygulamalar Güncellenir

İş mantığı değiştiğinde, vergi hesaplama kuralları, indirim kademeleri, uyumluluk gerektiren veri dönüşümleri gibi durumlarda, saklı prosedür bu mantığı tek bir yerde merkezileştirir. Prosedürü çağıran her uygulama, yeniden dağıtım gerektirmeden otomatik olarak güncellenmiş davranışı alır.

sql

-- Before: discount logic duplicated in three application services
-- After: centralized in one stored procedure
CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @DiscountedTotal DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Subtotal DECIMAL(10,2);
    DECLARE @CustomerTier NCHAR(1);

    SELECT @Subtotal     = SUM(li.Quantity * li.UnitPrice),
           @CustomerTier = c.Tier
    FROM OrderLineItems li
    JOIN Orders o ON o.OrderId = li.OrderId
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE li.OrderId = @OrderId
    GROUP BY c.Tier;

    -- Business rule: Gold customers get 15%, Silver get 8%, Standard get 0%
    SET @DiscountedTotal = @Subtotal * CASE @CustomerTier
        WHEN 'G' THEN 0.85
        WHEN 'S' THEN 0.92
        ELSE 1.00
    END;
END;

İndirim yüzdelerini tek bir yerden değiştirin. Arayan üç uygulama hizmetinin tümü CalculateOrderTotal Yeni oranları derhal yansıtın.

Çıkış Parametreleri ve Hata Yönetimi ile Kapsülleme

Saklı prosedürler, çıktı parametreleri aracılığıyla birden fazla değer döndürür ve dönüş kodları aracılığıyla işlem durumunu iletir; bu da basit bir SELECT sorgusuna kıyasla daha zengin etkileşim kalıplarına olanak tanır.

sql

CREATE PROCEDURE InsertOrder
    @CustomerId  INT,
    @OrderDate   DATE,
    @NewOrderId  INT OUTPUT,
    @StatusCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, @OrderDate, 'PENDING');

        SET @NewOrderId = SCOPE_IDENTITY();
        SET @StatusCode = 0;  -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @StatusCode = ERROR_NUMBER();
        SET @NewOrderId = -1;
    END CATCH;
END;

-- Usage
DECLARE @OrderId INT, @Status INT;
EXEC InsertOrder
    @CustomerId = 5001,
    @OrderDate  = '2026-07-15',
    @NewOrderId = @OrderId OUTPUT,
    @StatusCode = @Status OUTPUT;

IF @Status <> 0
    PRINT 'Insert failed with error: ' + CAST(@Status AS VARCHAR);
ELSE
    PRINT 'Created order: ' + CAST(@OrderId AS VARCHAR);

Performans Optimizasyonu: Uygulama Planı Size Ne Anlatıyor?

Sorgu yürütme planı, veritabanı motorunun bir sorguyu nasıl yürütmeyi seçtiğine, hangi indeksleri kullandığına, hangi birleştirme algoritmasını seçtiğine ve her adımda kaç satır tahmin ettiğine dair kaydıdır. Günde binlerce kez çağrılan bir saklı prosedür için, sorgu yürütme planı performans sorunlarının birincil teşhis aracıdır.

sql

-- Enable actual execution plan in SQL Server
-- Then run:
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

-- Check for parameter sniffing issues
-- (cached plan was optimized for a different parameter distribution)
EXEC GetCustomerOrders
    @CustomerId = 99999,  -- rare customer with very few orders
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30'
WITH RECOMPILE;  -- forces fresh plan for this call

Parametre koklama, saklı prosedürlerde en sık karşılaşılan performans sorunlarından biridir. SQL Server, prosedürün ilk çağrıldığı parametre kümesi için oluşturulan yürütme planını önbelleğe alır. Sonraki çağrılarda çok farklı parametre değerleri kullanılırsa (örneğin, 50,000 siparişi olan bir müşteri ile 2 siparişi olan bir müşteri), önbelleğe alınan plan bu değerler için oldukça verimsiz olabilir.

Azaltma stratejileri: OPTIMIZE FOR Temsili bir parametre değeri için optimizasyon ipucu; WITH RECOMPILE Her çağrıda yeni bir plan oluşturmak için prosedür düzeyinde (maliyetli ancak parametre dağılımları büyük ölçüde değiştiğinde etkili); koklamayı önlemek için prosedürün başında yerel değişken ataması:

sql

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    -- Local variable trick: prevents parameter sniffing
    DECLARE @LocalCustomerId INT = @CustomerId;
    DECLARE @LocalStart      DATE = @StartDate;
    DECLARE @LocalEnd        DATE = @EndDate;

    SELECT o.OrderId, o.OrderDate, o.TotalAmount
    FROM Orders o
    WHERE o.CustomerId = @LocalCustomerId
      AND o.OrderDate BETWEEN @LocalStart AND @LocalEnd;
END;

En İyi Uygulamalar: Bir Çalışma Kontrol Listesi

Bunlar genel ilkelerden ziyade, saklı prosedürlerin büyük ölçekte sürdürülebilir olmasını sağlayan uygulamalardır:

İsimlendirme ve organizasyon

• Tutarlı bir adlandırma kuralı kullanın: usp_ Kullanıcı tarafından saklanan prosedürler için önek, sp_ sistem prosedürleri için ayrılmıştır
• İşlemleri fiil + isim kullanarak adlandırın: GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
• Bir şemada ilgili prosedürleri gruplandırın: Sales.GetCustomerOrders, Inventory.UpdateStock

Kod yapısı

• Her işleme şu şekilde başlayın: SET NOCOUNT ON Müşterilerin yanlış yorumlayabileceği satır sayısı mesajlarını bastırmak için
• Kullanım BEGIN TRY / BEGIN CATCH açık bloklar BEGIN TRANSACTION / COMMIT / ROLLBACK
• Küme işlemleri için imleçlerden kaçının, mümkün olduğunca küme tabanlı SQL olarak yeniden yazın.
• Kullanmayın SELECT *İşlemin döndürdüğü her sütunu adlandırın.

Güvenlik

• Prosedürler üzerinde yürütme izinleri verin; uygulama rolleri için doğrudan tablo erişimini reddedin.
• Prosedürler içinde kullanıcı girdisinden oluşturulan dinamik SQL'den kaçının.
• Kullanım sp_executesql Dinamik SQL'in kaçınılmaz olduğu durumlarda parametreli sorgularla

Performans

• Büyük tablolar için tablo taramalarının yürütme planlarını kontrol edin, gerekirse indeks ekleyin.
• Koklama riskine açık prosedürler için temsili parametre değerleriyle test edin.
• İzliyoruz sys.dm_exec_procedure_stats yüksek uygulama gerektiren veya uzun süren işlemler için

Dökümanlar

• Her prosedüre bir başlık yorumu ekleyin: amaç, parametreler, dönüş değerleri, yazar, son değiştirilme tarihi.
• SQL sorgusunun yalnızca ne yaptığını değil, neden yaptığını da içeren, prosedür mantığına kodlanmış iş kurallarını belgeleyin.

Saklı Yordam Bağımlılıklarını Yönetme

Saklı prosedürler tek başına var olmazlar. Beş tablodan veri okuyan, iki başka prosedürü çağıran ve bir düzine uygulama servisi tarafından çağrılan bir prosedür, her üç yönde de karmaşık bağımlılıklara sahip bir bileşendir: neye bağlı olduğu, neyin ona bağlı olduğu ve diğer prosedürlerle neyi paylaştığı.

Bir tablo sütununun türü değiştiğinde, o sütuna referans veren her prosedürün test edilmesi gerekir. Bir prosedürün çıktı biçimi değiştiğinde, her çağıranın doğrulanması gerekir. Bir prosedürün değiştirilmesi düşünüldüğünde, değişikliğin kapsamını ve gerekli regresyon testini belirleyen şey, çağıranların tam kümesidir.

Önemli olan bağımlılık türleri:

• Nesne bağımlılıkları: Bu prosedürün referans verdiği tablolar, görünümler, fonksiyonlar ve diğer prosedürler
• Çağrı yapanın bağımlılıkları: Bu prosedürü çağıran uygulama kodu, diğer saklı prosedürler ve zamanlanmış işler.
• Şema bağımlılıkları: Prosedürün parametre türlerinin ve SELECT listelerinin eşleşmesi gereken tablolar ve sütun tanımları.
• İşlem bağımlılıkları: Çağrı yapanlarla veya birbirleriyle işlem kapsamını paylaşan prosedürler

On adet saklı prosedür içeren küçük bir veritabanında, bu bağımlılıklar manuel olarak izlenebilir. Ancak, kurumsal ortamlarda yaygın olan ve yıllarca süren iş mantığını kapsayan yüzlerce saklı prosedür içeren bir veritabanında, manuel bağımlılık izleme eksik haritalar ve değişiklikle ilgili olaylar üretir.

Ne kadar SMART TS XL Kurumsal ölçekte saklı prosedür bağımlılıklarını yönetir.

SMART TS XL'S statik kod analizi SQL saklı prosedürlerini, COBOL programlarını, Java servislerini, Python işlem hatlarını ve aynı veritabanıyla etkileşim kuran diğer bileşenleri ayrıştırır. Birleşik analiz, diller arası yapısal bir model üretir: yalnızca veritabanı içindeki SQL'den SQL'e bağımlılıkları değil, uygulama kodundan saklı prosedüre, altta yatan tablolara ve tekrar geri dönen tüm zinciri de kapsar.

Uygulama bağımlılık eşleme özelliği, eksiksiz çağıran grafiğini oluşturur: hangi COBOL programları, saklı prosedürlere ait tablolardan okuyan gömülü SQL kullanır, hangi Java servisleri JDBC aracılığıyla saklı prosedürleri çağırır, hangi JCL toplu işleri saklı prosedürleri çalıştıran veritabanı yardımcı programlarını çağırır. Bir saklı prosedürün imzası veya davranışı değiştiğinde, bağımlılık haritası her dildeki her çağıranı, değişikliğin üretime geçmeden önce test edilmesi gerekenlerin tam kapsamını gösterir.

MKS etki analizi Bu özellik, bu bağımlılık haritasını değişiklik planlaması için kullanılabilir hale getiriyor: bir değişiklik önerin. CalculateOrderTotal ve onu çağıran her bileşenin, okuduğu ve yazdığı her tablonun ve çağırdığı her alt prosedürün numaralandırılmış bir listesini alır. Bu, "bu neyi bozacak?" sorusunu, geleneksel bilgiye dayalı bir egzersizden, yapılandırılmış, kanıta dayalı bir kapsam raporuna dönüştürür.

MKS kurumsal arama Bu özellik, tam bağımlılık modelini sorgulanabilir hale getirir: okuma yapan her saklı prosedürü bulun. Ordersher arayanın GetCustomerOrdersVeritabanının büyüklüğü ne olursa olsun, belirli bir sütunu değiştiren her prosedür saniyeler içinde çalıştırılabilir.

Ekipler için miras modernizasyonu Taşıma sırasında korunması gereken, on yıllarca süren iş mantığını kodlayan saklı prosedürlere sahip programlar, SMART TS XLBu analiz, mantığın çıkarılabilir olmasını ve geçiş dizisinin planlanabilir olmasını sağlayan yapısal dokümantasyonu sunar.

Kendini Değerlendiren Veritabanı Katmanı

Saklı prosedürler, önceki veritabanı döneminin bir kalıntısı değildir. Veritabanına ait mantığı yerleştirmek için doğru yerdir: hangi uygulamanın verilere eriştiğine bakılmaksızın tutarlı olması gereken güvenlik uygulamaları, önbelleğe alınmış yürütme planlarından yararlanan performansa duyarlı sorgular ve bir kez güncellenip her yere yayılması gereken iş kuralları.

Tuzak, saklı prosedürleri kullanmak değil, bunların belgelenmemiş, haritalanmamış ve kimsenin tam olarak anlamadığı bir bağımlılık ağına dönüşmesine izin vermektir. Kritik bir iş hesaplaması yapan ancak belgelenmiş çağırıcıları olmayan, amacını açıklayan bir başlık yorumu bulunmayan ve değiştirilmeden önce etki analizi yapılmayan bir saklı prosedür, ne kadar iyi yazılmış olursa olsun bir yükümlülüktür. Bağımlılık grafiğini yönetmek, SQL'in kendisini yönetmek kadar önemlidir. Her ikisi de geleneksel bilgiden ziyade sistematik analiz gerektirir.