User Defined Functions (UDF)_4
18 Aralık 2016 Pazar
* Günün tarihini geri döndüren fonksiyon (Scalar) :
CREATE FUNCTION BugununTarihi()
RETURNS DATETIME
AS
BEGIN
DECLARE @tarih DATETIME
SET @tarih =GETDATE()
RETURN @tarih
END
Kullanımı: SELECT dbo.BugununTarihi()
--------------------------------------------------------
* AdventureWorks2012 veritabanı kullanarak oluşturulan promosyon koduna göre, Adı aralara virgül koyarak getiren fonksiyon (Scalar) :
CREATE FUNCTION ConcatPerson
(
@EmailPromotion int
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @name VARCHAR(MAX);
SET @name='';
SELECT @name=@name+ FirstName+',' FROM Person.Person
WHERE EmailPromotion=@emailpromotion;
RETURN @name;
END
Kullanımı: SELECT dbo.ConcatPerson(1)
--------------------------------------------------------
* Inline table-valued function (AdventureWork2012)
CREATE FUNCTION PersonDetails
(
@BusinessID int
)
RETURNS TABLE
AS
RETURN
(SELECT * FROM Person.Person WHERE BusinessEntityID=@BusinessID)
Kullanımı: SELECT * FROM PersonDetails(5)
--------------------------------------------------------
* Northwind veritabanında ilgili fonksiyona UrunID veriyoruz ve bunun sonucunda müşterinin bilgilerini döndürüyoruz.(Inline table-valued function)
CREATE FUNCTION GetCustomerDetails
(
@ProductID int
)
RETURNS TABLE
AS
RETURN SELECT ProductName, c.CustomerID, CompanyName FROM Products AS p
JOIN [Order Details] AS ord ON p.ProductID=ord.ProductID
JOIN Orders AS o ON ord.OrderID=o.OrderID
JOIN Customers AS c ON o.CustomerID=c.CustomerID
WHERE ord.ProductID=@ProductID
Kullanımı: SELECT * FROM dbo.GetCustomerDetails(4)
--------------------------------------------------------
* AdventureWorks2012 veritabanında ürün getiren fonksiyon.(Inline table-valued function)
CREATE FUNCTION fn_UrunGetir
(
@ProductID INT
)
RETURNS TABLE
AS
RETURN SELECT Name, ProductNumber, ListPrice
FROM Production.Product WHERE ProductID=@ProductID
Kullanımı: SELECT * FROM fn_UrunGetir(2)
--------------------------------------------------------
* Northwind veritabanında ürün adedini getiren fonksiyon. (Scalar)
CREATE FUNCTION fn_customer
(
@EmployeeID INT
)
RETURNS INT
AS
BEGIN
DECLARE @sayi INT
IF @EmployeeID IS NULL
SELECT @sayi= (SELECT COUNT(*) FROM Orders)
ELSE
SELECT @sayi= (SELECT COUNT(*) FROM Orders WHERE EmployeeID=@EmployeeID)
RETURN @sayi
END
Kullanımı: SELECT [dbo].[fn_customer](2)
--------------------------------------------------------
* (Northwind) KategoriID ye göre ilgili üründen kaç adet sipariş olduğu sonucunu döndüren fonksiyon.
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
Kullanımı: SELECT CategoryName, dbo.KatAdet(CategoryId) AS 'Toplam Miktar' FROM Categories
--------------------------------------------------------
* (Northwind) KategoriID ye göre ilgili kategoriden kaç adet olduğu sonucunu döndüren fonksiyon.
CREATE FUNCTION fn_KategoriUrunAdet (@KategoriID int)
RETURNS INT
AS
BEGIN
DECLARE @count int
SELECT @count= COUNT(p.ProductID) from Categories c
INNER JOIN Products p on p.CategoryID=c.CategoryID
WHERE c.CategoryID=@KategoriID
RETURN @count
END
Kullanımı: SELECT CategoryName, dbo.fn_KategoriUrunAdet(CategoryID) AS UrunAdet
FROM Categories
--------------------------------------------------------
* (Northwind) Employee tablosundaki çalışan adına göre verilen sipariş adetleri. (Scalar)
CREATE FUNCTION fn_SiparisHesapla(@ID INT)
RETURNS INT
AS
BEGIN
DECLARE @adet INT
SELECT @adet= SUM(od.Quantity) FROM Employees e
INNER JOIN Orders o ON o.EmployeeID=e.EmployeeID
INNER JOIN [Order Details] od ON od.OrderID=o.OrderID
WHERE e.EmployeeID=@ID GROUP BY e.EmployeeID
RETURN @adet
END
Kullanımı: SELECT FirstName, LastName, dbo.fn_SiparisHesapla(EmployeeID) as SiparisAdet FROM Employees
--------------------------------------------------------
* Dışardan girilen 2 sayıyı toplayan fonksiyon. (Scalar)
CREATE FUNCTION Fn_ToplamaYap(@sayi1 INT, @sayi2 INT)
RETURNS INT
AS
BEGIN
DECLARE @toplam INT
SET @toplam=@sayi1+@sayi2
RETURN @toplam
END
Kullanımı: SELECT dbo.Fn_ToplamaYap(2, 2)
--------------------------------------------------------
* Dışarıdan girilen 2 adet string değerin arasına bir boşluk koyarak sonucu döndüren fonksiyon.(Scalar)
CREATE FUNCTION fn_birlestir(@ad NVARCHAR(20), @soyad NVARCHAR(20))
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN @ad+space(1)+@soyad
END
Kullanımı: SELECT dbo.fn_birlestir('Alper','Çelik')
--------------------------------------------------------
* Dışarıdan girilen karakter formatı ve tarih formatına göre çıktı üreten fonksiyon. (Scalar)
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
Kullanımı: SELECT dbo.TarihFormatla(GETDATE(), '/', 'mdy')
--------------------------------------------------------
* (Northwind)
CREATE FUNCTION fn_TariheGore
(
@a DATETIME,
@b DATETIME
)
RETURNS TABLE
AS
RETURN (
SELECT TOP 1 (
SELECT COUNT(*) FROM Employees WHERE YEAR(BirthDate)=1952)As DogumTarihi ,
(SELECT COUNT(*) FROM Employees WHERE YEAR(HireDate)=1993) As IseGiris FROM Employees
)
Kullanım: SELECT * FROM fn_TariheGore(' ',' ')
--------------------------------------------------------
* Northwind veritabanı kullanılarak ünvana göre çalışan getiren fonksiyon.
CREATE FUNCTION fn_CalisanByUnvan(@unvan NVARCHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT EmployeeID, FirstName, LastName, Title FROM Employees WHERE TitleOfCourtesy=@unvan
)
Kullanım: SELECT * FROM fn_CalisanByUnvan('Mr.')
--------------------------------------------------------
* AdventureWorks veritabanı kullanarak hangi ürünün kaç adet sipariş edildiği sorgusu.
CREATE FUNCTION fn_OrderedProduct(@prodID INT)
RETURNS INT
AS
BEGIN
DECLARE @sonuc INT
SELECT @sonuc= SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE ProductID=@prodID
RETURN @sonuc
END
Kullanım: SELECT dbo.fn_OrderedProduct(707)
Aynı sonucu veren 2 sorgu aşağıdadır:
SELECT PP.Name,SUM(SOD.OrderQty) [Sipariş Adedi] FROM Production.Product PP
LEFT JOIN Sales.SalesOrderDetail SOD ON PP.ProductID=SOD.ProductID
GROUP BY PP.Name
SELECT Name,dbo.FN_OrderedProduct(ProductID) FROM Production.Product
--------------------------------------------------------
Umarım örnekler faydalı olmuştur. Sonraki makalelerimde görüşmek üzere.