T-SQL'de saklı prosedürler - oluşturma, değiştirme, silme. Microsoft sql sunucusunda saklı yordamlar oluşturma Sql saklı yordam oluşturma

Saklı yordam SQL dili ve prosedür uzantıları kullanılarak oluşturulan özel bir Transact-SQL deyim paketi türüdür. Bir paket ile saklı yordam arasındaki temel fark, ikincisinin bir veritabanı nesnesi olarak saklanmasıdır. Başka bir deyişle, saklı prosedürler tekrarlanabilir görevlerin performansını ve tutarlılığını artırmak için sunucu tarafında saklanır.

Veritabanı Motoru, saklı prosedürleri ve sistem prosedürlerini destekler. Saklı prosedürler diğer tüm veritabanı nesneleri ile aynı şekilde oluşturulur; 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ı bir prosedür oluşturduğunuzda, isteğe bağlı bir parametre listesi tanımlayabilirsiniz. Bu şekilde prosedür, her çağrıldığında uygun argümanları kabul edecektir. Saklı prosedürler, kullanıcı tanımlı bilgileri içeren bir değer veya bir hata durumunda uygun bir hata mesajı döndürebilir.

Saklı yordam, veritabanında bir nesne olarak saklanmadan önce önceden derlenir. Prosedürün önceden derlenmiş formu veritabanında saklanır ve her çağrıldığında kullanılır. Saklı prosedürlerin bu özelliği, (neredeyse tüm durumlarda) tekrarlanan prosedür derlemelerinin ortadan kaldırılması ve buna karşılık gelen performans iyileştirmelerinin sağlanması gibi önemli bir fayda sağlar. Saklı prosedürlerin bu özelliği, veritabanı sistemi ile uygulamalar arasında değiştirilen veri miktarı üzerinde de olumlu bir etkiye sahiptir. Özellikle, boyutu birkaç bin bayt olan bir saklı yordamın çağrılması, 50 bayttan daha azını gerektirebilir. Birden fazla kullanıcı, saklı prosedürleri kullanarak tekrarlayan görevleri gerçekleştirdiğinde, bu tasarrufların kümülatif etkisi oldukça önemli olabilir.

Saklı prosedürler aşağıdaki amaçlar için de kullanılabilir:

veritabanı tablolarıyla bir eylem günlüğü oluşturmak için.

Saklı prosedürlerin kullanılması, kullanıcılara farklı erişim ayrıcalıkları veren GRANT ve REVOKE ifadelerinin kullanılmasıyla sağlanan güvenliğin çok ötesine geçen bir güvenlik kontrolü 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, saklı yordamda yer alan nesneleri değiştirme yetkisinden bağımsızdır.

Tablo yazma ve/veya okuma işlemlerinin günlüklerini oluşturan saklı prosedürler, veritabanı güvenliği için ek bir seçenek sunar. Bu tür prosedürleri kullanarak veritabanı yöneticisi, kullanıcılar veya uygulama programları tarafından veritabanında yapılan değişiklikleri izleyebilir.

Saklı Prosedürlerin Oluşturulması ve Yürütülmesi

Saklı prosedürler bir ifade kullanılarak oluşturulur PROSEDÜR OLUŞTUR, aşağıdaki sözdizimine sahiptir:

CREATE PROC proc_name [((@param1) type1 [ DEĞİŞEN] [= default1] )] (, ...) AS toplu | EXTERNAL NAME method_name Sözdizimi kuralları

schema_name parametresi, oluşturulan saklı yordamın sahibi tarafından atanan şemanın adını belirtir. proc_name parametresi saklı yordamın adını belirtir. @param1 parametresi, veri türü type1 parametresi tarafından belirlenen bir prosedür parametresidir (resmi argüman). Prosedür parametreleri prosedür içinde yereldir, tıpkı yerel değişkenlerin paket içinde yerel olması gibi. Prosedür parametreleri, arayan tarafından prosedürde kullanılmak üzere iletilen değerlerdir. default1 parametresi, karşılık gelen prosedür parametresi için varsayılan değeri belirtir. (Varsayılan değer NULL da olabilir.)

ÇIKIŞ seçeneği bir prosedür parametresinin bir dönüş parametresi olduğunu ve saklı bir prosedürden çağıran prosedüre veya sisteme bir değer döndürmek için kullanılabileceğini belirtir.

Daha önce de belirtildiği gibi, bir prosedürün önceden derlenmiş formu veritabanı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: YENİDEN DERLEME seçeneği İLE. İLE RECOMPILE seçeneğinin kullanılması, saklı yordamların en önemli faydalarından birini ortadan kaldırır: tek bir derlemeden kaynaklanan performans artışı. Bu nedenle, İLE RECOMPILE seçeneği yalnızca saklı yordam tarafından kullanılan veritabanı nesneleri sık sık değiştirildiğinde 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 Veritabanı Motoru, saklı yordamın referans verdiği nesnelere erişim izinlerini doğrulamak için kullanıcı hesaplarının seçimini kontrol edebilir.

Varsayılan olarak, yalnızca sysadmin sabit sunucu rolünün ve db_owner veya db_ddladmin sabit veritabanı rollerinin üyeleri CREATE PROCEDURE deyimini kullanabilir. Ancak bu rollerin üyeleri bu hakkı, ifadeyi kullanarak 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ŞTURUN Bütçeyi Artır (@percent INT=5) GÜNCELLEME OLARAK Proje SET Bütçe = Bütçe + Bütçe * @percent/100;

Daha önce belirtildiği gibi iki paketi ayırmak için şunu kullanın: GİT talimatları. CREATE PROCEDURE deyimi aynı toplu işteki diğer Transact-SQL deyimleriyle birleştirilemez. Bütçeyi Artırma saklı yordamı, tüm projelerin bütçelerini @percent parametresi tarafından belirlenen belirli bir yüzde oranında artırır. Prosedür ayrıca, prosedür çalıştırıldığında bu bağımsız değişkenin mevcut olmaması durumunda kullanılacak varsayılan bir yüzde değerini (5) de tanımlar.

Saklı prosedürler var olmayan tablolara erişebilir. Bu özellik, önce uygun tabloları oluşturmanıza ve hatta hedef sunucuya bağlanmanıza gerek kalmadan prosedür kodunda hata ayıklamanıza olanak tanır.

Her zaman geçerli veritabanında saklanan birincil saklı yordamların aksine, her zaman geçici sistem veritabanı tempdb'de saklanan 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 ifade grubunun tekrar tekrar yürütülmesini önlemek olabilir. Yerel veya global geçici prosedürler oluşturabilirsiniz. Bunu yapmak için, yerel prosedürün adı tek # karakterle (#proc_name) belirtilir ve global prosedürün adı çift karakterle (##proc_name) belirtilir.

Yerel geçici saklı prosedür yalnızca onu oluşturan kullanıcı tarafından ve yalnızca oluşturulduğu veritabanına bağlıyken yürütülebilir. Genel bir 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ı (genellikle prosedürü oluşturanın bağlantısı) sona erene kadar.

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 birçok kez yürütülür. Saklı prosedür kullanılarak yürütülür Talimatları UYGULAYIN Bir prosedürün sahibi olan veya bu prosedüre erişmek için EXECUTE ayrıcalığına sahip olan bir kullanıcı. EXECUTE deyimi aşağıdaki sözdizimine sahiptir:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] değer | [@parameter1=] @variable ] | DEFAULT).. Sözdizimi kuralları

return_status parametresi dışında, EXECUTE ifadesinin tüm parametreleri, CREATE PROCEDURE ifadesinin aynı parametreleriyle aynı mantıksal anlama sahiptir. return_status parametresi, prosedürün dönüş durumunu saklayan bir tamsayı değişkenini belirtir. Bir parametreye bir sabit (değer) veya yerel bir değişken (@değişken) kullanılarak bir değer atanabilir. Adlandırılmış parametrelerin değerlerinin sırası önemli değildir ancak adsız parametrelerin değerleri CREATE PROCEDURE deyiminde tanımlandığı sıraya göre sağlanmalıdır.

DEFAULT yan tümcesi prosedür tanımında belirtilen prosedür parametresi için varsayılan değeri sağlar. Bir prosedür, varsayılan değeri tanımlanmayan ve parametrenin eksik olduğu veya DEFAULT anahtar sözcüğü belirtildiği bir parametre için değer beklediğinde bir hata oluşur.

EXECUTE ifadesi bir toplu işlemin ilk ifadesi olduğunda, EXECUTE anahtar sözcüğü atlanabilir. Ancak bu kelimenin her pakete dahil edilmesi daha güvenlidir. EXECUTE ifadesinin kullanımı aşağıdaki örnekte gösterilmiştir:

SampleDb'yi KULLANIN; Bütçe Artırma 10'u UYGULAYIN;

Bu örnekteki EXECUTE ifadesi, tüm projelerin bütçesini %10 oranında artıran ArtırmaBudget saklı yordamını yürütür.

Aşağıdaki örnek, Employee ve Works_on tablolarındaki verileri işlemek için saklı yordamın nasıl oluşturulacağını gösterir:

ModifyEmpId örnek prosedürü, referans bütünlüğünü koruma sürecinin bir parçası olarak (bu durumda Employee ve Works_on tabloları arasında) saklı prosedürlerin kullanımını gösterir. Benzer bir saklı prosedür, aslında referans bütünlüğü sağlayan bir tetikleyici tanımının içinde kullanılabilir.

Aşağıdaki örnek, bir saklı yordamda OUTPUT yan tümcesinin kullanımını gösterir:

Bu saklı prosedür aşağıdaki talimatlar kullanılarak yürütülebilir:

DECLARE @quantityDeleteEmployee INT; YÜRÜTME SilmeEmployee @empId=18316, @counter=@quantityDeleteEmployee ÇIKIŞI; PRINT N"Silinen çalışanlar: " + Convert(nvarchar(30), @quantityDeleteEmployee);

Bu prosedür, personel numarası @empId olan çalışanın üzerinde çalıştığı proje sayısını sayar ve ortaya çıkan değeri ©counter parametresine atar. Employee ve Works_on tablolarından belirli bir personel numarasına ait tüm satırlar silindikten sonra hesaplanan değer @quantityDeleteEmployee değişkenine atanır.

Parametre değeri yalnızca OUTPUT seçeneği belirtildiğinde çağırma prosedürüne döndürülür. Yukarıdaki örnekte, deleteEmployee prosedürü @counter parametresini çağıran prosedüre iletir, dolayısıyla saklı prosedür sisteme bir değer döndürür. Bu nedenle, @counter parametresinin hem bir prosedür bildirilirken OUTPUT seçeneğinde hem de onu çağırırken EXECUTE deyiminde belirtilmesi gerekir.

EXECUTE ifadesinin YOUR RESULTS SETS cümleciği

SQL Server 2012'de EXECUTE ifadesi için şunu girersiniz: İLE SONUÇ SETLERİ yan tümcesi belirli koşullar yerine getirildiğinde, 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, YOUR RESULTS SETS yan tümcesi atlandığında sonucun nasıl görünebileceğini gösteren giriş niteliğinde bir örnektir:

EmployeesInDept 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ı bir prosedür parametresidir ve çağrılırken belirtilmelidir. Bu prosedürün yürütülmesi, başlıkları veritabanı tablosundaki karşılık gelen sütunların adlarıyla eşleşen iki sütunlu bir tablo üretir; Kimlik ve Soyadı. Sonuç sütunlarının başlıklarını (ve veri türlerini) değiştirmek için SQL Server 2012, yeni YOUR RESULTS SETS yan tümcesini kullanır. Bu cümlenin uygulaması aşağıdaki örnekte gösterilmektedir:

SampleDb'yi KULLANIN; SONUÇ SETLERİ İLE EXEC EmployeesInDept "d1" (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

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 deyimini kullanarak bir saklı yordamı çalıştırmak, yordamın ürettiği sonuç kümesindeki sütunların adlarını ve veri türlerini değiştirmenize olanak tanır. Böylece, bu yeni işlevsellik, saklı yordamların yürütülmesinde ve sonuçlarının yeni bir tabloya yerleştirilmesinde daha fazla esneklik sağlar.

Saklı Prosedürlerin Yapısının Değiştirilmesi

Veritabanı Motoru aynı zamanda talimatları da destekler PROSEDÜRÜ DEĞİŞTİRİN Saklı prosedürlerin yapısını değiştirmek için. ALTER PROCEDURE ifadesi genellikle bir prosedür içindeki Transact-SQL ifadelerini değiştirmek için kullanılır. ALTER PROCEDURE ifadesinin tüm parametreleri, CREATE PROCEDURE ifadesinin aynı parametreleriyle aynı anlama sahiptir. Bu ifadeyi kullanmanın temel amacı, mevcut saklı yordam haklarının geçersiz kılınmasını önlemektir.

Veritabanı Motoru şunları destekler: İMLEÇ veri türü. Bu veri türü, saklı yordamlardaki imleçleri bildirmek için kullanılır. İmleç 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ülemesine izin vermek için kullanılan bir programlama yapısıdır.

Bir veya bir grup saklı yordamı silmek için şunu kullanın: BIRAKMA PROSEDÜRÜ talimatı. Yalnızca db_owner ve sysadmin sabit rollerinin sahibi veya üyeleri saklı yordamı silebilir.

Saklı prosedürler ve ortak dil çalışma zamanı

SQL Server, C# ve Visual Basic kullanarak çeşitli veritabanı nesneleri (saklı prosedürler, kullanıcı tanımlı işlevler, tetikleyiciler, kullanıcı tanımlı toplamalar ve özel veri türleri) geliştirmenize olanak tanıyan Ortak Dil Çalışma Zamanını (CLR) destekler. CLR ayrıca bu nesneleri ortak çalışma zamanı sistemini kullanarak yürütmenize de olanak tanır.

Ortak dil çalışma zamanı seçeneği kullanılarak etkinleştirilir ve devre dışı bırakılır clr_enabled sistem prosedürü sp_configure talimatla yürütülmek üzere başlatılan YENİDEN YAPILANDIR. Aşağıdaki örnek, CLR'yi etkinleştirmek için sp_configure sistem prosedürünü nasıl kullanabileceğinizi gösterir:

SampleDb'yi KULLANIN; EXEC sp_configure "clr_enabled",1 YENİDEN YAPILANDIR

CLR'yi kullanarak bir prosedür oluşturmak, derlemek ve kaydetmek için aşağıdaki adım dizisini gösterilen sırayla tamamlamanız gerekir:

C# veya Visual Basic'te saklı bir prosedür oluşturun ve ardından bunu uygun derleyiciyi kullanarak derleyin.

Yönergeleri kullanarak MONTAJ OLUŞTURUN, ilgili yürütülebilir dosyayı oluşturun.

EXECUTE deyimini kullanarak yordamı yürütün.

Aşağıdaki şekil, daha önce özetlenen adımların grafiksel bir diyagramını göstermektedir. Aşağıda bu sürecin daha ayrıntılı bir açıklaması bulunmaktadır.

Öncelikle gerekli programı Visual Studio gibi bir geliştirme ortamında oluşturun. Biten programı bir C# veya Visual Basic derleyicisi kullanarak nesne koduna derleyin. Bu kod, ara yürütülebilir kodu oluşturan CREATE ASSEMBLY deyiminin kaynağı olarak hizmet veren bir dinamik bağlantı kitaplığı (.dll) dosyasında depolanır. Daha sonra, yürütülen kodu bir veritabanı nesnesi olarak kaydetmek için bir CREATE PROCEDURE ifadesi yayınlayın. Son olarak tanıdık EXECUTE ifadesini kullanarak prosedürü çalıştırın.

Aşağıdaki örnek, C#'taki saklı yordamın kaynak kodunu gösterir:

System.Data.SqlClient Kullanımı; Microsoft.SqlServer.Server'ı kullanarak; genel kısmi sınıf StoredProcedures ( public static int CountEmployees() ( int satırlar; SqlConnection bağlantısı = new SqlConnection("Bağlam Bağlantısı=true"); Connection.Open(); SqlCommand cmd = Connection.CreateCommand(); cmd.CommandText = "select count(*) as "Çalışan sayısı" " + "Çalışandan"; satırlar = (int)cmd.ExecuteScalar(); bağlantı.Close(); return satırlar; ))

Bu prosedür, Çalışan tablosundaki satır sayısını saymak için bir sorgu uygular. Bir programın başlangıcındaki yönergeleri kullanmak, 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 kodunda sınıf adlarını belirtmenize olanak tanır. Daha sonra StoredProcedures sınıfı tanımlanır; SqlProcedure özelliğ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. Sınıfın bir örneği aracılığıyla veritabanı sistemine bağlantı kurulur Sql Bağlantısı. Bir bağlantı açmak için bu örneğin Open() yöntemi kullanılır. A CreateCommand() yöntemi bir sınıfın örneğine erişmenizi sağlar SqlCommnd, gerekli SQL komutunun iletildiği yer.

Aşağıdaki kod parçacığında:

Cmd.CommandText = ""Çalışan sayısı" olarak sayıyı(*) seçin " + "Çalışandan";

Çalışan tablosundaki satır sayısını saymak ve sonucu görüntülemek için SELECT ifadesini kullanır. Komut metni, cmd değişkeninin CommandText özelliğinin CreateCommand() yöntemi tarafından döndürülen örneğe ayarlanmasıyla belirtilir. Sonra buna denir ExecuteScalar() yöntemi SqlCommand örneği. Bu yöntem, tamsayı veri türüne dönüştürülen ve satır değişkenine atanan skaler bir değer döndürür.

Artık bu kodu Visual Studio'yu kullanarak derleyebilirsiniz. Bu sınıfı CLRStoredProcedures adlı bir projeye ekledim, böylece Visual Studio aynı adı taşıyan bir derlemeyi *.dll uzantısıyla derleyecektir. Aşağıdaki örnek, saklı yordam oluşturmanın bir sonraki adımını gösterir: yürütülebilir kodun oluşturulması. Bu örnekteki kodu çalıştırmadan önce derlenmiş dll dosyasının konumunu bilmeniz gerekir (genellikle projenin Debug klasöründe bulunur).

SampleDb'yi KULLANIN; PERMISSION_SET = GÜVENLİ İLE "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" İLE ASSEMBLY CLRStoredProcedures OLUŞTURUN

CREATE ASSEMBLY ifadesi, yönetilen kodu girdi olarak alır ve üzerinde CLR saklı yordamları, kullanıcı tanımlı işlevler ve tetikleyiciler oluşturabileceğiniz karşılık gelen bir nesne oluşturur. Bu talimat aşağıdaki sözdizimine sahiptir:

CREATE ASSEMBLY derleme_adı [ YETKİLENDİRME sahibi_adı ] FROM (dll_file) Sözdizimi kuralları

Assembly_name parametresi derlemenin adını belirtir. İsteğe bağlı YETKİLENDİRME yan tümcesi, bu derlemenin sahibi olarak rol adını belirtir. FROM yan tümcesi, derlemenin yükleneceği yolu belirtir.

PERMISSION_SET yan tümcesi İLE CREATE ASSEMBLY ifadesinin çok önemli bir cümlesidir ve her zaman belirtilmesi gerekir. Montaj koduna verilen izinler kümesini tanımlar. SAFE izin seti en kısıtlayıcı olanıdır. Bu haklara sahip olan derleme kodu, dosyalar gibi harici sistem kaynaklarına erişemez. EXTERNAL_ACCESS hakları seti, derleme kodunun belirli harici sistem kaynaklarına erişmesine izin verirken UNSAFE hakları seti, veritabanı sisteminin hem içindeki hem de dışındaki kaynaklara sınırsız erişime izin verir.

Montaj kodu bilgilerini kaydetmek için kullanıcının CREATE ASSEMBLY deyimini yayınlayabilmesi gerekir. Derlemenin sahibi, talimatı yürüten kullanıcıdır (veya roldür). CREATE SCHEMA ifadesinin AUTHORIZATION yan tümcesini kullanarak başka bir kullanıcıyı derlemenin sahibi yapabilirsiniz.

Veritabanı Motoru ayrıca ALTER ASSEMBLY ve DROP ASSEMBLY ifadelerini de destekler. ALTER ASSEMBLY bildirimi Montajı en son sürüme güncellemek için kullanılır. Bu talimat ayrıca ilgili montajla ilişkili dosyaları ekler veya kaldırır. DROP MONTAJ talimatı Belirtilen derlemeyi ve onunla ilişkili tüm dosyaları geçerli veritabanından kaldırır.

Aşağıdaki örnek, daha önce uyguladığınız yönetilen koda dayalı olarak saklı yordamın nasıl oluşturulacağını gösterir:

SampleDb'yi KULLANIN; GO PROSEDÜR OLUŞTURUN CountEmployees'u HARİCİ AD OLARAK CLRStoredProcedures.StoredProcedures.CountEmployees

Örnekteki CREATE PROCEDURE komutu önceki örneklerdeki aynı komuttan farklıdır çünkü aşağıdakileri içerir: HARİCİ ADI parametresi. Bu seçenek, kodun ortak dil çalışma zamanı tarafından oluşturulduğunu belirtir. Bu cümledeki isim üç bölümden oluşmaktadır:

montaj_adı.sınıf_adı.method_name

derleme_adı - derlemenin adını gösterir;

sınıf_adı - genel sınıfın adını gösterir;

yöntem_adı - isteğe bağlı kısım, sınıf içinde tanımlanan yöntemin adını belirtir.

CountEmployees prosedürünün yürütülmesi aşağıdaki örnekte gösterilmektedir:

SampleDb'yi KULLANIN; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Return 7

PRINT ifadesi, Çalışan tablosundaki geçerli satır sayısını döndürür.

Kullanıcılar SQL Server ile çalışırken belirli eylemleri uygulayan kendi prosedürlerini oluşturabilirler. Saklı prosedürler tam teşekküllü veritabanı nesneleridir ve bu nedenle her biri belirli bir veritabanında saklanır. Saklı bir prosedüre doğrudan çağrı, yalnızca prosedürün bulunduğu veritabanı bağlamında yapılması durumunda mümkündür.

Saklı Prosedür Türleri

SQL Server'da çeşitli türlerde saklı yordamlar bulunur.

Sistem saklı yordamları çeşitli yönetimsel eylemleri gerçekleştirmek için tasarlanmıştır. Neredeyse tüm sunucu yönetimi faaliyetleri onların yardımıyla gerçekleştirilir. Sistem saklı prosedürlerinin, sistem tablolarıyla çalışmayı sağlayan, sonuçta hem kullanıcı hem de sistem veritabanlarının sistem tablolarından veriyi değiştirmeye, eklemeye, silmeye ve almaya gelen bir arayüz olduğunu söyleyebiliriz. Sistem saklı yordamlarına sp_ ön eki eklenir, sistem veritabanında saklanır ve başka herhangi bir veritabanı bağlamında çağrılabilir.

Özel saklı prosedürler belirli eylemleri uygular. Saklı prosedürler tam teşekküllü bir veritabanı nesnesidir. Sonuç olarak, her saklı yordam, yürütüldüğü belirli bir veritabanında bulunur.

Geçici saklı prosedürler yalnızca kısa bir süre için mevcuttur ve sonrasında sunucu tarafından otomatik olarak yok edilirler. Yerel ve küresel olarak ikiye ayrılırlar. Yerel geçici saklı yordamlar yalnızca oluşturuldukları bağlantıdan çağrılabilir. Böyle bir prosedür oluştururken ona tek # karakterle başlayan bir isim vermelisiniz. Tüm geçici nesneler gibi, bu türdeki saklı prosedürler de kullanıcı bağlantısını kestiğinde veya sunucu yeniden başlatıldığında veya durdurulduğunda otomatik olarak silinir. Genel geçici saklı prosedürler, aynı prosedüre sahip bir sunucudan gelen herhangi bir bağlantı için kullanılabilir. Tanımlamak için ona ## karakterleriyle başlayan bir ad vermeniz yeterlidir. Bu prosedürler, sunucu yeniden başlatıldığında veya durdurulduğunda ya da oluşturuldukları bağlamdaki bağlantı kapatıldığında silinir.

Tetikleyiciler

Tetikleyiciler bir çeşit saklı prosedürdür. Tabloda bir veri işleme dili (DML) operatörü yürütüldüğünde yürütülürler. Tetikleyiciler veri bütünlüğünü kontrol etmek ve ayrıca işlemleri geri almak için kullanılır.

Tetiklemek yürütülmesi ilişkisel veritabanında belirli olayların meydana gelmesiyle koşullandırılan derlenmiş bir SQL prosedürüdür. Tetikleyicilerin kullanımı çoğunlukla veritabanı kullanıcıları için çok uygundur. Yine de bunların kullanımı genellikle G/Ç işlemleri için ek kaynak maliyetleri gerektirir. Saklı prosedürler veya uygulama programları kullanılarak aynı sonuçlara (çok daha az ek yük ile) ulaşılabildiğinde, tetikleyicilerin kullanımı pratik değildir.

Tetikleyiciler bir veritabanındaki veri bütünlüğünü korumak için kullanılan özel bir SQL sunucu aracıdır. Bütünlük kısıtlamaları, kuralları ve varsayılanları her zaman istenen işlevsellik düzeyine ulaşamayabilir. Güvenilirliklerini ve gerçekliklerini sağlamak için genellikle karmaşık veri doğrulama algoritmalarının uygulanması gerekir. Ek olarak bazen ilgili verilerin gerektiği gibi değiştirilebilmesi için tablo değerlerindeki değişiklikleri izlemeniz gerekir. Tetikleyiciler, kurallara, standart değerlere vb. uygun olarak tüm işlemler tamamlandıktan sonra devreye giren bir çeşit filtre olarak düşünülebilir.

Tetiklemek tetikleyicilerin ilişkili olduğu tablolardaki verileri değiştirme girişiminde bulunulduğunda sunucu tarafından otomatik olarak başlatılan özel bir saklı yordam türüdür. Her Tetiklemek belirli bir tabloya bağlıdır. Yaptığı tüm veri değişiklikleri tek bir işlem olarak kabul edilir. Bir hata veya veri bütünlüğü ihlali tespit edilirse işlem geri alınır. Bu nedenle değişiklik yapılması yasaktır. Tetikleyici tarafından halihazırda yapılmış olan tüm değişiklikler de geri alınır.

Oluşturur tetiklemek yalnızca veritabanı sahibi. Bu kısıtlama, tabloların yapısında, diğer nesneleri bunlara bağlama yollarında vb. yanlışlıkla değişiklik yapılmasını önlemenizi sağlar.

Tetiklemek Bu çok yararlı ve aynı zamanda tehlikeli bir çaredir. Dolayısıyla, eğer çalışma mantığı yanlışsa, tüm veritabanını kolayca yok edebilirsiniz, bu nedenle tetikleyicilerin hatalarının çok dikkatli bir şekilde ayıklanması gerekir.

Normal bir altprogramın aksine, tetiklemek Bir tetikleyici olay meydana geldiğinde örtülü olarak yürütülür ve hiçbir argümanı yoktur. Etkinleştirmeye bazen tetikleyiciyi ateşlemek denir. Tetikleyiciler kullanılarak aşağıdaki hedeflere ulaşılır:

Girilen verilerin doğruluğunun doğrulanması ve bir tablo üzerinde belirlenen bütünlük kısıtlamalarını kullanarak sürdürülmesi imkansız olmasa da zor olan karmaşık veri bütünlüğü kısıtlamalarının uygulanması;

belirli bir şekilde uygulanan bir tabloyu güncellerken size belirli eylemleri gerçekleştirmenizi hatırlatan uyarılar vermek;

yapılan değişiklikler ve bunları gerçekleştiren kişiler hakkındaki bilgilerin kaydedilmesi yoluyla denetim bilgilerinin toplanması;

çoğaltma desteği.

CREATE TRIGGER komutunun temel formatı aşağıda gösterilmiştir:

<Определение_триггера>::=

TRIGGER CREATE tetikleyici_adı

ÖNCE | SONRASINDA<триггерное_событие>

AÇIK<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Tetikleyici olaylar, bir tabloya satır ekleme, silme ve güncelleme işlemlerinden oluşur. İkinci durumda, tetikleyici olay için belirli tablo sütunu adlarını belirtebilirsiniz. Tetikleyicinin zamanlaması BEFORE anahtar kelimeleri ( Tetiklemek kendisiyle ilişkili olaylar yürütülmeden önce çalışır) veya SONRA (yürütüldükten sonra) çalışır.

Tetikleyici tarafından gerçekleştirilen eylemler, olayın kapsadığı her satır için (HER SATIR İÇİN) veya her olay için yalnızca bir kez (HER AÇIKLAMA İÇİN) belirtilir.

Yanlış yazılan tetikleyiciler, kilitlenme gibi ciddi sorunlara yol açabilir. Tetikleyiciler birçok kaynağı uzun süre engelleyebilir; bu nedenle erişim çakışmalarını en aza indirmeye özellikle dikkat edilmelidir.

Tetiklemek yalnızca geçerli veritabanında oluşturulabilir, ancak uzak sunucuda bulunanlar da dahil olmak üzere tetikleyici içindeki diğer veritabanlarına erişmek mümkündür.

saklı yordam ancak prosedürün bulunduğu veri tabanı bağlamında gerçekleştirilmesi durumunda mümkündür.

Saklı Prosedür Türleri

SQL Server'ın çeşitli türleri vardır saklı prosedürler.

Sistem saklı prosedürlerÇeşitli idari eylemleri gerçekleştirmek için tasarlanmıştır. Neredeyse tüm sunucu yönetimi faaliyetleri onların yardımıyla gerçekleştirilir. Sistemsel diyebiliriz saklı prosedürler sonuçta hem kullanıcı hem de sistem veritabanlarının sistem tablolarındaki verileri değiştirmek, eklemek, silmek ve almaktan ibaret olan sistem tablolarıyla çalışmayı sağlayan bir arayüzdür. Sistem saklı prosedürler sp_ önekine sahiptir, sistem veritabanında saklanır ve başka herhangi bir veritabanı bağlamında çağrılabilir.
Gelenek saklı prosedürler belirli eylemleri uygulayın. Saklı prosedürler– tam teşekküllü bir veritabanı nesnesi. Sonuç olarak her biri saklı yordam yürütüldüğü belirli bir veritabanında bulunur.
Geçici saklı prosedürler yalnızca bir süreliğine var olurlar ve sonrasında 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ştururken ona tek # karakterle başlayan bir isim vermelisiniz. Tüm geçici nesneler gibi, saklı prosedürler Bu türdeki dosyalar, kullanıcının bağlantısını kestiğinde veya sunucu yeniden başlatıldığında veya durdurulduğunda otomatik olarak silinir. Küresel geçici saklı prosedürler aynı prosedüre sahip bir sunucudan yapılan tüm bağlantılar için kullanılabilir. Tanımlamak için ona ## karakterleriyle başlayan bir ad vermeniz yeterlidir. Bu prosedürler, sunucu yeniden başlatıldığında veya durdurulduğunda ya da oluşturuldukları bağlamdaki bağlantı kapatıldığında silinir.

Saklı yordamları oluşturma, değiştirme ve silme

Yaratılış saklı yordam aşağıdaki sorunların çözümünü içerir:

oluşturulan türün belirlenmesi saklı yordam: geçici veya özel. Ayrıca kendi sisteminizi de oluşturabilirsiniz. saklı yordam, sp_ ön ekiyle bir ad vererek ve onu sistem veritabanına yerleştirerek. Bu prosedür herhangi bir yerel sunucu veri tabanı bağlamında mevcut olacaktır;
erişim haklarını planlamak. Oluştururken saklı yordam veritabanı nesnelerine, onu oluşturan kullanıcıyla aynı erişim haklarına sahip olacağı dikkate alınmalıdır;
tanım saklı yordam parametreleri. Çoğu programlama dilindeki prosedürlere benzer şekilde, saklı prosedürler giriş ve çıkış parametrelerine sahip olabilir;
kod geliştirme saklı yordam. Prosedür kodu, diğer SQL komutlarına yapılan çağrılar da dahil olmak üzere herhangi bir SQL komutunun bir dizisini içerebilir. saklı prosedürler.

Yeni bir tane oluşturmak ve mevcut olanı değiştirmek saklı yordam aşağıdaki komut kullanılarak yapılır:

<определение_процедуры>::= (CREATE | ALTER ) prosedür_adı [;sayı] [(@parametre_adı veri_türü ) [=varsayılan] ][,...n] AS sql_operator [...n]

Bu komutun parametrelerine bakalım.

Sp_, #, ## öneklerini kullanarak oluşturulan prosedür sistem veya geçici prosedür olarak tanımlanabilir. Komut sözdiziminden 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ın belirtilmesine izin verilmiyor. Böylece yaratılanı yerleştirmek için saklı yordam Belirli bir veritabanında, CREATE PROCEDURE komutunu o veritabanı bağlamında vermelisiniz. Bedenden dönerken saklı yordam kısaltılmış adlar aynı veritabanındaki nesneler için kullanılabilir, yani veritabanı adı belirtilmeden. Diğer veritabanlarında bulunan nesnelere erişmeniz gerektiğinde, veritabanı adının belirtilmesi zorunludur.

İsimdeki numara bir kimlik numarasıdır saklı yordam, onu bir prosedür grubu içinde benzersiz bir şekilde tanımlayan. Yönetim kolaylığı için prosedürler mantıksal olarak aynı tiptedir saklı prosedürler aynı isim fakat farklı kimlik numaraları verilerek gruplandırılabilir.

Oluşturulan giriş ve çıkış verilerini aktarmak için saklı yordam yerel değişkenlerin adları gibi adlarının @ simgesiyle başlaması gereken parametreler kullanılabilir. Bir saklı yordam Birden fazla parametreyi virgülle ayırarak belirtebilirsiniz. Bir yordamın gövdesi, adları bu yordamın parametrelerinin adlarıyla örtüşen yerel değişkenleri kullanmamalıdır.

Karşılık gelen veri tipini belirlemek için saklı yordam parametresi, kullanıcı tanımlı olanlar da dahil olmak üzere tüm SQL veri türleri uygundur. 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ün belirtilmesi sunucuya çıkış talimatını verir saklı yordam parametrenin geçerli değerini, yordamı parametre değeri olarak çağırırken belirtilen yerel değişkene atayın. OUTPUT anahtar sözcüğünü belirtirken, 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 izin verilen ifadelere veya sabitlere izin verilmez.

VARYING anahtar sözcüğü şununla birlikte kullanılır:

Saklı yordam Saklı yordam) adlandırılmış bir veritabanı programı nesnesidir. SQL Server'da çeşitli türlerde saklı yordamlar bulunur.

Sistemde saklı prosedürler sistem saklı yordamları) DBMS geliştiricileri tarafından sağlanır ve sistem dizini ile eylemler gerçekleştirmek veya sistem bilgilerini elde etmek için kullanılır. İsimleri genellikle "sp_" önekiyle başlar. EXEC olarak kısaltılabilen EXECUTE komutunu kullanarak tüm saklı yordam türlerini çalıştırırsınız. Örneğin, parametreler olmadan çalıştırılan sp_helplogins saklı yordamı, hesap adları hakkında iki rapor üretir. (İngilizce) oturum açma bilgileri) ve her veritabanındaki ilgili kullanıcılar (İngilizce) kullanıcılar).

EXEC sp_helplogins;

Sistemde saklı prosedürler kullanılarak gerçekleştirilen eylemler hakkında fikir vermek için Tablo 10.6 bazı örnekleri göstermektedir. Toplamda SQL Server'da binden fazla 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 veri tabanında saklı prosedürler oluşturabilir. İkinci durumda, saklı yordam şu şekilde olacaktır: geçici. Geçici tablolarda olduğu gibi, geçici bir saklı yordamın adı, yerel bir geçici saklı yordam ise "#" önekiyle veya genel bir saklı yordam ise "##" önekiyle başlamalıdır. Yerel bir geçici prosedür yalnızca oluşturulduğu bağlantı içinde kullanılabilir; global bir prosedür, diğer bağlantılarda da kullanılabilir.

SQL Server programlanabilir nesneleri, Transact-SQL araçları veya derlemeleri kullanılarak oluşturulabilir (İngilizce) Assembly) Microsoft.Net Framework'ün CRL (Ortak Dil Çalışma Zamanı) ortamında. Bu eğitim yalnızca ilk yöntemi kapsayacaktır.

Saklı prosedürler oluşturmak için formatı aşağıda verilen CREATE PROCEDURE (PROC olarak kısaltılabilir) ifadesini kullanın:

CREATE (PROC I PROSEDÜR) işlem_adı [ ; sayı ]

[(gparametre veri_türü)

[“varsayılan] |

[İLE [ ,...N ] ]

[KOPYALAMA İÇİN]

AS ([ BAŞLANGIÇ ] sql_statement [;] [ ...n ] [ SON ] )

ŞİFRELEME seçeneği ile bir saklı yordam (veya tetikleyici, işlev, görünüm) oluşturulursa kodu, metin okunmaz hale gelecek şekilde dönüştürülür. Aynı zamanda, belirtildiği gibi, kullanılan algoritma SQL Server'ın önceki sürümlerinden aktarılmıştır ve güvenilir bir koruma algoritması olarak kabul edilemez - ters dönüşümü hızlı bir şekilde gerçekleştirmenize olanak tanıyan yardımcı programlar vardır.

RECOMPILE seçeneği, prosedür her çağrıldığında sistemin metni yeniden derleyeceğini belirtir. Normal durumda, ilk çalıştırmada derlenen prosedür önbellekte saklanır ve bu da performansın artmasına olanak tanır.

EXECUTE AS, prosedürün yürütüleceği güvenlik içeriğini belirtir. Daha sonra f CALLER | değerlerinden biri | KENDİ | 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 programlanabilir nesnenin kendisine değil, aynı zamanda ondan etkilenen diğer veritabanı nesnelerine de izin vermesi gerekir. KENDİ KENDİSİ OLARAK ÇALIŞTIR, programlanabilir nesneyi oluşturan veya değiştiren kullanıcının bağlamını kullanmak anlamına gelir. SAHİP, kodun prosedürün geçerli sahibi bağlamında yürütüleceğini belirtir. Eğer sahibi belirtilmemişse ait olduğu şemanın sahibi olduğu varsayılır. EXECUTE AS "kullanıcı_adı", kullanıcı adını açıkça belirtmenize olanak tanır (tek tırnak içinde).

Bir prosedür için parametreler belirtilebilir. Bunlar bir prosedüre değer aktarmak için kullanılan yerel değişkenlerdir. Bir parametre OUTPUT (veya kısaca OUT) anahtar sözcüğüyle bildirilirse, bu bir çıktı değeridir: prosedür tamamlandıktan sonra ona verilen değer, prosedürü çağıran program tarafından kullanılabilir. READONLY anahtar sözcüğü, parametrenin değerinin saklı yordam içinde değiştirilemeyeceği anlamına gelir.

Parametrelere, prosedür çağrılırken parametre değeri açıkça belirtilmemişse kullanılacak varsayılan değerler atanabilir. Bir örneğe bakalım:

CREATE PROC surma (@a int, @b int=0,

©sonuç int ÇIKIŞ) AS

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: değeri çağıran programa döndürür. Gerçekleştirilen eylemler oldukça basittir - çıkış parametresi, iki giriş parametresinin toplamının değerini alır.

SQL Server Management Studio'da çalışırken oluşturulan saklı prosedür, programlanabilir veritabanı nesneleri bölümünde bulunabilir. (İngilizce) Programlanabilirlik) saklı prosedürler alt bölümünde (Şekil 10.2).

Bir prosedürü çağırırken hem değişkenleri hem de sabitleri giriş parametresi olarak kullanabilirsiniz. İki örneğe bakalım. İlkinde, prosedürün giriş parametreleri açıkça sabitler olarak belirtilir ve çağrıdaki çıkış parametresi için OUTPUT anahtar sözcüğü belirtilir. İkinci seçenek, ilk giriş parametresi olarak bir değişkenin değerini kullanır ve DEFAULT anahtar sözcüğü kullanılarak ikinci parametre için varsayılan değerin kullanılması gerektiğini belirtir:

Pirinç. 10.2.

DECLARE @с int;

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, 0c ÇIKIŞ;

YAZDIR 0c; – 5 görüntülenecek

Şimdi prosedürün sona erdiği dönüş kodunun analizini içeren bir örneği ele alalım. Belirli bir yıl aralığında Bookl tablosunda kaç kitabın yayınlandığını hesaplamamız gerektiğini varsayalım. Ayrıca, ilk yıl son yıldan büyükse prosedür “1” değerini döndürür ve sayılmaz, aksi takdirde kitap sayısını sayar ve 0 değerini döndürürüz:

PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS OLUŞTURUN

IF 0İlkYıl>0GeçenYıl DÖNÜŞ 1

SET @result= (dbo.Bookl'DAN COUNT(*) SEÇİN

0İlkYıl İLE 0GeçenYıl ARASINDA);

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 çeşidini ele alalım. PRINT deyiminde kullanılan CAST işlevi, Gres tamsayı değişkeninin değerini bir dize türüne dönüştürmek için kullanılır:

DECLARE 0ret int, Gres int

EXEC Gret = satır sayısı 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Başlangıç yılı bitiş yılından büyük"

PRINT "Kitap sayısı" + CAST(Gres as varchar(20))

Saklı prosedürler 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 örnekte hem bu yetenekler hem de geçici nesnelerin kapsamıyla ilgili sorunlar gösterilmektedir. Aşağıdaki saklı prosedür, #TaL2 geçici tablosunun varlığını kontrol eder; eğer bu tablo yoksa onu yaratır. Bundan sonra #TaL2 tablosuna iki sütunun değerleri girilir ve SELECT ifadesi kullanılarak tablonun içeriği görüntülenir:

PROC CREATE My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) NULL IS

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

dbo'DAN * SEÇİN. #Sekme2 –№1

Saklı yordamı ilk kez çağırmadan önce, içinde kullanılan #TaL2 geçici tablosunu oluşturacağız. EXEC operatörüne dikkat edin. Önceki örneklerde parametreler "konuma göre" prosedürüne aktarılmıştır, ancak bu durumda parametrelerin aktarılması için farklı bir format kullanılır - "ada göre", parametrenin adı ve değeri açıkça belirtilir:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name = "lvan", 0id = 2;

dbo'dan * SEÇİN.#Tab2; –№2

Yukarıdaki örnekte, SELECT ifadesi iki kez işlenecektir: ilkinde – prosedürün içinde, ikincisinde – çağıran kod parçasından (“No. 2” açıklamasıyla işaretlenmiştir).

Prosedürün ikinci çağrısından önce #TaL2 geçici tablosunu sileceğiz. Daha sonra saklı yordamdan aynı adda geçici bir 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ün içinde yer alan SELECT ifadesi (“Xa 1” açıklamasıyla birlikte) verileri görüntüleyecektir. SELECT "No. 2" komutunun çalıştırılması bir hatayla sonuçlanacaktır, çünkü saklı yordamda oluşturulan geçici tablo, yordamın geri döndüğü sırada tempdb veritabanından zaten silinecektir.

DROP PROCEDURE deyimini kullanarak saklı yordamı bırakabilirsiniz. Formatı aşağıda sunulmuştur. Birden fazla saklı yordamı tek bir ifadeyle silebilirsiniz ve bunları virgülle ayırarak listeleyebilirsiniz:

BIRAKMA (PROC I PROSEDÜRÜ) ( prosedür ) [

Örneğin daha önce oluşturduğumuz toplama prosedürünü silelim:

DROP PROC özeti;

ALTER PROCEDURE deyimini (izin verilir) kullanarak mevcut bir prosedürde değişiklikler yapabilirsiniz (ve aslında onu yeniden tanımlayabilirsiniz).

kısaltma PROC). ALTER anahtar sözcüğü dışında, ifadenin formatı aslında CREATE PROCEDURE ile aynıdır. Örneğin dbo prosedürünü değiştirelim. rownum, sahibinin güvenlik bağlamında yürütülecek şekilde ayarlanıyor:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

Sahip Olarak Yürütme İLE – kurulabilir seçenek

IF 0FirsYear>0LastYear RETURN 1 ELSE BAŞLA

SET 0result= (dbo.Bookl'DAN COUNT(*) SEÇİN

İlk Yıl ile Geçen Yıl Arasında);

Bazı durumlarda dinamik olarak bir komut oluşturmak ve onu veritabanı sunucusunda yürütmek gerekebilir. Bu sorun EXEC operatörü kullanılarak da çözülebilir. Aşağıdaki örnek, Year özelliğinin değişken tarafından belirtilen değere eşit olması durumunda Bookl tablosundan kayıtları alır:

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

Dinamik olarak oluşturulan talimatların yürütülmesi, "SQL enjeksiyonu" gibi bilgisayar saldırılarının uygulanması için ön koşulları oluşturur. (İngilizce) SQL enjeksiyonu). Saldırının özü, saldırganın kendi SQL kodunu dinamik olarak oluşturulan bir sorguya enjekte etmesidir. Bu genellikle, değiştirilen parametreler kullanıcı girişinin sonuçlarından alındığında meydana gelir.

Önceki örneği biraz değiştirelim:

DECLARE 0у varchar(100);

SET 0у = "2ООО"; – bunu kullanıcıdan aldık

SET ifadesinde atanan string değerini kullanıcıdan aldığımızı varsayarsak (nasıl olursa olsun, örneğin bir web uygulaması aracılığıyla), o zaman örnek, kodumuzun “normal” davranışını gösterir.

DECLARE 0у varchar(100);

SET 0у="2000; dbo.Book2'DEN SİL"; – enjeksiyon

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

Bu gibi durumlarda, SQL enjeksiyonlarının önündeki engellerden biri olan parametre türlerini kontrol etmenizi sağlayan sp_executcsql sistem saklı yordamının mümkünse kullanılması önerilir. Formatını ayrıntılı olarak düşünmeden, daha önce sunulana benzer bir örneğe bakalım:

Sp_executesql'i ÇALIŞTIRIN

N"SELECT * FROM dbo.Bookl WHERE =0y",

Bu, sorguda kullanılan parametrenin türünü açıkça belirtir ve yürütme sırasında SQL Server bunu kontrol eder. Tırnak işaretlerinin önündeki "N" harfi, prosedürün gerektirdiği şekilde bunun bir Unicode sabit değeri 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ı prosedür kavramı tanımlanmıştır. Saklı yordamları parametrelerle oluşturma, değiştirme ve kullanma örnekleri sağlar. Giriş ve çıkış parametrelerinin tanımı verilmiştir. Saklı yordamları oluşturma ve çağırma örnekleri verilmiştir.

Saklı prosedür kavramı

Saklı prosedürler Kullanımı programcının işini daha kolay ve daha esnek hale getiren birbirine bağlı SQL ifadeleri gruplarıdır, çünkü saklı yordam genellikle bireysel SQL ifadeleri dizisinden çok daha basittir. Saklı prosedürler, bir veya daha fazla SQL ifadesinden veya işlevinden oluşan ve bir veritabanında derlenmiş biçimde saklanan bir dizi komuttur. Veritabanında yürütme saklı prosedürler Bireysel SQL ifadeleri yerine kullanıcı aşağıdaki avantajlara sahiptir:

gerekli operatörler zaten veritabanında bulunmaktadır;
hepsi sahneyi geçti ayrıştırma ve çalıştırılabilir formattadır; önce saklı yordamın yürütülmesi SQL Server bunun için bir yürütme planı oluşturur, optimizasyonunu ve derlemesini gerçekleştirir;
saklı prosedürler Destek modüler programlama büyük görevleri bağımsız, daha küçük ve yönetimi daha kolay parçalara ayırmanıza izin verdikleri için;
saklı prosedürler başkalarına neden olabilir saklı prosedürler ve fonksiyonları;
saklı prosedürler diğer uygulama programlarından çağrılabilir;
genellikle, saklı prosedürler bir dizi bireysel ifadeden daha hızlı yürütülür;
saklı prosedürler kullanımı daha kolay: onlarca veya yüzlerce komuttan oluşabilirler, ancak bunları çalıştırmak için sadece istediğiniz komutun adını belirtmeniz yeterlidir. saklı yordam. Bu, istemciden sunucuya gönderilen isteğin boyutunu ve dolayısıyla ağdaki yükü azaltmanıza olanak tanır.

Prosedürlerin yürütüldüğü yerde saklanması, ağ üzerinden aktarılan veri miktarını azaltır ve genel sistem performansını artırır. Başvuru saklı prosedürler yazılım sistemlerinin bakımını ve değişiklik yapılmasını kolaylaştırır. Tipik olarak, kurallar ve veri işleme algoritmaları biçimindeki tüm bütünlük kısıtlamaları veritabanı sunucusunda uygulanır ve bir küme olarak son uygulamaya sunulur. saklı prosedürler, veri işleme arayüzünü temsil eder. Veri bütünlüğünü sağlamak ve güvenlik amacıyla, uygulama genellikle verilere doğrudan erişim sağlamaz - onunla yapılan tüm çalışmalar belirli çağrılar yapılarak gerçekleştirilir. saklı prosedürler.

Bu yaklaşım, tüm ağ kullanıcılarının hemen kullanımına sunulan veri işleme algoritmalarının değiştirilmesini çok basit hale getirir ve uygulamanın kendisinde değişiklik yapmadan sistemi genişletme yeteneği sağlar: sadece değiştirin saklı yordam veritabanı sunucusunda. Geliştiricinin uygulamayı yeniden derlemesine, kopyalarını oluşturmasına veya kullanıcılara yeni sürümle çalışmaları talimatını vermesine gerek yoktur. Kullanıcılar sistemde değişiklik yapıldığının farkında bile olmayabilir.

Saklı prosedürler tablolardan veya diğer veritabanı nesnelerinden bağımsız olarak var olur. İstemci programı tarafından çağrılır, başka bir saklı yordam veya tetikleyin. Geliştirici erişim haklarını yönetebilir saklı yordam yürütülmesine izin vermek veya yasaklamak. Kodu değiştir saklı yordam yalnızca sahibi veya sabit bir veritabanı rolünün bir üyesi tarafından izin verilir. Gerekirse mülkiyetini bir kullanıcıdan diğerine aktarabilirsiniz.

MS SQL Server ortamında saklı prosedürler

Kullanıcılar SQL Server ile çalışırken belirli eylemleri uygulayan kendi prosedürlerini oluşturabilirler. Saklı prosedürler tam teşekküllü veritabanı nesneleridir ve bu nedenle her biri belirli bir veritabanında saklanır. Direkt Arama saklı yordam ancak prosedürün bulunduğu veri tabanı bağlamında gerçekleştirilmesi durumunda mümkündür.

Saklı Prosedür Türleri

SQL Server'ın çeşitli türleri vardır saklı prosedürler.

Sistem saklı prosedürlerÇeşitli idari eylemleri gerçekleştirmek için tasarlanmıştır. Neredeyse tüm sunucu yönetimi faaliyetleri onların yardımıyla gerçekleştirilir. Sistemsel diyebiliriz saklı prosedürler sonuçta hem kullanıcı hem de sistem veritabanlarının sistem tablolarındaki verileri değiştirmek, eklemek, silmek ve almaktan ibaret olan sistem tablolarıyla çalışmayı sağlayan bir arayüzdür. Sistem saklı prosedürler sp_ önekine sahiptir, sistem veritabanında saklanır ve başka herhangi bir veritabanı bağlamında çağrılabilir.
Gelenek saklı prosedürler belirli eylemleri uygulayın. Saklı prosedürler– tam teşekküllü bir veritabanı nesnesi. Sonuç olarak her biri saklı yordam yürütüldüğü belirli bir veritabanında bulunur.
Geçici saklı prosedürler yalnızca bir süreliğine var olurlar ve sonrasında 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ştururken ona tek # karakterle başlayan bir isim vermelisiniz. Tüm geçici nesneler gibi, saklı prosedürler Bu türdeki dosyalar, kullanıcının bağlantısını kestiğinde veya sunucu yeniden başlatıldığında veya durdurulduğunda otomatik olarak silinir. Küresel geçici saklı prosedürler aynı prosedüre sahip bir sunucudan yapılan tüm bağlantılar için kullanılabilir. Tanımlamak için ona ## karakterleriyle başlayan bir ad vermeniz yeterlidir. Bu prosedürler, sunucu yeniden başlatıldığında veya durdurulduğunda ya da oluşturuldukları bağlamdaki bağlantı kapatıldığında silinir.

Saklı yordamları oluşturma, değiştirme ve silme

Yaratılış saklı yordam aşağıdaki sorunların çözümünü içerir:

oluşturulan türün belirlenmesi saklı yordam: geçici veya özel. Ayrıca kendi sisteminizi de oluşturabilirsiniz. saklı yordam, sp_ ön ekiyle bir ad vererek ve onu sistem veritabanına yerleştirerek. Bu prosedür herhangi bir yerel sunucu veri tabanı bağlamında mevcut olacaktır;
erişim haklarını planlamak. Oluştururken saklı yordam veritabanı nesnelerine, onu oluşturan kullanıcıyla aynı erişim haklarına sahip olacağı dikkate alınmalıdır;
tanım saklı yordam parametreleri. Çoğu programlama dilindeki prosedürlere benzer şekilde, saklı prosedürler giriş ve çıkış parametrelerine sahip olabilir;
kod geliştirme saklı yordam. Prosedür kodu, diğer SQL komutlarına yapılan çağrılar da dahil olmak üzere herhangi bir SQL komutunun bir dizisini içerebilir. saklı prosedürler.

Yeni bir tane oluşturmak ve mevcut olanı değiştirmek saklı yordam aşağıdaki komut kullanılarak yapılır:

<определение_процедуры>::= (CREATE | ALTER ) PROC prosedür_adı [;sayı] [(@parametre_adı veri_türü ) [=varsayılan] ][,...n] AS sql_operator [...n]

Bu komutun parametrelerine bakalım.

VARYING anahtar sözcüğü, CURSOR türündeki OUTPUT parametresiyle birlikte kullanılır. Bunu belirler çıkış parametresi bir sonuç kümesi olacaktır.

DEFAULT anahtar sözcüğü karşılık gelen değeri temsil eder. varsayılan parametre. Bu nedenle, bir prosedürü çağırırken ilgili parametrenin değerini açıkça belirtmeniz gerekmez.

Sunucu, sorgu yürütme planını ve derlenmiş kodu önbelleğe aldığından, prosedür bir sonraki çağrıldığında hazır değerler kullanılacaktır. Ancak bazı durumlarda prosedür kodunun yeniden derlenmesi hala gerekli olabilir. RECOMPILE anahtar sözcüğünün belirtilmesi sisteme bir yürütme planı oluşturma talimatını verir saklı yordam her aradığında.

FOR REPLICATION parametresi, verileri çoğaltırken ve oluşturulan verileri etkinleştirirken gereklidir. saklı yordam yayınlanacak bir makale olarak.

ENCRYPTION anahtar sözcüğü sunucuya kodu şifrelemesi talimatını verir saklı yordam işi uygulayan özel algoritmaların kullanımına karşı koruma sağlayabilen saklı yordam.

AS anahtar sözcüğü gövdenin başına yerleştirilir saklı yordam yani bunun veya bu eylemin uygulanacağı bir dizi SQL komutu. Prosedürün özünde neredeyse tüm SQL komutları kullanılabilir, işlemler bildirilebilir, kilitler ayarlanabilir ve diğerleri çağrılabilir. saklı prosedürler. Çıkış saklı yordam RETURN komutu kullanılarak yapılabilir.

Saklı Prosedürü Kaldırma komutla gerçekleştirilir:

BIRAKMA PROSEDÜRÜ (prosedür_adı) [,...n]

Saklı Prosedürün Yürütülmesi

İçin saklı yordamı yürütmek Kullanılan komut şudur:

[[ EXEC [ UTE] prosedür_adı [;sayı] [[@parametre_adı=](değer | @değişken_adı) |][,...n]

Eğer çağrı saklı yordam paketteki tek komut değildir, EXECUTE komutunun varlığı gereklidir. Ayrıca bu komut, başka bir prosedürün veya tetikleyicinin gövdesinden bir prosedürü çağırmak için gereklidir.

Bir prosedür çağrılırken OUTPUT anahtar sözcüğünün kullanımına yalnızca prosedür çağrılırken bildirilen parametreler için izin verilir. prosedür oluşturma OUTPUT anahtar sözcüğüyle.

Bir prosedür çağrılırken bir parametre için DEFAULT anahtar kelimesi belirtildiğinde bu anahtar kelime kullanılacaktır. varsayılan değer. Doğal olarak, belirtilen DEFAULT sözcüğüne yalnızca tanımlandığı parametreler için izin verilir. varsayılan değer.

EXECUTE komutunun sözdizimi, bir prosedür çağrılırken parametre adlarının atlanabileceğini gösterir. Ancak bu durumda, kullanıcının parametrelerin değerlerini, listelendikleri sırayla belirtmesi gerekir. prosedür oluşturma. Parametreye ata varsayılan değer, listelerken bunu atlayamazsınız. Tanımlandığı parametreleri atlamak istiyorsanız varsayılan değerçağırırken parametre adlarını açıkça belirtmeniz yeterlidir. saklı yordam. Üstelik bu sayede parametreleri ve değerlerini istediğiniz sırayla listeleyebilirsiniz.

Bir prosedürü çağırırken, değerleri olan parametre adlarının veya yalnızca parametre adı olmayan değerlerin belirtildiğini unutmayın. Bunları birleştirmeye izin verilmez.

Örnek 12.1. Parametresiz prosedür. Ivanov tarafından satın alınan malların isimlerini ve maliyetlerini elde etmek için bir prosedür geliştirin.

PROC my_proc1'İ OLUŞTURUN Ürün.Adı, Ürün.Fiyat*İşlem.Miktar AS Maliyet, Müşteri.Soyadı Müşteriden INNER JOIN (Product INNER JOIN İşlemi Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode NEREDE Müşteri .Soyadı='Ivanov' Örnek 12.1. Ivanov tarafından satın alınan malların isimlerini ve değerlerini alma prosedürü.