Transcript Stored Procedure - metinakbulut.com

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çerisinde
tutulmuş hali demektir.
sıkça yapılan işlemlerin bir defa yazılarak
program akışına göre tekrar tekrar
kullanılmasını sağlamaktır.
Extended Stored Procedure
CLR Stored Procedure
System Stored Procedure
Stored Procedure Oluşturmak
• Stored Procedure’ün oluşturulma şekli
aşağıdaki gibidir.
CREATE PROC [ EDURE ] prosedür_adı
AS
T-SQL ifadeleri
GO
• 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
AS
SELECT * FROM Laptop
GO
EXEC uspLaptop
--veya sadece
uspLaptop
uygulama2
CREATE PROCEDURE uspProduct
@Maker varchar(10) =NULL /*‘hata’*/
AS
SELECT * FROM Product
WHERE Maker = @Maker
GO
EXEC uspProduct @Maker = 'B'
uygulama3
CREATE PROCEDURE usptype2
@model varchar(50) = NULL,
@type varchar(50) =NULL
AS
SELECT * FROM product
WHERE type =isnull(@type ,'typeyaz')
AND model LIKE '%' + ISNULL(@model ,'modelyaz') + '%'
GO
EXEC usptype2 @model = '11', @type = 'pc'
Procedure silme
DROP PROCEDURE usptype
GO
-- veya
DROP PROC usptype
GO
– 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) =NULL
AS
SELECT *
FROM product
WHERE 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 -AS
BEGIN
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
as
begin
INSERT INTO pcsilinen select * from deleted
end
Update trigger
ALTER trigger [dbo].[pcguncelle]
on [dbo].[PC]
after update
as
begin
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 tablosilmeengeller
ON DATABASE --[ON ALL SERVER / ON DATABASE]
/*Veritabanı seviyesinde geçerli bir trigger*/
FOR DROP_TABLE
AS
PRINT 'Silme işlemi iptal edildi'
ROLLBACK
triggeri veritabanından silmek
DROP TRIGGER tablosilmeengeller ON
DATABASE