Saklı yordamların sorgu performansını düşürebileceği bir durum göz önünde bulundurulur.
MS SQL Server 2000'de saklı yordamlar derlenirken, saklı yordamlar yordamsal önbelleğe yerleştirilir, bu da saklı yordam kodunun ayrıştırılması, optimize edilmesi ve derlenmesi ihtiyacını ortadan kaldırarak yürütmeleri sırasında performansı iyileştirebilir.
Öte yandan, saklı bir yordamın derlenmiş kodunun saklanmasında ters etkiye sahip olabilecek tuzaklar vardır.
Gerçek şu ki, bir saklı yordam derlenirken, yordam kodunu oluşturan operatörlerin yürütme planı sırasıyla derlenir, derlenmiş saklı yordam önbelleğe alınırsa, yürütme planı önbelleğe alınır ve bu nedenle saklı yordam olmaz belirli bir durum ve sorgu parametreleri için optimize edilebilir.
Bunu göstermek için küçük bir deney yapacak.
AŞAMA 1... Veritabanı oluşturma.
Deney için ayrı bir veri tabanı oluşturalım.
VERİTABANI OLUŞTUR test_sp_perf
AÇIK (NAME = "test_data", DOSYAADI = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
OTURUM AÇ (İSİM = "test_log", DOSYAADI = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, DOSYA ADI = 1Mb)
ADIM 2. Tablo oluşturma.
CREATE TABLE sp_perf_test (sütun1 int, sütun2 karakter (5000))
AŞAMA 3. Tabloyu test çizgileriyle doldurma. Yinelenen satırlar kasıtlı olarak tabloya eklenir. 10.000 satır 1'den 10.000'e kadar numaralandırılmıştır ve 10.000 satır 50.000 olarak numaralandırılmıştır.
DECLARE @i int
SET @ ben = 1
süre (@i)<10000)
BAŞLAMAK
INSERT INTO sp_perf_test (sütun1, sütun2) DEĞERLER (@i, "Test dizesi #" + CAST (@i as char (8)))
INSERT INTO sp_perf_test (sütun1, sütun2) DEĞERLERİ (50000, "Test dizesi #" + CAST (@i char (8) olarak))
SET @ ben = @ ben + 1
SON
sp_perf_test'TEN SAYI SEÇİN (*)
Gitmek
ADIM 4. Kümelenmemiş bir dizin oluşturun. Yürütme planı prosedürle birlikte önbelleğe alındığından, dizin tüm çağrılar için aynı şekilde kullanılacaktır.
SP_perf_test ON CL_perf_test KÜMELENMEMİŞ INDEX OLUŞTUR (sütun1)
Gitmek
ADIM 5. Saklı yordam oluşturma. Prosedür basitçe bir SELECT deyimini bir koşulla yürütür.
CREATE PROC proc1 (@param int)
OLARAK
sp_perf_test NERDEN sütun1, sütun2 SEÇİN [e-posta korumalı]
Gitmek
ADIM 6. Saklı bir yordam çalıştırma. Güvenlik açığı olan bir prosedür başlatılırken, özel olarak seçici bir parametre kullanılır. İşlem sonucunda 1 satır elde ediyoruz. Yürütme planı, kümelenmemiş bir dizin kullanımını gösterir, çünkü sorgu seçicidir ve bir satırı almanın en iyi yoludur. Bir satırı getirmek için optimize edilmiş bir prosedür, prosedürel önbellekte saklanır.
EXEC proc1 1234
Gitmek
ADIM 7. Seçici olmayan bir parametreyle saklı bir yordam çalıştırma. Parametre olarak 50.000 değeri kullanılır.Kümelenmemiş bir dizin ve yer imi arama işlemi kullanan, sırasıyla yaklaşık 10.000 gibi ilk sütun değerine sahip satırlar etkisizdir, ancak yürütme planıyla derlenmiş kod depolandığından prosedürel önbellek, kullanılacaktır. Yürütme planı bunu göstermekle birlikte yer imi arama işleminin 9999 satır için yapıldığını da göstermektedir.
EXEC proc1 50.000
Gitmek
ADIM 8.İlk alanı 50.000'e eşit olan satırlar getiriliyor. Ayrı bir sorgu yürütmek, sorguyu optimize edecek ve ilk sütunun belirli değeriyle derleyecektir. Sonuç olarak, sorgu iyileştirici, alanın birçok kez çoğaltıldığını algılar ve bu durumda kümelenmemiş bir dizin kullanmaktan çok daha verimli olan tablo tarama işlemini kullanmaya karar verir.
sp_perf_test'TEN sütun1, sütun2'yi SEÇİN NEREDE sütun1 = 50000
Gitmek
Bu nedenle, saklı yordamları kullanmanın her zaman sorgu performansını iyileştirmeyebileceği sonucuna varabiliriz. Değişken sayıda satıra sahip sonuçlar üzerinde çalışan ve farklı yürütme planları kullanan saklı yordamlar konusunda çok dikkatli olmalısınız.
Deneyi yeni bir MS SQL sunucusunda tekrarlamak için komut dosyasını kullanabilirsiniz.
saklı yordam türleri
SQL Server'da birkaç tür vardır saklı prosedürler.
- sistemik saklı prosedürlerçeşitli idari işlemleri gerçekleştirmek için tasarlanmıştır. Hemen hemen tüm sunucu yönetimi eylemleri onların yardımıyla gerçekleştirilir. sistem diyebiliriz saklı prosedürler hem kullanıcı hem de sistem veritabanlarının sistem tablolarını değiştirme, ekleme, silme ve bunlardan veri almaya kadar giden sistem tablolarıyla çalışmayı sağlayan bir arayüzdür. sistemik saklı prosedürlerön eki sp_'dir, sistem veritabanında depolanır ve herhangi bir başka veritabanı bağlamında çağrılabilir.
- Gelenek saklı prosedürler belirli eylemleri gerçekleştirin. saklı prosedürler- tam teşekküllü bir veritabanı nesnesi. Sonuç olarak, her saklı yordam yürütüldüğü belirli bir veritabanında bulunur.
- Geçici saklı prosedürler yalnızca bir süre için var olurlar, ardından sunucu tarafından otomatik olarak yok edilirler. Yerel ve küresel olarak ikiye ayrılırlar. Yerel geçici saklı prosedürler yalnızca oluşturuldukları bağlantıdan çağrılabilir. Böyle bir prosedür oluşturduğunuzda, ona tek bir # karakterle başlayan bir isim vermeniz gerekir. Tüm geçici nesneler gibi, saklı prosedürler bu türden olanlar, kullanıcı sunucunun bağlantısını kestiğinde, sunucuyu yeniden başlattığında veya durdurduğunda otomatik olarak silinir. küresel geçici saklı prosedürler aynı prosedüre sahip herhangi bir sunucu bağlantısı için kullanılabilir. Tanımlamak için ## sembolleriyle başlayan bir isim vermeniz yeterlidir. Bu prosedürler, sunucu yeniden başlatıldığında veya durdurulduğunda veya oluşturuldukları bağlamdaki bağlantı kapatıldığında silinir.
Saklı Prosedürleri Oluşturma, Değiştirme ve Silme
oluşturma saklı yordam aşağıdaki görevlerin çözümünü içerir:
- oluşturulan türün tanımlanması saklı yordam: geçici veya özel. Ayrıca kendi sisteminizi oluşturabilirsiniz. saklı yordam ona sp_ ön eki olan bir isim vererek ve onu sistem veritabanına yerleştirerek. Bu prosedür, yerel sunucudaki herhangi bir veritabanı bağlamında mevcut olacaktır;
- erişim haklarının planlanması. oluştururken saklı yordam veritabanı nesnelerine, onu oluşturan kullanıcıyla aynı erişim haklarına sahip olacağı akılda tutulmalıdır;
- tanım saklı yordam parametreleri... Çoğu programlama dilinde bulunan prosedürler gibi, saklı prosedürler giriş ve çıkış parametrelerine sahip olabilir;
- kod geliştirme saklı yordam... Prosedür kodu, diğerlerine yapılan çağrılar da dahil olmak üzere herhangi bir SQL komutunun bir dizisini içerebilir. saklı prosedürler.
Yeni bir tane oluşturma ve mevcut olanı değiştirme saklı yordam aşağıdaki komutla yapılır:
<определение_процедуры>:: = (CREATE | ALTER) prosedür_adı [; sayı] [(@ parametre_adı veri türü) [= varsayılan]] [, ... n] AS sql_operator [... n]
Bu komutun parametrelerini ele alalım.
Sp_, #, ## önekleri kullanılarak oluşturulan prosedür sistem veya geçici olarak tanımlanabilir. Komut söz diziminden de görebileceğiniz gibi, oluşturulan prosedürün sahibi olacak sahibinin adının yanı sıra bulunması gereken veritabanının adını belirtmeye izin verilmez. Böylece, oluşturulan yerleştirmek için saklı yordam belirli bir veritabanında, o veritabanı bağlamında CREATE PROCEDURE komutunu çalıştırmalısınız. Vücut dışında işlem yaparken saklı yordam aynı veritabanındaki nesneler için kısaltılmış adlar kullanabilirsiniz, yani veritabanı adını belirtmeden. Diğer veritabanlarında bulunan nesnelere başvurmanız gerektiğinde, veritabanının adının belirtilmesi gerekir.
İsimdeki numara kimlik numarasıdır saklı yordam, bu da onu bir dizi prosedürde benzersiz bir şekilde tanımlar. Yönetim prosedürlerinin rahatlığı için, mantıksal olarak aynı türden saklı prosedürler aynı ad, ancak farklı kimlik numaraları verilerek gruplandırılabilir.
Oluşturulan sistemde giriş ve çıkış verilerini aktarmak için saklı yordam yerel değişkenlerin adları gibi adları @ simgesiyle başlaması gereken parametreler kullanılabilir. Bir saklı yordam virgülle ayırarak birçok parametre belirtilebilir. Bir prosedürün gövdesi, adları bu prosedürün parametrelerinin adlarıyla aynı olan yerel değişkenleri kullanmamalıdır.
Karşılık gelen veri türünü belirlemek için saklı yordam parametresi, kullanıcı tanımlı olanlar da dahil olmak üzere tüm SQL veri türleri çalışacaktır. Ancak, CURSOR veri türü yalnızca şu şekilde kullanılabilir: çıkış parametresi saklı yordam, yani OUTPUT anahtar sözcüğünü belirterek.
OUTPUT anahtar sözcüğünün varlığı, karşılık gelen parametrenin veri döndürmeyi amaçladığı anlamına gelir. saklı yordam... Ancak bu, parametrenin değerleri aktarmak için uygun olmadığı anlamına gelmez. saklı yordam... OUTPUT anahtar sözcüğünü belirtmek, sunucuya çıkış yapması talimatını verir. saklı yordam yordam çağrılırken parametre değeri olarak belirtilen yerel değişkene parametrenin geçerli değerini atayın. OUTPUT anahtar sözcüğü belirtildiğinde, bir prosedür çağrılırken karşılık gelen parametrenin değerinin yalnızca yerel bir değişken kullanılarak ayarlanabileceğini unutmayın. Normal parametreler için geçerli olan herhangi bir ifade veya sabit kullanmanıza izin verilmez.
VARYING anahtar sözcüğü şununla birlikte kullanılır:
Saklı yordam (İng. saklı yordam), adlandırılmış bir veritabanı program nesnesidir. SQL Server'da birkaç tür saklı yordam vardır.
Sistem saklı yordamlar (İng. sistem saklı yordamı) VTYS geliştiricileri tarafından sağlanır ve sistem kataloğuyla eylemler gerçekleştirmek veya sistem bilgilerini almak için kullanılır. İsimleri genellikle "sp_" öneki ile başlar. Her türden saklı yordam, EXEC olarak kısaltılabilen EXECUTE komutuyla çağrılır. Örneğin, parametresiz çalışan sp_helplogins saklı yordamı, hesap adları hakkında iki rapor oluşturur. (İng. oturumlar) ve her bir veritabanındaki ilgili kullanıcıları (İng. kullanıcılar).
EXEC sp_helplogins;
Sistemde saklı yordamlar kullanılarak gerçekleştirilen işlemler hakkında fikir vermek için tablo halinde. 10.6 bazı örnekler sağlar. SQL Server'da binin üzerinde sistem saklı yordamı vardır.
Tablo 10.6
SQL Server Sistemi Saklı Yordam Örnekleri
Kullanıcı, kullanıcı veritabanlarında ve geçici nesneler için veritabanlarında saklı yordamlar oluşturabilir. İkinci durumda, saklı yordam olacaktır geçici. Geçici tablolarda olduğu gibi, geçici saklı yordamın adı, yerel bir geçici saklı yordamsa "#" önekiyle, genelse "##" ile başlamalıdır. Yerel geçici prosedür, yalnızca oluşturulduğu bağlantı içinde, global olanda ve diğer bağlantılarda kullanılabilir.
SQL Server programlanabilir nesneler, Transact-SQL araçları veya derlemeler kullanılarak oluşturulabilir (İng. derleme) Microsoft .Net Framework'ün Common Language Runtime (CRL) içinde. Bu öğreticide yalnızca ilk yöntem ele alınacaktır.
Saklı prosedürler, formatı aşağıda gösterilen CREATE PROCEDURE ifadesi (PROC olarak kısaltılabilir) kullanılarak oluşturulur:
CREATE (PROC I PROSEDÜR) proc_name [; numara]
[(g parametresi veri_türü)
[“Varsayılan] |
[İLE
[ÇOĞALTMA İÇİN]
AS ([BEGIN] sql_statement [;] [... n] [END])
ŞİFRELEME seçeneğiyle bir saklı yordam (veya tetikleyici, işlev, görünüm) oluşturulursa, kodu, metin okunamaz hale gelecek şekilde dönüştürülür. Aynı zamanda, belirtildiği gibi, kullanılan algoritma SQL Server'ın önceki sürümlerinden alınmıştır ve güvenilir bir koruma algoritması olarak kabul edilemez - ters dönüştürmeyi hızlı bir şekilde gerçekleştirmenize izin veren yardımcı programlar vardır.
RECOMPILE seçeneği, yordam her çağrıldığında sisteme metni yeniden derlemesi talimatını verir. Normalde, ilk başlangıçta derlenen prosedür, performansı artırabilecek şekilde önbelleğe kaydedilir.
EXECUTE AS, prosedürün yürütülmesi gereken güvenlik bağlamını tanımlar. Ardından değerlerden biri f CALLER | ÖZ | SAHİBİ | "Kullanıcı adı"). CALLER varsayılandır ve kodun bu modülü çağıran kullanıcının güvenlik bağlamında yürütüleceği anlamına gelir. Buna göre, kullanıcının yalnızca programlanan nesnenin kendisi için değil, aynı zamanda ondan etkilenen diğer veritabanı nesneleri için de izinlere sahip olması gerekir. EXECUTE AS SELF, programlanabilir bir nesneyi oluşturan veya değiştiren kullanıcı bağlamını kullanmak anlamına gelir. SAHİP, kodun prosedürün mevcut sahibi bağlamında yürütüleceğini belirtir. Sahip tanımlanmamışsa, ait olduğu şemanın sahibi kastedilmektedir. OLARAK ÇALIŞTIR "kullanıcı_adı", kullanıcı adını (tek tırnak içinde) açıkça belirtmenize olanak tanır.
Prosedür için parametreler belirtilebilir. Değerleri prosedüre geçirmek için kullanılan yerel değişkenlerdir. Bir parametre OUTPUT (veya kısaca OUT) anahtar sözcüğüyle bildirilirse, bu bir çıktı parametresidir: sonlandırıldıktan sonra prosedürde belirtilen değer, prosedürü çağıran program tarafından kullanılabilir. READONLY anahtar sözcüğü, parametre değerinin saklı yordam içinde değiştirilemeyeceği anlamına gelir.
Parametrelere değerler atanabilir, ancak prosedür çağrıldığında parametre değeri açıkça belirtilmemişse kullanılacak olan varsayılan değerdir. Bir örnek düşünelim:
CREATE PROC surma (@a int, @b int = 0,
© sonuç int ÇIKTI) OLARAK
SET @ sonuç = 0a + 0b
Üç parametreli bir prosedür oluşturduk ve @b parametresinin varsayılan değeri 0'dır ve @result parametresi bir çıktı parametresidir: bunun aracılığıyla değer çağıran programa döndürülür. Gerçekleştirilen işlemler oldukça basittir - çıkış parametresi iki girişin toplamının değerini alır.
SQL Server Management Studio'da çalışırken, oluşturulan saklı yordam Programlanabilir DB Nesneleri bölümünde bulunabilir. (İng. Programlanabilirlik) saklı yordamlar için alt bölümde (Şekil 10.2).
Bir prosedürü çağırırken, hem değişkenleri hem de sabitleri giriş parametreleri olarak kullanabilirsiniz. İki örneğe bakalım. İlkinde, prosedürün giriş parametreleri açıkça sabitler tarafından belirlenir; çağrıdaki çıkış parametresi için OUTPUT anahtar sözcüğü belirtilir. İkinci seçenekte değişkenin değeri ilk giriş parametresi olarak kullanılır ve ikinci parametre için DEFAULT anahtar sözcüğü kullanılarak varsayılan değerin kullanılması gerektiği belirtilir:
Pirinç. 10.2.
DECLARE @ int ile;
EXEC toplamı 10.5, @ c ÇIKIŞ;
YAZDIR 0c; - 15 görüntülenecek
DECLARE Gi int = 5;
- arama yaparken varsayılan değeri kullanın
EXEC toplamı Gi, VARSAYILAN, 0с ÇIKTI;
YAZDIR 0c; - 5 görüntülenecek
Şimdi prosedürün sona erdiği dönüş kodunun analizi ile bir örnek düşünelim. Belirli bir yıl aralığında Bookl tablosunda kaç kitap yayınlandığını hesaplamak gereksin. Ayrıca, ilk yıl son yıldan daha fazla olduğu ortaya çıkarsa, prosedür "1" döndürür ve sayılmaz, aksi takdirde kitap sayısını sayar ve 0 döndürürüz:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) OLARAK
EĞER 0İlkYıl> 0GeçenYıl DÖNÜŞÜ 1
SET @ sonuç = (dbo.Bookl'DAN SAYI SEÇ (*)
0İlkYıl VE 0GeçenYıl ARASINDAKİ NEREDE);
Dönüş kodunun 0ret tamsayı değişkeninde saklandığı ve ardından değerinin analiz edildiği (bu durumda 1 olacaktır) bu prosedürü çağırmanın bir varyantını düşünün. PRINT operatöründe kullanılan CAST işlevi, Gres tamsayı değişkeninin değerini bir dize tipine dönüştürmek için kullanılır:
DECLARE 0ret int, Gres int
EXEC Gret = satır numarası 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Başlangıç yılı bitiş yılından büyük"
YAZDIR "Kitap sayısı" + CAST (Gres as varchar (20))
Saklı yordamlar yalnızca bir tablodaki verileri okumakla kalmaz, aynı zamanda verileri değiştirebilir ve hatta tablolar ve bir dizi başka veritabanı nesnesi oluşturabilir.
Ancak, saklı bir prosedürden şemalar, işlevler, tetikleyiciler, prosedürler ve görünümler oluşturamazsınız.
Aşağıdaki örnek, hem bu yetenekleri hem de geçici nesnelerin kapsamını göstermektedir. Aşağıdaki saklı yordam, # thhab2 geçici tablosunun varlığını kontrol eder; bu tablo yoksa, onu oluşturur. Bundan sonra # Tab2 tablosuna iki sütunun değerleri girilir ve tablonun içeriği SELECT deyimi ile görüntülenir:
PROC CREATE My_Procl (@id int, @name varchar (30))
EĞER OBJECT_ID ("tempdb.dbo. # Tab21) IS NULL
INSERT INTO dbo.# Tab2 (id, name) VALUES (0id, 0name)
dbo'DAN * SEÇİN. # Tab2 –№1
Saklı yordamın ilk çağrısından önce, içinde kullanılan geçici # Thab2 tablosunu oluşturacağız. EXEC operatörüne dikkat edin. Önceki örneklerde, parametreler "konuma göre" prosedürüne iletildi ve bu durumda, parametreleri iletmek için başka bir format kullanılır - "ada göre", parametre adı ve değeri açıkça belirtilir:
CREATE TABLE dbo.# Tab2 (id int, isim varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
dbo'DAN * SEÇİN # Tab2; –№2
Yukarıdaki örnekte, SELECT ifadesi iki kez yürütülecektir: ilk kez - prosedür içinde, ikinci kez - çağıran kod parçasından ("# 2" yorumuyla işaretlenmiştir).
Prosedürün ikinci çağrısından önce, geçici tablo # Tab2'yi sileceğiz. Ardından, saklı yordamdan aynı adı taşıyan geçici tablo oluşturulacaktır:
DROP TABLE dbo # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
dbo'DAN * SEÇİN # Tab2; –№2
Bu durumda, yalnızca prosedür içindeki SELECT ifadesi ("Xa 1" yorumuyla birlikte) verileri görüntüler. SELECT "# 2", saklı yordamda oluşturulan geçici tablo yordam döndüğünde tempdb'den zaten silinmiş olduğundan bir hataya neden olur.
DROP PROCEDURE ifadesini kullanarak saklı bir prosedürü bırakabilirsiniz. Formatı aşağıda gösterilmiştir. Bir operatör, birkaç saklı yordamı virgülle ayırarak listeleyerek silebilir:
DROP (PROC I PROSEDÜRÜ) (prosedür) [
Örneğin, önceden oluşturulmuş prosedür özetini kaldıralım:
DROP PROC toplamı;
ALTER PROCEDURE ifadesini (kabul edilebilir) kullanarak mevcut bir prosedürde değişiklikler yapabilirsiniz (ve aslında onu geçersiz kılabilirsiniz).
kısaltması PROC). ALTER anahtar sözcüğü dışında, ifadenin biçimi CREATE PROCEDURE'ın biçimine benzer. Örneğin, dbo prosedürünü değiştirelim. sahibinin güvenlik bağlamında bir yürütme seçeneği ayarlayarak rownum:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
İLE EXECUTE AS Owner, kurulabilir bir seçenektir
EĞER 0FirsYear> 0SonYıl DÖNÜŞ 1 BAŞKA BAŞLA
SET 0result = (dbo.Bookl'DAN SAYI SEÇ (*)
SFirsYear VE SLastYear ARASINDAKİ YERDE);
Bazı durumlarda, dinamik olarak bir komut oluşturmak ve bunu veritabanı sunucusunda yürütmek gerekebilir. Bu görev, EXEC operatörü kullanılarak da çözülebilir. Aşağıdaki örnek, Year özniteliğinin bir değişken kullanılarak ayarlanan değere eşit olması koşuluyla Bookl tablosundan kayıtları getirir:
DECLARE 0y int = 2000;
YÜRÜTME ("SELECT * FROM dbo.Bookl WHERE =" [e-posta korumalı]) ;
Dinamik olarak oluşturulmuş talimatların yürütülmesi, "SQL enjeksiyonu" gibi bilgisayar saldırılarının uygulanması için ön koşullar yaratır. (İng. SQL enjeksiyonu). Saldırının özü, saldırganın kendi SQL kodunu dinamik olarak oluşturulmuş bir sorguya enjekte etmesidir. Bu genellikle, kullanıcı girdisinin sonuçlarından değiştirilen parametreler alındığında olur.
Önceki örneği biraz değiştirelim:
DECLARE 0y varchar (100);
AYARLA 0y = "2OOO"; - bunu kullanıcıdan aldık
SET deyiminde atanan dize değerini kullanıcıdan aldığımızı varsayarsak (örneğin, bir web uygulaması aracılığıyla nasıl olursa olsun), örnek, kodumuzun "normal" davranışını gösterir.
DECLARE 0y varchar (100);
SET 0y = "2000; dbo.Book2'DEN SİL"; - enjeksiyon
YÜRÜTÜCÜ ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
Bu gibi durumlarda, SQL enjeksiyonunun önündeki engellerden biri olan parametre türünü kontrol etmenize olanak tanıyan, mümkün olduğunda sp_executcsql sistem saklı yordamını kullanmanız önerilir. Formatını ayrıntılı olarak düşünmeden, daha önce sunulana benzer bir örneği analiz edelim:
YÜRÜT sp_executesql
N " dbo.Bookl'DAN * SEÇİN NEREDE = 0y",
Bu, sorguda kullanılan parametrenin türünü açıkça belirtir ve yürütme sırasında SQL Server tarafından izlenir. Tırnakların önündeki "N" harfi, bunun prosedürün gerektirdiği şekilde bir Unicode değişmez sabiti olduğunu gösterir. Bir parametreye yalnızca sabit bir değer değil, aynı zamanda başka bir değişkenin değeri de atanabilir.
Saklı yordam SQL dili ve yordamsal uzantılar kullanılarak oluşturulan özel bir Transact-SQL deyimleri grubudur. Paket ve saklı yordam arasındaki temel fark, ikincisinin bir veritabanı nesnesi olarak saklanmasıdır. Diğer bir deyişle, performansı ve tekrarlanabilirliği artırmak için sunucu tarafında saklı yordamlar sürdürülür.
Veritabanı Motoru, saklı yordamları ve sistem yordamlarını destekler. Saklı yordamlar, diğer tüm veritabanı nesneleri ile aynı şekilde oluşturulur, yani. DDL dilini kullanarak. Sistem prosedürleri Veritabanı Motoru tarafından sağlanır ve sistem kataloğundaki bilgilere erişmek ve bunları değiştirmek için kullanılabilir.
Saklı yordam oluştururken isteğe bağlı bir parametre listesi tanımlayabilirsiniz. Böylece, prosedür her çağrıldığında uygun argümanları kabul edecektir. Saklı yordamlar, kullanıcı tanımlı bilgileri içeren bir değer veya bir hata durumunda ilişkili bir hata mesajı döndürebilir.
Saklı yordam, veritabanında bir nesne olarak saklanmadan önce önceden derlenir. Prosedürün önceden derlenmiş formu veri tabanında saklanır ve her çağrıldığında kullanılır. Saklı yordamların bu özelliği, yordamın yeniden derlenmesini (hemen hemen tüm durumlarda) ortadan kaldırması ve ilgili performans iyileştirmeleri sağlaması bakımından önemli bir fayda sağlar. Saklı yordamların bu özelliği, veritabanı sistemi ve uygulamalar arasında değiş tokuş edilen veri miktarı üzerinde de olumlu bir etkiye sahiptir. Özellikle, boyutu birkaç bin bayt olan bir saklı yordama yapılan bir çağrı, 50 bayttan daha az gerektirebilir. Birden çok kullanıcı, saklı yordamları kullanarak tekrarlayan görevler gerçekleştirdiğinde, bu tasarrufların kümülatif etkisi önemli olabilir.
Saklı yordamlar aşağıdaki amaçlar için de kullanılabilir:
veritabanı tablolarıyla bir eylem günlüğü oluşturmak için.
Saklı yordamların kullanımı, kullanıcılara farklı erişim ayrıcalıkları veren GRANT ve REVOKE deyimlerinin kullanımıyla sağlanan güvenlik düzeyini önemli ölçüde aşan bir güvenlik denetimi düzeyi sağlar. Bu mümkündür, çünkü bir saklı yordamı yürütme yetkisi, bir sonraki bölümde açıklandığı gibi, belirli bir saklı yordamda yer alan nesneleri değiştirme yetkisinden bağımsızdır.
Tablolar üzerinde yazma ve/veya okumalar için günlükler oluşturan saklı yordamlar, veritabanı için ek güvenlik sağlar. Bu prosedürleri kullanarak, veritabanı yöneticisi, kullanıcılar veya uygulamalar tarafından veritabanında yapılan değişiklikleri izleyebilir.
Saklı Prosedürlerin Oluşturulması ve Yürütülmesi
Saklı yordamlar ifade kullanılarak oluşturulur PROSEDÜR OLUŞTUR aşağıdaki sözdizimine sahip:
CREATE PROC proc_name [((@ param1) type1 [DEĞİŞEN] [= varsayılan1])] (,…) toplu olarak | HARİCİ ADI method_name Sözdizimi Kuralları
schema_name parametresi, oluşturulan saklı yordamın sahibi olarak atanan şemanın adını belirtir. proc_name parametresi, saklı yordamın adını belirtir. @ param1 parametresi, veri tipi type1 parametresi tarafından belirtilen bir prosedür parametresidir (resmi argüman). Prosedür parametreleri bir prosedür içinde yereldir, tıpkı yerel değişkenlerin bir paket içinde yerel olması gibi. Prosedür parametreleri, arayan tarafından içinde kullanılmak üzere prosedüre geçirilen değerlerdir. default1 parametresi, karşılık gelen prosedür parametresi için varsayılan değeri tanımlar. (Varsayılan NULL da olabilir.)
ÇIKIŞ seçeneği bir prosedür parametresinin, saklı bir prosedürden çağıran prosedüre veya sisteme bir değer döndürmek için kullanılabilen döndürülebilir bir parametre olduğunu gösterir.
Daha önce belirtildiği gibi, prosedürün önceden derlenmiş formu veri tabanında saklanır ve her çağrıldığında kullanılır. Herhangi bir nedenle saklı yordamın her çağrıldığında derlenmesi gerekiyorsa, yordamı bildirirken şunu kullanın: RECOMPILE İLE seçenek... İLE RECOMPILE seçeneğini kullanmak, saklı yordamların en önemli avantajlarından birini ortadan kaldırır: tek bir derlemeden elde edilen performans iyileştirmeleri. Bu nedenle, RECOMPILE İLE seçeneği yalnızca saklı yordam tarafından kullanılan veritabanı nesnelerinde sık sık değişiklik yaparken kullanılmalıdır.
EXECUTE AS yan tümcesi saklı yordamın çağrıldıktan sonra yürütülmesi gereken güvenlik bağlamını tanımlar. Bu bağlamı ayarlayarak, bu saklı yordam tarafından başvurulan nesnelerde erişim izinlerini denetlemek için kullanıcı hesaplarının seçimini denetlemek için Veritabanı Altyapısını kullanabilirsiniz.
Varsayılan olarak, yalnızca sysadmin sabit sunucu rolünün ve db_owner veya db_ddladmin sabit veritabanı rolünün üyeleri CREATE PROCEDURE ifadesini kullanabilir. Ancak bu rollerin üyeleri, talimatı kullanarak bu hakkı diğer kullanıcılara atayabilir. HİBE OLUŞTURMA PROSEDÜRÜ.
Aşağıdaki örnek, Proje tablosuyla çalışmak için basit bir saklı yordamın nasıl oluşturulacağını gösterir:
SampleDb'yi KULLANIN; GİT PROSEDÜR OLUŞTURMA PROSEDÜRÜ Arttır (@yüzde INT = 5) GÜNCELLEME Proje AYARLA Bütçe = Bütçe + Bütçe * @ yüzde / 100;
Daha önce de belirtildiği gibi, iki paketi ayırmak için şunu kullanın: GO talimatı... CREATE PROCEDURE ifadesi, aynı paketteki diğer Transact-SQL ifadeleriyle birleştirilemez. BoostBudget saklı yordamı, tüm projeler için bütçeleri @percent parametresiyle belirtilen belirli bir yüzde sayısı kadar artırır. Prosedür ayrıca, prosedür sırasında bu argüman mevcut değilse kullanılan varsayılan bir yüzde değeri (5) tanımlar.
Saklı yordamlar var olmayan tablolara erişebilir. Bu özellik, önce uygun tabloları oluşturmadan, hatta hedef sunucuya bağlanmadan prosedür kodunda hata ayıklamanıza izin verir.
Her zaman geçerli veritabanında saklanan temel saklı yordamların aksine, her zaman geçici sistem veritabanı tempdb'ye yerleştirilen geçici saklı yordamlar oluşturmak mümkündür. Geçici saklı yordamlar oluşturmanın bir nedeni, bir veritabanına bağlanırken belirli bir grup ifadenin tekrar tekrar yürütülmesini önlemek olabilir. Yerel veya küresel geçici prosedürler oluşturabilirsiniz. Bunun için yerel prosedürün adı tek bir # sembolü (#proc_name) ile belirtilir ve global prosedürün adı bir double (## proc_name) ile belirtilir.
Yerel bir geçici saklı yordam, yalnızca onu oluşturan kullanıcı tarafından ve yalnızca oluşturulduğu veritabanına bağlantı sırasında yürütülebilir. Genel geçici prosedür, tüm kullanıcılar tarafından yürütülebilir, ancak yalnızca yürütüldüğü son bağlantı bitene kadar (genellikle prosedürü oluşturanın bağlantısı).
Bir saklı yordamın yaşam döngüsü iki aşamadan oluşur: oluşturulması ve yürütülmesi. Her prosedür bir kez oluşturulur ve tekrar tekrar yürütülür. saklı yordam tarafından yürütülür EXECUTE ifadeleri bir prosedüre sahip olan veya bu prosedüre erişmek için YÜRÜTME iznine sahip bir kullanıcı. EXECUTE deyimi aşağıdaki sözdizimine sahiptir:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ parametre1 =] değer | [@ parametre1 =] @değişken] | DEFAULT) .. Sözdizimi kuralları
return_status parametresi dışında, EXECUTE ifadesinin tüm parametreleri, CREATE PROCEDURE ifadesinin aynı adı taşıyan parametreleriyle aynı mantıksal anlama sahiptir. return_status parametresi, prosedürün dönüş durumunu saklayan bir tamsayı değişkeni tanımlar. Bir sabit (değer) veya yerel bir değişken (@değişken) kullanılarak bir parametreye bir değer atanabilir. Adlandırılan parametrelerin değerlerinin sırası önemli değildir ancak adsız parametrelerin değerleri CREATE PROCEDURE deyiminde tanımlandıkları sıraya göre verilmelidir.
DEFAULT yan tümcesi prosedür tanımında belirtilen bir prosedür parametresi için varsayılan değerler sağlar. Bir prosedür, hiçbir varsayılan değer tanımlanmamış ve hiçbir parametre belirtilmemiş bir parametre için bir değer beklediğinde veya DEFAULT anahtar sözcüğü belirtildiğinde bir hata oluşur.
EXECUTE ifadesi bir toplu işteki ilk ifade olduğunda, EXECUTE anahtar sözcüğü atlanabilir. Ancak, bu kelimeyi her pakete dahil etmek daha güvenlidir. EXECUTE ifadesinin kullanımı aşağıdaki örnekte gösterilmiştir:
SampleDb'yi KULLANIN; UYGULAMA ArtışBütçesi 10;
Bu örnekteki EXECUTE ifadesi, tüm projeler için bütçeyi %10 artıran BoostBudget saklı yordamını yürütür.
Aşağıdaki örnek, Employee ve Works_on tablolarındaki verileri işlemek için bir saklı yordamın nasıl oluşturulacağını gösterir:
Örnekteki ModifyEmpId yordamı, başvuru bütünlüğü sürecinin bir parçası olarak saklı yordamların kullanımını gösterir (bu durumda, Çalışan ve Works_on tabloları arasında). Benzer bir saklı yordam, gerçekten bilgi bütünlüğünü zorlayan bir tetikleyici tanımı içinde kullanılabilir.
Aşağıdaki örnek, bir saklı yordamda OUTPUT yan tümcesinin kullanımını gösterir:
Bu saklı yordam aşağıdaki talimatlar kullanılarak yürütülebilir:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteÇalışan @ empId = 18316, @ [e-posta korumalı]ÇIKTI; PRINT N "Silinen çalışanlar:" + convert (nvarchar (30), @quantityDeleteEmployee);
Bu prosedür, personel numarası @empId olan çalışanın meşgul olduğu proje sayısını sayar ve elde edilen değeri parametre © sayacına atar. Çalışan ve Works_on tablolarından belirli bir personel numarası için tüm satırlar silindikten sonra hesaplanan değer @quantityDeleteEmployee değişkenine atanır.
Parametre değeri, yalnızca ÇIKIŞ seçeneği belirtilmişse, çağıran prosedüre döndürülür. Yukarıdaki örnekte, DeleteEmployee prosedürü, @counter parametresini çağıran prosedüre iletir, böylece saklı prosedür sisteme bir değer döndürür. Bu nedenle @counter parametresi hem bir prosedür bildirilirken OUTPUT seçeneğinde hem de çağrılırken EXECUTE deyiminde belirtilmelidir.
EXECUTE deyiminin RESULTS SETS yan tümcesi
SQL Server 2012'de EXECUTE ifadesi girilir İLE RESULTS SETS yan tümcesi, aracılığıyla, belirli koşullar karşılandığında, saklı yordamın sonuç kümesinin biçimini değiştirebilirsiniz.
Aşağıdaki iki örnek bu cümleyi açıklamaya yardımcı olacaktır. İlk örnek, WITH RESULTS SETS yan tümcesi çıkarıldığında sonucun nasıl görünebileceğini gösteren bir giriş örneğidir:
ÇalışanlarInDept prosedürü, belirli bir departmanda çalışan tüm çalışanların personel numaralarını ve soyadlarını görüntüleyen basit bir prosedürdür. Departman numarası prosedürün bir parametresidir ve çağrılırken belirtilmelidir. Bu prosedürün gerçekleştirilmesi, başlıkları veritabanı tablosunun ilgili sütunlarının adlarıyla çakışan iki sütunlu bir tablo görüntüler; Kimlik ve Soyadı. SQL Server 2012, sonuç sütun başlıklarını (veri türlerinin yanı sıra) değiştirmek için yeni WITH RESULTS SETS yan tümcesini kullanır. Bu teklifin uygulaması aşağıdaki örnekte gösterilmiştir:
SampleDb'yi KULLANIN; SONUÇ KÜMELERİYLE YÜRÜTÜN ÇalışanInDept "d1" ((INT NOT NULL, [Soyadı] CHAR (20) NULL DEĞİL));
Bu şekilde çağrılan bir saklı yordamın yürütülmesinin sonucu aşağıdaki gibi olacaktır:
Gördüğünüz gibi, EXECUTE deyimindeki WITH RESULT SETS yan tümcesini kullanarak saklı yordamı çalıştırmak, yordam tarafından döndürülen sonuç kümesindeki sütunların adlarını ve veri türünü değiştirmenize olanak tanır. Böylece, bu yeni işlevsellik, saklı yordamları yürütmede ve sonuçlarını yeni bir tabloya yerleştirmede daha fazla esneklik sağlar.
Saklı yordamların yapısını değiştirme
Veritabanı Motoru da deyimi destekler PROSEDÜR DEĞİŞTİRME saklı yordamların yapısını değiştirmek için. ALTER PROCEDURE genellikle bir prosedür içinde Transact-SQL deyimlerini değiştirmek için kullanılır. ALTER PROCEDURE ifadesinin tüm parametreleri, CREATE PROCEDURE ifadesinin aynı adı taşıyan parametreleriyle aynı anlama sahiptir. Bu ifadeyi kullanmanın temel amacı, mevcut saklı yordam haklarını geçersiz kılmaktan kaçınmaktır.
Veritabanı Motoru şunları destekler: veri türü İMLEÇ... Bu veri türü, saklı yordamlarda imleçleri bildirmek için kullanılır. imleç bir sorgunun sonuçlarını (genellikle bir dizi satır) depolamak ve kullanıcıların bu sonucu satır satır görüntülemesini sağlamak için kullanılan bir programlama yapısıdır.
Bir veya bir grup saklı yordamı silmek için şunu kullanın: DROP PROSEDÜRÜ ifadesi... Yalnızca db_owner ve sysadmin sabit rollerinin sahibi veya üyeleri saklı yordamı silebilir.
Saklı Prosedürler ve CLR
SQL Server, C# ve Visual Basic kullanarak çeşitli veritabanı nesneleri (depolanmış yordamlar, UDF'ler, tetikleyiciler, UDF'ler ve UDD'ler) geliştirmenize olanak tanıyan Ortak Dil Çalışma Zamanı'nı (CLR) destekler. CLR, bu nesneleri ortak çalışma zamanı sistemini kullanarak yürütmenize de olanak tanır.
CLR'ye seçenek tarafından izin verilir ve reddedilir clr_enabled sistem prosedürü sp_configure, talimat tarafından yürütülmek üzere başlatılan YENİDEN YAPILANDIR... Aşağıdaki örnek, sp_configure sistem prosedürü kullanılarak CLR'nin nasıl etkinleştirilebileceğini gösterir:
SampleDb'yi KULLANIN; EXEC sp_configure "clr_enabled", 1 YENİDEN YAPILANDIRMA
CLR kullanarak bir yordam oluşturmak, derlemek ve kaydetmek için aşağıdaki adımları sırasıyla tamamlamanız gerekir:
C# veya Visual Basic'te saklı bir prosedür oluşturun ve ardından uygun derleyiciyi kullanarak onu derleyin.
talimatı kullanma MONTAJ OLUŞTUR, ilgili yürütülebilir dosyayı oluşturun.
EXECUTE deyimini kullanarak prosedürü yürütün.
Aşağıdaki şekil, daha önce ana hatlarıyla belirtilen adımların bir akış şemasını göstermektedir. Aşağıda bu sürecin daha ayrıntılı bir açıklaması yer almaktadır.
Öncelikle gerekli programı Visual Studio gibi bir geliştirme ortamında oluşturun. Bitmiş programı C# veya Visual Basic derleyicisini kullanarak nesne koduna derleyin. Bu kod, yürütülebilir ara kod üreten CREATE ASSEMBLY ifadesinin kaynağı olan bir dinamik bağlantı kitaplığı (.dll) dosyasına kaydedilir. Ardından, yürütülebilir kodu bir veritabanı nesnesi olarak kaydetmek için CREATE PROCEDURE ifadesini çalıştırın. Son olarak, tanıdık EXECUTE deyimini kullanarak prosedürü çalıştırın.
Aşağıdaki örnek, C#'da saklı bir yordamın kaynak kodunu gösterir:
System.Data.SqlClient'i kullanma; Microsoft.SqlServer.Server'ı kullanarak; genel kısmi sınıf StoredProcedures (genel statik int CountEmployees () (int satırlar; SqlConnection bağlantı = yeni SqlConnection ("Bağlam Bağlantısı = doğru"); connection.Open (); SqlCommand cmd = bağlantı.CreateCommand (); cmd.CommandText = "select (*) "Çalışan Sayısı" olarak "+" Çalışandan "; satırlar = (int) cmd.ExecuteScalar (); connection.Close (); satırları döndür;))
Bu prosedür, Çalışan tablosundaki satır sayısını saymak için bir sorgu uygular. Programın başındaki using yönergeleri, programı yürütmek için gereken ad alanlarını belirtir. Bu yönergeleri kullanmak, karşılık gelen ad alanlarını açıkça belirtmeden kaynak kodda sınıf adlarını belirtmenize olanak tanır. Ardından, StoredProcedures sınıfı tanımlanır, bunun için SqlProcedure özniteliği derleyiciye bu sınıfın saklı bir prosedür olduğunu bildirir. CountEmployees() yöntemi, sınıf kodunun içinde tanımlanır. Veritabanı sistemine bağlantı, sınıfın bir örneği aracılığıyla kurulur. SQL Bağlantısı... Bir bağlantı açmak için o örneğin Open () yöntemi kullanılır. A CreateCommand() yöntemi sınıfın bir örneğine erişmenizi sağlar SqlKomut gerekli SQL komutunun iletildiği.
Aşağıdaki kod parçacığında:
Cmd.CommandText = "sayıyı (*)" Çalışan Sayısı "" + "Çalışandan" olarak seçin;
Çalışan tablosundaki satır sayısını saymak ve sonucu görüntülemek için bir SELECT ifadesi kullanır. Komut metni, cmd değişkeninin CommandText özelliği, CreateCommand() yöntemi tarafından döndürülen örneğe ayarlanarak belirtilir. Sonraki denir ExecuteScalar() yöntemi bir SqlCommand örneği. Bu yöntem, int veri türüne dönüştürülen ve satırlara atanan bir skaler değer döndürür.
Artık bu kodu Visual Studio kullanarak derleyebilirsiniz. Bu sınıfı CLRStoredProcedures adlı projeye ekledim, böylece Visual Studio aynı isimli derlemeyi * .dll uzantısıyla derleyecek. Aşağıdaki örnek, bir saklı yordam oluşturmanın sonraki adımını gösterir: yürütülebilir kod oluşturma. Bu örnekteki kodu çalıştırmadan önce, derlenmiş dll dosyasının (genellikle projenin Debug klasöründe bulunur) konumunu bilmeniz gerekir.
SampleDb'yi KULLANIN; GİT, PERMISSION_SET = GÜVENLİ "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" İLE MONTAJ CLRStoredProcedures OLUŞTURUN
CREATE ASSEMBLY ifadesi, yönetilen kodu girdi olarak alır ve kendisi için CLR saklı yordamlar, UDF'ler ve tetikleyiciler oluşturabileceğiniz uygun bir nesne oluşturur. Bu ifade aşağıdaki sözdizimine sahiptir:
CREATE ASSEMBLY Assembly_name [YETKİ sahibi_adı] FROM (dll_file) Sözdizimi Kuralları
Assembly_name, derlemenin adını belirtir. İsteğe bağlı AUTHORIZATION yan tümcesi, bu derlemenin sahibi olarak rol adını belirtir. FROM yan tümcesi, yüklenecek montajın bulunduğu yolu belirtir.
İLE PERMISSION_SET yan tümcesi CREATE ASSEMBLY ifadesinin çok önemli bir maddesidir ve her zaman dahil edilmelidir. Derleme koduna verilen erişim hakları kümesini tanımlar. GÜVENLİ haklar grubu en kısıtlayıcı olanıdır. Bu haklara sahip olan derleme kodu, dosyalar gibi harici sistem kaynaklarına erişemez. EXTERNAL_ACCESS ayrıcalık seti, derleme kodunun belirli harici sistem kaynaklarına erişmesine izin verirken, UNSAFE ayrıcalık seti, hem veritabanı sisteminin içindeki hem de dışındaki kaynaklara sınırsız erişim sağlar.
Kullanıcı, derleme kodu hakkındaki bilgileri korumak için CREATE ASSEMBLY deyimini yürütebilmelidir. Derleme, bu ifadeyi yürüten kullanıcıya (veya role) aittir. CREATE SCHEMA ifadesinin AUTHORIZATION yan tümcesini kullanarak derleme sahibini başka bir kullanıcı yapabilirsiniz.
Veritabanı Motoru ayrıca ALTER ASSEMBLY ve DROP ASSEMBLY deyimlerini de destekler. ALTER MONTAJ Beyanı bir derlemeyi en son sürüme güncellemek için kullanılır. Bu ifade ayrıca ilgili derlemeyle ilişkili dosyaları ekler veya kaldırır. DROP MONTAJ Beyanı belirtilen derlemeyi ve tüm ilişkili dosyaları geçerli veritabanından kaldırır.
Aşağıdaki örnek, daha önce uyguladığınız yönetilen koda dayalı olarak bir saklı yordamın nasıl oluşturulacağını gösterir:
SampleDb'yi KULLANIN; GO PROSEDÜR OLUŞTURUNÇalışanları HARİCİ AD OLARAK Say
Örnekteki CREATE PROCEDURE ifadesi, önceki örneklerdeki aynı ifadeden aşağıdakileri içermesi bakımından farklıdır: HARİCİ AD parametresi... Bu parametre, kodun CLR tarafından oluşturulduğunu gösterir. Bu cümledeki isim üç kısımdır:
derleme_adı.sınıf_adı.yöntem_adı
Assembly_name Derlemenin adını belirtir.
sınıf_adı - genel sınıfın adını belirtir;
method_name - isteğe bağlı, sınıf içinde belirtilen yöntemin adını belirtir.
CountEmployees prosedürünün yürütülmesi aşağıdaki örnekte gösterilmiştir:
SampleDb'yi KULLANIN; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count - 7 döndürür
PRINT ifadesi, Çalışan tablosundaki geçerli satır sayısını döndürür.
Microsoft SQL Server'da kendi algoritmalarını uygulamak ve otomatikleştirmek için ( hesaplamalar), saklı yordamları kullanabilirsiniz, bu nedenle bugün bunların nasıl oluşturuldukları, değiştirildikleri ve silindikleri hakkında konuşacağız.
Ama önce, T-SQL'de saklı yordamların ne olduğunu ve ne işe yaradığını anlamanız için biraz teori.
Not! Acemi programcılar için T-SQL konusunda aşağıdaki faydalı materyalleri öneriyorum:
- T-SQL dili hakkında daha ayrıntılı bir çalışma için, T-SQL Programcının Yolu adlı kitabı da okumanızı tavsiye ederim. Transact-SQL dili için öğretici.
T-SQL'de saklı yordamlar nelerdir?
saklı prosedürler Bir dizi SQL ifadesi biçiminde bir algoritma içeren veritabanı nesneleridir. Başka bir deyişle, saklı yordamların bir veri tabanı içindeki programlar olduğunu söyleyebiliriz. Saklı yordamlar, yeniden kullanılabilir kodu sunucuya kaydetmek için kullanılır, örneğin bir algoritma, sıralı hesaplama veya çok adımlı SQL deyimi yazdınız ve bu algoritmada yer alan tüm talimatları her seferinde yürütmemek için tasarlayabilirsiniz. saklı bir prosedür olarak. Aynı zamanda, bir SQL prosedürü oluşturduğunuzda, sunucu kodu derler ve ardından, bu SQL prosedürünü her çalıştırdığınızda, sunucu artık onu yeniden derlemeyecektir.
SQL Server'da bir saklı yordam çalıştırmak için, adından önce EXECUTE komutunu yazmak gerekir, yoksa kısaltılmış EXEC komutu da mümkündür. Bir SELECT deyiminde saklı yordamın örneğin bir işlev olarak çağrılması artık çalışmayacaktır, yani. prosedürler ayrı ayrı yürütülür.
Saklı yordamlarda, işlevlerin aksine, UNSERT, UPDATE, DELETE gibi veri değiştirme işlemlerini zaten gerçekleştirebilirsiniz. Ayrıca, prosedürlerde, hemen hemen her türden SQL deyimlerini kullanabilirsiniz, örneğin, tablolar oluşturmak için CREATE TABLE veya EXECUTE, yani. diğer prosedürleri çağırmak. İstisna, aşağıdakiler gibi çeşitli talimat türleridir: işlevler, görünümler, tetikleyiciler, şemalar oluşturma ve diğer birkaç benzer talimat oluşturma veya değiştirme, örneğin, saklı bir yordamda veritabanı bağlantı bağlamını (USE) değiştirmek de imkansızdır.
Saklı bir yordam giriş parametrelerine ve çıkış parametrelerine sahip olabilir, tablo verileri döndürebilir, hiçbir şey döndüremez, yalnızca içerdiği talimatları yürütebilir.
Saklı prosedürler çok faydalıdır, birçok işlemi otomatikleştirmemize veya basitleştirmemize yardımcı olur, örneğin, pivot tabloları kullanarak sürekli olarak çeşitli karmaşık analitik raporlar oluşturmanız gerekir, yani. PIVOT operatörü. Bu operatörle sorgu oluşturmayı kolaylaştırmak için ( Bildiğiniz gibi, PIVOT'un sözdizimi oldukça karmaşıktır), Sizin için dinamik olarak özet raporlar oluşturacak bir prosedür yazabilirsiniz, örneğin "Dynamic PIVOT in T-SQL" materyalinde bu özelliğin bir saklı prosedür şeklinde nasıl uygulanabileceğinin bir örneği sunulmaktadır.
Microsoft SQL Server'da Saklı Yordamlarla Çalışma Örnekleri
Örnekler için ilk veriler
Aşağıdaki tüm örnekler Microsoft SQL Server 2016 Express'te yürütülecektir. Saklı yordamların gerçek verilerle nasıl çalıştığını göstermek için bu verilere ihtiyacımız var, onu oluşturalım. Örneğin, bir test tablosu oluşturalım ve ona birkaç kayıt ekleyelim, diyelim ki ürünlerin fiyatları ile birlikte bir listesini içeren bir tablo.
Tablo oluşturma ifadesi CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Veri ekleme talimatı TestTable'a INSERT INTER (CategoryId, ProductName, Price) VALUES (1 , "Fare", 100), (1, "Klavye", 200), (2, "Telefon", 400) GİT -- İsteği seç Test Tablosundan SEÇ *
Veriler orada, şimdi saklı yordamlar oluşturmaya geçelim.
T-SQL Saklı Yordam Oluşturma - CREATE PROCEDURE İfadesi
Saklı yordamlar ifade kullanılarak oluşturulur PROSEDÜR OLUŞTUR, bu talimattan sonra işleminizin adını yazmalı, gerekirse parantez içinde giriş ve çıkış parametrelerini tanımlamalısınız. Daha sonra AS anahtar sözcüğünü yazıp komut bloğunu BEGIN anahtar sözcüğü ile açarsınız, bu bloğu END sözcüğü ile kapatırsınız. Bu bloğun içine, algoritmanızı veya bir tür sıralı hesaplamayı uygulayan tüm talimatları yazarsınız, başka bir deyişle T-SQL'de programlarsınız.
Örneğin, yeni bir kayıt ekleyecek bir saklı yordam yazalım, yani. test çizelgemize yeni ürün. Bunu yapmak için üç gelen parametre tanımlayacağız: @CategoryId - ürün kategorisi tanımlayıcısı, @ProductName - ürün adı ve @Price - ürün fiyatı, bu parametre isteğe bağlı olacaktır, yani. prosedüre geçmek mümkün olmayacaktır ( mesela daha fiyatını bilmiyoruz), bunun için tanımında varsayılan değeri ayarlayacağız. Bu parametreler prosedürün gövdesindedir, yani. BEGIN ... END bloğunda sıradan değişkenlerle aynı şekilde kullanabilirsiniz ( bildiğiniz gibi değişkenler @ ile gösterilir.). Çıkış parametrelerini belirtmeniz gerekiyorsa, parametre adından sonra OUTPUT anahtar sözcüğünü belirtin ( veya kısaltılmış OUT).
BEGIN… END bloğunda, veri eklemek için bir ifade ve prosedürün sonunda bir SELECT ifadesi yazacağız; , sadece ürün eklendi. Ayrıca bu saklı yordamda, yanlışlıkla birkaç boşluk eklendiğinde durumlardan kaçınmak için giriş parametresinin işlenmesini, yani metin satırının başında ve sonunda fazladan boşlukların kaldırılmasını ekledim.
İşte bu prosedürün kodu ( ben de ona yorum yaptım).
Bir prosedür oluşturun CREATE PROCEDURE TestProcedure (--Giriş parametreleri @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) BAŞLANGIÇ OLARAK --Algoritmanızı uygulayan talimatlar --Gelen parametreleri işleyin --Başlangıçta fazladan boşlukları silin ve metin satırının sonunda SET @ProductName = LTRIM (RTRIM (@ProductName)); --Yeni bir INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) kaydı ekleyin - Verileri döndürün SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
T-SQL Saklı Yordam Çalıştırma - EXECUTE Komutu
Daha önce belirttiğim gibi, EXECUTE veya EXEC komutunu kullanarak saklı bir prosedür başlatabilirsiniz. Gelen parametreler, prosedür adından sonra basitçe listelenip uygun değerler belirtilerek prosedürlere iletilir ( çıktı parametreleri için OUTPUT komutunu da belirtmeniz gerekir.). Bununla birlikte, parametrelerin adı atlanabilir, ancak bu durumda değerleri belirleme sırasını takip etmek gerekir, yani. giriş parametrelerinin tanımlandığı sırayla değerleri belirtin ( bu aynı zamanda çıkış parametreleri için de geçerlidir).
Varsayılan değerlere sahip parametrelerin belirtilmesi gerekmez, bunlara isteğe bağlı parametreler denir.
Saklı yordamları, özellikle de test yordamımızı çalıştırmanın bazı farklı ama eşdeğer yolları.
1. Fiyatı belirtmeden prosedürü çağırın UYGULAYIN TestProcedure @CategoryId = 1, @ProductName = "Test product 1" ---2. EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 ---3 fiyat göstergesiyle prosedürü çağırıyoruz. EXEC TestProcedure 1, "Test Product 3", 400 parametrelerinin adını belirtmeden prosedürü çağırıyoruz.
Saklı bir prosedürü T-SQL - ALTER PROCEDURE deyimine değiştirme
Talimatları kullanarak prosedürün algoritmasında değişiklik yapabilirsiniz. PROSEDÜR DEĞİŞTİRME... Başka bir deyişle, mevcut bir prosedürü değiştirmek için CREATE PROCEDURE yerine ALTER PROCEDURE yazmanız ve diğer her şeyi gerektiği gibi değiştirmeniz yeterlidir.
Diyelim ki test prosedürümüzde değişiklik yapmamız gerekiyor, @Price parametresini söyleyin, yani. fiyat, bunu zorunlu hale getireceğiz, bunun için varsayılan değeri kaldıracağız ve ayrıca sonuçta ortaya çıkan veri kümesini almamıza gerek olmadığını hayal edeceğiz, bunun için SELECT ifadesini saklı prosedürden kaldıracağız.
ALTER PROCEDURE TestProcedure'ı değiştiriyoruz (--Gelen parametreler @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) BAŞLANGIÇ OLARAK --Algoritmanızı uygulayan komutlar --Giriş parametrelerini işlemek - Başta ve sonunda gereksiz boşlukları kaldırmak metin satırlarının sonu SET @ProductName = LTRIM (RTRIM (@ProductName)); --Yeni bir INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO kaydı ekleyin
Bir T-SQL Saklı Yordamını Bırakma - DROP PROCEDURE Bildirimi
Gerekirse, saklı yordamı silebilirsiniz, bu talimat kullanılarak yapılır. BIRAKMA PROSEDÜRÜ.
Örneğin oluşturduğumuz test prosedürünü silelim.
BIRAKMA PROSEDÜRÜ Test Prosedürü
Saklı yordamları silerken, yordama başka yordamlar veya SQL deyimleri tarafından başvuruluyorsa, sildikten sonra, başvurdukları yordam artık mevcut olmadığından başarısız olacaklarını hatırlamakta fayda var.
Her şeye sahibim, umarım materyal şimdiye kadar sizin için ilginç ve faydalı olmuştur!
