Ms sql sunucu ortamında saklı yordamlar. T-SQL'de Saklı Prosedürler - Oluşturun, Değiştirin, Silin

1. Prosedürlerinize şu satırı ekleyin: SET NOCOUNT ON: Her DML deyimiyle SQL sunucusu bize dikkatlice işlenmiş kayıtların sayısını içeren bir mesaj döndürür. Bu bilgiler kodun hatalarını ayıklarken işimize yarayabilir ancak bundan sonra tamamen işe yaramaz hale gelecektir. SET NOCOUNT ON yazarak bu özelliği devre dışı bırakıyoruz. Birden fazla ifade veya döngü içeren saklı prosedürler için bu eylem, trafik miktarını önemli ölçüde azaltacağından önemli bir performans artışı sağlayabilir.

PROC OLUŞTUR dbo.ProcName
GİBİ
NOCOUNT'U AYARLAYIN;
--Prosedür kodu burada
dbo.TblTable1'DEN sütun1'İ SEÇİN
--SET NOCOUNT'u başlangıç ​​durumuna getirin
NOCOUNT'U KAPALI AYARLAYIN;
GİTMEK

2. Şema adını nesne adıyla birlikte kullanın: Bence gayet açık. Bu işlem, sunucuya nesneleri nerede arayacağını söyler ve çöp kutularını rastgele karıştırmak yerine nereye gitmesi gerektiğini ve ne alması gerektiğini hemen bilir. Çok sayıda veritabanı, tablo ve saklı prosedür ile zamandan ve sinirlerimizden önemli ölçüde tasarruf sağlayabilir.

dbo.MyTable'dan SELECT * --Bu bunu yapmanın iyi bir yoludur
-- Yerine
SELECT * FROM MyTable --Bu yapılacak kötü bir şey
--Prosedür çağrısı
EXEC dbo.MyProc -- Yine güzel
--Yerine
EXEC MyProc --Kötü!

3. Saklı prosedürlerinizin adında "sp_" önekini kullanmayın: Prosedür ismimiz "sp_" ile başlıyorsa SQL Server öncelikle kendi ana veritabanına bakacaktır. Gerçek şu ki, bu önek sunucunun kişisel dahili saklı yordamları için kullanılıyor. Bu nedenle veritabanında sizinkiyle aynı isimde bir prosedür bulunması durumunda kullanılması ek maliyetlere ve hatta hatalı sonuçlara yol açabilir.

4. IF EXISTS (SELECT *) yerine IF EXISTS (SELECT 1) seçeneğini kullanın: Bir kaydın başka bir tabloda olup olmadığını kontrol etmek için IF EXISTS deyimini kullanırız. Bu ifade, iç ifadeden en az bir değer döndürülüyorsa true değerini döndürür; "1", tüm sütunlar veya tablo fark etmez. İade edilen veriler prensip olarak hiçbir şekilde kullanılmaz. Bu nedenle veri iletimi sırasında trafiği sıkıştırmak için aşağıda gösterildiği gibi "1" kullanılması daha mantıklıdır:

EĞER VARSA (sysobjects'TEN 1 SEÇİN
NEREDE adı = "Tablom" VE türü = "U")

5. Hataları yakalamak için TRY-Catch'i kullanın: 2005 sunucusundan önce, her istekten sonra prosedürde çok sayıda hata kontrolü yazıldı. Daha fazla kod her zaman daha fazla kaynak ve daha fazla zaman tüketir. SQL Server 2005'ten bu yana bu sorunu çözmenin daha doğru ve kullanışlı bir yolu ortaya çıktı:

DENEMEYE BAŞLA
--kod
SON DENEME
YAKALAMAYA BAŞLA
-- hata yakalama kodu
YAKALAMAYI SONLANDIR

Çözüm
Temel olarak, bugünlük sahip olduğum tek şey bu. Burada yalnızca kişisel olarak uygulamamda kullandığım tekniklerin olduğunu ve bunların etkililiğine kefil olabileceğimi bir kez daha tekrar ediyorum.

Not:
İlk mesajım kesinlikle yargılamayın.

Microsoft SQL Server'da kendi algoritmalarını uygulamak ve otomatikleştirmek ( hesaplamalar) saklı yordamları kullanabilirsiniz, bu nedenle bugün bunların nasıl oluşturulduğu, değiştirildiği ve silindiği hakkında konuşacağız.

Ama önce, T-SQL'de saklı prosedürlerin ne olduğunu ve ne işe yaradığını anlamanız için küçük bir teori.

Not! Acemi programcılar için T-SQL konusuyla ilgili aşağıdaki faydalı materyalleri öneririm:

• T-SQL dili hakkında daha ayrıntılı bir çalışma için, T-SQL Programcının Yolu kitabını da okumanızı tavsiye ederim. Transact-SQL Eğitimi.

T-SQL'de saklı prosedürler nelerdir?

saklı yordamlar- Bunlar, algoritmanın bir dizi SQL talimatı biçiminde yerleştirildiği veritabanı nesneleridir. Başka bir deyişle, saklı yordamların bir veritabanı içindeki programlardır diyebiliriz. Saklı prosedürler, yeniden kullanılabilir kodu sunucuda depolamak için kullanılır; örneğin, bir algoritma, sıralı bir hesaplama veya çok adımlı bir SQL ifadesi yazdınız ve bu algoritmanın içerdiği tüm talimatları her seferinde yürütmemek için, düzenleyebilirsiniz. saklı bir prosedür olarak. Bu durumda, bir SQL yordamı oluşturduğunuzda, sunucu kodu derler ve bu yordamı her çalıştırdığınızda, SQL sunucusu artık onu yeniden derlemez.

SQL Server'da bir saklı yordamın çalıştırılabilmesi için adından önce EXECUTE komutunun yazılması gerekir, bu EXEC komutunun kısaltılmış olarak yazılması da mümkündür. Örneğin, bir işlev artık çalışmayacağından, SELECT deyiminde saklı bir yordamı çağırın; prosedürler ayrı ayrı yürütülür.

Saklı prosedürlerde, işlevlerden farklı olarak UNSERT, UPDATE, DELETE gibi veri değiştirme işlemlerini gerçekleştirmek zaten mümkündür. Ayrıca prosedürlerde hemen hemen her tür SQL ifadesini kullanabilirsiniz; örneğin tablolar oluşturmak için CREATE TABLE veya EXECUTE, yani. diğer prosedürleri çağırmak. Bunun istisnası, aşağıdakiler gibi çeşitli talimat türleridir: işlevler, görünümler, tetikleyiciler oluşturma veya değiştirme, şemalar oluşturma ve diğer birkaç benzer talimat; örneğin, saklı bir prosedürde veritabanı bağlantı bağlamını (KULLANIM) değiştirmek de imkansızdır.

Saklı bir prosedür, giriş parametrelerine ve çıkış parametrelerine sahip olabilir, tablo halindeki verileri döndürebilir, hiçbir şey döndürmeyebilir, 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; PİVOT operatörü. Bu operatörle sorgu oluşumunu basitleştirmek için ( bildiğiniz gibi PIVOT sözdizimi oldukça karmaşıktır), sizin için dinamik olarak özet raporlar oluşturacak bir prosedür yazabilirsiniz, örneğin “T-SQL'de Dinamik PIVOT” materyalinde, bu özelliğin saklı prosedür biçiminde uygulanmasının bir örneği sunulmaktadır.

Microsoft SQL Server'da saklı prosedürlerle çalışma örnekleri

Örnekler için başlangıç ​​verileri

Aşağıdaki örneklerin tümü Microsoft SQL Server 2016 Express'te yürütülecektir. Saklı prosedürlerin gerçek verilerle nasıl çalıştığını göstermek için bu verilere ihtiyacımız var, hadi oluşturalım. Örnek olarak bir test tablosu oluşturalım ve içine bazı kayıtlar ekleyelim, diyelim ki bu, ürünlerin fiyatlarıyla birlikte listesini içeren bir tablo olacak.

Tablo oluşturma bildirimi CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Veri bildirimi ekle INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Fare", 100), (1, "Klavye", 200), (2, "Telefon", 400) GO -- SELECT * FROM TestTable sorgusu

Veriler var, şimdi saklı yordamlar oluşturmaya geçelim.

T-SQL'de saklı yordam oluşturma - CREATE PROCEDURE deyimi

Saklı prosedürler deyimi kullanılarak oluşturulur PROSEDÜR OLUŞTUR Bu talimattan sonra prosedürünüzün adını yazmalı, ardından gerekirse giriş ve çıkış parametrelerini parantez içinde tanımlamalısınız. Bundan sonra AS anahtar kelimesini yazıp BEGIN anahtar kelimesiyle bir talimat bloğu açarsınız, bu bloğu END kelimesiyle kapatırsınız. Bu bloğun içine algoritmanızı uygulayan tüm talimatları veya bir tür sıralı hesaplamayı yani T-SQL'de programladığınız tüm talimatları yazarsınız.

Örneğin yeni bir kayıt ekleyecek bir saklı yordam yazalım. test grafiğimize yeni öğe. Bunu yapmak için üç giriş parametresi tanımlayacağız: @CategoryId - ürün kategorisi tanımlayıcısı, @ProductName - ürün adı ve @Price - ürün fiyatı, bu parametre bizim için isteğe bağlı olacaktır, yani. prosedüre geçilemez ( örneğin henüz fiyatını bilmiyoruz), bunun için tanımındaki varsayılan değeri ayarlayacağız. Bu parametreler prosedürün içeriğinde yer almaktadır; BEGIN…END bloğundaki sıradan değişkenlerle aynı şekilde kullanılabilir ( bildiğiniz gibi değişkenler @ işaretiyle gösterilir). Çıkış parametrelerini belirtmeniz gerekiyorsa, parametre adından sonra OUTPUT anahtar sözcüğünü ( veya kısaltılmış OUT).

BEGIN…END bloğuna veri eklemek için bir talimat yazacağız ve ayrıca prosedürün sonunda bir SELECT ifadesi yazacağız, böylece saklı prosedür, yeniyi dikkate alarak belirtilen kategorideki ürünler hakkında tablo halinde veri döndürür. ürünü yeni ekledim. Ayrıca bu saklı prosedüre, gelen parametrenin işlenmesini, yani birden fazla boşluğun kazara girildiği durumları önlemek için metin dizesinin başındaki ve sonundaki fazladan boşlukların kaldırılmasını da ekledim.

İşte bu prosedürün kodu Ben de buna yorum yapmıştım).

Prosedür oluştur PROSEDÜR OLUŞTUR TestProcedure (--Gelen parametreler @CategoryId INT, @ÜrünAdı VARCHAR(100), @Fiyat PARA = 0) BAŞLANGIÇTA --Algoritmanızı uygulayan talimatlar --Gelen parametrelerin işlenmesi --Başlangıçtaki fazladan boşlukların kaldırılması ve metin dizesinin sonunda SET @ÜrünAdı = LTRIM(RTRIM(@ÜrünAdı)); --Yeni giriş ekleyin INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Verileri döndürün SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

T-SQL'de Saklı Prosedür Çalıştırma - EXECUTE Komutu

Daha önce belirttiğim gibi, EXECUTE veya EXEC komutunu kullanarak saklı yordamı çalıştırabilirsiniz. Gelen parametreler basitçe numaralandırılarak ve prosedür adından sonra uygun değerler sağlanarak prosedürlere aktarılır ( çıkış parametreleri için ayrıca OUTPUT komutunu da belirtmeniz gerekir.). Ancak parametrelerin adı belirtilmeyebilir ancak bu durumda değerlerin belirtilme sırasını takip etmek gerekir; değerleri giriş parametrelerinin tanımlandığı sıraya göre belirtin ( bu aynı zamanda çıkış parametreleri için de geçerlidir).

Varsayılan değerleri olan parametreler belirtilmeyebilir, bunlar isteğe bağlı parametrelerdir.

Saklı yordamları, özellikle de test yordamımızı çalıştırmanın birkaç farklı ancak eşdeğer yolunu burada bulabilirsiniz.

1. Fiyatı belirtmeden prosedürü çağırın EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test ürünü 1" --2. Belirtilen fiyatla prosedürü çağırıyoruz EXEC TestProcedure @CategoryId = 1, @ProductName = "Test ürünü 2", @Price = 300 --3. EXEC parametrelerinin adını belirtmeden prosedürü çağırıyoruz TestProcedure 1, "Test item 3", 400

Saklı Prosedürü T-SQL'e Değiştirme - ALTER PROCEDURE Beyanı

Talimatları kullanarak prosedürün algoritmasında değişiklikler yapabilirsiniz. PROSEDÜRÜ DEĞİŞTİRİN. 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 @Price parametresinde değişiklik yapmamız gerekiyor. fiyat, bunu zorunlu hale getireceğiz, bunun için varsayılan değeri kaldıracağız ve ayrıca artık ortaya çıkan veri setini almamıza gerek olmadığını hayal edeceğiz, bunun için SELECT ifadesini saklı yordamdan kaldıracağız.

Prosedürü değiştirin ALTER PROCEDURE TestProcedure (--Gelen parametreler @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) BAŞLANGIÇ OLARAK --Algoritmanızı uygulayan talimatlar --Gelen parametrelerin işlenmesi --Baştaki ve sondaki fazladan boşlukları kaldırın metin satırlarının SET @ÜrünAdı = LTRIM(RTRIM(@ÜrünAdı)); --Yeni kayıt ekle INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

T-SQL'de saklı bir prosedürün silinmesi - DROP PROCEDURE deyimi

Gerekirse saklı yordamı silebilirsiniz; bu, ifade 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ı prosedürleri silerken, prosedüre başka prosedürler veya SQL ifadeleri tarafından başvuruluyorsa, onu sildikten sonra, başvurdukları prosedür artık mevcut olmadığından bir hatayla başarısız olacaklarını hatırlamakta fayda var.

Her şeye sahibim, umarım materyal sizin için ilginç ve yararlı olmuştur, hoşçakalın!

Saklı prosedür kavramı tanımlanmıştır. Saklı prosedürlerin parametrelerle oluşturulması, değiştirilmesi ve kullanılmasına ilişkin örnekler verilmiştir. Giriş ve çıkış parametrelerinin tanımı verilmiştir. Saklı yordamların oluşturulması ve çağrılması örnekleri verilmiştir.

Saklı prosedür kavramı

saklı yordamlar yürütülmesi programcının işini daha kolay ve esnek hale getiren, birbirine bağlı SQL ifadeleri gruplarıdır. 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 veritabanında derlenmiş biçimde saklanan bir dizi komuttur. Veritabanında yürütme saklı prosedürler Bireysel SQL ifadeleri yerine kullanıcıya aşağıdaki avantajları sağlar:

• gerekli operatörler zaten veritabanındadı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, onu optimize eder ve derler;
• saklı prosedürler Destek modüler programlama büyük görevleri bağımsız, daha küçük ve yönetimi 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 ve yüzlerce komuttan oluşabilirler, ancak bunları çalıştırmak için yalnızca 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. Genellikle, 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. Verilerin bütünlüğünü sağlamak ve güvenlik amacıyla, uygulama genellikle verilere doğrudan erişim sağlamaz - onlarla yapılan tüm çalışmalar, birini veya diğerini arayarak yapılır. 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 kolaylaştırır ve uygulamanın kendisinde değişiklik yapmadan sistemi genişletme yeteneği sağlar: değiştirmek yeterlidir saklı yordam veritabanı sunucusunda. Geliştiricinin uygulamayı yeniden derlemesine, kopyalarını oluşturmasına ve ayrıca kullanıcılara yeni sürümle çalışmanın gerekliliği konusunda talimat vermesine gerek yoktur. Kullanıcılar sistemde değişiklik yapıldığının farkında bile olmayabilir.

saklı yordamlar 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 verir veya yasaklar. Kodu değiştir saklı yordam yalnızca sahibi veya sabit 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ı yordamlar tam teşekküllü veritabanı nesneleridir ve bu nedenle her biri belirli bir veritabanında saklanır. Direkt Arama saklı yordam yalnızca prosedürün bulunduğu veritabanı bağlamında yürütülürse mümkündür.

Saklı Prosedür Türleri

SQL Server'da birkaç tür vardır saklı prosedürler.

• Sistemik saklı prosedürlerÇeşitli idari eylemleri gerçekleştirmek için tasarlanmıştır. Neredeyse tüm sunucu yönetimi eylemleri onların yardımıyla gerçekleştirilir. Sistem diyebiliriz saklı prosedürler sistem tablolarıyla çalışmayı sağlayan ve sonuçta hem kullanıcı hem de sistem veritabanlarının sistem tablolarındaki verileri değiştirmeye, eklemeye, silmeye ve almaya gelen bir arayüzdür. Sistemik saklı prosedürler sp_ ön ekidir, 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ı yordamlar- tam bir veritabanı nesnesi. Bunun sonucunda 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 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 mutlaka tek # karakterle başlayan bir isim verilmelidir. Tüm geçici nesneler gibi, saklı prosedürler Bu türdeki dosyalar, kullanıcı sunucunun bağlantısını kestiğinde, yeniden başlattığında veya durdurduğunda otomatik olarak silinir. Küresel geçici saklı prosedürler aynı prosedüre sahip tüm sunucu bağlantıları için kullanılabilir. Tanımlamak için ## karakterleriyle başlayan bir isim vermeniz yeterlidir. Bu prosedürler, sunucu yeniden başlatıldığında veya durdurulduğunda ya da bağlamında oluşturuldukları bağlantı kapatıldığında silinir.

Saklı Prosedürlerin Oluşturulması, Değiştirilmesi ve Silinmesi

Yaratılış saklı yordam aşağıdaki görevleri çözmeyi içerir:

• türünü tanımlayan saklı yordam: geçici veya özel. Ayrıca kendi sisteminizi de oluşturabilirsiniz. saklı yordam, sp_ önekiyle bir ad vererek ve sistem veritabanına yerleştirerek. Böyle bir prosedür, yerel sunucudaki herhangi bir veritabanı bağlamında mevcut olacaktır;
• erişim planlaması. oluştururken saklı yordam veritabanı nesnelerine, onu oluşturan kullanıcıyla aynı erişim haklarına sahip olacağını unutmayın;
• tanım saklı yordam parametreleri. Çoğu programlama dilinde yer alan prosedürler gibi, saklı prosedürler giriş ve çıkış parametrelerine sahip olabilir;
• kod geliştirme saklı yordam. Prosedür kodu, diğerlerini çağırmak 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 komutla yapılır:

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

Bu komutun parametrelerini göz önünde bulundurun.

Sp_, #, ## öneklerini kullanarak oluşturulan prosedür, sistem veya geçici prosedür olarak tanımlanabilir. Komutun sözdiziminden de görebileceğiniz gibi, oluşturulan prosedürün ait olacağı sahibinin adının yanı sıra yerleştirilmesi gereken veritabanının adının belirtilmesine izin verilmiyor. Böylece yaratılanlara uyum sağlamak için saklı yordam belirli bir veritabanında, CREATE PROCEDURE komutunu o veritabanının bağlamında çalıştırmalısınız. Vücuttan çıkarıldığında saklı yordam Aynı veritabanındaki nesneler için kısaltılmış adlar kullanılabilir, yani veritabanı adı belirtilmeden. Diğer veritabanlarında bulunan nesnelere atıfta bulunmak istediğinizde veritabanı adının belirtilmesi gerekir.

İsimdeki numara kimlik numarasıdır saklı yordam Bu, onu bir prosedürler grubu içinde benzersiz bir şekilde tanımlar. Prosedürleri yönetme kolaylığı için mantıksal olarak aynı tür saklı prosedürler aynı isim fakat farklı kimlik numaraları verilerek gruplandırılabilir.

Oluşturulan giriş ve çıkış verilerini iletmek için saklı yordam yerel değişkenlerin adları gibi adlarının da @ simgesiyle başlaması gereken parametreler kullanılabilir. Bir saklı yordam Virgüllerle ayırarak birden fazla seçenek belirleyebilirsiniz. Bir yordamın gövdesi, adları yordamın parametrelerinin adlarıyla aynı olan 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 herhangi bir SQL veri türü uygundur. Ancak CURSOR veri türü yalnızca şu şekilde kullanılabilir: çıkış parametresi saklı yordam yani OUTPUT anahtar kelimesiyle.

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 şu durumdan çıkması talimatını verir: saklı yordam parametrenin geçerli değerini, prosedür parametre değeri olarak çağrıldığında 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 hiçbir ifadeye veya sabite izin verilmez.

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

DEFAULT anahtar sözcüğü karşılık gelen değerdir. varsayılan ayarlar. Bu nedenle, bir prosedürü çağırırken ilgili parametrenin değerini açıkça belirtemezsiniz.

Sunucu, sorgu yürütme planını ve derlenmiş kodu önbelleğe aldığından, prosedür bir sonraki çağrıldığında önceden hazırlanmış 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 çağrıldığında.

FOR REPLICATION parametresi, verileri çoğaltırken ve oluşturulan verileri dahil ederken gereklidir. saklı yordam bir yayında makale olarak.

ENCRYPTION anahtar sözcüğü sunucuya kodu şifrelemesi talimatını verir saklı yordamçalışmayı uygulayan telif hakkı algoritmalarının kullanımına karşı koruma sağlayabilecek saklı yordam.

AS anahtar sözcüğü asıl gövdenin başına yerleştirilir saklı yordam yani şu veya bu eylemin uygulanacağı bir dizi SQL komutu. Prosedürün gövdesinde hemen hemen 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 ile yapılabilir.

Saklı yordamı silme komutla gerçekleştirilir:

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

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

İçin saklı yordamın yürütülmesi komut kullanılır:

[[ 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ğilse EXECUTE komutunun varlığı gerekir. 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 aşağıdaki durumlarda bildirilen parametreler için izin verilir: prosedür oluşturma OUTPUT anahtar sözcüğüyle.

Bir prosedür çağrısı için DEFAULT anahtar sözcüğü belirtildiğinde, DEFAULT anahtar sözcüğü 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özdiziminden, bir prosedür çağrılırken parametre adlarının atlanabileceğini görebilirsiniz. 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, numaralandırma sırasında basitçe atlamak mümkün değildir. Eğer parametrelerin atlanması gerekiyorsa 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ıldığında ya değerleri olan parametre adlarının ya da yalnızca parametre adı olmayan değerlerin belirtildiğini unutmayın. Kombinasyonlarına izin verilmez.

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

PROC my_proc1'İ SELECT OLARAK OLUŞTURUN Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.Last Name From Customer INNER JOIN (Item INNER JOIN İşlemi ON Item.ItemId=Trade.ItemId) ON Customer.CustomerCode=Trade.CustomerCode WHERE Müşteri .Soyadı='Ivanov' Örnek 12.1. Ivanov tarafından satın alınan malların isimlerini ve fiyatlarını alma prosedürü.

İçin prosedüre çağrı komutlar kullanılabilir:

EXEC my_proc1 veya my_proc1

Prosedür bir dizi veri döndürür.

Örnek 12.2. Parametresiz prosedür. Birinci sınıf bir ürünün fiyatını %10 oranında düşürmek için bir prosedür oluşturun.

İçin prosedüre çağrı komutlar kullanılabilir:

EXEC my_proc2 veya my_proc2

Prosedür herhangi bir veri döndürmez.

Örnek 12.3. Giriş parametresi ile prosedür. Belirli bir müşteri tarafından satın alınan ürünlerin adlarını ve fiyatlarını almak için bir prosedür oluşturun.

PROC my_proc3 @k VARCHAR(20) OLARAK SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.LastName FROM Customer INNER JOIN (Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID) Client.CustomerID'DE OLUŞTURUN =Anlaşma.MüşteriKodu NEREDE Müşteri.Soyadı=@k Örnek 12.3. Belirli bir müşteri tarafından satın alınan ürünlerin adlarını ve fiyatlarını almaya yönelik bir prosedür.

İçin prosedüre çağrı komutlar kullanılabilir:

EXEC my_proc3 "Ivanov" veya my_proc3 @k="Ivanov"

Örnek 12.4.. Belirli bir türdeki ürünün fiyatını belirtilen yüzdeye göre düşürmek için bir prosedür oluşturun.

İçin prosedüre çağrı komutlar kullanılabilir:

EXEC my_proc4 "Waffle",0.05 veya EXEC my_proc4 @t="Waffle", @p=0.05

Örnek 12.5. Giriş parametreleriyle ilgili prosedür ve varsayılan değerler. Belirli bir türdeki ürünün fiyatını belirtilen yüzdeye göre düşürmek için bir prosedür oluşturun.

PROC OLUŞTUR my_proc5 @t VARCHAR(20)='Şeker`, @p FLOAT=0.1 GÜNCELLEME OLARAK Öğe SET Fiyat=Fiyat*(1-@p) WHERE Tür=@t Örnek 12.5. Giriş parametreleri ve varsayılan değerlerle ilgili prosedür. Belirli bir türdeki ürünün fiyatını belirtilen yüzdeye göre düşürmek için bir prosedür oluşturun.

İçin prosedüre çağrı komutlar kullanılabilir:

EXEC my_proc5 "Waffle",0.05 veya EXEC my_proc5 @t="Waffle", @p=0.05 veya EXEC my_proc5 @p=0.05

Bu durumda tatlıların fiyatı düşer (prosedür çağrılırken türün değeri belirtilmez ve varsayılan olarak alınır).

İkinci durumda, prosedür çağrılırken her iki parametre de (hem tür hem de yüzde) belirtilmez, değerleri varsayılan olarak alınır.

Örnek 12.6. Giriş ve çıkış parametreleriyle ilgili prosedür. Belirli bir ayda satılan malların toplam maliyetini belirlemek için bir prosedür oluşturun.

PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Item.Price*Trade.Quantity) FROM Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID GROUP BY Month(Trade.Date) HAVING Month( Anlaşma.Tarih)=@m Örnek 12.6. Giriş ve çıkış parametreleriyle ilgili prosedür. Belirli bir ayda satılan malların toplam maliyetini belirlemek için bir prosedür oluşturun.

İçin prosedüre çağrı komutlar kullanılabilir:

DECLARE @st FLOAT EXEC my_proc6 1,@st ÇIKIŞ SEÇİMİ @st

Bu komut bloğu, Ocak ayında satılan malların maliyetini belirlemenizi sağlar ( giriş parametresi ay 1 olarak ayarlanmıştır).

Belirli bir çalışanın çalıştığı firma tarafından satın alınan toplam mal miktarını belirlemek için bir prosedür oluşturun.

Öncelikle çalışanın çalıştığı şirketin belirlenmesine yönelik bir prosedür geliştireceğiz.

Örnek 12.7. kullanım iç içe geçmiş prosedürler. Belirli bir çalışanın çalıştığı firma tarafından satın alınan toplam mal miktarını belirlemek için bir prosedür oluşturun.

Daha sonra ilgilendiğimiz firmanın satın aldığı toplam mal miktarını sayan bir prosedür oluşturacağız.

PROC OLUŞTUR my_proc8 @fam VARCHAR(20), @kol INT ÇIKTI AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm ÇIKTI SEÇİMİ @kol=Sum(Trade.Quantity) Müşteriden INNER JOIN Trade ON Client.ClientCode= Deal.ClientCode Client.Company'E GÖRE GRUPLANMIŞ Client.Company=@firm Örnek 12.7. Belirli bir çalışanın çalıştığı firma tarafından satın alınan toplam mal miktarını belirlemek için bir prosedür oluşturun.

Prosedür şu komut kullanılarak çağrılır:

DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k ÇIKTI SEÇİMİ @k

Saklı yordam SQL dili ve prosedür uzantıları kullanılarak oluşturulan özel bir Transact-SQL deyimi toplu 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ı yordamlar, tekrarlanan görevlerin performansını ve tutarlılığını artırmak için sunucu tarafında depolanı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ı prosedür oluştururken isteğe bağlı bir parametre listesi tanımlayabilirsiniz. Böylece prosedür her çağrıldığında uygun argümanları alacaktır. 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ı bir prosedür, 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) prosedür yeniden derlemelerinin ortadan kaldırılması ve buna karşılık gelen bir performans artışı elde edilmesi 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, birkaç bin baytlık bir saklı prosedüre yapılan çağrı, 50 bayttan daha azını gerektirebilir. Birden fazla kullanıcı, saklı prosedürleri kullanarak tekrarlanan görevleri gerçekleştirdiğinde, bu tasarrufların kümülatif etkisi önemli olabilir.

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

veritabanı tablolarıyla eylemler hakkında bir günlük kaydı oluşturmak için.

Saklı prosedürlerin kullanımı, kullanıcılara farklı erişim ayrıcalıkları veren GRANT ve REVOKE ifadelerinin kullanımı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.

Tablolara yazma ve/veya okuma işlemlerini günlüğe kaydeden saklı prosedürler, veritabanı için ek güvenlik sağlar. Bu tür prosedürleri kullanarak veritabanı yöneticisi, kullanıcılar veya uygulamalar tarafından veritabanında yapılan değişiklikleri takip edebilir.

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

Saklı prosedürler deyimi 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 şema adını belirtir. proc_name parametresi saklı yordamın adını belirtir. @param1 parametresi, veri türü type1 parametresi tarafından belirtilen bir prosedür parametresidir (resmi bir argüman). Yerel değişkenler bir paket içinde yerel olduğu gibi, prosedür parametreleri de bir prosedür içinde yereldir. 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 döndürülebilir 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, prosedürün önceden derlenmiş formu veritabanında saklanır ve her çağrıldığında kullanılır. Herhangi bir nedenle bir saklı yordamın her çağrıldığında derlenmesi gerekiyorsa, yordamın bildirimi şunu kullanır: YENİDEN DERLEME İLE seçenek. İ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 YENİDEN DERLEME seçeneği yalnızca saklı yordamın kullandığı veritabanı nesnelerinde sık sık değişiklik olması durumunda kullanılmalıdır.

Teklif OLARAK YÜRÜT 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, bu saklı yordamın referans verdiği nesnelere ilişkin erişim izinlerini kontrol etmek 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ı rolünün üyeleri CREATE PROCEDURE deyimini kullanabilir. Ancak bu rollerin üyeleri, bu hakkı talimatı 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 de belirtildiği gibi iki paketi ayırmak için kullanılır GO talimatı. 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 belirtilen belirli bir yüzde oranında artırır. Prosedür ayrıca, prosedürün yürütülmesi sırasında bu bağımsız değişkenin mevcut olmaması durumunda uygulanacak 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şturmadan ve hatta hedef sunucuya bağlanmadan prosedür kodunda hata ayıklamanıza olanak tanır.

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 nedenlerinden biri, bir veritabanına bağlanırken belirli bir grup ifadenin 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ı 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 bir geçici prosedür tüm kullanıcılar tarafından yürütülebilir, ancak yalnızca üzerinde çalıştığı son bağlantı (genellikle prosedürü oluşturanın bağlantısı) sonlandırılana 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 tarafından yürütülür EXECUTE ifadeleri Prosedürün sahibi olan veya prosedüre erişim için EXECUTE hakkına sahip 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, aynı adı taşıyan CREATE PROCEDURE ifadesinin parametreleriyle aynı boolean değerine sahiptir. return_status parametresi, prosedürün dönüş durumunu saklayan bir tamsayı değişkenini tanımlar. Bir parametreye bir sabit (değer) veya yerel bir değişken (@değişken) kullanılarak bir değer atanabilir. Adlandırılmış parametre değerlerinin sırası önemli değildir ancak adsız parametre değerlerinin CREATE PROCEDURE deyiminde tanımlandığı sıraya göre sağlanması gerekir.

DEFAULT yan tümcesi prosedür tanımında belirtilen bir prosedür parametresi için varsayılan değerleri sağlar. Bir prosedür, varsayılan değeri tanımlanmayan ve parametrenin eksik olduğu bir parametre için 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 kelimenin her pakette yer alması 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:

Örnekteki ModifyEmpId yordamı, bilgi bütünlüğü sürecinin bir parçası olarak (bu durumda Çalışan ve Works_on tabloları arasında) saklı yordamların kullanımını gösterir. Böyle bir saklı prosedür, aslında referans bütünlüğünü zorlayan bir tetikleyici tanımının içinde kullanılabilir.

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

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

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

Bu prosedür, personel numarası @empId olan bir ç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 yordamı @counter parametresini çağıran yordama aktarır, böylece saklı yordam değeri sisteme döndürür. Bu nedenle, @counter parametresinin hem prosedür bildirilirken OUTPUT seçeneğinde, hem de çağrılırken EXECUTE deyiminde belirtilmesi gerekir.

Bir EXECUTE ifadesinin YOUR RESULTS SETS yan tümcesi

SQL Server 2012'de EXECUTE ifadesi yazılmıştır İLE SONUÇ SETLERİ yan tümcesi Belirli koşullar altında saklı yordamın sonuç kümesinin şeklini değiştirebilen bir.

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 prosedür ç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 tablonun çıktısını verir; kimlik ve soyadı. Sonuç sütunu 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 maddenin uygulanması aşağıdaki örnekte gösterilmektedir:

SampleDb'yi KULLANIN; SONUÇ SETLERİ İLE EXEC EmployeesInDept "d1" (( INT NOT NULL, [Soyadı] 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, bir EXECUTE deyiminde With RESULT SETS yan tümcesini kullanarak saklı bir yordamı çalıştırmak, yordamın ürettiği sonuç kümesi sütunlarının adlarını ve veri türünü 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 da bu ifadeyi destekliyor 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, aynı adı taşıyan CREATE PROCEDURE ifadesinin parametreleriyle aynı anlama sahiptir. Bu ifadeyi kullanmanın temel amacı, mevcut saklı yordam izinlerinin 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ı kaldırmak için şunu kullanın: BIRAKMA PROSEDÜRÜ beyanı. Yalnızca saklı yordamın sahibi veya db_owner ve sysadmin sabit rollerinin ü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 kullanıcı tanımlı veri türleri) geliştirmenize olanak tanıyan Ortak Dil Çalışma Zamanını (CLR) destekler. Ortak dil çalışma zamanı aynı zamanda bu nesnelerin ortak çalışma zamanı sistemi kullanılarak yürütülmesine de olanak tanır.

Ortak dil çalışma zamanı seçeneği aracılığıyla etkinleştirilir ve devre dışı bırakılır 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ünü kullanarak ortak dil çalışma zamanını nasıl etkinleştirebileceğ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, listelenen sıraya göre aşağıdaki adım dizisini gerektirir:

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

Talimatı kullanma MONTAJ OLUŞTURUN, uygun yürütülebilir dosyayı oluşturun.

EXECUTE deyimini kullanarak bir prosedürü 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 istediğiniz programı Visual Studio gibi bir geliştirme ortamında oluşturun. Tamamlanan programı C# veya Visual Basic derleyicisini kullanarak nesne koduna derleyin. Bu kod, ara yürütülebilir kod oluşturan CREATE ASSEMBLY deyimi için kaynak görevi gören bir dinamik bağlantı kitaplığı (.dll) dosyasında depolanır. Daha sonra, yürütülmekte olan 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 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ın yürütülmesi için gereken ad alanlarını belirtir. Bu direktiflerin kullanımı, karşılık gelen ad alanlarını açıkça belirtmeden, kaynak kodda 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. Sınıf kodunun içinde CountEmployees() yöntemi tanımlanmıştı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 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İstenilen 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. Sonraki denir ExecuteScalar() yöntemi SqlCommand örneği. Bu yöntem, int 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ı projeye CLRStoredProcedures ismiyle ekledim, böylece Visual Studio aynı isimdeki derlemeyi *.dll uzantısıyla derleyecektir. Aşağıdaki örnek, saklı yordam oluşturmanın bir sonraki adımını gösterir: çalıştırılacak kodun oluşturulması. 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; 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 ortak dil çalışma zamanı (CLR) saklı yordamları, kullanıcı tanımlı işlevler ve tetikleyiciler oluşturabileceğiniz uygun 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 bir rolün 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 erişim hakları kümesini tanımlar. SAFE hakları 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şim sağlar.

Derleme kodu bilgilerini depolamak için kullanıcının CREATE ASSEMBLY deyimini yayınlayabilmesi gerekir. Derleme, ifadeyi yürüten kullanıcıya (veya role) aittir. CREATE SCHEMA ifadesinin AUTHORIZATION yan tümcesini kullanarak bir derlemenin sahibini değiştirebilirsiniz.

Veritabanı Motoru ayrıca ALTER ASSEMBLY ve DROP ASSEMBLY ifadelerini de destekler. ALTER ASSEMBLY Beyanı Bir derlemeyi 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 Beyanı belirtilen derlemeyi ve ilişkili tüm dosyaları geçerli veritabanından kaldırır.

Aşağıdaki örnek, daha önce uygulanan 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 ifadesi önceki örneklerdeki aynı ifadeden farklı olarak aşağıdakileri içerir: HARİCİ ADI parametresi. Bu seçenek kodun CLR 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ı belirtir;

sınıf_adı - genel sınıfın adını belirtir;

yöntem_adı - isteğe bağlı kısım, sınıf içinde ayarlanan 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 -- 7 değerini döndürür

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

Prosedür bildirimi

PROSEDÜR OLUŞTUR [({GİRİŞ|ÇIKIŞ|GİRİŞ} [,…])]
[DİNAMİK SONUÇ SETİ ]
BAŞLAMAK [ATOMİK]

SON

Anahtar Kelimeler
. IN (Giriş) – giriş parametresi
. OUT (Çıkış) – çıkış parametresi
. INOUT - giriş ve çıkışın yanı sıra bir alan (parametre yok)
. DİNAMİK SONUÇ AYARI, prosedürün, prosedür geri döndükten sonra açık kalacak olan belirtilen sayıda imleç açabileceğini gösterir.

Notlar
Ağ ve yığın yoğunluğu nedeniyle saklı yordamlarda (öncelikle büyük sayılar ve karakter dizeleri) çok sayıda parametrenin kullanılması önerilmez. Uygulamada, Transact-SQL, PL/SQL ve Informix'in mevcut lehçeleri, hem parametre bildiriminde hem de kullanımda, değişken bildiriminde ve alt rutin çağrısında standarttan önemli sapmalar göstermektedir. Microsoft, saklı yordamların önbellek boyutunu tahmin etmek için aşağıdaki yaklaşımın kullanılmasını önerir:
=(maksimum eşzamanlı kullanıcı sayısı)*(en büyük yürütme planı boyutu)*1,25. Uygulama planının boyutunu sayfalar halinde belirlemek şu komut kullanılarak yapılabilir: DBCC MEMUSAGE.

Prosedür çağrısı

Mevcut DBMS'lerin çoğunda, saklı prosedürler şu ifade kullanılarak çağrılır:

PROSEDÜRÜ UYGULAYIN [(][)]

Not: Saklı prosedürlere çağrı, bir uygulama içinden, başka bir saklı prosedürden veya etkileşimli olarak yapılabilir.

Prosedür bildirimi örneği

CREATE PROCEDURE Proc1 AS //bir prosedür bildirmek
SES SEÇİMİ İÇİN Cur1 İMLECİNİ BİLDİRİsim, Şehir Satış Kişilerinden NEREDE Derecelendirme>200 //bir imleç bildir
OPEN Cur1 //imleci aç
FETCH NEXT FROM Cur1 //imleçten veriyi oku
WHILE @@Fetch_Status=0
BAŞLAMAK
Cur1'DEN SONRAKİ ÜRÜNÜ GETİR
SON
CLOSE Cur1 //imleci kapat
Cur1'i DAĞITIN
EXECUTE Proc1 //prosedürü çalıştır

Polimorfizm
İki alt programın parametreleri birbirinden ayırt edilebilecek kadar farklıysa, aynı şemada aynı ada sahip iki alt program oluşturulabilir. Aynı şemada aynı ada sahip iki rutini birbirinden ayırmak için her birine alternatif ve benzersiz bir ad (özel ad) verilir. Alt program tanımlandığında böyle bir ad açıkça belirtilebilir. Birden fazla aynı ada sahip alt programları çağırırken, istenen alt programın belirlenmesi birkaç adımda gerçekleştirilir:
. Başlangıçta, belirtilen ada sahip tüm prosedürler tanımlanır ve eğer yoksa, belirtilen ada sahip tüm işlevler tanımlanır.
. Daha fazla analiz için, yalnızca verilen kullanıcının yürütme ayrıcalığına (EXECUTE) sahip olduğu rutinler kalır.
. Onlar için, parametre sayısı çağrı argümanlarının sayısına karşılık gelenler seçilir. Parametrelerin belirtilen veri türleri ve konumları kontrol edilir.
. Birden fazla alt program kaldıysa, daha kısa niteleyici adı olan seçilir.
Uygulamada, Oracle'da, yalnızca bir pakette bildirilen işlevler için polimorfizm desteklenir, DB@ - farklı şemalarda ve Sybase ve MS SQL Server'da aşırı yükleme yasaktır.

Prosedürlerin kaldırılması ve değiştirilmesi
Bir prosedürü silmek için aşağıdaki ifade kullanılır:

Bir prosedürü değiştirmek için şu ifadeyi kullanın:

PROSEDÜRÜ DEĞİŞTİRİN [([{GİRİŞ|ÇIKIŞ|GİRİŞ}])]
BAŞLAMAK [ATOMİK]

SON

Prosedürleri yürütme ayrıcalıkları

HİBE YÜRÜTME DEVAM EDİYOR İLE |HALK [HİBE SEÇENEĞİ İLE]

Sistem Prosedürleri
Çoğu DBMS (SQL Server dahil), kendi amaçlarınız için kullanabileceğiniz belirli bir dizi yerleşik sistem saklı yordamına sahiptir.