MS SQL 'de With Check Option

Merhaba arkadaşlar.

Sql Sever da bazı durumlarda, "view" nesnelerini tanımlarken, gövdesinde "where" anahtar sözcüğüyle bir kriter belirtmiş olabiliriz. Böyle bir "view" nesnesini kullanırken de "view" üzerinden tablolarımıza "insert" işlemi gerçekleştirebiliriz. Genelde piyasada şubelere, veritabanında bazı tabloların bazı kolonlarını görme kısıtlaması getirmek için, bir "view" verilir. Şube ne gibi bir işlem yapmak isterse bu "view" üzerinden gerçekleştirmesi istenir. İşte arkadaşlar eğer bir "view" nesnesi oluşturulurken "where" koşulu kullanılmışsa, o "view" üzerinden tablolara bir "insert" işlemi gerçekleştirilecekse ve bu "insert" işleminin sadece "where" koşuluna göre gerçekleşmesi isteniyorsa, "view" nesnesini tanımlarken "where" koşulundan sonra "with check option" parametresini kullanmak gerekir. Çok basit bir kullanımı vardır. "Where" kriterinden sonra "with check option" yazılır.

Örneğin bir arkadaşlık siteniz var ve sadece İzmir içinden üye kabul ediyor. Bunun için bir "view" yazalım. Öncesinde "Kisi" adında bir tablo oluşturalım. "Id (int), Ad (nvarchar), Sehir ((nvarchar)" kolonlarından meydana gelsin.

USE Deneme

GO

CREATE VIEW IzmirUyeleri

AS

SELECT * FROM dbo.Kisi

WHERE Sehir='izmir'

WITH CHECK OPTION

Şimdi bu oluşturduğumuz "view" üzerinden "insert" işlemi gerçekleştirelim.

INSERT INTO IzmirUyeleri VALUES ('Gaffar','izmir')

Koşul arandı, koşula uyuyor. "insert" gerçekleşti.            

SELECT * FROM dbo.Kisi

INSERT INTO IzmirUyeleri VALUES ('Serdar','Bursa')

Koşul arandı, koşula uymuuyor. "insert" gerçekleşmedi. Aşağıda görüldüğü gibi "WITH CHECK OPTION constraint" hatası ile karşılaşıldı. 

"The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated."

Böylece yaptığımız kontrol, tabloya yalnızca istediğimiz kriter ile ekleme işlemini sağladı. Peki doğrudan tabloya ekleme işlemine bir kısıtlama getirildi mi?

INSERT INTO dbo.Kisi( Ad, Sehir ) VALUES ('Volkan','izmir')            

INSERT INTO dbo.Kisi( Ad, Sehir ) VALUES ('Ali','istanbul')

SELECT * FROM dbo.Kisi

Görüldüğü üzere doğrudan tabloya yapılan "insert" işlemlerinde her hangibir kısıtlama getirilmemiştir. "WITH CHECK OPTION" ile getirilen kısıtlama sadece "view" üzerinden yapılan "insert" işlemleri için geçerlidir. 

Bir noktaya dikkat çekmek istiyorum. Yukarıda "serdar" ismiyle, tabloya "view" üzerinden yapılmaya çalışılan fakat koşula uymadığı gerekçesiyle gerçekleşemeyen "insert" işlemi girişiminde bir "transaction" çalışmıştır. Önce "insert" gerçekleşmiş, daha sonra koşula bakılmış, koşula uymadı anlaşılınca o kayıt "delete" edilmiştir. Tükettiği "id" değerinden de kolayca farkedilebilir.

"with check option", "view" nesnelerinde "encryption ve schemabinding" gibi parametrelerle de rahatlıkla kullanılabilir.

ALTER VIEW IzmirUyeleri
WITH SCHEMABINDING,ENCRYPTION

AS

SELECT * FROM dbo.Kisi

WHERE Sehir='izmir'

WITH CHECK OPTION


Umarım yardımcı olabilmişimdir. Bir başka yazıda görüşmek dileğiyle, esenlikler dilerim. Hoşçakalın...

MS SQL 'de With Schemabinding

Merhaba arkadaşlar.
Sql Server 'da bazen oluşturduğumuz "view, function, stored procedure yada trigger" gibi veritabanı nesnelerimizde kullandığımız belirli kolonlarımızın değiştirilememesini yada silinememesini isteyebiliriz. İşte bunun için nesnelerimizi oluştururken "with schemabinding" parametresini ekleyerek kullandığımız kolonları koruma altına alabiliriz. Kolonlar ilgili tabloda hiçbir şekilde değiştirilemez ve silinemez. Örneğin kolonun adı yada tipi değiştirilemez. Ancak kolondaki verilere her türlü değişiklik yapılabilir, yani kolonlara "insert, update, delete" komutları uygulanabilir.

"Schemabinding" parametresini kullanırken dikkat edilmesi gereken iki önemli kuralı vardır:

1. Birinci kural: Nesnemi oluştururken kullandığım kolonları  "Asterisk" (*) işareti ile belirtemem. Hangi kolonları koruma altına almak istiyorsam, kolonların adlarını yazarak belirtmeliyim.Gerekirse tüm kolonları koruma altına alacaksam hepsini "from" deyiminden önce yazmalıyım.
2. İkinci kural: Nesnemi "schemabinding" ile oluştururken kullandığım tabloları "dbo" (database owner) anahtar sözcüğüyle kullanmam gerekiyor. "dbo" deyimine, "nesnenin ikinci kısmı" da denir. Veritabanı sahibi olduğumu belirtmeliyim. "Scalar Functions" nesneleri de ikinci kısımlarıyla çağrılması gerekiyordu.

Örneklerde "View" üzerinden gideceğim. "Schemabinding" 'in kullanımı diğer nesnelerde de aynıdır. "View" nesnemizde "Products" tablosundaki "foreign key" sütünlarını daha anlamlı görebilmek için "join" ile bir sorgu yazalım.

CREATE VIEW UrunKatTed

WITH SCHEMABINDING

AS

SELECT p.ProductName, c.CategoryName, s.CompanyName

FROM dbo.Products p

INNER JOIN dbo.Categories c

ON p.CategoryID = c.CategoryID

INNER JOIN dbo.Suppliers s

ON s.SupplierID = p.SupplierID

"View" imizi oluşturduk. Şimdi çağıralım. Çağırırken "dbo" kullanmama gerek yok. İki türlü de çağırabilirim.

SELECT * FROM UrunKatTed 

SELECT * FROM dbo.UrunKatTed 

Eğer "view" nesnemizde tanımlanan tabloları, "view" i "alter" yaparak, "dbo" 'suz kullansaydım, "view" nesnem değişmeyecek ve aşağıdaki hata ile karşılaşılacaktır.

ALTER VIEW UrunKatTed

WITH SCHEMABINDING

AS

SELECT p.ProductName, c.CategoryName, s.CompanyName

FROM Products p

INNER JOIN Categories c

ON p.CategoryID = c.CategoryID

INNER JOIN Suppliers s

ON s.SupplierID = p.SupplierID

"Cannot schema bind view 'UrunKatTed' because name 'Products' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."

"View" tanımlanırken ilk önce "Products" tablosu kullanıldığı için bu tablo için hata mesajı verdi ve altındaki kodlar çalışmadı. Yine eğer nesnemi tanımlarken spesific kolonlar belirtmeseydim, "asterisk" işareti kullansaydım, yine hata alacaktım.

ALTER VIEW UrunKatTed

WITH SCHEMABINDING

AS

SELECT * --p.ProductName, c.CategoryName, s.CompanyName

FROM dbo.Products p

INNER JOIN dbo.Categories c

ON p.CategoryID = c.CategoryID

INNER JOIN dbo.Suppliers s

ON s.SupplierID = p.SupplierID

Eğer "encryption" özelliğini de kullanmak istersem, aşağıdaki gibi bir kullanım söz konusudur.

CREATE VIEW UrunKat

WITH SCHEMABINDING,ENCRYPTION

AS

    SELECT p.ProductName,c.CategoryName 

    FROM dbo.Products p

    INNER JOIN dbo.Categories c

    ON p.CategoryID = c.CategoryID

    WHERE c.CategoryName>'C'

Böyle bir kullanımla: hem kaynak kodlarının görünmemesini hem de kaynak kodlarında kullanılan kolonların değiştirilememesini ve silinememesini sağlayabiliriz. Ayrıca bu oluşturulan "view" nesnesine yapılan "insert" işlemlerinin, "view" de tanımlanan kritere uygunluğunu da kontrol edebiliriz. Bu kontrol için de "with check option" parametresini kullanmalıyız. Bu parametreyi de başka yazılarda incelemek dileğiyle, hoşçakalın..

MS SQL'de With Encryption

Merhaba arkadaşlar.

Bazen oluşturduğumuz nesnelerimizin güvenlik sebebiyle kilitlemek isteyebiliriz. Bu nesnelerim "view, function, triggger" yada bir "stored procedure" olabilir. Bunun için nesnelerimi tanımlarken, "as" deyiminden önce "with encryption" anahtar kelimesiyle, nesnemin gövdesinin artık kullanan kişilere karşı görünmemesini sağlayabilirim. "Encryption", kelime anlamı olarak şifreleme işlemidir. Hemen bir örnek ile, ilk önce bir "view" nesnesi üzerinde görelim. "UrunKatTed" adında "Products" tablosundaki "foreign key" kolonlarını daha anlamlı görebilmek için bir "view" yazalım ve bunu "with encryption" parametresiyle kilitleyelim.
NOT: Örneklerde "View" üzerinden gideceğim. "Encryption" 'in kullanımı diğer nesnelerde de aynıdır.

CREATE VIEW UrunKatTed

WITH ENCRYPTION

AS

SELECT p.ProductName,c.CategoryName, s.CompanyName

FROM dbo.Products AS p

INNER JOIN dbo.Categories AS c ON p.CategoryID = c.CategoryID

INNER JOIN dbo.Suppliers AS s ON p.SupplierID = s.SupplierID

"View" nesnem oluştu. "Object Explorer" penceresinde "view" 'lerin bulunduğu sekmeye gidip bir bakıyorum.

"View" oluştu fakat ikonunda bir "kilit" simgesi meydana geldi. Sistemde kayıtlı olan her hangibir "view" ' sağtık yaptığımda "design" seçeneğinin aktif olduğu açıkça görülüyor.

Bir de "with encryption" ile oluşturduğumuz "view" nesnemize bakalım.

Görüldüğü üzere "design" seçeneği inaktif. 

NOT: "With encryption" ile oluşturulan bir nesnenin "design" seçeneğine erişilemez. Erişilebilseydi "encryp" etmenin bir anlamı olmazdı. Bir de script 'ini almaya çalışırsam, aşağıdaki gibi "encrypted" bilgisi mesajıyla, erişilemez hatası verir.

Bu nedenle "encrypted" ettiğim bir nesnenin gövdesinin kaynak kodlarını saklamakta fayda var. Çünkü sonradan nesnemin ne iş yaptığına bakmak isteyebilirim. Bunun dışında "encrypted" ettiğim nesnemi "alter" ile istediğim şekilde değiştirebilir. Yine "drop" ile de silebilirim. Yukarıda tanımladığımız "view" nesnemizin gövdesi aynı kalacak şekilde oluşturduğumuz kilidi kaldırmak istersek, "with encryption" parametresini kaldırarak "alter" etmek yeterli olacaktır.

ALTER VIEW UrunKatTed

--WITH ENCRYPTION

AS

SELECT p.ProductName, c.CategoryName, s.CompanyName

FROM dbo.Products AS p

INNER JOIN dbo.Categories AS c ON p.CategoryID = c.CategoryID

INNER JOIN dbo.Suppliers AS s ON p.SupplierID = s.SupplierID

Böylece "view" nesnesi yine aynı işi yapacaktır. İsteseydik "with encryption" parametresini kaldırırken gövdesini de değiştirebilirdik. Eğer "schemabinding" özelliğini de kullanmak istenirse, aşağıdaki gibi bir "view" tanımlanarak "encryption" ile kullanılabilir.

ALTER VIEW UrunKatTed

WITH ENCRYPTION,SCHEMABINDING

AS

    SELECT p.ProductName, c.CategoryName, s.CompanyName

    FROM dbo.Products p

    INNER JOIN dbo.Categories c

    ON p.CategoryID = c.CategoryID

    INNER JOIN dbo.Suppliers s

    ON s.SupplierID = p.SupplierID

Böyle bir kullanımla: hem kaynak kodlarının görünmemesini hem de kaynak kodlarında kullanılan kolonların değiştirilememesini ve silinememesini sağlayabiliriz. "With schemabinding" parametresini ayrıca başka bir yazıda incelemek dileğiyle, hoşçakalın..

MS SQL 'de Views

Merhaba arkadaşlar.
Sql server 'da çok sık kullandığım "select" sorgularım olabilir. Bu çok sık kullandığım "select" sorgularını, kullanmam gereken yerde her defasında uzun uzadıya yazmaktansa, bir kere tanımlayıp, ismiyle birlikte çağırmak daha mantıklıdır. İşte arkadaşlar bunun için "Views" nesneleri oluşturabiliriz. "View", kısaca kaydedilmiş "select" sorgularıdır, "select" dışında bir sorgu kullanılmaz. Özellikler ilişkisel veritabanlarında "join" gibi bir çok tablodan veri çekilirse daha performanslı çalışır. Veriyi hiç bir zaman kendi üzerilerinde taşımazlar, veri tabloda durur ve "select" sorgusunun sonucunu güncel olarak getirir. Veri tabanı nesneleri olduklarından dolayı "DDL" ile manipüle edilirler.

Neden Kullanırız ?

• Performans:   "Strored Procedure" yada "Function" nesnelerinde nasıl bir performans söz konusuysa "Views" nesneleri için de bu performans geçerlidir. Nasıl bir performanstan bahsediyoruz biraz inceleyelim. Bir sorgu çalışmadan önce 5 aşamadan geçer. Önce kodun yazım hataları, syntax 'ı, semantic 'i kontrol edilir(Parse). Daha sonra sorguda kullanılan tabloların kolonları var mı, ona bakılır(Resolve). Bu ıslemden sonra sorgunun sonucunu nasıl döndüreceğini, indexlemeleri kontrol eder(Optimize). Daha sonra döndüreceği şeklı belirledikten sonra derleme işlemi yapılır(Compile). Son olarak sorgu çalışır ve sonuç döner(Execute). Tanımlanan "View" nesnesi çağırıldığında, "compile" işlemi dahil compile 'dan önceki tüm aşamaları işlemeden geçer. Geriye sadece "execute" işlemi kalır. Bu da önemli bir hız demektir.

• Güvenlik: Kurum yada departmanlar arası veriye sınırlı erişim yetkileri verilebilir. Bir şirkette her birimin her bilgiyi görmemesi istenir. "View" nesneleri kullanılarak istenilen birimin, istenilen tablodan, istenilen satır ve sütunlara görme yetkisi verilebilir.  Örneğin sekreterimin çalışanlara ulaşabilmesi için onların maaşlarını görmesine gerek yoktur. Aksi takdirde çalışanlar arasında bir çekememezlik meydana gelebilir. Bu genelde genelde raporlamada kullanılır.

NOT: "View" nesneleri "Strored Procedure" yada "Function" nesneleri gibi parametre alamazlar. Sadece parametresiz kullanılırlar. Fakat "Strored Procedure" nesnelerinin aksine, "Functions" nesneleri gibi "sub query" olarak sorgu içinde kullanılabilirler.

Bir örnek vererek "view" kullanımını kavrayalım. Yine "Northwind" veri tabanı üzerinde çalışacağız. "Products" tablosundaki "foreign key" sütunlarını daha anlamlı hale getirmek için "CREATE" ddl komutuyla, "UrunKatTed" adında bir "view" oluşturalım. Tipini de "VIEW" olarak belirtiyorum.

USE Northwind

GO

CREATE VIEW UrunKatTed

AS

SELECT p.ProductName,p.UnitsInStock,c.CategoryName,s.CompanyName

FROM dbo.Products p

INNER JOIN dbo.Categories c ON p.CategoryID = c.CategoryID

INNER JOIN dbo.Suppliers s ON p.SupplierID = s.SupplierID

"View" 'ler veri tabanı nesneleridir demiştik. Bu yüzden "object explorer" penceresinde oluşturuldukları veritabanında "Views" sekmesi altında yerini alırlar. "View" nesnem oluştu. Kullanmak istediğimde "FROM" anahtar sözcüğünden sonra istediğim gibi çağırabilirim. Aşağıda görüldüğü gibi istediğim kolon yada "WHERE" anahtar sözcüğüyle istediğim kritere göre filtreleme yapabilirim.

    SELECT * FROM UrunKatTed

    SELECT CategoryName, CompanyName FROM UrunKatTed

    SELECT * FROM  UrunKatTed  WHERE CategoryName='Beverages'

    SELECT ProductName,UnitsInStock FROM UrunKatTed WHERE ProductName>'C' 

ORDER BY 1

Yukarıda oluşturduğum "view" nesnesini "subquery" ile de yapabilirdik. Fakat her defasında yazmak zorunda kalacaktık ve performans kaybı yaşanacaktı. 

NOT:  Normal kullanımda "view", verilerini tek tablodan alıyorsa, "view" nesnesini kullanırken üzerinde tüm "DML" manipülayonlarını (select, insert, update, delete) yapılabilir. Fakat tanımlanırken verilerini birden fazla tablodan alıyorsa, "null" (boş) geçilemez kolonları doldursam bile "insert, update, delete" işlemlerini yapamam, sadece "select" yapabilirim.

INSERT UrunKatTed(ProductName, UnitsInStock, CategoryName, CompanyName)

VALUES ('Baocha',30,'Kahve','Java')

Hata mesajındaki "multiple base table" ifadesi "view" nesnesinin verilerini birden fazla tablodan çektiğinin göstergesidir. Bu yüzden "insert" işlemi gerçekleştirilememiştir. Başka bir "view" yazalım. Satış yapan çalışanların, satış numaralarıyla birlikte ad ve soyad bilgilerini getirsin. 

CREATE VIEW SipCal

AS

SELECT OrderID,dbo.Employees.FirstName+' '+dbo.Employees.LastName 

/*AS [Ad Soyad]*/

FROM dbo.Orders

INNER JOIN dbo.Employees

ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID

NOT: "View" nesneleri "No Column Name" (isimsiz kolon) hatası verir. Yani tanımlanırken isimsiz kolonlara izin vermez. Çektiğimiz verileri listelerken, sadece gösterimde kendimiz oluşturduğumuz kolonlara isim verilmelidir. "Stored Procedures" ve "Functions" nesneleri için böyle bir kısıtlama yoktur.

CREATE VIEW SipCal

AS

SELECT OrderID,dbo.Employees.FirstName+' '+dbo.Employees.LastName AS [Ad Soyad]

FROM dbo.Orders

INNER JOIN dbo.Employees

ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID

GO

SELECT * FROM SipCal

Bu "view" 'imizi biraz değiştirelim. Çalışanlara göre, çalışanları yaptığı satış sayısını getirsin. Yine "DDL" komutlarıyla bir değişiklik yapıyorum.

ALTER VIEW SipCal

AS

SELECT COUNT(dbo.Orders.OrderID) AS [Siparis Sayısı],

dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS [AD SOYAD]

FROM dbo.Orders

INNER JOIN dbo.Employees

ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID

GROUP BY dbo.Employees.FirstName + ' ' + dbo.Employees.LastName

GO

SELECT * FROM SipCal

Daha sonra yine "DDL" komutlarıyla "view" nesnemi silebilirim. 

DROP VIEW sipcal

NOT: Normalde "top" deyimi olmadan, "order by" sıralama kriter sözcüğünü kullanabiliyorduk arkadaşlar, "order by" olmadan "top" deyimini kullanamıyorduk. Fakat bir "view" tanımlarken "Order By" tek başına kullanılamaz, ancak ve ancak "top" ile kullanilir. Yani birisini kullanmak istiyorsak, ikisini birlikte kullanmalıyım.
Peki ama ben kayıtlarımın hepsini listelemek istiyorum derseniz, "TOP 100 PERCENT" kullanım şekli kayıtlarınızın 100% 'ünü getirecektir.

CREATE VIEW CalisanTelefon

AS

SELECT TOP 100 PERCENT FirstName,LastName,HomePhone 

FROM dbo.Employees ORDER BY FirstName

GO

SELECT * FROM CalisanTelefon

"View" nesnelerinde "with encryption, with schemabinding, with check option" gibi kontroller oldukça yaygın kullanılır. Ayrıca "View" 'lerde indexleme de yapılabilir. Bu konulara da ilerleyen yazılarımda değinmek dileğiyle, hoşçakalın...

MS SQL 'de User Defined Functions-(3) Multistatement Table Valued Functions

Merhaba arkadaşlar.
"In Line Table Valued" fonksiyonlar geriye bir tablo döner  ve gövdesinde sadece "select" sorguları kullanılır demiştik. "User Defined" fonksiyonların sonuncusu olan "Multistatement Table Valued" (çoklu durum tablo değerli) fonksiyonları da geriye bir tablo döner. Fakat adından da anlaşılacağı üzere tanımlanırken gövdesinde DML dilinin tüm sorgularını (select, insert, update, delete) tümünü kullanabilir. Bunu yaparken de gövdesinin "begin-end" skopları arasında olması şarttır. "RETURNS" ifadesinden sonra geriye döndüreceği geçici bir tablo tanımlanır, tip olarak da "TABLE" tipi belirtilir. Bu yüzden en sonda "RETURN" 'den sonra bir şey yazmama gerek kalmaz. Bu tablonun kolonları tipleriyle birlikte tanımlanır. Tanımladığım bu tablo remde geçici olarak tutulur.

Örneğin bir fonksiyonumuz olsun. Parametre olarak verdiğimiz "ad" yada "soyad" 'a göre "Employees" tablosundan veriler çeksin. "Select" ile çektiği veriler aynı zamanda belirttiğimiz geçici tabloma da "insert" etsin. Burada dikkat edilmesi gereken nokta tabloma ekleyeceğim verilerin, tablomun sütün sayısına ve sütunlarımın tiplerine uygun olması gerektiğidir. 

CREATE FUNCTION Kisilerim(@tip nvarchar(7))

RETURNS @Tablom TABLE

(Id INT,Isim NVARCHAR(50))

AS

BEGIN

    IF(@tip='ad')

        BEGIN

            INSERT @Tablom SELECT EmployeeID,FirstName FROM dbo.Employees

        END

    ELSE IF(@tip='adsoyad')

        BEGIN

            INSERT @Tablom SELECT EmployeeID,FirstName+' '+LastName AS Isim 

            FROM dbo.Employees

        END

    RETURN

END

Fonksiyonum "Object Explorer" penceresinde "Nortwind" veritabanının altında "Programmability", "Functions", "Table-valued Functions" sekmesi altında yerini aldı. Fonksiyonumu kullanırken "Scalar" fonksiyonlarımdaki gibi "dbo" kullanmama gerek yoktur. İki sütunluk bir tablo döndürmek istediğim için "select" ile çağırıp istediği kolonları içerisinden çekebilirim.

SELECT * FROM Kisilerim('ad')          SELECT * FROM Kisilerim('adsoyad')                             

Örneğin ben tabloma kayıt girmeye çalışsam veya bir kaydımı silmeye çalışırsam "Employees" tablomda bir değişiklik olur mu?

ALTER FUNCTION Kisilerim(@tip nvarchar(7))

RETURNS @Tablom TABLE

(Id INT,Isim NVARCHAR(50))

AS

BEGIN

    IF(@tip='ad')

    BEGIN

        INSERT @Tablom SELECT EmployeeID,FirstName FROM dbo.Employees

    END

    ELSE IF(@tip='adsoyad')

    BEGIN

        INSERT @Tablom SELECT EmployeeID,FirstName+' '+LastName AS Isim 

FROM dbo.Employees

        DELETE FROM @Tablom WHERE Id=11

    END

RETURN

END

Hayır arkadaşlar, "Employees" tablomda bir değişiklik olmaz.  Fonksiyonumun tanımını değiştirirken geçici tablom üzerinde bir silme işlemi yaptığımdan dolayı, tablomu "select" ettiğimde bilgileri sadece filtreleyerek gösterecektir. Aşağıda görüldüğü üzere orjinal bilgi "Employees" tablomdadır.

SELECT * FROM Kisilerim('adsoyad')        SELECT * FROM dbo.Employees

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...

MS SQL 'de User Defined Functions-(1) Scalar Valued Functions

Merhaba arkadaşlar.
"User Defined Functions" lardan ilki "Scalar" fonksiyonlarını inceleyeceğiz. "User Defined Functions" Giriş makalemde genel olarak fonksiyonlardan bahsetmiştik. Fonksiyonların birer veritabanı nesneleri olduğundan, bu yüzden DDL ile (create, alter, drop) yönetildiğinden. Geriye mutlaka değer döndürdüklerinden, bu sebeple "return" ve "returns" anahtar sözcüklerini kullanmamız gerektiğinden söz etmiştik. Bu hatırlatmalardan  sonra "Scalar" fonksiyonları adından da anlaşılacağı üzere geriye tek bir değer dönen fonksiyonlardır. Bu sebeple "returns" ifadesinden sonra geriye dönen değerin tipi belirtilir. Hemen basit bir örnek üzerinden konuyu kavramaya çalışalım. Oluştururken "CREATE" 'den sonra, nesnenin ne olduğunu yani "FUNCTION" yazmalıyım. Daha sonra fonksiyonumun ismini yazıyorum.

CREATE FUNCTION BugununTarihi()

RETURNS DATETIME

AS

BEGIN

      DECLARE @Tarih DATETIME

      set @Tarih= GETDATE()

      RETURN @Tarih

END

Fonsiyonumu parametresiz tanımladım. Geriye bugünün tarihini döndüreceğim için " RETURNS " 'den sonra "DATETIME" tipini belirttim. "AS" 'den sonra fonksiyonumun gövdesini "BEGIN" ve "END" skoplarının arasında tanımlamalıyım. "RETURN" diyip geriye değerini döndürmek istediğim değişkenin adını yazıyorum. En son fonksiyonumu çalıştırdım (execute).

Fonksiyonum başarılı bir şekilde oluşturuldu. "Object Explorer" penceresinde, database 'imin altında "Programmability", "Functions", "Scalar-valued Functions" sekmesinin altında imiyle birlikte yer aldı. Şimdi sıra geldi çalıştırmaya. Geriye tek değer döneceğim için "from" kullanmama gerek yok. "Select" yada "print" ile çağırabilirim. "Select"  ile çağırırsam "result" tipinde, "print" olarak çağırırsam "message" tipinde dönecektir. Fakat fonksiyonumu ikinci kısmıyla (dbo) birlikte çağırmalıyım, "Database Owner" olduğumu belirtmem gerek. Çağırmazsam "böyle bir fonksiyon tanımlı değil" hatası ile karşılaşırım. Bunun için "Scalar" fonksiyonlar "dbo.fonksiyonAdi" ile çağırılır.

SELECT dbo.BugununTarihi()

PRINT dbo.BugununTarihi()

Örneğin bir tane de parametre alan bir fonksiyon tanımlayalım. "Northwind" veritabanını kullanarak, hangi kategoriden kaç adet ürün satıldığını görelim.

CREATE FUNCTION KatAdet(@KatId INT)

RETURNS INT

AS

BEGIN

      RETURN 

      (SELECT SUM(dbo.[Order Details].Quantity) 

       FROM dbo.[Order Details]

       INNER JOIN dbo.Products 

       ON dbo.Products.ProductID=dbo.[Order Details].ProductID

       WHERE dbo.Products.CategoryID=@KatId)   

END 

SELECT CategoryName,dbo.KatAdet(CategoryId) AS 'Toplam Miktar' FROM Categories

Son örnek olarak bir fonksiyonumuz olsun. Parametre olarak verdiğimiz tarihi, istediğimiz formatta ve yine istediğimiz ayracı ay, gün ve yılın arasında kullanarak bize geri dönsün. Verdiğimiz tarihteki ay ve gün değeri tek haneliyse başına "0" eklesin. Bunun için ay ve günün uzunluğunu almalıyım. Uzunluğun "1" ise "string methods" kullanarak "0" ile "1" rakamlarını birleştirmeliyim. 

create function dbo.tarihFormatla

(@tarih datetime,@ayrac nchar(1),@format nchar(3))

returns nchar(10)

as

begin

      declare @yil nchar(4) = year(@tarih)

      declare @ay nchar(2) = month(@tarih)

      declare @gun nchar(2) = day(@tarih)

      declare @formatliTarih nchar(10)

      if len(@gun) = 1

            set @gun = '0' + @gun

      if len(@ay) = 1

            set @ay = '0' + @ay

           

      if @format = 'dmy'

            set @formatliTarih = @gun + @ayrac + @ay + @ayrac + @yil

      else if @format = 'mdy'

          set @formatliTarih = @ay + @ayrac + @gun + @ayrac + @yil

      else if @format = 'ymd'

          set @formatliTarih = @yil + @ayrac + @ay + @ayrac + @gun

      return @formatliTarih

end

go

select dbo.tarihFormatla(getdate(),'/','ymd')

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...