MS sql server muhitida saqlanadigan protseduralar. T-SQL da saqlangan protseduralar - yaratish, o'zgartirish, o'chirish

1. Protseduralaringizga qatorni kiriting - SET NOCOUNT ON: Har bir DML bayonoti bilan SQL server ehtiyotkorlik bilan bizga qayta ishlangan yozuvlar sonini o'z ichiga olgan xabarni qaytaradi. Ushbu ma'lumot kodni disk raskadrovka qilishda biz uchun foydali bo'lishi mumkin, ammo bundan keyin u butunlay foydasiz bo'ladi. SET NOCOUNT ON yozish orqali biz bu funksiyani o‘chirib qo‘yamiz. Bir nechta iboralar yoki\va tsikllarni o'z ichiga olgan saqlangan protseduralar uchun bu harakat unumdorlikni sezilarli darajada oshirishi mumkin, chunki trafik miqdori sezilarli darajada kamayadi.

PROC dbo.ProcName YARATING
AS
NO COUNT NI YOQISH;
--Bu erda protsedura kodi
dbo.TblTable1DAN 1-ustunni tanlang
--SET NOCOUNT ni boshlang'ich holatiga o'tkazing
NOCOUNT NI O‘CHIRISh;
BOSH

2. Ob'ekt nomi bilan sxema nomidan foydalaning: Xo'sh, menimcha, bu aniq. Bu operatsiya serverga ob'ektlarni qayerda qidirish kerakligini aytadi va u qutilarini tasodifiy varaqlash o'rniga, u qaerga borishi va nimani olish kerakligini darhol bilib oladi. Ko'p sonli ma'lumotlar bazalari, jadvallar va saqlangan protseduralar bilan bizning vaqtimiz va asablarimizni sezilarli darajada tejash mumkin.

SELECT * FROM dbo.MyTable --Buni qilishning yaxshi usuli
-- O'rniga
Mening jadvalimdan * ni tanlang - Bu yomon ish
--Protsedura chaqiruvi
EXEC dbo.MyProc -- Yana yaxshi
--O'rniga
EXEC MyProc --Yomon!

3. Saqlangan protseduralaringiz nomida "sp_" prefiksidan foydalanmang: Agar protsedura nomi "sp_" bilan boshlansa, SQL Server birinchi navbatda o'zining asosiy ma'lumotlar bazasini ko'rib chiqadi. Gap shundaki, ushbu prefiks serverning shaxsiy ichki saqlangan protseduralari uchun ishlatiladi. Shuning uchun, uning ma'lumotlar bazasida sizniki bilan bir xil nomdagi protsedura topilsa, undan foydalanish qo'shimcha xarajatlarga olib kelishi va hatto noto'g'ri natijalarga olib kelishi mumkin.

4. IF EXISTS (SELECT *) oʻrniga IF EXISTS (1-ni tanlang) dan foydalaning: Yozuvning boshqa jadvalda mavjudligini tekshirish uchun biz IF EXISTS bayonotidan foydalanamiz. Agar ichki ifodadan kamida bitta qiymat qaytarilsa, bu ifoda "true"ni qaytaradi, bu "1", barcha ustunlar yoki jadval muhim emas. Qaytarilgan ma'lumotlar, qoida tariqasida, hech qanday tarzda ishlatilmaydi. Shunday qilib, ma'lumotlarni uzatish paytida trafikni siqish uchun quyida ko'rsatilganidek, "1" dan foydalanish mantiqiyroqdir:

AGAR MAVJUD BO'LSA (Sysobyektlardan 1 tasini tanlang
WHERE nomi = "Mening jadvalim" VA turi = "U")

5. Xatolarni aniqlash uchun TRY-Catch-dan foydalaning: 2005-yil serveridan oldin, har bir so'rovdan so'ng, protsedurada juda ko'p xato tekshiruvlari yozilgan. Ko'proq kod har doim ko'proq resurslar va ko'proq vaqt sarflaydi. SQL Server 2005 dan boshlab, ushbu muammoni hal qilishning yanada to'g'ri va qulay usuli paydo bo'ldi:

UYINING
--kod
UYINING TUGASH
QO'SHLASHNI BOSHLASH
-- xatolikni aniqlash kodi
QO'SHIQNI TUG'IRISh

Xulosa
Umuman olganda, bugun menda bor narsa shu. Yana bir bor takrorlaymanki, bu erda faqat men o'z amaliyotimda qo'llagan usullar bor va men ularning samaradorligiga kafolat bera olaman.

P.S.
Mening birinchi postim, qat'iy hukm qilmang.

Microsoft SQL Serverda o'z algoritmlarini amalga oshirish va avtomatlashtirish uchun ( hisob-kitoblar) saqlangan protseduralardan foydalanishingiz mumkin, shuning uchun bugun biz ularni qanday yaratish, o'zgartirish va o'chirish haqida gaplashamiz.

Lekin birinchi navbatda, T-SQL da saqlangan protseduralar nima ekanligini va ular nima uchun ekanligini tushunishingiz uchun kichik bir nazariya.

Eslatma! Ajam dasturchilar uchun men T-SQL mavzusi bo'yicha quyidagi foydali materiallarni tavsiya qilaman:

• T-SQL tilini batafsilroq o'rganish uchun men "T-SQL dasturchisining yo'li" kitobini ham o'qishni tavsiya qilaman. Transact-SQL o'quv qo'llanma.

T-SQL da qanday saqlangan protseduralar mavjud?

Saqlangan protseduralar- Bular ma'lumotlar bazasi ob'ektlari bo'lib, unda algoritm SQL ko'rsatmalari to'plami shaklida joylashtirilgan. Boshqacha qilib aytganda, saqlangan protseduralar ma'lumotlar bazasi ichidagi dasturlar deb aytishimiz mumkin. Saqlangan protseduralar serverda qayta foydalanish mumkin bo'lgan kodni saqlash uchun ishlatiladi, masalan, siz algoritm, ketma-ket hisoblash yoki ko'p bosqichli SQL bayonotini yozdingiz va har safar ushbu algoritmga kiritilgan barcha ko'rsatmalarni bajarmaslik uchun siz tartibga solishingiz mumkin. saqlangan protsedura sifatida. Bunday holda, siz SQL protsedurasini yaratganingizda, server kodni kompilyatsiya qiladi va keyin har safar ushbu protsedurani bajarganingizda, SQL server uni boshqa kompilyatsiya qilmaydi.

SQL Serverda saqlangan protsedurani ishga tushirish uchun uning nomidan oldin EXECUTE buyrug'ini yozish kerak, bu EXEC buyrug'ini stenografiyada ham yozish mumkin. SELECT iborasida saqlangan protsedurani chaqiring, masalan, funktsiya endi ishlamaydi, ya'ni. protseduralar alohida amalga oshiriladi.

Saqlangan protseduralarda, funktsiyalardan farqli o'laroq, ma'lumotlarni o'zgartirish operatsiyalarini bajarish mumkin, masalan: UNSERT, UPDATE, DELETE. Bundan tashqari, protseduralarda siz deyarli har qanday turdagi SQL operatoridan foydalanishingiz mumkin, masalan, jadvallar yaratish uchun CREATE TABLE yoki EXECUTE, ya'ni. boshqa protseduralarni chaqirish. Istisno bir necha turdagi ko'rsatmalardir, masalan: funktsiyalarni, ko'rinishlarni, triggerlarni yaratish yoki o'zgartirish, sxemalarni yaratish va boshqa shunga o'xshash ko'rsatmalar, masalan, saqlangan protsedurada ma'lumotlar bazasi ulanish kontekstini (USE) almashtirish ham mumkin emas.

Saqlangan protsedura kiritish parametrlari va chiqish parametrlariga ega bo'lishi mumkin, u jadval ma'lumotlarini qaytarishi mumkin, u hech narsani qaytarmasligi mumkin, faqat undagi ko'rsatmalarni bajaradi.

Saqlangan protseduralar juda foydali, ular bizga ko'plab operatsiyalarni avtomatlashtirish yoki soddalashtirishga yordam beradi, masalan, siz doimiy ravishda pivot jadvallari yordamida turli xil murakkab tahliliy hisobotlarni yaratishingiz kerak, ya'ni. PIVOT operatori. Ushbu operator yordamida so'rovlarni shakllantirishni soddalashtirish uchun ( Ma'lumki, PIVOT sintaksisi ancha murakkab), siz dinamik ravishda jamlama hisobotlarni yaratadigan protsedurani yozishingiz mumkin, masalan, "T-SQLda dinamik PIVOT" materialida ushbu xususiyatni saqlangan protsedura shaklida amalga oshirish misoli keltirilgan.

Microsoft SQL Serverda saqlangan protseduralar bilan ishlashga misollar

Misollar uchun dastlabki ma'lumotlar

Quyidagi barcha misollar Microsoft SQL Server 2016 Express da bajariladi. Saqlangan protseduralar haqiqiy ma'lumotlar bilan qanday ishlashini ko'rsatish uchun bizga ushbu ma'lumotlar kerak, keling, uni yarataylik. Masalan, test jadvalini tuzamiz va unga ba'zi yozuvlarni qo'shamiz, deylik, bu mahsulotlarning narxlari ro'yxatini o'z ichiga olgan jadval bo'ladi.

Jadval yaratish bayonoti CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Ma'lumotlar bayonotini qo'shing INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1) , "Sichqoncha", 100), (1, "Klaviatura", 200), (2, "Telefon", 400) O'TISH -- TestTable so'rovidan * TANLASH

Ma'lumotlar mavjud, endi saqlangan protseduralarni yaratishga o'tamiz.

T-SQL da saqlangan protsedura yaratish - CREATE PROCEDURE operatori

Saqlangan protseduralar bayonot yordamida yaratiladi TARTIBI YARATISH, ushbu ko'rsatmadan so'ng siz protsedura nomini yozishingiz kerak, agar kerak bo'lsa, kirish va chiqish parametrlarini qavs ichida aniqlang. Shundan so'ng siz AS kalit so'zini yozasiz va BEGIN kalit so'zi bilan ko'rsatmalar blokini ochasiz, bu blokni END so'zi bilan yoping. Ushbu blokning ichida siz o'zingizning algoritmingizni yoki qandaydir ketma-ket hisob-kitoblarni amalga oshiradigan barcha ko'rsatmalarni yozasiz, boshqacha aytganda, T-SQL da dastur qilasiz.

Misol uchun, yangi yozuv qo'shadigan saqlangan protsedurani yozaylik, ya'ni. sinov jadvalimizga yangi element. Buning uchun biz uchta kirish parametrini aniqlaymiz: @CategoryId - mahsulot toifasi identifikatori, @Mahsulot nomi - mahsulot nomi va @Price - mahsulot narxi, bu parametr biz uchun ixtiyoriy bo'ladi, ya'ni. uni protseduraga o'tkazib bo'lmaydi ( masalan, biz hali narxini bilmaymiz), buning uchun biz uning ta'rifida standart qiymatni o'rnatamiz. Ushbu parametrlar protseduraning tanasida, ya'ni. BEGIN…END blokida oddiy o‘zgaruvchilar bilan bir xilda foydalanish mumkin ( Ma'lumki, o'zgaruvchilar @ belgisi bilan belgilanadi). Agar siz chiqish parametrlarini belgilashingiz kerak bo'lsa, parametr nomidan keyin OUTPUT kalit so'zini ko'rsating ( yoki qisqartirilgan OUT).

BEGIN…END blokida biz ma'lumotlarni qo'shish bo'yicha ko'rsatmani yozamiz, shuningdek protsedura oxirida SELECT iborasini yozamiz, shunda saqlangan protsedura yangisini hisobga olgan holda belgilangan toifadagi mahsulotlar haqida jadval ma'lumotlarini qaytaradi. faqat qo'shilgan mahsulot. Shuningdek, ushbu saqlangan protsedurada men kiruvchi parametrni qayta ishlashni qo'shdim, ya'ni bir nechta bo'sh joy tasodifiy kiritilgan holatlarning oldini olish uchun matn satrining boshida va oxirida qo'shimcha bo'shliqlarni olib tashlash.

Mana bu protsedura uchun kod Men ham bunga izoh berdim).

Protsedura yaratish CREATE PROCEDURE TestProcedure (--Kirish parametrlari @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BAŞLADI --Algoritmingizni amalga oshiradigan ko'rsatmalar --Kiruvchi parametrlarni qayta ishlash --Boshdagi qo'shimcha bo'shliqlarni olib tashlang va matn satri oxirida SET @MahsulotName = LTRIM(RTRIM(@MahsulotName)); --Yangi yozuv qo'shing INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @MahsulotName, @Price) --Qaytish ma'lumotlarini TANLASH * TestTable FROM CategoryId = @CategoryId QAYERGA O'TISH

T-SQL da saqlangan protsedurani bajarish - EXECUTE buyrug'i

Siz yuqorida aytib o'tganimdek, EXECUTE yoki EXEC buyrug'i yordamida saqlangan protsedurani ishga tushirishingiz mumkin. Kiruvchi parametrlar protseduralarga oddiygina sanab o'tish va protsedura nomidan keyin tegishli qiymatlarni berish orqali o'tkaziladi ( chiqish parametrlari uchun OUTPUT buyrug'ini ham ko'rsatishingiz kerak). Biroq, parametrlarning nomi ko'rsatilmasligi mumkin, ammo bu holda qiymatlarni ko'rsatish ketma-ketligiga rioya qilish kerak, ya'ni. kirish parametrlari aniqlangan tartibda qiymatlarni belgilang ( bu chiqish parametrlariga ham tegishli).

Standart qiymatlarga ega bo'lgan parametrlar belgilanmasligi mumkin, bular ixtiyoriy parametrlar deb ataladi.

Bu erda saqlangan protseduralarni ishga tushirishning bir necha xil, ammo ekvivalent usullari, xususan, bizning sinov protseduramiz.

1. Narxni ko'rsatmasdan protsedurani chaqiring EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test mahsuloti 1" --2. Biz EXEC TestProcedure @CategoryId = 1, @MahsulotName = "Sinov mahsuloti 2", @Price = 300 --3 deb belgilangan narx bilan protsedurani chaqiramiz. EXEC parametrlarining nomini ko'rsatmasdan protsedurani chaqiramiz TestProcedure 1, "Test elementi 3", 400

Saqlangan protsedurani T-SQL ga o'zgartirish - ALTER PROCEDURE bayonoti

Ko'rsatmalardan foydalanib, protsedura algoritmiga o'zgartirishlar kiritishingiz mumkin TARTIBINI O‘ZGARTIRISh. Boshqacha qilib aytganda, allaqachon mavjud protsedurani o'zgartirish uchun CREATE PROCEDURE o'rniga ALTER PROCEDURE yozishingiz va kerak bo'lganda hamma narsani o'zgartirishingiz kerak.

Aytaylik, test protseduramizga o'zgartirishlar kiritishimiz kerak, deylik @Price parametri, ya'ni. narx, biz uni majburiy qilib qo'yamiz, buning uchun biz standart qiymatni olib tashlaymiz, shuningdek, endi olingan ma'lumotlar to'plamini olishimiz shart emas deb tasavvur qilamiz, buning uchun biz saqlangan protseduradan SELECT iborasini olib tashlaymiz.

Protsedurani o'zgartiring ALTER PROCEDURE TestProcedure (--Kiruvchi parametrlar @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) BOSHLADI --Algoritmingizni amalga oshiradigan ko'rsatmalar --Kirish parametrlarini qayta ishlash --Bosh va oxirida qo'shimcha bo'shliqlarni olib tashlang matn satrlaridan SET @MahsulotName = LTRIM(RTRIM(@MahsulotName)); --Yangi yozuvni qo'shing INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @MahsulotName, @Price) TUGASH

T-SQL - DROP PROCEDURE bayonotida saqlangan protsedurani o'chirish

Agar kerak bo'lsa, saqlangan protsedurani o'chirishingiz mumkin, bu bayonot yordamida amalga oshiriladi TOSHLASH TARTIBI.

Masalan, biz yaratgan test protsedurasini o'chirib tashlaymiz.

DOPLASH TARTIBI TestProtsedurasi

Saqlangan protseduralarni o'chirishda shuni esda tutish kerakki, agar protseduraga boshqa protseduralar yoki SQL iboralari tomonidan havola qilingan bo'lsa, uni o'chirib tashlaganingizdan so'ng, ular xato bilan muvaffaqiyatsiz bo'ladi, chunki ular murojaat qilgan protsedura endi mavjud emas.

Menda hamma narsa bor, umid qilamanki, material siz uchun qiziqarli va foydali bo'ldi, xayr!

Saqlangan protseduralar tushunchasi aniqlangan. Parametrlar bilan saqlangan protseduralarni yaratish, o'zgartirish va ulardan foydalanish misollari keltirilgan. Kirish va chiqish parametrlarining ta'rifi berilgan. Saqlangan protseduralarni yaratish va chaqirishga misollar keltirilgan.

Saqlangan protsedura tushunchasi

Saqlangan protseduralar bir-biriga bog'langan SQL operatorlari guruhlari bo'lib, ulardan foydalanish dasturchining ishini osonlashtiradi va moslashuvchan qiladi, chunki bajarish uchun saqlangan protsedura ko'pincha alohida SQL iboralari ketma-ketligiga qaraganda ancha sodda. Saqlangan protseduralar - bu bir yoki bir nechta SQL operatorlari yoki funktsiyalaridan iborat bo'lgan va ma'lumotlar bazasida kompilyatsiya qilingan shaklda saqlanadigan buyruqlar to'plami. Ma'lumotlar bazasida bajarilishi saqlangan protseduralar Shaxsiy SQL bayonotlari o'rniga u foydalanuvchiga quyidagi afzalliklarni beradi:

• kerakli operatorlar allaqachon ma'lumotlar bazasida;
• hammasi sahnadan o'tdi tahlil qilish va bajariladigan formatda; oldin saqlangan protsedurani bajarish SQL Server buning uchun bajarish rejasini yaratadi, uni optimallashtiradi va kompilyatsiya qiladi;
• saqlangan protseduralar qo'llab-quvvatlash modulli dasturlash, chunki ular katta vazifalarni mustaqil, kichikroq va boshqarish oson qismlarga ajratish imkonini beradi;
• saqlangan protseduralar boshqalarga sabab bo'lishi mumkin saqlangan protseduralar va funktsiyalari;
• saqlangan protseduralar boshqa turdagi amaliy dasturlardan chaqirish mumkin;
• Qoida sifatida, saqlangan protseduralar alohida bayonotlar ketma-ketligiga qaraganda tezroq bajariladi;
• saqlangan protseduralar foydalanish osonroq: ular o'nlab va yuzlab buyruqlardan iborat bo'lishi mumkin, ammo ularni ishga tushirish uchun faqat kerakli nomni ko'rsatish kifoya. saqlangan protsedura. Bu mijozdan serverga yuborilgan so'rov hajmini va shuning uchun tarmoqdagi yukni kamaytirish imkonini beradi.

Protseduralarni ular bajariladigan joyda saqlash tarmoq orqali uzatiladigan ma'lumotlar hajmini kamaytiradi va umumiy tizim ish faoliyatini yaxshilaydi. Ilova saqlangan protseduralar dasturiy ta'minot tizimlariga texnik xizmat ko'rsatish va ularga o'zgartirishlar kiritishni soddalashtiradi. Odatda, qoidalar va ma'lumotlarni qayta ishlash algoritmlari ko'rinishidagi barcha yaxlitlik cheklovlari ma'lumotlar bazasi serverida amalga oshiriladi va yakuniy dastur uchun to'plam sifatida mavjud. saqlangan protseduralar, ma'lumotlarni qayta ishlash interfeysini ifodalaydi. Ma'lumotlarning yaxlitligini ta'minlash uchun, shuningdek, xavfsizlik maqsadlarida dastur odatda ma'lumotlarga to'g'ridan-to'g'ri kirish huquqiga ega emas - ular bilan barcha ishlar u yoki bu qo'ng'iroq orqali amalga oshiriladi. saqlangan protseduralar.

Ushbu yondashuv ma'lumotlarni qayta ishlash algoritmlarini o'zgartirishni juda oson qiladi, ular darhol barcha tarmoq foydalanuvchilari uchun mavjud bo'ladi va dasturning o'ziga o'zgartirishlar kiritmasdan tizimni kengaytirish imkoniyatini beradi: o'zgartirish kifoya qiladi. saqlangan protsedura ma'lumotlar bazasi serverida. Ishlab chiquvchiga ilovani qayta kompilyatsiya qilish, uning nusxalarini yaratish, shuningdek, foydalanuvchilarga yangi versiya bilan ishlash zarurligi haqida ko'rsatma berish shart emas. Foydalanuvchilar hatto tizimga o'zgartirishlar kiritilganidan bexabar bo'lishlari mumkin.

Saqlangan protseduralar jadvallar yoki boshqa ma'lumotlar bazasi ob'ektlaridan mustaqil ravishda mavjud. Ular mijoz dasturi tomonidan chaqiriladi, boshqa saqlangan protsedura yoki tetik. Ishlab chiquvchi kirish huquqlarini boshqarishi mumkin saqlangan protsedura, uni amalga oshirishga ruxsat berish yoki taqiqlash. Kodni o'zgartiring saqlangan protsedura faqat uning egasi yoki sobit maʼlumotlar bazasi rolining aʼzosi tomonidan ruxsat etiladi. Agar kerak bo'lsa, siz unga egalik huquqini bir foydalanuvchidan boshqasiga o'tkazishingiz mumkin.

MS SQL Server muhitida saqlanadigan protseduralar

SQL Server bilan ishlashda foydalanuvchilar muayyan harakatlarni amalga oshiradigan o'zlarining protseduralarini yaratishlari mumkin. Saqlangan protseduralar to'liq ma'lumotlar bazasi ob'ektlari bo'lib, shuning uchun ularning har biri ma'lum bir ma'lumotlar bazasida saqlanadi. To'g'ridan-to'g'ri qo'ng'iroq saqlangan protsedura faqat protsedura joylashgan ma'lumotlar bazasi kontekstida bajarilgan taqdirdagina mumkin.

Saqlangan protseduralar turlari

SQL Serverda bir nechta turlar mavjud saqlangan protseduralar.

• Tizimli saqlangan protseduralar turli ma'muriy harakatlarni bajarish uchun mo'ljallangan. Deyarli barcha server ma'muriyati harakatlari ularning yordami bilan amalga oshiriladi. Aytishimiz mumkinki, tizim saqlangan protseduralar Tizim jadvallari bilan ishlashni ta'minlovchi interfeys bo'lib, u oxir-oqibatda foydalanuvchi va tizim ma'lumotlar bazalarining tizim jadvallaridan ma'lumotlarni o'zgartirish, qo'shish, o'chirish va olish bilan bog'liq. Tizimli saqlangan protseduralar sp_ prefiksi bilan biriktiriladi, tizim ma'lumotlar bazasida saqlanadi va boshqa har qanday ma'lumotlar bazasi kontekstida chaqirilishi mumkin.
• Maxsus saqlangan protseduralar muayyan harakatlarni amalga oshirish. Saqlangan protseduralar- to'liq ma'lumotlar bazasi ob'ekti. Buning natijasida har bir saqlangan protsedura muayyan ma'lumotlar bazasida joylashgan bo'lib, u amalga oshiriladi.
• Vaqtinchalik saqlangan protseduralar faqat qisqa vaqt davomida mavjud bo'ladi, shundan so'ng ular server tomonidan avtomatik ravishda yo'q qilinadi. Ular mahalliy va global bo'linadi. Mahalliy vaqtinchalik saqlangan protseduralar faqat ular yaratilgan aloqadan chaqirish mumkin. Bunday protsedurani yaratishda unga bitta # belgidan boshlanadigan nom berilishi kerak. Barcha vaqtinchalik ob'ektlar singari, saqlangan protseduralar foydalanuvchi serverni uzganda, qayta ishga tushirganda yoki to'xtatganda avtomatik ravishda o'chiriladi. Global vaqtinchalik saqlangan protseduralar bir xil protseduraga ega bo'lgan har qanday server ulanishlari uchun mavjud. Uni aniqlash uchun unga ## belgilaridan boshlanadigan nom berish kifoya. Ushbu protseduralar server qayta ishga tushirilganda yoki to'xtatilganda yoki ular kontekstda yaratilgan ulanish yopilganda o'chiriladi.

Saqlangan protseduralarni yaratish, o'zgartirish va o'chirish

Yaratilish saqlangan protsedura quyidagi vazifalarni hal qilishni nazarda tutadi:

• turini aniqlash saqlangan protsedura: vaqtinchalik yoki odatiy. Bundan tashqari, siz o'zingizning tizimingizni yaratishingiz mumkin saqlangan protsedura, unga sp_ prefiksi bilan nom berish va uni tizim ma'lumotlar bazasiga joylashtirish. Bunday protsedura mahalliy serverdagi har qanday ma'lumotlar bazasi kontekstida mavjud bo'ladi;
• kirishni rejalashtirish. Yaratish paytida saqlangan protsedura shuni yodda tutingki, u ma'lumotlar bazasi ob'ektlariga uni yaratgan foydalanuvchi bilan bir xil kirish huquqiga ega bo'ladi;
• ta'rifi saqlangan protsedura parametrlari. Ko'pgina dasturlash tillariga kiritilgan protseduralar kabi, saqlangan protseduralar kirish va chiqish parametrlariga ega bo'lishi mumkin;
• kodni ishlab chiqish saqlangan protsedura. Protsedura kodi har qanday SQL buyruqlar ketma-ketligini o'z ichiga olishi mumkin, shu jumladan boshqalarga qo'ng'iroq qilish. saqlangan protseduralar.

Yangisini yaratish va mavjudini o'zgartirish saqlangan protsedura quyidagi buyruq bilan amalga oshiriladi:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name datatype ) [=default] ][,...n] AS sql_statement [...n]

Ushbu buyruqning parametrlarini ko'rib chiqing.

sp_ , #, ## prefikslari yordamida yaratilgan protsedura tizim yoki vaqtinchalik protsedura sifatida belgilanishi mumkin. Buyruqning sintaksisidan ko'rinib turibdiki, yaratilgan protsedura tegishli bo'lgan egasining nomini, shuningdek, uni joylashtirish kerak bo'lgan ma'lumotlar bazasi nomini ko'rsatishga yo'l qo'yilmaydi. Shunday qilib, yaratilganlarni joylashtirish uchun saqlangan protsedura ma'lum bir ma'lumotlar bazasida siz ushbu ma'lumotlar bazasi kontekstida CREATE PROCEDURE buyrug'ini ishga tushirishingiz kerak. Tanadan ishlov berilganda saqlangan protsedura Qisqartirilgan nomlar bir xil ma'lumotlar bazasidagi ob'ektlar uchun ishlatilishi mumkin, ya'ni ma'lumotlar bazasi nomini ko'rsatmasdan. Boshqa ma'lumotlar bazalarida joylashgan ob'ektlarga murojaat qilmoqchi bo'lsangiz, ma'lumotlar bazasi nomini ko'rsatish majburiydir.

Ismdagi raqam identifikatsiya raqamidir saqlangan protsedura, bu uni protseduralar guruhida noyob tarzda belgilaydi. Jarayonlarni boshqarish qulayligi uchun mantiqan bir xil turdagi saqlangan protseduralar bir xil ism, lekin turli identifikatsiya raqamlarini berish orqali guruhlash mumkin.

Yaratilganda kirish va chiqish ma'lumotlarini uzatish uchun saqlangan protsedura parametrlardan foydalanish mumkin, ularning nomlari mahalliy o'zgaruvchilar nomlari kabi @ belgisi bilan boshlanishi kerak. Bir saqlangan protsedura Vergul bilan ajratilgan bir nechta variantni belgilashingiz mumkin. Protsedura tanasi nomlari protsedura parametrlari nomlari bilan bir xil bo'lgan mahalliy o'zgaruvchilardan foydalanmasligi kerak.

Tegishli ma'lumotlar turini aniqlash uchun saqlangan protsedura parametri, har qanday SQL ma'lumotlar turi yaxshi, shu jumladan foydalanuvchi tomonidan belgilangan. Biroq, CURSOR ma'lumotlar turi faqat sifatida ishlatilishi mumkin chiqish parametri saqlangan protsedura, ya'ni. OUTPUT kalit so'zi bilan.

OUTPUT kalit so'zining mavjudligi mos keladigan parametr ma'lumotlarni qaytarish uchun mo'ljallanganligini bildiradi saqlangan protsedura. Biroq, bu parametr qiymatlarni o'tkazish uchun mos emas degani emas saqlangan protsedura. OUTPUT kalit so'zini belgilash serverdan chiqishni buyuradi saqlangan protsedura parametrning qiymati sifatida protsedura chaqirilganda ko'rsatilgan mahalliy o'zgaruvchiga parametrning joriy qiymatini belgilash. E'tibor bering, OUTPUT kalit so'zini ko'rsatganda, protsedurani chaqirishda mos keladigan parametrning qiymati faqat mahalliy o'zgaruvchi yordamida o'rnatilishi mumkin. Oddiy parametrlar uchun ruxsat etilgan har qanday ifoda yoki konstantalarga ruxsat berilmaydi.

VARYING kalit so'zi CURSOR tipidagi OUTPUT parametri bilan birgalikda ishlatiladi. Buni belgilaydi chiqish parametri hosil bo'lgan to'plam bo'ladi.

DEFAULT kalit so'zi mos keladigan qiymatdir standart sozlama. Shunday qilib, protsedurani chaqirishda siz mos keladigan parametrning qiymatini aniq ko'rsata olmaysiz.

Server so'rovni bajarish rejasini va tuzilgan kodni keshda saqlaganligi sababli, protsedura keyingi safar chaqirilganda allaqachon tayyorlangan qiymatlardan foydalaniladi. Biroq, ba'zi hollarda protsedura kodini qayta kompilyatsiya qilish kerak. RECOMPILE kalit so'zini ko'rsatish tizimga ijro rejasini yaratishni buyuradi saqlangan protsedura har safar chaqiriladi.

REPLICATION UCHUN parametri ma’lumotlarni replikatsiya qilishda va yaratilganlarni o‘z ichiga olganda talab qilinadi saqlangan protsedura nashrda maqola sifatida.

ENCRYPTION kalit so'zi serverga kodni shifrlashni buyuradi saqlangan protsedura, bu ishni amalga oshiradigan mualliflik huquqi algoritmlaridan foydalanishdan himoyani ta'minlashi mumkin saqlangan protsedura.

AS kalit so'zi haqiqiy tananing boshida joylashtiriladi saqlangan protsedura, ya'ni. SQL buyruqlar to'plami, ular yordamida u yoki bu harakat amalga oshiriladi. Protsedura tanasida deyarli barcha SQL buyruqlaridan foydalanish mumkin, tranzaksiyalarni e'lon qilish, qulflarni o'rnatish va boshqalarni chaqirish mumkin. saqlangan protseduralar. dan chiqish saqlangan protsedura RETURN buyrug'i bilan amalga oshirilishi mumkin.

Saqlangan protsedurani o'chirish buyrug'i bilan amalga oshiriladi:

TO'CHIRISH TARTIBI (protsedura_nomi) [,...n]

Saqlangan protsedurani bajarish

Uchun saqlangan protsedurani bajarish buyruq ishlatiladi:

[[ EXEC [ UTE] protsedura_nomi [;raqam] [[@parametr_nomi=](qiymat | @oʻzgaruvchan_nom) |][,...n]

Agar qo'ng'iroq bo'lsa saqlangan protsedura paketdagi yagona buyruq emas, u holda EXECUTE buyrug'ining mavjudligi talab qilinadi. Bundan tashqari, ushbu buyruq boshqa protsedura yoki trigger tanasidan protsedurani chaqirish uchun talab qilinadi.

Protsedura chaqiruvida OUTPUT kalit so'zidan foydalanishga faqat e'lon qilingan parametrlar uchun ruxsat beriladi. protsedura yaratish OUTPUT kalit so'zi bilan.

Protsedura chaqiruvi uchun DEFAULT kalit so'zi belgilangan bo'lsa, DEFAULT kalit so'zidan foydalaniladi. standart qiymat. Tabiiyki, belgilangan DEFAULT so'ziga faqat u belgilangan parametrlar uchun ruxsat beriladi standart qiymat.

EXECUTE buyrug'ining sintaksisidan protsedurani chaqirishda parametr nomlarini o'tkazib yuborish mumkinligini ko'rish mumkin. Biroq, bu holda, foydalanuvchi parametrlar uchun qiymatlarni ular ro'yxatga olingan tartibda ko'rsatishi kerak. protsedura yaratish. Parametrga tayinlash standart qiymat, sanab o'tish mumkin bo'lmaganda uni o'tkazib yuborish kifoya. Agar parametrlarni o'tkazib yuborish kerak bo'lsa standart qiymat, qo'ng'iroq qilishda parametr nomlarini aniq ko'rsatish kifoya saqlangan protsedura. Bundan tashqari, shu tarzda siz parametrlarni va ularning qiymatlarini istalgan tartibda ro'yxatlashingiz mumkin.

E'tibor bering, protsedura chaqirilganda, qiymatlari bo'lgan parametr nomlari yoki faqat parametr nomisiz qiymatlar ko'rsatiladi. Ularning kombinatsiyasiga yo'l qo'yilmaydi.

12.1-misol. Parametrlarsiz protsedura. Ivanov tomonidan sotib olingan tovarlarning nomlari va narxlarini olish tartibini ishlab chiqish.

PROC my_proc1 NI TANLANGAN QO'LLANISH mahsulot.Nomi, Narx*Savdo.Miqdori, Mijoz.Familiyasi Mijoz ICHKI JOIN (Item.ItemId=Trade.ItemId) ON Customer.CustomerCode.Code. Mijoz .Lastname='Ivanov' 12.1-misol. Ivanov tomonidan sotib olingan tovarlarning nomlari va narxlarini olish tartibi.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

EXEC my_proc1 yoki my_proc1

Jarayon ma'lumotlar to'plamini qaytaradi.

12.2-misol. Parametrlarsiz protsedura. Birinchi navli buyumning narxini 10% ga tushirish tartibini yarating.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

EXEC my_proc2 yoki my_proc2

Jarayon hech qanday ma'lumotni qaytarmaydi.

12.3-misol. Kirish parametri bilan protsedura. Berilgan mijoz tomonidan sotib olingan narsalarning nomlari va narxlarini olish tartibini yarating.

PROC my_proc3 @k VARCHAR(20) NI TANLASH SIFATIDA Element.Nomi, Narxi*Savdo.Miqdori, Xaridor.Familiyasi Mijoz ICHKI QOʻSHILIShDAN (Item.ItemID=Trade.ItemID.Custo) ONLIK OLIB PROC YARATING =Deal.CustomerCode WHERE Customer.LastName=@k 12.3-misol. Muayyan mijoz tomonidan sotib olingan narsalarning nomlari va narxlarini olish tartibi.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

EXEC my_proc3 "Ivanov" yoki my_proc3 @k="Ivanov"

12.4-misol.. Berilgan turdagi mahsulot narxini belgilangan % ga mos ravishda pasaytirish tartibini tuzing.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

EXEC my_proc4 "Waffle", 0,05 yoki EXEC my_proc4 @t="Vaffle", @p=0,05

12.5-misol. Kirish parametrlari bilan protsedura va standart qiymatlar. Berilgan turdagi mahsulot narxini belgilangan % ga mos ravishda pasaytirish tartibini tuzing.

PROC CREATE my_proc5 @t VARCHAR(20)='Candy`, @p FLOAT=0,1 YANGILANGAN SO'Z SO'YIB Narx=Narx*(1-@p) QERDA Turi=@t 12.5-misol. Kirish parametrlari va standart qiymatlari bilan protsedura. Berilgan turdagi mahsulot narxini belgilangan % ga mos ravishda pasaytirish tartibini tuzing.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

EXEC my_proc5 "Waffle", 0,05 yoki EXEC my_proc5 @t="Vaffle", @p=0,05 yoki EXEC my_proc5 @p=0,05

Bunday holda, shirinliklarning narxi pasayadi (turning qiymati protsedurani chaqirganda ko'rsatilmaydi va sukut bo'yicha olinadi).

Ikkinchi holda, protsedurani chaqirishda ikkala parametr ham (turi ham, foiz ham) ko'rsatilmaydi, ularning qiymatlari sukut bo'yicha olinadi.

12.6-misol. Kirish va chiqish parametrlari bilan ishlash tartibi. Muayyan oyda sotilgan tovarlarning umumiy qiymatini aniqlash tartibini yarating.

PROC CREATE my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Item.Nrice*Trade.Quantity) FROM ICHKI JOIN Savdo ON Item.ItemID=Trade.ItemID GROUP BY BY (Savdo.Sana) Oy(Oy) Bitim.Sana)=@m 12.6-misol. Kirish va chiqish parametrlari bilan ishlash tartibi. Muayyan oyda sotilgan tovarlarning umumiy qiymatini aniqlash tartibini yarating.

Uchun protseduraga chaqirish buyruqlardan foydalanish mumkin:

@st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st E'lon qiling

Ushbu buyruqlar bloki yanvar oyida sotilgan tovarlarning narxini aniqlash imkonini beradi ( kirish parametri oy 1 ga o'rnatiladi).

Berilgan xodim ishlaydigan firma tomonidan sotib olingan tovarlarning umumiy miqdorini aniqlash tartibini yarating.

Birinchidan, biz xodim ishlaydigan kompaniyani aniqlash tartibini ishlab chiqamiz.

12.7-misol. Foydalanish ichki tartiblar. Berilgan xodim ishlaydigan firma tomonidan sotib olingan tovarlarning umumiy miqdorini aniqlash tartibini yarating.

Keyin bizni qiziqtirgan firma tomonidan sotib olingan tovarlarning umumiy miqdorini hisoblaydigan tartib yaratamiz.

PROC YARATING my_proc8 @fam VARCHAR(20), @kol KIRISH ISHLATISHNI E'lon qilish kabi @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Savdo.Miqdor) Mijozdan ICHKI JOIN Trade.Clide=Client. Deal.ClientCode Client.Company BY GURUH BO'LGAN Client.Company=@firm 12.7-misol. Berilgan xodim ishlaydigan firma tomonidan sotib olingan tovarlarning umumiy miqdorini aniqlash tartibini yarating.

Jarayon buyruq yordamida chaqiriladi:

DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k

Saqlangan protsedura SQL tili va protsessual kengaytmalar yordamida yaratilgan Transact-SQL bayonotining maxsus turi. Paket va saqlangan protsedura o'rtasidagi asosiy farq shundaki, ikkinchisi ma'lumotlar bazasi ob'ekti sifatida saqlanadi. Boshqacha qilib aytganda, takrorlanuvchi vazifalarning ishlashi va izchilligini yaxshilash uchun saqlangan protseduralar server tomonida saqlanadi.

Ma'lumotlar bazasi mexanizmi saqlangan protseduralar va tizim protseduralarini qo'llab-quvvatlaydi. Saqlangan protseduralar ma'lumotlar bazasining barcha boshqa ob'ektlari bilan bir xil tarzda yaratiladi, ya'ni. DDL tilidan foydalanish. Tizim protseduralari Ma'lumotlar bazasi mexanizmi tomonidan taqdim etiladi va tizim katalogidagi ma'lumotlarga kirish va o'zgartirish uchun ishlatilishi mumkin.

Saqlangan protsedurani yaratishda siz parametrlarning ixtiyoriy ro'yxatini belgilashingiz mumkin. Shunday qilib, protsedura har safar chaqirilganda tegishli dalillarni oladi. Saqlangan protseduralar foydalanuvchi tomonidan aniqlangan ma'lumotlarni o'z ichiga olgan qiymatni yoki xato bo'lsa, tegishli xato xabarini qaytarishi mumkin.

Saqlangan protsedura ma'lumotlar bazasida ob'ekt sifatida saqlanishidan oldin oldindan kompilyatsiya qilinadi. Jarayonning oldindan tuzilgan shakli ma'lumotlar bazasida saqlanadi va har safar chaqirilganda ishlatiladi. Saqlangan protseduralarning bu xususiyati protseduralarni qayta kompilyatsiya qilishni (deyarli barcha holatlarda) yo'q qilish va tegishli ish faoliyatini yaxshilashning muhim afzalliklarini ta'minlaydi. Saqlangan protseduralarning bu xususiyati ma'lumotlar bazasi tizimi va ilovalar o'rtasida almashinadigan ma'lumotlar hajmiga ham ijobiy ta'sir ko'rsatadi. Xususan, bir necha ming baytlik saqlangan protseduraga qo'ng'iroq qilish uchun 50 baytdan kamroq vaqt kerak bo'lishi mumkin. Agar bir nechta foydalanuvchilar saqlangan protseduralar yordamida takroriy vazifalarni bajarsa, bu tejashning umumiy ta'siri sezilarli bo'lishi mumkin.

Saqlangan protseduralar quyidagi maqsadlarda ham qo'llanilishi mumkin:

ma'lumotlar bazasi jadvallari bilan harakatlar haqida jurnallar jurnalini yaratish.

Saqlangan protseduralardan foydalanish foydalanuvchilarga turli xil kirish imtiyozlarini beradigan GRANT va REVOKE bayonotlaridan foydalanish bilan ta'minlangan xavfsizlikdan ancha yuqori darajadagi xavfsizlik nazoratini ta'minlaydi. Bu mumkin, chunki saqlanadigan protsedurani bajarish uchun ruxsat keyingi bo'limda tavsiflanganidek, saqlangan protsedura tarkibidagi ob'ektlarni o'zgartirishga ruxsatdan mustaqildir.

Jadvallarni yozish va/yoki o'qishni qayd qiluvchi saqlangan protseduralar ma'lumotlar bazasi uchun qo'shimcha xavfsizlikni ta'minlaydi. Bunday protseduralardan foydalangan holda ma'lumotlar bazasi ma'muri foydalanuvchilar yoki ilovalar tomonidan ma'lumotlar bazasiga kiritilgan o'zgartirishlarni kuzatishi mumkin.

Saqlangan protseduralarni yaratish va bajarish

Saqlangan protseduralar bayonot yordamida yaratiladi TARTIBI YARATISH, quyidagi sintaksisga ega:

PROC proc_name YARASH [((@param1) type1 [ VARYING] [= default1] )] (, …) AS ommaviy | EXTERNAL NAME usuli_nomi Sintaksis qoidalari

schema_name parametri yaratilgan saqlangan protsedura egasi tomonidan tayinlangan sxema nomini belgilaydi. proc_name parametri saqlangan protsedura nomini belgilaydi. @param1 parametri protsedura parametri (rasmiy argument), uning ma'lumotlar turi type1 parametri bilan belgilanadi. Lokal o'zgaruvchilar paket ichida mahalliy bo'lganidek, protsedura parametrlari protsedura ichida mahalliy bo'ladi. Jarayon parametrlari - bu qo'ng'iroq qiluvchi tomonidan undan foydalanish tartibiga o'tkaziladigan qiymatlar. default1 parametri mos keladigan protsedura parametri uchun standart qiymatni belgilaydi. (Standart qiymat NULL ham bo'lishi mumkin.)

OUTPUT opsiyasi protsedura parametrining qaytarilishini va undan qiymatni saqlangan protseduradan chaqiruvchi protsedura yoki tizimga qaytarish uchun foydalanish mumkinligini bildiradi.

Yuqorida aytib o'tilganidek, protseduraning oldindan tuzilgan shakli ma'lumotlar bazasida saqlanadi va har safar chaqirilganda ishlatiladi. Agar biron sababga ko'ra saqlangan protsedura har safar chaqirilganda kompilyatsiya qilinishi kerak bo'lsa, protsedura deklaratsiyasida RECOMPILE BILAN variant. WITH RECOMPILE opsiyasidan foydalanish saqlangan protseduralarning eng muhim afzalliklaridan birini inkor etadi: bitta kompilyatsiya tufayli unumdorlikni oshirish. Shuning uchun, WITH RECOMPILE opsiyasi faqat saqlanadigan protsedura tomonidan ishlatiladigan ma'lumotlar bazasi ob'ektlarida tez-tez o'zgarishlar bo'lsa ishlatilishi kerak.

Taklif sifatida ISHLATISH saqlangan protsedura chaqirilgandan keyin bajarilishi kerak bo'lgan xavfsizlik kontekstini belgilaydi. Ushbu kontekstni o'rnatish orqali Ma'lumotlar bazasi mexanizmi ushbu saqlangan protsedura tomonidan havola qilingan ob'ektlarga kirish ruxsatlarini tekshirish uchun foydalanuvchi hisoblarini tanlashni boshqarishi mumkin.

Odatiy bo'lib, faqat sysadmin sobit server rolining a'zolari va db_owner yoki db_ddladmin sobit ma'lumotlar bazasi roli CREATE PROCEDURE bayonotidan foydalanishi mumkin. Biroq, ushbu rollarning a'zolari ushbu huquqni boshqa foydalanuvchilarga ko'rsatma yordamida belgilashlari mumkin GRANT BERISH TARTIBI.

Quyidagi misol loyiha jadvali bilan ishlash uchun oddiy saqlangan protsedurani qanday yaratishni ko'rsatadi:

SampleDb dan foydalanish; O'TISH TARTIBINI YARATING O'sishBudjeti (@foiz INT=5) YANGILANGAN Loyiha to'plami Byudjet = Byudjet + Byudjet * @foiz/100;

Yuqorida aytib o'tilganidek, ikkita paketni ajratish uchun ishlatiladi GO ko'rsatmasi. CREATE PROCEDURE iborasini bitta to'plamdagi boshqa Transact-SQL bayonotlari bilan birlashtirib bo'lmaydi. IncreaseBudget saqlangan protsedurasi barcha loyihalar uchun byudjetlarni @percent parametri bilan belgilangan ma'lum foizga oshiradi. Protsedura shuningdek, protsedurani bajarish paytida ushbu argument mavjud bo'lmasa, qo'llaniladigan standart foiz qiymatini (5) belgilaydi.

Saqlangan protseduralar mavjud bo'lmagan jadvallarga kirishi mumkin. Bu xususiyat avval tegishli jadvallarni yaratmasdan va hatto maqsad serverga ulanmasdan protsedura kodini disk raskadrovka qilish imkonini beradi.

Har doim joriy ma'lumotlar bazasida saqlanadigan asosiy saqlanadigan protseduralardan farqli o'laroq, vaqtinchalik saqlanadigan protseduralarni yaratish mumkin, ular doimo tempdb vaqtinchalik tizim ma'lumotlar bazasiga joylashtiriladi. Vaqtinchalik saqlanadigan protseduralarni yaratishning sabablaridan biri ma'lumotlar bazasiga ulanishda ma'lum bir guruh bayonotlarining takroriy bajarilishini oldini olish bo'lishi mumkin. Mahalliy yoki global vaqtinchalik tartiblarni yaratishingiz mumkin. Buning uchun lokal protsedura nomi bitta # belgi (#proc_name), global protsedura nomi esa qo'sh belgi (##proc_name) bilan ko'rsatiladi.

Mahalliy vaqtinchalik saqlanadigan protsedura faqat uni yaratgan foydalanuvchi tomonidan va faqat u yaratilgan ma'lumotlar bazasiga ulanish vaqtida bajarilishi mumkin. Global vaqtinchalik protsedura barcha foydalanuvchilar tomonidan bajarilishi mumkin, lekin faqat u ishlayotgan oxirgi ulanish (odatda protsedura yaratuvchisining ulanishi) to'xtatilgunga qadar.

Saqlangan protseduraning hayot aylanishi ikki bosqichdan iborat: uni yaratish va bajarish. Har bir protsedura bir marta yaratiladi va ko'p marta bajariladi. Saqlangan protsedura tomonidan bajariladi EXECUTE bayonotlari protseduraga egalik qiluvchi yoki protseduraga kirish uchun EXECUTE huquqiga ega foydalanuvchi. EXECUTE iborasi quyidagi sintaksisga ega:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] qiymat | [@parameter1=] @variable ] | DEFAULT).. Sintaksis qoidalari

return_status parametridan tashqari, EXECUTE operatorining barcha parametrlari bir xil nomdagi CREATE PROCEDURE operatorining parametrlari bilan bir xil mantiqiy qiymatga ega. return_status parametri protseduraning qaytish holatini saqlaydigan butun o'zgaruvchini belgilaydi. Qiymat parametrga doimiy (qiymat) yoki mahalliy o'zgaruvchi (@variable) yordamida tayinlanishi mumkin. Nomlangan parametr qiymatlarining tartibi muhim emas, lekin nomsiz parametr qiymatlari CREATE PROCEDURE bayonotida belgilangan tartibda kiritilishi kerak.

DEFAULT bandi protsedura ta'rifida ko'rsatilgan protsedura parametri uchun standart qiymatlarni beradi. Agar protsedura standart qiymat aniqlanmagan va parametr etishmayotgan yoki DEFAULT kalit so'zi ko'rsatilgan parametr uchun qiymatni kutsa, xatolik yuzaga keladi.

EXECUTE operatori to'plamdagi birinchi bayonot bo'lsa, EXECUTE kalit so'zini o'tkazib yuborish mumkin. Biroq, bu so'zni har bir paketga kiritish xavfsizroqdir. EXECUTE operatoridan foydalanish quyidagi misolda ko'rsatilgan:

SampleDb dan foydalanish; IncreaseBudget 10ni ISHLATISH;

Ushbu misoldagi EXECUTE operatori IncreaseBudget saqlanadigan protsedurani bajaradi, bu esa barcha loyihalar byudjetini 10% ga oshiradi.

Quyidagi misol Xodim va Ishlar_jadvallarida ma'lumotlarni qayta ishlash uchun saqlangan protsedurani qanday yaratishni ko'rsatadi:

Misoldagi ModifyEmpId protsedurasi saqlangan protseduralardan havolalar yaxlitligi jarayonining bir qismi sifatida foydalanishni ko'rsatadi (bu holda Xodim va Works_on jadvallari o'rtasida). Bunday saqlangan protsedura aslida referent yaxlitligini ta'minlaydigan trigger ta'rifi ichida ishlatilishi mumkin.

Quyidagi misol saqlangan protsedurada OUTPUT bandidan foydalanishni ko'rsatadi:

Ushbu saqlangan protsedura quyidagi iboralar yordamida bajarilishi mumkin:

E'lon qiling @quantityDeleteEmployee INT; ISHLATISH DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Xodimlar o'chirildi: " + convert(nvarchar(30), @quantityDeleteEmployee);

Ushbu protsedura @empId personal raqamiga ega bo'lgan xodim ishlayotgan loyihalar sonini hisoblaydi va natijada olingan qiymatni ©counter parametriga belgilaydi. "Xodimlar" va "Ishlar_on" jadvallaridan berilgan xodimlar soni uchun barcha qatorlar o'chirilgandan so'ng, hisoblangan qiymat @miqdoriDeleteEmployee o'zgaruvchisiga tayinlanadi.

Parametr qiymati faqat OUTPUT opsiyasi belgilangan bo'lsa, chaqiruv protsedurasiga qaytariladi. Yuqoridagi misolda DeleteEmployee protsedurasi @counter parametrini chaqiruvchi protseduraga o'tkazadi, shuning uchun saqlangan protsedura qiymatni tizimga qaytaradi. Shuning uchun @counter parametri protsedurani e'lon qilishda OUTPUT variantida ham, uni chaqirishda EXECUTE operatorida ham ko'rsatilishi kerak.

WITH RESULTS SETS EXECUTE operatorining bandi

SQL Server 2012 da EXECUTE operatori teriladi WITH RESULTS SETS bandi Muayyan sharoitlarda saqlangan protsedura natijalari to'plamining shaklini o'zgartirishi mumkin.

Quyidagi ikkita misol bu jumlani tushuntirishga yordam beradi. Birinchi misol, WITH RESULTS SETS bandi o'tkazib yuborilganda natija qanday ko'rinishini ko'rsatadigan kirish misolidir:

EmployeesInDept protsedurasi - bu ma'lum bir bo'limda ishlaydigan barcha xodimlarning shaxsiy raqamlari va familiyalarini ko'rsatadigan oddiy protsedura. Bo'lim raqami protsedura parametridir va protsedurani chaqirishda ko'rsatilishi kerak. Ushbu protsedurani bajarish, sarlavhalari ma'lumotlar bazasi jadvalidagi mos keladigan ustunlar nomlariga mos keladigan ikkita ustunli jadvalni chiqaradi, ya'ni. id va familiya. Natija ustunlari sarlavhalarini (shuningdek, ularning ma'lumotlar turini) o'zgartirish uchun SQL Server 2012 yangi WITH RESULTS SETS bandidan foydalanadi. Ushbu bandning qo'llanilishi quyidagi misolda ko'rsatilgan:

SampleDb dan foydalanish; EXEC EmployeesInDept "d1" NATIJALAR TO'PLAMLARI (( INT NOT NULL, [Familiya] CHAR(20) NO NULL));

Shu tarzda chaqirilgan saqlangan protsedurani bajarish natijasi quyidagicha bo'ladi:

Ko'rib turganingizdek, EXECUTE operatorida WITH RESULT SETS bandidan foydalangan holda saqlangan protsedurani ishga tushirish protsedura tomonidan ishlab chiqarilgan natijalar to'plami ustunlarining nomlari va ma'lumotlar turini o'zgartirishga imkon beradi. Shunday qilib, ushbu yangi funksiya saqlangan protseduralarni bajarish va ularning natijalarini yangi jadvalga joylashtirishda ko'proq moslashuvchanlikni ta'minlaydi.

Saqlangan protseduralar tarkibini o'zgartirish

Ma'lumotlar bazasi mexanizmi ham bayonotni qo'llab-quvvatlaydi TARTIBINI O‘ZGARTIRISh saqlangan protseduralar tuzilishini o'zgartirish uchun. ALTER PROCEDURE iborasi odatda protsedura ichidagi Transact-SQL iboralarini o'zgartirish uchun ishlatiladi. ALTER PROCEDURE operatorining barcha parametrlari xuddi shu nomdagi CREATE PROCEDURE operatorining parametrlari bilan bir xil ma’noga ega. Ushbu bayonotdan foydalanishning asosiy maqsadi mavjud saqlangan protsedura ruxsatnomalarini bekor qilishni oldini olishdir.

Ma'lumotlar bazasi mexanizmi qo'llab-quvvatlaydi CURSOR maʼlumotlar turi. Ushbu ma'lumotlar turi saqlangan protseduralarda kursorlarni e'lon qilish uchun ishlatiladi. Kursor so'rov natijalarini (odatda qatorlar to'plami) saqlash va foydalanuvchilarga ushbu natijani satr bo'yicha ko'rsatishga ruxsat berish uchun foydalaniladigan dasturlash konstruktsiyasidir.

Bir yoki bir nechta saqlangan protseduralarni o'chirish uchun foydalaning DROP PROCEDURE bayonoti. Faqat saqlangan protsedura egasi yoki db_owner va sysadmin o'zgarmas rollari a'zolari saqlangan protsedurani o'chirib tashlashi mumkin.

Saqlangan protseduralar va umumiy til ish vaqti

SQL Server Common Language Runtime (CLR) ni qo'llab-quvvatlaydi, bu sizga C# va Visual Basic-dan foydalangan holda turli xil ma'lumotlar bazasi ob'ektlarini (saqlanuvchi protseduralar, foydalanuvchi tomonidan belgilangan funktsiyalar, triggerlar, foydalanuvchi tomonidan aniqlangan agregatlar va foydalanuvchi tomonidan belgilangan ma'lumotlar turlari) ishlab chiqish imkonini beradi. Umumiy tilning ishlash vaqti ushbu ob'ektlarni umumiy ish vaqti tizimi yordamida bajarishga imkon beradi.

Umumiy tilning ishlash vaqti parametr orqali yoqiladi va o'chiriladi clr_enabled tizim protsedurasi sp_configure, bu ko'rsatma bilan bajarish uchun ishga tushiriladi QAYTA SOZLASH. Quyidagi misol sp_configure tizim protsedurasi yordamida umumiy til ish vaqtini qanday yoqishingiz mumkinligini ko'rsatadi:

SampleDb dan foydalanish; EXEC sp_configure "clr_enabled", 1 QAYTA SOZLASH

CLR yordamida protsedurani yaratish, kompilyatsiya qilish va saqlash quyidagi ketma-ketlikni talab qiladi:

C# yoki Visual Basic da saqlangan protsedura yarating va keyin tegishli kompilyator yordamida uni kompilyatsiya qiling.

Yo'riqnomadan foydalanish ASSAMBLY YARATING, tegishli bajariladigan faylni yarating.

EXECUTE operatori yordamida protsedurani bajaring.

Quyidagi rasmda oldindan belgilangan bosqichlarning grafik diagrammasi ko'rsatilgan. Quyida ushbu jarayonning batafsil tavsifi keltirilgan.

Birinchidan, Visual Studio kabi ishlab chiqish muhitida kerakli dasturni yarating. C# yoki Visual Basic kompilyatoridan foydalanib, tayyor dasturni ob'ekt kodiga kompilyatsiya qiling. Ushbu kod oraliq bajariladigan kodni yaratuvchi CREATE ASSEMBLY bayonoti uchun manba bo'lib xizmat qiluvchi dinamik havola kutubxonasi (.dll) faylida saqlanadi. Keyinchalik, bajarilayotgan kodni ma'lumotlar bazasi ob'ekti sifatida saqlash uchun CREATE PROCEDURE bayonotini chiqaring. Nihoyat, tanish EXECUTE iborasi yordamida protsedurani bajaring.

Quyidagi misol C# da saqlangan protsedura manba kodini ko'rsatadi:

System.Data.SqlClient-dan foydalanish; Microsoft.SqlServer.Server yordamida; umumiy qisman sinf StoredProcedures ( umumiy statik int CountEmployees() ( int qatorlar; SqlConnection ulanishi = yangi SqlConnection("Kontekst ulanishi=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "tanlash count(*) "Xodimlar soni" sifatida " + "xodimdan"; satrlar = (int)cmd.ExecuteScalar(); connection.Close(); qaytish qatorlari; ) )

Ushbu protsedura Xodimlar jadvalidagi qatorlar sonini hisoblash uchun so'rovni amalga oshiradi. Dastur boshida direktivalardan foydalanib, uni bajarish uchun zarur bo'lgan nomlar maydonini belgilang. Ushbu direktivalardan foydalanish sizga tegishli nomlar bo'shliqlarini aniq ko'rsatmasdan, manba kodida sinf nomlarini belgilash imkonini beradi. Keyinchalik, StoredProcedures klassi aniqlanadi, buning uchun SqlProcedure atributi, bu kompilyatorga ushbu klass saqlangan protsedura ekanligini bildiradi. Sinf kodi ichida CountEmployees() usuli aniqlanadi. Ma'lumotlar bazasi tizimiga ulanish sinfning namunasi orqali o'rnatiladi SqlConnection. Ulanishni ochish uchun ushbu misolning Open() usuli qo'llaniladi. A CreateCommand() usuli sinfning namunasiga kirish imkonini beradi SqlCommnd, unga kerakli SQL buyrug'i uzatiladi.

Quyidagi kod parchasida:

Cmd.CommandText = "Xodimlardan " + "xodimlar soni" sifatida count(*) ni tanlang";

Xodimlar jadvalidagi qatorlar sonini hisoblash va natijani ko'rsatish uchun SELECT operatoridan foydalanadi. Buyruq matni cmd o'zgaruvchisining CommandText xususiyatini CreateCommand() usuli bilan qaytarilgan misolga o'rnatish orqali aniqlanadi. Keyingi deb ataladi ExecuteScalar() usuli SqlCommand misoli. Bu usul int butun ma'lumotlar turiga aylantirilgan va qatorlar o'zgaruvchisiga tayinlangan skalyar qiymatni qaytaradi.

Endi siz ushbu kodni Visual Studio yordamida kompilyatsiya qilishingiz mumkin. Men ushbu sinfni loyihaga CLRStoredProcedures nomi bilan qo'shdim, shuning uchun Visual Studio bir xil nomdagi assembleyni *.dll kengaytmasi bilan kompilyatsiya qiladi. Quyidagi misolda saqlangan protsedurani yaratishning keyingi bosqichi ko'rsatilgan: ishga tushirish uchun kod yaratish. Ushbu misoldagi kodni bajarishdan oldin siz kompilyatsiya qilingan .dll faylining joylashuvini bilishingiz kerak (odatda loyihaning Debug papkasida joylashgan).

SampleDb dan foydalanish; PERMISSION_SET = XAVFSIZLIK BILAN "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll"DAN CLRStoredProcedures ASSEMBLYNI YARATING.

CREATE ASSEMBLY bayonoti boshqariladigan kodni kirish sifatida qabul qiladi va siz umumiy tilda ish vaqti (CLR) saqlanadigan protseduralar, foydalanuvchi tomonidan belgilangan funktsiyalar va triggerlarni yaratishingiz mumkin bo'lgan tegishli ob'ektni yaratadi. Ushbu ko'rsatma quyidagi sintaksisga ega:

YORAT ASSEMBLY assembly_nomi [ AVTORIZATION egasi_nomi ] FROM (dll_fayl) sintaksisi qoidalari

assembly_name parametri yig'ilish nomini belgilaydi. Ixtiyoriy AUTORIZATION bandi ushbu yig'ilish egasi sifatida rol nomini belgilaydi. FROM bandi yuklanadigan yig'ilish joylashgan yo'lni belgilaydi.

WITH PERMISSION_SET bandi CREATE ASSEMBLY iborasining juda muhim bandidir va har doim ko'rsatilishi kerak. U montaj kodiga berilgan kirish huquqlari to'plamini belgilaydi. SAFE huquqlari to'plami eng cheklovchi hisoblanadi. Ushbu huquqlarga ega bo'lgan assambleya kodi fayllar kabi tashqi tizim resurslariga kira olmaydi. EXTERNAL_ACCESS huquqlar to'plami yig'ish kodiga ma'lum tashqi tizim resurslariga kirish imkonini beradi, UNSAFE huquqlar to'plami esa ma'lumotlar bazasi tizimi ichida va tashqarisida resurslarga cheklanmagan kirishni ta'minlaydi.

Yig'ish kodi ma'lumotlarini saqlash uchun foydalanuvchi CREATE ASSEMBLY bayonotini chiqarishi kerak. Assambleya bayonotni bajaradigan foydalanuvchiga (yoki rolga) tegishli. CREATE SCHEMA iborasining AVTORIZATION bandidan foydalanib, yig'ilish egasini o'zgartirishingiz mumkin.

Ma'lumotlar bazasi mexanizmi ALTER ASSEMBLY va DROP ASSEMBLY bayonotlarini ham qo'llab-quvvatlaydi. ALTER ASSEMBLY bayonoti yig'ilishni so'nggi versiyaga yangilash uchun ishlatiladi. Ushbu ko'rsatma, shuningdek, tegishli yig'ilish bilan bog'liq fayllarni qo'shadi yoki o'chiradi. TO'CHIRISH ASSEMBLY bayonoti joriy ma'lumotlar bazasidan belgilangan yig'ilishni va barcha bog'langan fayllarni olib tashlaydi.

Quyidagi misol ilgari amalga oshirilgan boshqariladigan kod asosida saqlangan protsedurani qanday yaratishni ko'rsatadi:

SampleDb dan foydalanish; O'TISH TARTIBINI YARATING CountEmployees TAShQI NOMI O'ZBEKISTONDA CLRStoredProcedures.StoredProcedures.CountEmployees

Misoldagi CREATE PROCEDURE iborasi oldingi misollardagi bir xil gapdan farq qiladi, chunki unda mavjud EXTERNAL NAME parametri. Ushbu parametr kod CLR tomonidan yaratilganligini bildiradi. Ushbu jumladagi ism uch qismdan iborat:

assembly_name.class_name.method_name

assembly_name - assambleyaning nomini belgilaydi;

class_name - umumiy sinf nomini belgilaydi;

method_name - ixtiyoriy qism, sinf ichida o'rnatilgan usulning nomini belgilaydi.

CountEmployees protsedurasining bajarilishi quyidagi misolda ko'rsatilgan:

SampleDb dan foydalanish; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Qaytadi 7

PRINT bayonoti Xodimlar jadvalidagi joriy qatorlar sonini qaytaradi.

Jarayon deklaratsiyasi

TARTIBI YARATISH [({IN|OUT|INOUT} [,…])]
[DINAMIK NATIJALAR TOPLAMI ]
BOSHLANISHI [ATOM]

OXIRI

Kalit so'zlar
. IN (Kirish) – kiritish parametri
. OUT (Output) – chiqish parametri
. INOUT - kirish va chiqish, shuningdek maydon (parametrlar yo'q)
. DYNAMIC RESULT SET protsedura qaytgandan keyin ochiq qoladigan kursorlarning belgilangan sonini ochishi mumkinligini bildiradi.

Eslatmalar
Saqlangan protseduralarda (birinchi navbatda katta raqamlar va belgilar satrlari) tarmoq va stekning tiqilib qolishi sababli ko'p parametrlardan foydalanish tavsiya etilmaydi. Amalda Transact-SQL, PL/SQL va Informix ning mavjud dialektlari parametrlarni e’lon qilishda ham, foydalanishda ham, o‘zgaruvchilarni e’lon qilishda ham, subprogrammani chaqirishda ham standartdan sezilarli chetlanishlarni ko‘rsatadi. Microsoft saqlangan protseduralarning kesh hajmini baholash uchun quyidagi taxminiy qiymatdan foydalanishni tavsiya qiladi:
=(maksimal bir vaqtda foydalanuvchilar)*(eng katta ijro rejasi hajmi)*1.25. Sahifalardagi ijro rejasining hajmini aniqlash quyidagi buyruq yordamida amalga oshirilishi mumkin: DBCC MEMUSAGE.

Jarayon chaqiruvi

Ko'pgina mavjud DBMSlarda saqlangan protseduralar quyidagi bayonot yordamida chaqiriladi:

PROSEDURASINI BAJARISH [(][)]

Eslatma: Saqlangan protseduralarga qo'ng'iroq ilova ichidan, boshqa saqlangan protseduradan yoki interaktiv tarzda amalga oshirilishi mumkin.

Protsessual deklaratsiya namunasi

CREATE PROCEDURE Proc1 AS //protsedurani e'lon qilish
TANLANGAN SName UCHUN Cur1 KURSORNI E'LON ET
OPEN Cur1 // kursorni ochish
FETCH NEXT FROM Cur1 //kursordan ma'lumotlarni o'qish
WHILE @@Fetch_Status=0
BOSHLANISHI
Cur1DAN KEYINGI OLISH
OXIRI
CLOSE Cur1 //kursorni yoping
DEALLOCATE Cur1
EXECUTE Proc1 // protsedurani bajaring

Polimorfizm
Xuddi shu nomdagi ikkita kichik dasturni bir xil sxemada yaratish mumkin, agar ikkita pastki dasturning parametrlari bir-biridan etarlicha farq qiladigan bo'lsa, ularni ajratish mumkin. Xuddi shu sxemada bir xil nomga ega ikkita tartibni farqlash uchun har biriga muqobil va noyob nom (maxsus nom) beriladi. Bunday nom quyi dastur aniqlanganda aniq ko'rsatilishi mumkin. Bir nechta bir xil nomlarga ega bo'lgan kichik dasturlarni chaqirganda, kerakli pastki dasturni aniqlash bir necha bosqichda amalga oshiriladi:
. Dastlab, ko'rsatilgan nomga ega bo'lgan barcha protseduralar aniqlanadi, agar ular bo'lmasa, ko'rsatilgan nomga ega barcha funktsiyalar.
. Keyinchalik tahlil qilish uchun faqat ushbu foydalanuvchi bajarish huquqiga (EXECUTE) ega bo'lgan tartiblar qoldiriladi.
. Ular uchun parametrlar soni chaqiruv argumentlari soniga mos keladiganlar tanlanadi. Parametrlarning belgilangan ma'lumotlar turlari va ularning joylashuvi tekshiriladi.
. Agar bir nechta pastki dastur qolsa, qisqaroq nomga ega bo'lgan dastur tanlanadi.
Amalda, Oracle-da polimorfizm faqat paketda, DB@ - turli sxemalarda e'lon qilingan funksiyalar uchun qo'llab-quvvatlanadi va Sybase va MS SQL Serverda ortiqcha yuklash taqiqlanadi.

Jarayonlarni olib tashlash va o'zgartirish
Jarayonni o'chirish uchun quyidagi bayonot ishlatiladi:

Jarayonni o'zgartirish uchun quyidagi bayonotdan foydalaning:

TARTIBINI O‘ZGARTIRISh [([{IN|OUT|INOUT}])]
BOSHLANISHI [ATOM]

OXIRI

Protseduralarni bajarish uchun imtiyozlar

GRANT IJRO BERISH TO |JAMOAT [GRANT VARIANT BILAN]

Tizim protseduralari
Ko'pgina DBMS (shu jumladan SQL Server) o'z maqsadlaringiz uchun foydalanishingiz mumkin bo'lgan o'rnatilgan tizimda saqlanadigan protseduralarning ma'lum to'plamiga ega.