Makale Özeti

Veritabanı programlaması ile uğraşan herkesin er geç tanıştığı stored procedurelar üzerine güzel bir yazı

Makale

Stored Procedure’ler, veri tabanınızı daha kolay yönetmenizi ve veri tabanı hakkındaki bilgilerin daha kolay görüntülenmesini sağlar. SQL ifadelerinin önceden derlenmiş şekli olarak karşımıza çıkarlar. Veri tabanı içerisinde saklanırlar ve uygulamanızdan bir çağrı yaptığınızda işlevsellik kazanırlar.  Sizin tarafınızdan tanımlanmış belli  şartlara göre program akışı üzerinde etkinlik gösterirler. Stored Procedure’ler mantıksal ifadeler, veri tabanı sorgulama cümleleri içerebilir. Dışarıdan parametreler alabilirler, ve geriye değer veya değer kümesi döndürebilirler.  Stored Procedure’ler sayesinde birden çok kez yazmak  zorunda kaldığımız uzun sql cümleciklerini, sadece bir kez yazıp bunu stored procedure içerisinde saklayarak, daha sonraki kullanımlarda da sadece kaydettiğimiz stored procedure’nin ismini referans göstererek kullanabiliriz. Stored Procedure’lerin bu kullanımı, daha önceden yazılıp derlendiği için yazılan uygulamanın hızını arttıracak; aynı ifadeleri bir daha yazma ihtiyacı duyduğumuzda, sadece bu prosedürün ismini referans göstereceğimizden dolayı da, uygulamamızın boyutunu azaltacaktır.  

 

Yazılım geliştiriciler, veri tabanı kullanan programlarını geliştirirken iki farklı yöntem izleyebilirler. Bunlardan ilki, programcı  veri tabanından bilgi çekerken kullanacağı tüm ifadeleri programının içerisine yerleştirir. Server üzerinde veri tabanı, ve local tarafta da veri tabanı sorgulamalarını içeren uygulama yer alır. Program bir bilgiye ihtiyaç duyduğunda localde tutulan sql ifadesini, veri tabanına gönderir, veri tabanı da verilen talebe uygun cevapları bir recordset (kayıt kümesi) biçiminde kullanıcıya döndürür.  Her hangi bir hata olması durumunda,  program kırıldığında kullanıcı veri tabanından bilgi çekilirken kullanılan sql cümlelerini görebilir. Bu istenmeyen durumdur. Diğer bir durumda kullanıcıların görüntülemeye yetkisi olmadığı tablolar üzerine sorgulama yapmalarıdır. Bu da diğer bir istenmeyen durumdur. Dolayısı ile etkin ve güvenli bir uygulama açısından bu yöntem tercih edilmemelidir. Peki alternatif nedir?  Alternatif, yani yukarıda varlığından bahsettiğimiz ikinci yöntem, veri tabanı üzerinde yapılacak mümkünse tüm, mümkün değilse güvenlik ve/veya hız açısından önemli olan tüm veri tabanı sorgulamalarını stored procedure’ler yoluyla yapmaktır. Bu sayede veri tabanı üzerinde bazı kayıtları görüntüleme yetkisi olmayan kimselerin, yapacakları sorgulamalarda önlenmiş olur.

 

Stored Procedure’leri kullanmanın diğer bir avantajı ise, ağ trafiğini azaltmalarıdır. Veri tabanı üzerinde bir sorgulama işlemi gerçekleştirirken, sorgulama ifadesini client bilgisayardan, server’a göndermek ağ trafiğini arttırır. Ama stored procedure kullanılmışsa, client sadece stored procedure’nin ismini vererek uzun kod kümesine çağrı gönderecek, tüm işlemler server üzerinde gerçekleşecek ve sadece sonuç ağ üzerinden client’a döndürülecektir. Bu sayede, sql sorgularının ağ trafiği üzerindeki etkisi oldukça azalır.

 

Stored Procedure’lerin bir kaç tipi vardır. Bunlar  System Stored Procedure’ler, Local Stored Procedure’ler,  Temporary Stored Procedure’ler,  Remote Stored Procedure’ler, Extended Stored Procedure’ler...

 

System Stored Procedure’ler; SQL Server üzerinde  master isimli veri tabanında saklanan prosedürlerdir. Default olarak Server’ı kurduğunuzda yüklenirler, herhangi bir veri tabanı üzerinde işlem yaparken kullanılabilirler. Diğer bir deyişle, herhangi bir veri tabanına direk olarak  bağlı değillerdir. Bunlarda yine kendi içlerinde birkaç gruba ayrılırlar. Mesela SQL Server’ların kaydı sırasında kullanılan Active Directory Procedure’ler; ODBC uygulamalarını sistem tabloları üzerindeki değişikliklerden izole eden ve ODBC veri işleme prosedürlerini barındıran,Catalog Procedure’ler; kursör değişkenin fonksiyonlarını yerine getiren, Cursor Procedure’ler, Full-Text Search Procedure’ler, Database Maintenance Plan Procedure’ler, Security Procedure’ler, SQL Mail Procedure’ler, XML Procedure’ler, Web Assistant Procedure’ler.

 

Temporary Stored Procedure’ler;  local veya global olarak oluşturulabilirler, bir tane sayı işareti (#) ile başlayan local prosedürler, bir anda sadece bir kullanıcı tarafından kullanılabilirler. İki tane sayı işareti ile başlayan (##) global prosedürler ise herkes tarafından görülebilir.

 

Extended Stored Procedure’ler ise SQL Server’ın dışında çalıştırılan dinamik-link kütüphaneleri (DLL) ‘dir. Diğer stored procedure’ler ‘sp_ ‘ ön ekini alırken, extended Stored Procedure’ler ‘xp_’ ön ekini alırlar.

 

Local Stored Procedure’ler; Server’dan bağımsız olarak, bir veri tabanı üzerinde işlem yapabilen biz, yazılım geliştiricilerin oluşturduğu prosedürlerdir. Bu makalemizde bizim asıl ilgileneceğimiz, prosedür tipi budur.  Bu prosedürler, SQL Server üzerinde oluşturuldukları veri tabanının altında ‘Stored Procedures’ dizininin altında saklanırlar ( Şekil 1 ) .

 

Şekil 1.  SQL Server Enterprise Manager Ekranı

 

Burada görüldüğü üzere Northwind veri tabanının altında bir çok Stored Procedure kayıtlı. Bunlardan tipi “system” olanlar, sistem tarafından otomatik olarak oluşturulmuş, başka bir deyişle master altındaki sistem prosedürlerinden örnek olarak alınmış prosedürlerdir. Tipi “User” olan prosedürler ise, kullanıcı (database administrator- veri tabanı yöneticisi veya yazılım geliştirici) tarafından yazılmış olanlar prosedürlerdir. Prosedürün ‘owner’ı diğer bir deyişler, Stored Procedure’nin sahibi, dbo (database owner- veri tabanının sahibi)’dur.

 

Herhangi bir stored procedure üzerinde mouse’un sağ tuşuna basıp, stored procedure’nin özelliklerine (properties) bakacak olursak(Şekil 2), bu prosedürün ne iş yaptığını görebiliriz. Eğer Stored Procedure’nin tipi ‘system’ ise  kodlar üzerinde bir değişiklik yapamayız.

Şekil 2. Bir Stored Procedure’nin özellikleri

 

Şekilde gösterilen prosedür, User tipli bir stored procedure, dolayısı ile prosedür üzerinde değişikli yaparak, bu değişiklikleri kayıt edebiliriz.  Buradaki “Permission” butonu ise, kimlere bu stored procedure’i kullanabileceğinin tayin etmemizi sağlar. Bu bir kullanıcı olabilir, yada bir kullanıcılar grubu olabilir (Şekil 3).

 

 

 

 

 

 

 

 

 

 

 

 

 

Şekil 3. Bir Stored Procedure’in İzinleri

 

 

Bu menüden kime hangi yetkiler verileceği seçilir. Burada select, veri sorgulama işlemini; insert, bilgi girişini; update, veri güncelleme işlemini; delete, veri silme işlemini; exec, bu stored procedure’i çalıştırabilme iznini ifade eder. Yukarıdaki örnekten SalesByCategory Stored Procedure’in herkes tarafından kullanılabileceği anlaşılıyor.

 

Stored Procedure’lerin SQL Server üzerinde nasıl kullanılacağını gördükten sonra şimdi de SQL Query Analyzer üzerinde Transact SQL cümleleri biçiminde nasıl oluşturulduğunu görelim.

 

Basitçe bir stored procedure aşağıdaki biçimde oluşturulur.

 

CREATE  PROCEDURE  dbo.[prosedür ismi]

AS

 

[İstediğimiz işlemi gerçekleştiren komut bloğu]

 

GO

 

Şimdi bu yazılıma göre bir örnek yapalım. Mesela “Northwind” veri tabanı üzerindeki “Employees” tablosu üzerinden USA’da  çalışan  işçilerin isimlerini getiren bir stored procedure yazalım.

 

CREATE PROCEDURE dbo.GetEmployeesWorkedInUSA

AS

     SELECT   FirstName, LastName

FROM  dbo.Employees

WHERE  country=USA

GO

 

Burada dikkat edilmesi gereken noktalardan biri, bu prosedürü yazarken Northwind veri tabanı üzerinde çalıştığınızdan emin olmaktır. Bunu da SQL Query Analyzer üzerinde, tollbardaki o an üzerinde çalışılan veri tabanını gösteren ‘Current Database’ isimli ComboBox’ı kontrol ederek yapabilirsiniz. Her şeye karşın önlem almak istiyorsanız, stored procedure’nizin başına,

 

USE  [prosedür ismi]

GO

 

Komut setini ekleyerek bu problemin üstesinden gelebilirsiniz. Örneğimizde

 

USE Northwind

GO

 

Satırlarını ekleyip, tollbar üzerinden start(başla) butonuna tıklarsak, veya F% tuşuna basarsak, yazdığımız stored procedure çalışır ve kendini Nortwind altındaki “stored procedures” isimli dizinin altına oluşturur. Bu ilk çalışma olduğu için prosedür bu yazılan select cümlesini çalıştırmaz. Bu ilk derleme aşamasına mahsus olarak yapılmış bir işlemdir. Bundan sonraki çalıştırılmalarında prosedür bize veri döndürecektir. Şimdi bunu görmek üzere,

 

EXECUTEGetEmployeesWorkedInUSA

 

Not : Bazı noktalarda SQL Server üzerinde kısaltmalar kullanılabiliyor. Örneğin; EXECUTE yerine EXEC de yazabilirsiniz, yada PROCEDURE yerine PROC gibi.

Komut satırını çalıştırırsak, şekil 4’te gösterilen kayıt grubunu elde ederiz.

 

Şekil 4. GetEmployeesWorkedInUSA Stored Procedure’in döndürdüğü değerler

 

 

Yalnız “EXEC GetEmployeesWorkedInUSA” komut satırını çalıştırırken dikkat edilmesi gereken bir nokta vardır. Eğer bu satırı yazdıktan sonra start butonuna veya F5 tuşuna basarsak Şekil 5’teki gibi bir hata mesajı alırız.

Şekil 5. Hata Mesajı

 

Bu hatanın anlamı, daha önceden oluşturulmuş olan bir nenenin tekrar oluşturulmaya çalışılmasıdır. Hatırlayacağınız gibi biz daha önce  GetEmployeesWorkedInUSA Prosedürünü oluşturmuştuk. Bu durumda ne yapacağız?  Bu durumda yapılması gereken, yada daha açık bir ifade ile, daha sonradan yazdığımız ve çalıştırmak istediğimiz ifadeleri önce mouse ile işaretleyip, sonra start butonuna veya F5 tuşuna basmalıyız. Bu yüzden Şekil 4 üzerinde gördüğünüz “EXEC GetEmployeesWorkedInUSA” komut satırı işaretlenmiş biçimdedir.

 Varolan bir stored procedure üzerinde bir değişiklik yapmak istiyor isek kullanmamız gereken komut satırı ise şu şekildedir.

 

ALTER  PROCEDURE  dbo.[prosedür ismi]

AS

 

[İstediğimiz işlemi gerçekleştiren komut bloğu]

 

GO

Mesela biraz önceki örneğimizde USA’de çalışan işçilerin sadece isimlerini  görüntülemiştik. Şimdi prosedürümüzü bu kişilerin tüm bilgilerini gösterecek biçimde değiştirelim.

 

ALTER PROCEDURE dbo.GetEmployeesWorkedInUSA

AS

     SELECT   *

FROM  dbo.Employees

WHERE  country=USA

GO

 

Prosedürü tekrar Create ile oluşturarak düzeltmeyi yapmaya çalışsaydım, bu prosedür daha önceden SQL server  üzerinde oluşturulduğu için hata verecekti, Bu yüzden Alter deyimini kullandık. Tüm komut satırlarını seçili hale getirdik ve çalıştırdık. Artık GetEmployeesWorkedInUSA prosedürü USA’de çalışanların tüm bilgilerini gösterir hale geldi . Şimdi bunu test etmek için, daha önceden yazmış olduğumuz,” EXEC GetEmployeesWorkedInUSA” komut satırını seçili hale getirip çalıştıralım çıktı şekil 6 daki gibi olacaktır.

Şekil 6. ALTER ile yazılmış ve çalıştırılmış Stored Procedure

Şimdi de bu oluşturduğumuz stored procedure silelim. Bunun içinde aşağıdaki komut yazılımını kullanırız.

 

DROP PROC [prosedür ismi]

Go

 

Örneğimize göre;

 

DROP   PROC    GetEmployeesWorkedInUSA

Go

 

Bu adımdan sonra oluşturmuş olduğumuz prosedür silinmiş olur. Benzer işlemi, prosedürün kayıtlı olduğu veri tabanının “stored procedures” dizininde silmek istediğimiz prosedürü bulup, onun üzerine tıklayıp, mouse’un sağ tuşuna tıklayıp, “DELETE” seçeneğini seçerek te yapabilirdik.

 

Bir sonraki yazımızda da  Stored Procedure’lerin nasıl değer aldığını, bizim dışarıdan nasıl değer gönderildiğimizi, bunlar ile ne tür işlemler yapabileceğimizi, tartışacağız. Bir sonraki yazıda görüşmek üzere .. Yazılımla kalın J))