Saqlangan protsedura SQL tili va protsessual kengaytmalar yordamida yaratilgan Transact-SQL bayonot paketining maxsus turi. Paket va saqlangan protsedura o'rtasidagi asosiy farq shundaki, ikkinchisi ma'lumotlar bazasi ob'ekti sifatida saqlanadi. Boshqacha qilib aytganda, takrorlanadigan 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 yaratganingizda, parametrlarning ixtiyoriy ro'yxatini belgilashingiz mumkin. Shunday qilib, protsedura har safar chaqirilganda tegishli argumentlarni qabul qiladi. 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 (deyarli barcha holatlarda) takroriy protsedura kompilyatsiyalarini yo'q qilish va tegishli ish faoliyatini yaxshilashga erishishning 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 bayt hajmdagi saqlangan protsedurani chaqirish uchun 50 baytdan kamroq vaqt talab qilinishi mumkin. Agar bir nechta foydalanuvchilar saqlangan protseduralar yordamida takrorlanadigan 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 jurnalini yaratish.

Saqlangan protseduralardan foydalanish foydalanuvchilarga turli xil kirish huquqlarini beruvchi GRANT va REVOKE bayonotlaridan foydalanish orqali taqdim etilgan xavfsizlikdan ancha yuqori bo'lgan xavfsizlik nazorati darajasini ta'minlaydi. Bu mumkin, chunki saqlanadigan protsedurani bajarish uchun ruxsat keyingi bo'limda tavsiflanganidek, saqlangan protsedura tarkibidagi ob'ektlarni o'zgartirishga ruxsatdan mustaqildir.

Jadval yozish va/yoki o'qish operatsiyalari jurnallarini yaratadigan saqlangan protseduralar qo'shimcha imkoniyat ma'lumotlar bazasi xavfsizligini ta'minlash. Bunday protseduralardan foydalangan holda ma'lumotlar bazasi ma'muri foydalanuvchilar yoki amaliy dasturlar 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 CREATE [((@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 aniqlanadi. Mahalliy o'zgaruvchilar paket ichida mahalliy bo'lgani kabi, protsedura parametrlari protsedura ichida mahalliydir. 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 parametri qaytarish parametri ekanligini va undan qiymatni saqlangan protseduradan chaqiruvchi protsedura yoki tizimga qaytarish uchun foydalanish mumkinligini ko'rsatadi.

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, protsedurani e'lon qilishda foydalaning. QAYTA TUZISH opsiyasi BILAN. WITH RECOMPILE opsiyasidan foydalanish saqlangan protseduralarning eng muhim afzalliklaridan birini inkor etadi: bitta kompilyatsiya tufayli unumdorlikni oshirish. Shuning uchun, WITH RECOMPILE opsiyasi faqat saqlangan protsedura tomonidan ishlatiladigan ma'lumotlar bazasi ob'ektlari tez-tez o'zgartirilganda ishlatilishi kerak.

EXECUTE AS bandi saqlangan protsedura chaqirilgandan keyin bajarilishi kerak bo'lgan xavfsizlik kontekstini belgilaydi. Ushbu kontekstni o'rnatish orqali Ma'lumotlar bazasi mexanizmi 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 rollari CREATE PROCEDURE bayonotidan foydalanishi mumkin. Ammo bu rollarning a'zolari ushbu huquqni bayonot yordamida boshqa foydalanuvchilarga berishlari 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 foydalaning GO ko'rsatmalari. CREATE PROCEDURE iborasini bitta to'plamdagi boshqa Transact-SQL bayonotlari bilan birlashtirib bo'lmaydi. IncreaseBudget saqlangan protsedurasi barcha loyihalar uchun byudjetlarni @percent parametri bilan belgilanadigan ma'lum foizga oshiradi. Protsedura, shuningdek, protsedura bajarilganda ushbu argument mavjud bo'lmasa, ishlatiladigan standart foiz qiymatini (5) belgilaydi.

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

Har doim joriy ma'lumotlar bazasida saqlanadigan birlamchi saqlangan protseduralardan farqli o'laroq, har doim vaqtinchalik ma'lumotlar bazasiga joylashtiriladigan vaqtinchalik saqlanadigan protseduralarni yaratish mumkin. tizim bazasi tempdb ma'lumotlari. Vaqtinchalik saqlanadigan protseduralarni yaratishning sabablaridan biri ma'lumotlar bazasiga ulanishda ma'lum bir bayonotlar guruhini qayta-qayta bajarishdan qochish bo'lishi mumkin. Mahalliy yoki global vaqtinchalik tartiblarni yaratishingiz mumkin. Bu nom uchun mahalliy protsedura bitta # belgisi (#proc_name) bilan, global protsedura nomi esa juft # (##proc_name) bilan belgilanadi.

Mahalliy vaqtinchalik saqlanadigan protsedura faqat uni yaratgan foydalanuvchi tomonidan va faqat u yaratilgan ma'lumotlar bazasiga ulangan holda bajarilishi mumkin. Global vaqtinchalik protsedura barcha foydalanuvchilar tomonidan bajarilishi mumkin, lekin faqat u bajariladigan oxirgi ulanish (odatda protsedura yaratuvchisining ulanishi) tugaguncha.

Saqlangan protseduraning hayot aylanishi ikki bosqichdan iborat: uni yaratish va bajarish. Har bir protsedura bir marta yaratiladi va ko'p marta bajariladi. Saqlangan protsedura yordamida amalga oshiriladi Ko'rsatmalarni ISHLATISH protsedura egasi bo'lgan yoki ushbu protseduraga kirish uchun EXECUTE imtiyoziga 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 CREATE PROCEDURE operatorining bir xil parametrlari bilan bir xil mantiqiy ma'noga 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 parametrlarning qiymatlari tartibi muhim emas, lekin nomlanmagan parametrlarning qiymatlari CREATE PROCEDURE bayonotida belgilangan tartibda taqdim etilishi kerak.

DEFAULT bandi protsedura ta'rifida ko'rsatilgan protsedura parametri uchun standart qiymatni 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 partiyaning birinchi bayonoti 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 misolda Xodimlar va Ishlar_jadvallarida ma'lumotlarni qayta ishlash uchun saqlangan protsedurani yaratish ko'rsatilgan:

ModifyEmpId misol protsedurasi ma'lumotnoma yaxlitligini saqlash jarayonining bir qismi sifatida saqlangan protseduralardan foydalanishni ko'rsatadi (bu holda Xodim va Works_on jadvallari o'rtasida). Shunga o'xshash saqlangan protsedura trigger ta'rifi ichida ishlatilishi mumkin, bu aslida havolaning yaxlitligini ta'minlaydi.

Quyidagi misol saqlangan protsedurada OUTPUT bandidan foydalanishni ko'rsatadi:

Ushbu saqlangan protsedura quyidagi ko'rsatmalar yordamida bajarilishi mumkin:

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

Ushbu protsedura @empId personal raqamiga ega bo'lgan xodim ishlayotgan loyihalar sonini hisoblab chiqadi va natijada olingan qiymatni ©counter parametriga belgilaydi. Berilgan xodimlar soni uchun barcha qatorlar Xodim va Ishlar_jadvallaridan o'chirilgandan so'ng, hisoblangan qiymat @quantityDeleteEmployee 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 tizimga qiymat qaytaradi. Shuning uchun @counter parametri protsedurani e'lon qilishda OUTPUT variantida ham, uni chaqirishda EXECUTE operatorida ham ko'rsatilishi kerak.

EXECUTE bayonining WITH RESULTS SETS bandi

IN SQL Server EXECUTE bayonoti uchun 2012 yil kiritildi WITH RESULTS SETS bandi, bu orqali ma'lum shartlar bajarilganda saqlangan protseduraning natijalar to'plami shaklini o'zgartirishingiz 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 uni chaqirishda ko'rsatilishi kerak. Ushbu protsedurani bajarishda sarlavhalari ma'lumotlar bazasi jadvalidagi mos ustunlar nomlariga mos keladigan ikkita ustunli jadval hosil bo'ladi, ya'ni. Id va familiya. Natija ustunlarining sarlavhalarini (shuningdek, ularning ma'lumotlar turini) o'zgartirish uchun SQL Server 2012 yangi WITH RESULTS SETS bandidan foydalanadi. Ushbu jumlaning qo'llanilishi quyidagi misolda ko'rsatilgan:

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

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

Ko'rib turganingizdek, EXECUTE operatoridagi WITH RESULT SETS bandidan foydalangan holda saqlangan protsedurani ishga tushirish protsedura tomonidan ishlab chiqarilgan natijalar to'plamidagi ustunlarning nomlari va ma'lumotlar turlarini o'zgartirish imkonini beradi. Shunday qilib, ushbu yangi funksionallik saqlangan protseduralarni bajarish va ularning natijalarini yangi jadvalga joylashtirishda ko'proq moslashuvchanlikni ta'minlaydi.

Saqlangan protseduralar tarkibini o'zgartirish

Ma'lumotlar bazasi mexanizmi ham ko'rsatmalarni qo'llab-quvvatlaydi TARTIBINI O‘ZGARTIRISh saqlangan protseduralar tuzilishini o'zgartirish uchun. ALTER PROCEDURE operatori odatda protsedura ichidagi Transact-SQL operatorlarini o'zgartirish uchun ishlatiladi. ALTER PROCEDURE operatorining barcha parametrlari CREATE PROCEDURE operatorining bir xil parametrlari bilan bir xil ma'noga ega. Ushbu bayonotdan foydalanishning asosiy maqsadi mavjud saqlangan protsedura huquqlarini bekor qilishdan qochishdir.

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 uchun foydalaniladigan dasturlash konstruksiyasi boʻlib, foydalanuvchilarga ushbu natijani satr boʻyicha koʻrsatish imkonini beradi.

Bir yoki bir guruh saqlangan protseduralarni o'chirish uchun foydalaning DOPLASH PROSEDURI ko'rsatmasi. Faqat db_owner va sysadmin o'zgarmas rollarining egasi yoki 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 maxsus ma'lumotlar turlari) ishlab chiqish imkonini beradi. CLR shuningdek, umumiy ish vaqti tizimi yordamida ushbu ob'ektlarni bajarishga imkon beradi.

Umumiy tilda ishlash vaqti parametr yordamida yoqiladi va o'chiriladi clr_enabled tizim protsedurasi sp_configure, ko'rsatma bo'yicha bajarish uchun ishga tushiriladi QAYTA SOZLASH. Quyidagi misol CLRni yoqish uchun sp_configure tizim protsedurasidan qanday foydalanishingiz mumkinligini ko'rsatadi:

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

CLR yordamida protsedurani yaratish, kompilyatsiya qilish va saqlash uchun siz quyidagi ketma-ketlikni ko'rsatilgan tartibda bajarishingiz kerak:

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

Ko'rsatmalardan foydalanish ASSAMBLY YARATING, mos keladigan bajariladigan faylni yarating.

EXECUTE operatori yordamida protsedurani bajaring.

Quyidagi rasmda ko'rsatilgan grafik diagrammasi ilgari belgilangan qadamlar. Quyida ko'proq batafsil tavsif bu jarayon.

Birinchidan, kerakli dasturni Visual Studio kabi ishlab chiqish muhitida 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 bog'langan kutubxona (.dll) faylida saqlanadi. Keyinchalik, bajaruvchi 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 uchun 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, dasturni bajarish uchun zarur bo'lgan nomlar maydonini belgilang. Ushbu direktivalardan foydalanish sizni belgilash imkonini beradi manba kodi tegishli nomlar bo'shliqlarini aniq ko'rsatmasdan sinf nomlari. Keyinchalik, StoredProcedures klassi aniqlanadi, buning uchun SqlProcedure atributi, bu kompilyatorga ushbu klass saqlangan protsedura ekanligini bildiradi. CountEmployees() usuli sinf kodi ichida 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. Keyinchalik u deyiladi ExecuteScalar() usuli SqlCommand misoli. Bu usul butun son 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 CLRStoredProcedures deb nomlangan loyihaga qo'shdim, shuning uchun Visual Studio *.dll kengaytmasi bilan bir xil nomdagi assembleyani kompilyatsiya qiladi. Quyidagi misolda saqlangan protsedurani yaratishning keyingi bosqichi ko'rsatilgan: bajariladigan kodni yaratish. Ushbu misoldagi kodni ishga tushirishdan 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 kiritish sifatida qabul qiladi va CLR saqlanadigan protseduralar, foydalanuvchi tomonidan belgilangan funktsiyalar va triggerlarni yaratishingiz mumkin bo'lgan mos keladigan 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 assambleyaning egasi sifatida rol nomini belgilaydi. FROM bandi yuklanadigan yig'ilish joylashgan yo'lni belgilaydi.

WITH PERMISSION_SET bandi CREATE ASSEMBLY bayonotining juda muhim bandi bo'lib, har doim ko'rsatilishi kerak. U montaj kodiga berilgan ruxsatlar to'plamini belgilaydi. SAFE ruxsatnomasi eng cheklovchi hisoblanadi. Ushbu huquqlarga ega bo'lgan assambleya kodi fayllar kabi tashqi tizim resurslariga kira olmaydi. EXTERNAL_ACCESS huquqlar to'plami montaj kodiga ma'lum tashqi tizim resurslariga kirish imkonini beradi, UNSAFE huquqlar to'plami esa ma'lumotlar bazasi tizimi ichidagi va tashqarisidagi resurslarga cheksiz kirish imkonini beradi.

Yig'ish kodi ma'lumotlarini saqlash uchun foydalanuvchi CREATE ASSEMBLY bayonotini chiqarishi kerak. Assambleyaning egasi ko'rsatmani bajaruvchi foydalanuvchi (yoki rol) hisoblanadi. CREATE SCHEMA iborasining AVTORIZATION bandidan foydalanib, boshqa foydalanuvchini assambleyaning egasi qilishingiz mumkin.

Ma'lumotlar bazasi mexanizmi ALTER ASSEMBLY va DROP ASSEMBLY bayonotlarini ham qo'llab-quvvatlaydi. ALTER ASSEMBLY bayonoti yig'ishni yangilash uchun ishlatiladi oxirgi versiya. Ushbu ko'rsatma, shuningdek, tegishli yig'ilish bilan bog'liq fayllarni qo'shadi yoki o'chiradi. YIG'LASH bo'yicha ko'rsatma Joriy ma'lumotlar bazasidan ko'rsatilgan yig'ilishni va unga bog'langan barcha fayllarni olib tashlaydi.

Quyidagi misolda siz ilgari amalga oshirgan boshqariladigan kod asosida saqlangan protsedurani qanday yaratish ko'rsatilgan:

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

Misoldagi CREATE PROCEDURE ko'rsatmasi avvalgi misollardagi bir xil yo'riqnomadan farq qiladi, chunki unda EXTERNAL NAME parametri. Ushbu parametr kodning umumiy til ish vaqti tomonidan yaratilganligini bildiradi. Ushbu jumladagi ism uch qismdan iborat:

assembly_name.class_name.method_name

assembly_name - yig'ilish nomini ko'rsatadi;

class_name - umumiy sinf nomini bildiradi;

method_name - ixtiyoriy qism, sinf ichida aniqlangan usul nomini belgilaydi.

CountEmployees protsedurasining bajarilishi quyidagi misolda ko'rsatilgan:

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

PRINT bayonoti Xodimlar jadvalidagi joriy qatorlar sonini qaytaradi.

SQLda saqlanadigan protseduralar turli ob'ektlar ko'rinishida saqlanishi mumkin bo'lgan bajariladigan dastur modullaridir. Boshqacha qilib aytganda, bu SQL ko'rsatmalarini o'z ichiga olgan ob'ekt. Ushbu saqlangan protseduralar olish uchun dastur mijozida bajarilishi mumkin yaxshi ishlash. Bundan tashqari, bunday ob'ektlar ko'pincha boshqa skriptlardan yoki hatto boshqa bo'limdan chaqiriladi.

Kirish

Ko'pchilik ularni turli xil protseduralarga o'xshash deb hisoblashadi (mos ravishda, MS SQLdan tashqari). Balki bu haqiqatdir. Ular o'xshash parametrlarga ega va o'xshash qiymatlarni ishlab chiqarishi mumkin. Bundan tashqari, ba'zi hollarda ular tegadi. Masalan, ular DDL va DML ma'lumotlar bazalari, shuningdek, foydalanuvchi funktsiyalari (kod nomi UDF) bilan birlashtirilgan.

Aslida, SQL-da saqlanadigan protseduralar ularni o'xshash jarayonlardan ajratib turadigan keng ko'lamli afzalliklarga ega. Xavfsizlik, dasturlashning moslashuvchanligi, mahsuldorlik - bularning barchasi ma'lumotlar bazalari bilan ishlaydigan ko'proq foydalanuvchilarni jalb qiladi. Protseduralarning eng yuqori mashhurligi 2005-2010 yillarda, Microsoft-dan "SQL Server Management Studio" deb nomlangan dastur chiqarilganda sodir bo'ldi. Uning yordami bilan ma'lumotlar bazalari bilan ishlash ancha oson, amaliy va qulay bo'ldi. Yildan yilga bu dasturchilar orasida mashhurlikka erishdi. Bugungi kunda bu mutlaqo tanish dastur bo'lib, ma'lumotlar bazalari bilan "muloqot" qiladigan foydalanuvchilar uchun Excel bilan bir qatorda.

Jarayon chaqirilganda, u serverning o'zi tomonidan keraksiz jarayonlar yoki foydalanuvchi aralashuvisiz darhol qayta ishlanadi. Shundan so'ng siz har qanday o'chirish, bajarish yoki o'zgartirishni amalga oshirishingiz mumkin. Bularning barchasi uchun DDL operatori mas'ul bo'lib, u ob'ektlarni qayta ishlash uchun eng murakkab harakatlarni bir o'zi bajaradi. Bundan tashqari, bularning barchasi juda tez sodir bo'ladi va server aslida yuklanmagan. Ushbu tezlik va ishlash sizga katta hajmdagi ma'lumotlarni foydalanuvchidan serverga va aksincha, juda tez o'tkazish imkonini beradi.

Axborot bilan ishlash uchun ushbu texnologiyani amalga oshirish uchun bir nechta dasturlash tillari mavjud. Bularga, masalan, Oracle’dan PL/SQL, InterBase va Firebird tizimlarida PSQL, shuningdek, klassik Microsoft Transact-SQL kiradi. Ularning barchasi saqlanadigan protseduralarni yaratish va bajarish uchun mo'ljallangan, bu esa katta ma'lumotlar bazasi protsessorlariga o'z algoritmlaridan foydalanish imkonini beradi. Bu shuningdek, bunday ma'lumotlarni boshqaradiganlar barcha ob'ektlarni uchinchi shaxslar tomonidan ruxsatsiz kirishdan va shunga mos ravishda ma'lum ma'lumotlarni yaratish, o'zgartirish yoki o'chirishdan himoya qilishlari uchun zarurdir.

Hosildorlik

Ushbu ma'lumotlar bazasi ob'ektlarini turli usullar bilan dasturlash mumkin. Bu foydalanuvchilarga kuch va vaqtni tejash, eng mos keladigan usul turini tanlash imkonini beradi. Bundan tashqari, protsedura o'z-o'zidan qayta ishlanadi, bu server va foydalanuvchi o'rtasidagi aloqa uchun sarflangan katta vaqtni oldini oladi. Shuningdek, modulni istalgan vaqtda qayta dasturlash va kerakli yo'nalishda o'zgartirish mumkin. Ayniqsa, SQL-da saqlanadigan protsedurani ishga tushirish tezligini ta'kidlash kerak: bu jarayon unga o'xshash boshqalarga qaraganda tezroq sodir bo'ladi, bu esa uni qulay va universal qiladi.

Xavfsizlik

Axborotni qayta ishlashning bu turi shu kabi jarayonlardan kafolatlanishi bilan farq qiladi xavfsizlikni oshirdi. Bu boshqa foydalanuvchilar tomonidan protseduralarga kirishni butunlay chiqarib tashlash mumkinligi bilan ta'minlanadi. Bu ma'murga ma'lumotlarni ushlab qolishdan yoki ma'lumotlar bazasiga ruxsatsiz kirishdan qo'rqmasdan, ular bilan mustaqil ravishda operatsiyalarni amalga oshirish imkonini beradi.

Ma'lumotlarni uzatish

SQL-da saqlangan protsedura va mijoz ilovasi o'rtasidagi bog'liqlik parametrlardan foydalanish va qiymatlarni qaytarishdir. Ikkinchisi ma'lumotlarni saqlangan protseduraga o'tkazishi shart emas, lekin bu ma'lumotlar (asosan foydalanuvchining iltimosiga binoan) SQL uchun qayta ishlanadi. Saqlangan protsedura o'z ishini tugatgandan so'ng, u ma'lumotlar paketlarini uni chaqirgan dasturga qaytarib yuboradi (lekin yana, ixtiyoriy ravishda) turli usullar, uning yordamida saqlangan SQL protsedurasiga qo'ng'iroq qilish ham, qaytish ham mumkin, masalan:

Chiqish turi parametri yordamida ma'lumotlarni uzatish;

Qaytish operatori yordamida ma'lumotlarni uzatish;

Tanlangan operator yordamida ma'lumotlarni uzatish.

Keling, bu jarayon ichkaridan qanday ko'rinishini aniqlaylik.

1. SQLda EXEC saqlanadigan protsedura yarating

MS SQL (Managment Studio) da protsedura yaratishingiz mumkin. Jarayon yaratilgandan so'ng, u ma'lumotlar bazasining dasturlashtiriladigan tugunida ro'yxatga olinadi, unda yaratish protsedurasi operator tomonidan amalga oshiriladi. Amalga oshirish uchun SQL saqlangan protseduralari ob'ekt nomini o'z ichiga olgan EXEC jarayonidan foydalanadi.

Protsedura yaratganingizda avval uning nomi, keyin esa unga tayinlangan bir yoki bir nechta parametrlar paydo bo'ladi. Parametrlar ixtiyoriy bo'lishi mumkin. Parametr(lar), ya'ni protsedura tanasi yozilgandan so'ng, ba'zi kerakli operatsiyalarni bajarish kerak.

Gap shundaki, tanada mahalliy o'zgaruvchilar joylashgan bo'lishi mumkin va bu o'zgaruvchilar protseduralarga nisbatan ham lokaldir. Boshqacha qilib aytganda, ularni faqat Microsoft SQL Server protsedurasi doirasida ko'rish mumkin. Bu holatda saqlangan protseduralar mahalliy hisoblanadi.

Demak, protsedurani yaratish uchun protseduraning nomi va protsedura tanasi sifatida kamida bitta parametr kerak. shu esta tutilsinki ajoyib variant bu holda klassifikatorda sxema nomi bilan protsedurani yaratish va bajarishdir.

Protseduraning tanasi har qanday ko'rinishda bo'lishi mumkin, masalan, jadval yaratish, jadvalning bir yoki bir nechta qatorlarini kiritish, ma'lumotlar bazasining turi va xarakterini belgilash va hokazo. Biroq, protsedura organi muayyan operatsiyalarni uning doirasida amalga oshirilishini cheklaydi. Ba'zi muhim cheklovlar quyida keltirilgan:

Tana boshqa saqlangan protseduralarni yaratmasligi kerak;

Tana ob'ekt haqida noto'g'ri taassurot yaratmasligi kerak;

Tana hech qanday qo'zg'atuvchini yaratmasligi kerak.

2. Protsedura tanasida o'zgaruvchini o'rnatish

Siz o'zgaruvchilarni protsedura tanasi uchun mahalliy qilishingiz mumkin va keyin ular faqat protsedura tanasida joylashadi. Saqlangan protsedura tanasining boshida o'zgaruvchilarni yaratish yaxshi amaliyotdir. Lekin o'zgaruvchilarni berilgan ob'ekt tanasining istalgan joyiga ham o'rnatishingiz mumkin.

Ba'zan bir qatorda bir nechta o'zgaruvchilar o'rnatilganligini va har bir o'zgaruvchi parametri vergul bilan ajratilganligini sezishingiz mumkin. Shuningdek, o'zgaruvchi @ bilan prefiks ekanligini unutmang. Jarayonning asosiy qismida siz o'zgaruvchini xohlagan joyga o'rnatishingiz mumkin. Masalan, @NAME1 o'zgaruvchisi protsedura tanasining oxirida e'lon qilinishi mumkin. E'lon qilingan o'zgaruvchiga qiymat berish uchun shaxsiy ma'lumotlar to'plamidan foydalaniladi. Bir qatorda bir nechta o'zgaruvchilar e'lon qilingan vaziyatdan farqli o'laroq, bu holatda faqat bitta shaxsiy ma'lumotlar to'plami ishlatiladi.

Foydalanuvchilar tez-tez savol berishadi: "Qanday qilib protsedura tanasida bitta bayonotda bir nechta qiymatlarni belgilash mumkin?" Xo'sh. Bu qiziq savol, lekin buni qilish siz o'ylagandan ko'ra osonroq. Javob: "Select Var = value" kabi juftlarni ishlatish. Bu juftlarni vergul bilan ajratib foydalanishingiz mumkin.

Turli misollar odamlarning oddiy saqlangan protsedurani yaratishi va uni bajarishini ko'rsatadi. Biroq, protsedura shunday parametrlarni qabul qilishi mumkinki, uni chaqirayotgan jarayon unga yaqin qiymatlarga ega bo'ladi (lekin har doim ham emas). Agar ular bir-biriga to'g'ri kelsa, tanada tegishli jarayonlar boshlanadi. Misol uchun, agar siz qo'ng'iroq qiluvchidan shahar va viloyatni qabul qiladigan va tegishli shahar va mintaqaga qancha mualliflar tegishliligi to'g'risidagi ma'lumotlarni qaytaradigan protsedura yaratsangiz. Protsedura ushbu mualliflarni hisoblashni amalga oshirish uchun Publar kabi ma'lumotlar bazasining muallif jadvallarini so'raydi. Ushbu ma'lumotlar bazalarini olish uchun, masalan, Google SQL skriptini SQL2005 sahifasidan yuklab oladi.

Oldingi misolda protsedura ikkita parametrni oladi, bu esa Ingliz tili shartli ravishda @State va @City deb nomlanadi. Ma'lumotlar turi ilovada belgilangan turga mos keladi. Protseduraning tanasida @TotalAuthors ichki o'zgaruvchilar mavjud va bu o'zgaruvchi mualliflar sonini ko'rsatish uchun ishlatiladi. Keyin hamma narsani hisoblaydigan so'rovlarni tanlash bo'limi keladi. Nihoyat, hisoblangan qiymat chop etish bayonoti yordamida chiqish oynasida chop etiladi.

SQL da saqlangan protsedurani qanday bajarish kerak

Jarayonni bajarishning ikki yo'li mavjud. Birinchi usul parametrlarni o'tkazish orqali protsedura nomidan keyin vergul bilan ajratilgan ro'yxat qanday bajarilishini ko'rsatadi. Aytaylik, bizda ikkita qiymat bor (oldingi misolda bo'lgani kabi). Ushbu qiymatlar @State va @City protsedura parametrlari o'zgaruvchilari yordamida yig'iladi. Parametrlarni o'tkazishning ushbu usulida tartib muhim ahamiyatga ega. Bu usul ordinal argument uzatish deb ataladi. Ikkinchi usulda parametrlar allaqachon bevosita tayinlangan va bu holda tartib muhim emas. Bu ikkinchi usul nomli argumentlarni uzatish sifatida tanilgan.

Jarayon odatdagidan biroz farq qilishi mumkin. Hammasi oldingi misolda bo'lgani kabi, lekin faqat bu erda parametrlar o'zgartiriladi. Ya'ni, birinchi navbatda @City parametri saqlanadi va @State standart qiymat yonida saqlanadi. Standart parametr odatda alohida ta'kidlanadi. SQLda saqlangan protseduralar faqat parametr sifatida uzatiladi. Bunday holda, "UT" parametri standart "CA" qiymatini almashtiradi. Ikkinchi bajarilishda @City parametri uchun faqat bitta argument qiymati uzatiladi va @State parametri standart "CA" qiymatini oladi. Tajribali dasturchilar barcha o'zgaruvchilar sukut bo'yicha parametrlar ro'yxatining oxirida joylashgan bo'lishi kerakligini maslahat berishadi. Aks holda, bajarish mumkin emas, keyin siz uzunroq va murakkabroq bo'lgan nomli argumentlarni topshirish bilan ishlashingiz kerak.

4. SQL Serverda saqlangan protseduralar: Qaytish usullari

Saqlangan protsedura deb ataladigan ma'lumotlarni yuborishning uchta muhim usuli mavjud. Ular quyida keltirilgan:

Saqlangan protsedura qiymatini qaytarish;

Saqlangan protsedura parametrining chiqishi;

Saqlangan protseduralardan birini tanlash.

4.1 SQL saqlangan protseduralardan qiymatlarni qaytarish

Ushbu texnikada protsedura mahalliy o'zgaruvchiga qiymat beradi va uni qaytaradi. Jarayon to'g'ridan-to'g'ri doimiy qiymatni qaytarishi mumkin. Quyidagi misolda biz mualliflarning umumiy sonini qaytaradigan protsedura yaratdik. Agar siz ushbu protsedurani avvalgilari bilan taqqoslasangiz, chop etish qiymati teskari ekanligini ko'rishingiz mumkin.

Endi protsedurani qanday bajarish va uning qaytish qiymatini chop etishni ko'rib chiqamiz. Jarayonni bajarish uchun o'zgaruvchini o'rnatish va chop etish talab qilinadi, bu butun jarayondan keyin amalga oshiriladi. E'tibor bering, chop etish bayonoti o'rniga Select @RetValue va OutputValue kabi Select iborasidan foydalanishingiz mumkin.

4.2 SQL saqlangan protsedura parametrlari chiqishi

Javob qiymati bitta o'zgaruvchini qaytarish uchun ishlatilishi mumkin, bu biz oldingi misolda ko'rgan narsadir. Chiqish parametridan foydalanish protseduraga qo'ng'iroq qiluvchiga bir yoki bir nechta o'zgaruvchan qiymatlarni yuborish imkonini beradi. Chiqish parametri protsedura yaratishda aynan shu kalit so'z bilan belgilanadi "Output". Agar chiqish parametri sifatida parametr berilgan bo'lsa, protsedura ob'ekti unga qiymat berishi kerak. SQLda saqlangan protseduralar, ularning misollarini quyida ko'rish mumkin, bu holda xulosa ma'lumotlari bilan qaytariladi.

Bizning misolimizda ikkita chiqish nomi bo'ladi: @TotalAuthors va @TotalNoContract. Ular parametrlar ro'yxatida ko'rsatilgan. Ushbu o'zgaruvchilar protseduraning asosiy qismidagi qiymatlarni belgilaydi. Chiqish parametrlaridan foydalanganda, qo'ng'iroq qiluvchi protsedura tanasi ichida o'rnatilgan qiymatni ko'rishi mumkin.

Bundan tashqari, oldingi stsenariyda MS SQL Serverda chiqish parametrida o'rnatilgan protseduralarni saqlagan qiymatlarni ko'rish uchun ikkita o'zgaruvchi e'lon qilingan. Keyin protsedura "CA" parametrining normal qiymatini berish orqali amalga oshiriladi. Quyidagi parametrlar chiqish parametrlari va shuning uchun e'lon qilingan o'zgaruvchilar belgilangan tartibda uzatiladi. E'tibor bering, o'zgaruvchilarni uzatishda chiqish kalit so'zi ham shu erda o'rnatiladi. Jarayon muvaffaqiyatli yakunlangandan so'ng, chiqish parametrlari tomonidan qaytarilgan qiymatlar xabar oynasida ko'rsatiladi.

4.3 SQLda saqlanadigan protseduralardan birini tanlash

Ushbu usul ma'lumotlar jadvali (RecordSet) sifatida qiymatlar to'plamini chaqiruvchi saqlangan protseduraga qaytarish uchun ishlatiladi. Ushbu misolda @AuthID parametrlari bilan saqlangan SQL protsedurasi @AuthId parametri yordamida qaytarilgan yozuvlarni filtrlash orqali Mualliflar jadvalini so'raydi. Select iborasi saqlangan protsedurani chaqiruvchiga nima qaytarilishi kerakligini hal qiladi. Saqlangan protsedura bajarilganda, AuthId qaytariladi. Ushbu protsedura bu erda har doim faqat bitta yozuvni qaytaradi yoki umuman yo'q. Ammo saqlangan protsedura bir nechta yozuvlarni qaytarish uchun hech qanday cheklovlarga ega emas. Bir nechta jami taqdim etish orqali hisoblangan o'zgaruvchilarni o'z ichiga olgan tanlangan parametrlar yordamida ma'lumotlar qaytariladigan misollarni ko'rish odatiy hol emas.

Nihoyat

Saqlangan protsedura - bu qaytaradigan yoki o'tadigan, shuningdek, mijoz ilovasi tufayli kerakli o'zgaruvchilarni o'rnatadigan juda jiddiy dastur moduli. Saqlangan protsedura o'z-o'zidan serverda ishlayotganligi sababli, server va mijoz ilovasi o'rtasida (ba'zi hisob-kitoblar uchun) katta hajmdagi ma'lumotlar almashinuvining oldini olish mumkin. Bu sizga SQL serverlariga yukni kamaytirish imkonini beradi, bu esa, albatta, ularning egalariga foyda keltiradi. Kichik turlardan biri T SQLda saqlanadigan protseduralardir, ammo ularni o'rganish ta'sirchan ma'lumotlar bazalarini yaratuvchilar uchun zarurdir. Saqlangan protseduralarni o'rganishda foydali bo'lishi mumkin bo'lgan juda ko'p, hatto juda ko'p sonli nuanslar ham mavjud, ammo bu dasturlash bilan shug'ullanishni rejalashtirganlar, shu jumladan professionallar uchun ko'proq kerak.

Saqlangan protsedura - ma'lumotlar bazasi ob'ekti, u bir marta kompilyatsiya qilinadigan va serverda saqlanadigan SQL ko'rsatmalari to'plamidir. Saqlangan protseduralar oddiy yuqori darajadagi til protseduralariga juda o'xshaydi, ular kiritish va chiqish parametrlari va mahalliy o'zgaruvchilarga ega bo'lishi mumkin, ular raqamli hisoblar va belgilar ma'lumotlari ustida operatsiyalarni bajarishi mumkin, ularning natijalari o'zgaruvchilar va parametrlarga tayinlanishi mumkin. Saqlangan protseduralar bajarilishi mumkin standart operatsiyalar ma'lumotlar bazalari bilan (ham DDL, ham DML). Bundan tashqari, saqlangan protseduralar tsikllar va filiallarga ruxsat beradi, ya'ni ular bajarish jarayonini boshqarish uchun ko'rsatmalardan foydalanishi mumkin.

Saqlangan protseduralar foydalanuvchi tomonidan belgilangan funktsiyalarga (UDF) o'xshaydi. Asosiy farq shundaki, foydalanuvchi tomonidan belgilangan funksiyalar SQL bayonotidagi boshqa ifodalar kabi ishlatilishi mumkin, saqlangan protseduralar esa CALL funksiyasi yordamida chaqirilishi kerak:

QO'NG'IROQ protsedurasi (…)

EXECUTE protsedurasi (…)

Saqlangan protseduralar bir nechta natijalarni, ya'ni SELECT so'rovi natijalarini qaytarishi mumkin. Bunday natijalar to'plamlari kursorlar, natijalar to'plami ko'rsatkichini qaytaruvchi boshqa saqlangan protseduralar yoki ilovalar yordamida qayta ishlanishi mumkin. Saqlangan protseduralar, shuningdek, ma'lumotlarni qayta ishlash uchun e'lon qilingan o'zgaruvchilarni va kursorlarni o'z ichiga olishi mumkin, bu sizga jadvaldagi bir nechta satrlar bo'ylab aylanish imkonini beradi. SQL standarti IF, LOOP, REPEAT, CASE va boshqalar bilan ishlash imkonini beradi. Saqlangan protseduralar o'zgaruvchilarni qabul qilishi, natijalarni qaytarishi yoki o'zgaruvchilarni o'zgartirishi va o'zgaruvchining e'lon qilingan joyiga qarab ularni qaytarishi mumkin.

Saqlangan protseduralarni amalga oshirish bir DBMSdan boshqasiga farq qiladi. Ko'pgina yirik ma'lumotlar bazasi sotuvchilari ularni u yoki bu shaklda qo'llab-quvvatlaydi. DBMSga qarab, saqlangan protseduralar SQL, Java, C yoki C++ kabi turli dasturlash tillarida amalga oshirilishi mumkin. SQL-da yozilmagan saqlanadigan protseduralar SQL so'rovlarini mustaqil ravishda bajarishi yoki bajarmasligi mumkin.

Orqada

Mantiqni boshqa ilovalar bilan almashish. Saqlangan protseduralar funksionallikni qamrab oladi; bu turli ilovalar bo'ylab ma'lumotlarga kirish va boshqarish uchun ulanishni ta'minlaydi.

Foydalanuvchilarni ma'lumotlar bazasi jadvallaridan ajratish. Bu sizga saqlangan protseduralarga kirish imkonini beradi, lekin jadval ma'lumotlarining o'ziga emas.

Himoya mexanizmini ta'minlaydi. Oldingi bandga ko'ra, agar siz ma'lumotlarga faqat saqlangan protseduralar orqali kirishingiz mumkin bo'lsa, SQL DELETE buyrug'i orqali boshqa hech kim ma'lumotlaringizni o'chira olmaydi.

Tarmoq trafigining kamayishi natijasida yaxshilangan ijro. Saqlangan protseduralar yordamida bir nechta so'rovlarni birlashtirish mumkin.

Qarshi

Ishning ko'p qismi server tomonida, kamroq esa mijoz tomonida bajarilishi tufayli ma'lumotlar bazasi serveriga yukning ortishi.

Siz ko'p narsalarni o'rganishingiz kerak bo'ladi. Saqlangan protseduralaringizni yozish uchun MySQL ifoda sintaksisini o'rganishingiz kerak bo'ladi.

Siz ilova mantig'ini ikki joyda takrorlaysiz: server kodi va saqlangan protseduralar uchun kod, shu bilan ma'lumotlarni manipulyatsiya qilish jarayonini murakkablashtirasiz.

Bir DBMS dan boshqasiga (DB2, SQL Server va boshqalar) o'tish muammolarga olib kelishi mumkin.

Saqlangan protseduralarning maqsadi va afzalliklari

Saqlangan protseduralar ishlashni yaxshilaydi, dasturlash imkoniyatlarini oshiradi va ma'lumotlar xavfsizligi xususiyatlarini qo'llab-quvvatlaydi.

Tez-tez ishlatiladigan so'rovni saqlash o'rniga, mijozlar tegishli saqlangan protseduraga murojaat qilishlari mumkin. Saqlangan protsedura chaqirilganda, uning tarkibi darhol server tomonidan qayta ishlanadi.

Saqlangan protseduralar so'rovni amalda bajarishdan tashqari, hisob-kitoblarni amalga oshirish va ma'lumotlarni manipulyatsiya qilish imkonini beradi - DDL bayonotlarini o'zgartirish, o'chirish, bajarish (barcha DBMSlarda emas!) va boshqa saqlangan protseduralarni chaqirish va murakkab tranzaksiya mantiqini bajarish. Bitta bayonot tarmoq bo'ylab yuzlab buyruqlarni yuborishdan va xususan, mijozdan serverga katta hajmdagi ma'lumotlarni uzatish zaruratidan qochib, saqlangan protsedura tarkibidagi murakkab skriptni chaqirish imkonini beradi.

Aksariyat DBMSlarda saqlangan protsedura birinchi marta ishga tushirilganda, u kompilyatsiya qilinadi (tahlil qilinadi va ma'lumotlarga kirish rejasi tuziladi). Kelajakda uni qayta ishlash tezroq. Oracle DBMS ma'lumotlar lug'atida saqlangan protsessual kodni sharhlaydi. Oracle 10g dan boshlab, C tilida saqlangan protsessual kodning mahalliy kompilyatsiyasi, so'ngra maqsadli mashinaning mashina kodiga kiritilishi qo'llab-quvvatlanadi, shundan so'ng saqlangan protsedura chaqirilganda uning kompilyatsiya qilingan ob'ekt kodi bevosita bajariladi.

Dasturlash qobiliyatlari

Yaratilgan saqlangan protsedura istalgan vaqtda chaqirilishi mumkin, bu modullikni ta'minlaydi va kodni qayta ishlatishni rag'batlantiradi. Ikkinchisi ma'lumotlar bazasini saqlashni osonlashtiradi, chunki u biznes qoidalarini o'zgartirishdan ajratiladi. Saqlangan protsedurani istalgan vaqtda yangi qoidalarga muvofiq o'zgartirishingiz mumkin. Shundan so'ng, undan foydalanadigan barcha ilovalar to'g'ridan-to'g'ri o'zgartirishlarsiz avtomatik ravishda yangi biznes qoidalariga mos keladi.

Xavfsizlik

Saqlangan protseduralardan foydalanish foydalanuvchilarning ma'lumotlar bazasi jadvallariga to'g'ridan-to'g'ri kirishini cheklash yoki butunlay yo'q qilish imkonini beradi, bu esa foydalanuvchilarga faqat ma'lumotlarga bilvosita va qat'iy tartibga solinadigan kirishni ta'minlaydigan saqlangan protseduralarni bajarish uchun ruxsatlarni qoldiradi. Bundan tashqari, ba'zi DBMSlar saqlangan protseduraning matnli shifrlanishini (o'rash) qo'llab-quvvatlaydi.

Ushbu xavfsizlik xususiyatlari ma'lumotlar bazasi strukturasini foydalanuvchidan ajratib, ma'lumotlar bazasi yaxlitligi va ishonchliligini ta'minlash imkonini beradi.

SQL in'ektsiyasi kabi harakatlar ehtimoli kamayadi, chunki yaxshi yozilgan saqlangan protseduralar so'rovni DBMSga o'tkazishdan oldin kiritish parametrlarini qo'shimcha ravishda tekshiradi.

Saqlangan protseduralarni amalga oshirish

Saqlangan protseduralar odatda SQL tili yoki uning tanlangan ma'lumotlar bazasida o'ziga xos amalga oshirilishi yordamida yaratiladi. Masalan, bu maqsadlar uchun Microsoft SQL Server ma'lumotlar bazasida Transact-SQL tili, Oracle da - PL/SQL, InterBase va Firebird-da - PSQL, PostgreSQLda - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 da - SQL/PL (ingliz tili), Informix da - SPL. MySQL SQL:2003 standartiga juda mos keladi, uning tili SQL/PLga o'xshaydi.

Ba'zi DBMSlar mustaqil bajariladigan fayllarni yaratishi mumkin bo'lgan har qanday dasturlash tilida yozilgan saqlangan protseduralardan foydalanishga imkon beradi, masalan, C++ yoki Delphi. Microsoft SQL Server terminologiyasida bunday protseduralar kengaytirilgan saqlangan protseduralar deb ataladi va oddiygina Win32 DLL-da joylashgan funktsiyalardir. Va, masalan, Interbase va Firebird-da DLL/SO dan chaqirilgan funksiyalar boshqa nomga ega - UDF (User Defined Function). MS SQL 2005 har qanday .NET tilida saqlangan protseduralarni yozish imkoniyatini taqdim etdi va kelajakda kengaytirilgan saqlangan protseduralardan voz kechish rejalashtirilgan. Oracle DBMS, o'z navbatida, Java-da saqlangan protseduralarni yozish imkonini beradi. IBM DB2 da saqlangan protseduralar va funktsiyalarni an'anaviy dasturlash tillarida yozish an'anaviy usul bo'lib, boshidanoq qo'llab-quvvatlanadi va SQL protsessual kengaytmasi ushbu DBMSga faqat ANSI standartiga kiritilganidan keyin juda kech versiyalarida qo'shilgan. Informix shuningdek, Java va C-dagi protseduralarni qo'llab-quvvatlaydi.

Oracle DBMS da saqlangan protseduralar paketlar deb ataladigan narsalarga birlashtirilishi mumkin. Paket ikki qismdan iborat - saqlangan protsedura ta'rifini belgilaydigan paket spetsifikatsiyasi va uning amalga oshirilishini o'z ichiga olgan paket tanasi. Shu tarzda Oracle interfeysni ajratish imkonini beradi dastur kodi uni amalga oshirishdan.

IBM DB2 DBMS da saqlangan protseduralar modullarga birlashtirilishi mumkin.

Sintaksis

`p2`() PROSEDURASINI YARATISH

SQL XAVFSIZLIK TA'NIYORI

Izoh “Protsedura”

"Salom dunyo!" ni tanlang;

Kodning birinchi qismi saqlangan protsedurani yaratadi. Keyingisi ixtiyoriy parametrlarni o'z ichiga oladi. Keyin nom va nihoyat, protseduraning o'zi keladi.

Saqlangan protseduraning 4 ta xarakteristikasi:

Til: Portativlik uchun standart SQL hisoblanadi.

Deterministik: agar protsedura har doim bir xil natijani qaytarsa ​​va bir xil kirish parametrlarini olsa. Bu replikatsiya va ro'yxatga olish jarayoni uchun. Standart qiymat DETERMINISTIC EMAS.

SQL xavfsizligi: qo'ng'iroq paytida foydalanuvchi huquqlari tekshiriladi. INVOKER - saqlangan protsedurani chaqiruvchi foydalanuvchi. DEFINER - protseduraning "yaratuvchisi". Standart qiymat DEFINER.

Sharh: Hujjatlar uchun standart qiymat ""

Saqlangan protsedurani chaqirish

Saqlangan_protsedura_nomiga qo'ng'iroq qiling (param1, param2, ....)

CALL procedure1(10 , "string parametri" , @parameter_var);

Saqlangan protsedurani o'zgartirish

MySQL-da protseduralarni o'zgartirish uchun ALTER PROCEDURE bayonoti mavjud, ammo u faqat ma'lum xususiyatlarni o'zgartirish uchun mos keladi. Agar siz protseduraning parametrlarini yoki tanasini o'zgartirishingiz kerak bo'lsa, uni o'chirishingiz va qayta yaratishingiz kerak.

Olib tashlashsaqlanadiprotseduralar

AGAR MAVJUD BOLSA TOSHLASH TARTIBI p2;

Bu oddiy buyruq. IF EXISTS bayonoti, agar bunday protsedura mavjud bo'lmasa, xatolikni aniqlaydi.

Variantlar

PROTSEDURI YARATISH proc1(): bo'sh ro'yxat parametrlari

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): bitta kirish parametri. IN so'zi ixtiyoriy, chunki standart parametrlar IN (in) dir.

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): bitta parametr qaytarildi.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): bitta parametr, ham kiritish, ham qaytarish.

O'zgaruvchilar deklaratsiyasi sintaksisi quyidagicha ko'rinadi:

varname DATA-TYPE DEFAULT standart qiymatni E'lon qiling;

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 (parametrlarsiz)
. DYNAMIC RESULT SET protsedura qaytgandan keyin ochiq qoladigan ma'lum miqdordagi kursorlarni ochishi mumkinligini bildiradi.

Eslatmalar
Tarmoq va stekning haddan tashqari yuklanishi tufayli saqlangan protseduralarda (birinchi navbatda katta raqamlar va belgilar qatorlari) ko'p parametrlardan foydalanish tavsiya etilmaydi. Amalda Transact-SQL, PL/SQL va Informix ning mavjud dialektlari standartdan parametrlarni e’lon qilish va ishlatishda, o‘zgaruvchilar deklaratsiyasida ham, kichik dasturlarni chaqirishda ham sezilarli farqlarga ega. Saqlangan protsedura kesh hajmini baholash uchun Microsoft quyidagi taxminiylikdan foydalanishni tavsiya qiladi:
=(bir vaqtning o'zida foydalanuvchilarning maksimal soni)*(eng katta ijro rejasining hajmi)*1,25. Sahifalardagi ijro rejasining hajmini aniqlash quyidagi buyruq yordamida amalga oshirilishi mumkin: DBCC MEMUSAGE.

Jarayonni chaqirish

Ko'pgina mavjud DBMSlarda saqlangan protseduralar operator yordamida chaqiriladi:

PROSEDURASINI BAJARISH [(][)]

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

Jarayon deklaratsiyasiga misol

CREATE PROCEDURE Proc1 AS //protsedurani e'lon qilish
TANLANGAN SName, shahar FROM SalesPeople WHERE Reyting>200 //kursorni e'lon qilish.
OPEN Cur1 //kursorni oching
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 nomli ikkita tartibni farqlash uchun har biriga muqobil va noyob ism(aniq ism). 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 mavjud bo'lmasa, u holda berilgan nom bilan barcha funktsiyalar aniqlanadi.
. Qo'shimcha tahlil qilish uchun faqat tegishli subprogrammalar bu foydalanuvchi bajarish huquqiga ega (EXECUTE).
. 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, unda malakali nomi qisqaroq bo'lgani tanlanadi.
Amalda, Oracle faqat paketda e'lon qilingan funktsiyalar uchun polimorfizmni qo'llab-quvvatlaydi, DB@ - ichida turli sxema, va Sybase va MS SQL Serverda ortiqcha yuklash taqiqlanadi.

Jarayonlarni o'chirish va o'zgartirish
Jarayonni olib tashlash uchun operatordan foydalaning:

Jarayonni o'zgartirish uchun operatordan foydalaning:

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

OXIRI

Jarayonlarni bajarish uchun imtiyozlar

GRANT IJRO BERISH TO |JAMOAT [GRANT VARIANT BILAN]

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

Biz saqlangan protseduralar so'rovlar samaradorligini pasaytirishi mumkin bo'lgan vaziyatni ko'rib chiqamiz.

MS SQL Server 2000 da saqlangan protseduralarni kompilyatsiya qilishda saqlangan protseduralar protsedura keshiga joylashtiriladi, bu esa saqlangan protsedura kodini tahlil qilish, optimallashtirish va kompilyatsiya qilish zaruratini bartaraf etish orqali ularni bajarishda ish faoliyatini yaxshilashi mumkin.
Boshqa tomondan, saqlangan protseduraning tuzilgan kodini saqlashda teskari ta'sir ko'rsatishi mumkin bo'lgan tuzoqlar mavjud.
Gap shundaki, saqlangan protsedurani kompilyatsiya qilishda protsedura kodini tashkil etuvchi operatorlarning bajarilish rejasi tuziladi; shunga ko'ra, agar kompilyatsiya qilingan saqlanadigan protsedura keshlangan bo'lsa, unda uning bajarilish rejasi keshlanadi va shuning uchun saqlangan protsedura bo'lmaydi. muayyan vaziyat va so'rov parametrlari uchun optimallashtirilgan.
Keling, buni isbotlash uchun kichik tajriba qilaylik.

1-QADAM. Ma'lumotlar bazasini yaratish.
Tajriba uchun biz alohida ma'lumotlar bazasini yaratamiz.

MA'LUMOTLAR BAZASINI YARATING test_sp_perf
YON (NAME="test_ma'lumotlar", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
KIRISH (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

2-QADAM. Jadval yaratish.
JADVAL YARATISH sp_perf_test(ustun 1 int, ustun 2 char(5000))

3-QADAM. Jadvalni sinov qatorlari bilan to'ldirish. Ikki nusxadagi qatorlar ataylab jadvalga qo'shiladi. 1 dan 10 000 gacha raqamlar bilan 10 000 qator va 50 000 raqamlari bilan 10 000 qator.

E'lon qiling @i int
SET @i=1
WHILE(@i<10000)
BOSHLANISHI
INSERT INTO sp_perf_test(1-ustun, 2-ustun) VALUES(@i,"Test string #"+CAST(@i belgisi(8)))
INSERT INTO sp_perf_test(ustun 1, ustun 2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SET @i= @i+1
OXIRI

sp_perf_test dan COUNT(*) TANI TANlang
BOSH

4-QADAM. Klasterli bo'lmagan indeks yaratish. Amalga oshirish rejasi protsedura bilan keshlanganligi sababli, indeks barcha qo'ng'iroqlarda bir xil tarzda qo'llaniladi.

sp_perf_test (ustun 1) da CL_perf_test NO KLASTER BO'LMAGAN INDEKSI YARATING
BOSH

5-QADAM. Saqlangan protsedurani yaratish. Protsedura oddiygina shart bilan SELECT operatorini bajaradi.

PROC proc1 yaratish (@param int)
AS
1-ustunni, 2-ustunni sp_perf_test QAYoDAN TANGLASH ustuni1=@param
BOSH

6-QADAM. Saqlangan protsedurani ishga tushirish. Zaif protsedurani ishga tushirishda selektiv parametr maxsus ishlatiladi. Jarayon natijasida biz 1 qatorni olamiz. Ijro rejasi klasterli bo'lmagan indeksdan foydalanishni ko'rsatadi, chunki So'rov tanlangan va bu qatorni olishning eng yaxshi usuli. Bitta qatorni olish uchun optimallashtirilgan protsedura protsessual keshda saqlanadi.

EXEC proc1 1234
BOSH

7-QADAM. Tanlanmagan parametr bilan saqlangan protsedurani ishga tushiring. Parametr sifatida ishlatiladigan qiymat 50000. Birinchi ustunda bu qiymatga ega 10000 ga yaqin satrlar mavjud, shuning uchun klasterli bo'lmagan indeks va xatcho'plarni qidirish operatsiyasidan foydalanish samarasiz, lekin ijro rejasi bilan tuzilgan kod saqlanadi. protsessual kesh, bu ishlatiladi. Amalga oshirish rejasi buni, shuningdek, 9999 qator uchun xatcho'plarni qidirish operatsiyasi bajarilganligini ko'rsatadi.

EXEC proc1 50000
BOSH

8-QADAM. Birinchi maydon 50000 ga teng bo'lgan qatorlarni tanlashni amalga oshirish. Alohida so'rovni bajarishda so'rov optimallashtiriladi va birinchi ustun uchun ma'lum bir qiymat bilan kompilyatsiya qilinadi. Natijada, so'rov optimallashtiruvchisi maydonning ko'p marta takrorlanishini aniqlaydi va jadvalni skanerlash operatsiyasidan foydalanishga qaror qiladi, bu holda klasterli bo'lmagan indeksni ishlatishdan ko'ra ancha samaraliroqdir.

1-ustunni, 2-ustunni sp_perf_test QERDAN TANGLASH ustuni1=50000
BOSH

Shunday qilib, biz saqlangan protseduralardan foydalanish har doim ham so'rovlar samaradorligini oshirishi mumkin emas degan xulosaga kelishimiz mumkin. O'zgaruvchan qatorlar soniga ega natijalar bilan ishlaydigan va turli xil bajarish rejalaridan foydalanadigan saqlangan protseduralarga juda ehtiyot bo'lishingiz kerak.
Siz MS SQL serveringizda tajribani takrorlash uchun skriptdan foydalanishingiz mumkin.