Stored Procedure

Öğr.Gör.Metin Akbulut

Stored Procedure

Bir amaca ulaşmak için takip edilen yol ve yöntem tanımından yola çıkarak bir prosedür, herhangi bir işlevi yerine getirmek için yazılan kodların bir paket içerisindetutulmuş hali demektir.

sıkça yapılan işlemlerin bir defa yazılarak program akışına göre tekrar tekrarkullanılmasını sağlamaktır.Extended Stored ProcedureCLR Stored ProcedureSystem Stored Procedure

Stored Procedure Oluşturmak

• Stored Procedure’ün oluşturulma şekli aşağıdaki gibidir.

CREATE PROC [ EDURE ] prosedür_adıAST-SQL ifadeleriGO

• Veri tabanınızın Programmability klasörünün solundaki + işaretine tıkladığınızda açılan Stored Procedures klasörü üzerinde farenizle sağ tıklayınız.

• Yeni bir Stored Procedure oluşturabilmeniz için New Stored Procedure komutunu tıklatınız

EXEC komutu oluşturduğunuz Stored Procedure’ü çalıştıracak olan komuttur.EXEC’ten sonra yazılan ifade ise oluşturduğunuz Stored Procedure’ün adıdır.

Yazdığınız bu Query’i çalıştırarak (F5) sonucu görebilirsiniz.

Derleme

• Bir Stored Procedure’de çok fazla değişiklikler yapılıyorsa EXEC komutuyla

• çalıştırılırken yeniden derlenmesi istenebilir. Bunun için, RECOMPILE kullanılmalıdır.

• Örnek:• EXEC UNotlar WITH RECOMPILE

Stored Procedure’ü Silmek

Var olan bir Stored Procedure’ü silmek için DROP komutunu kullanmak gereklidir.DROP komutundan sonra Stored Procedure’ün sahibinin adı ve Stored Procedure’ün adıyazılmalıdır.Genel Kullanımı

DROP PROC sahip.prosedür_adıÖrnek:

DROP PROC dbo.UNotlar

Değer Alan Stored Procedure’ler

Stored Procedure’lerin daha etkin kullanılabilmesi ve işlevsel bir hale gelebilmesi için dışarıdan değer almalarına ihtiyaç duyulur. Bu nedenle girdi parametreleri (Input parameter) kullanılır.

Bir öğrenciye ait 3 not bilgisi Query’de ilk değerleri atanarak not ortalamalarıhesaplanacaktır.

Hesaplamaların yapılacağı Stored Procedure’ü yazmak için yeni bir Query sayfası açınız. Stored Procedure’ün adını “UHesapla” olarak belirterek ortalamayı karşılayacak değişkeni tanımlayınız ve başarının belirleneceği “if” yapısını yazınız.

uygulama1

SELECT * FROM Laptop

CREATE PROCEDURE uspLaptop ASSELECT * FROM Laptop GO

EXEC uspLaptop --veya sadece uspLaptop

uygulama2

CREATE PROCEDURE uspProduct@Maker varchar(10) =NULL /*‘hata’*/ AS SELECT * FROM ProductWHERE Maker = @MakerGO

EXEC uspProduct @Maker = 'B'

uygulama3

CREATE PROCEDURE usptype2@model varchar(50) = NULL,@type varchar(50) =NULLAS SELECT * FROM productWHERE type =isnull(@type ,'typeyaz') AND model LIKE '%' + ISNULL(@model ,'modelyaz') + '%'GO

EXEC usptype2 @model = '11', @type = 'pc'

Procedure silme

DROP PROCEDURE usptype GO -- veyaDROP PROC usptypeGO – veya DROP PROC dbo.usptype

Çoklu silme

DROP PROCEDURE usptype1, usptype2, usptype3 GO -- veya

DROP PROC usptype1, usptype2, usptype3 GO

Procedure değişiklik

ALTER PROCEDURE [dbo].[usptype]--null vermek zorundayız(Açıklama örnek)@type varchar(50) =NULLAS SELECT * FROM productWHERE type LIKE @type + '%'

İf IF Koşul

BEGIN PRINT 'Koşulumuz doğru' -- SELECT,UPDATE... --END

ELSE

BEGIN PRINT 'Koşulumuz yanlış' -- SELECT,UPDATE... –

END

CREATE PROCEDURE [dbo].[DeleteEmployee]@EmployeeId uniqueidentifier -- SP Parametremiz --ASBEGIN DECLARE @Count int -- EmployeeId id'li kişinin kaç kişinin amiri olduğunu tutan sayacımız -- DECLARE @ReturnBool bit -- Döndüreceğimiz boolean tipi 0 veya 1 -- SELECT @Count = COUNT(*) FROM Employee WHERE DirectorId = @EmployeeId -- Kişi amir pozisyonunda mı? -- IF @Count <= 0 -- Kişi herhangi bir kişinin amiri değilse yani sayaç değerimiz 0'a eşit veya küçükse -- BEGIN DELETE FROM Employee WHERE EmployeeId = @EmployeeId -- Kişi silinir -- SET @ReturnBool = 1 -- Dönüş değeri 'true' yapılır -- SELECT @ReturnBool -- Değer döndürülür -- END ELSE -- Kişi bir kişinin amiri ise -- SET @ReturnBool = 0 -- Kişi silinmez ve dönüş değeri 'false' yapılır -- SELECT @ReturnBool -- Değer döndürülür -- END

Intersect and Except

TRIGGER

• Trigger’lar, tablo üzerinde tanımlanabilen ve bu tablo üzerinde bir işlem gerçekleştiğinde tetiklenen programlama öğeleridir. Trigger tanım olarak da tetikleyici anlamındadır.

İki tip trigger vardır.

• DML Trigger Her hangi bir tablo üzerinde yapılan insert, update, delete işlemi öncesinde veya sonrasında otomatik olarak aynı veya başka bir tabloda veri ekleme, veri değiştirme veya veri silme işlemi yapılmak istendiğinde kullanılır.

• DDL Trigger Veritabanı üzerinde tablo veya diğer nesneler üzerinde create, alter, drop ifadelerinden sonra tetiklenen triggerlardır

• AFTER(ya da FOR) => Bu trigger’lar tetiklendiğinde işlem gerçekleşmiş demektir. Yani yazdığımız trigger ‘after delete’ şeklindeyse, o tablo üzerinde data silindikten sonra bu trigger devreye girer.

• INSTEAD OF => Bu trigger’lar tetiklendiğinde işlem gerçekleşmemiştir. Yani yazdığımız trigger ‘instead of delete’ şeklindeyse, o tablo üzerindeki kayıt silme işlemi yerine, bu trigger devreye girecektir.

Delete trigger

Create trigger [dbo].[satisIptalEt] on [dbo].[PC] after delete asbegin

INSERT INTO pcsilinen select * from deleted

end

Update triggerALTER trigger [dbo].[pcguncelle] on [dbo].[PC] after update asbegin

INSERT INTO pceklenen

SELECT code,model,GETDATE() FROM inserted

end

Insert trigger

ALTER TRIGGER [dbo].[PasifYap]

ON [dbo].[PC]

INSTEAD OF DELETE

AS

UPDATE P SET Aktif=0 FROM Pc P

JOIN deleted D ON P.code=D.code

ALTER TRIGGER [dbo].[PasifYap]

ON [dbo].[PC]

INSTEAD OF DELETE

AS

UPDATE P SET Aktif=0 FROM Pc P

JOIN deleted D ON P.code=D.code

DISABLE - ENABLE TRIGGER

Oluşturulan triggerlarin bir süre durdurulması (pasif edilmesi) istenirse DISABLE TRIGGER trg_adi --on database

Tekrar aktif etmek için ise ENABLE TRIGGER trg_adi --on database

DDL Triggerlar

CREATE TRIGGER tablosilmeengellerON DATABASE --[ON ALL SERVER / ON DATABASE] /*Veritabanı seviyesinde geçerli bir trigger*/

FOR DROP_TABLEAS

PRINT 'Silme işlemi iptal edildi'

ROLLBACK

triggeri veritabanından silmek

DROP TRIGGER tablosilmeengeller ON DATABASE