3-4- ish. Mavzu. Funktsiyalar va saqlangan protseduralarni yaratish, o'zgartirish, qo'llash va o'chirish (10 balli)

Guruch. 10.2.
DECLARE @ int bilan;
EXEC summasi 10.5, @ c OUTPUT;
PRINT 0c; - 15 ko'rsatiladi
E'lon qiling Gi int = 5;
- qo'ng'iroq qilishda standart qiymatdan foydalaning
EXEC summasi Gi, DEFAULT, 0s OUTPUT;
PRINT 0c; - 5 ko'rsatiladi
Keling, protsedura tugaydigan qaytarish kodini tahlil qilish bilan bir misolni ko'rib chiqaylik. Berilgan yillar oralig'ida Bookl jadvalidagi qancha kitob nashr etilganligini hisoblash kerak bo'lsin. Bundan tashqari, agar dastlabki yil yakuniy yildan ko'p bo'lsa, protsedura "1" ni qaytaradi va hisobga olinmaydi, aks holda biz kitoblar sonini hisoblaymiz va 0 ni qaytaramiz:
PROC CREATE dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
AGAR 0Birinchi Yil> 0O'tgan Yil QAYTISH 1
SET @ natija = (dbo.Bookl FROM COUNT (*) TANLANG
0Birinchi Yil VA 0O'tgan Yil O'rtasidagi QAYERDA);
Ushbu protsedurani chaqirish variantini ko'rib chiqing, bunda qaytarish kodi 0ret butun o'zgaruvchisida saqlanadi, shundan so'ng uning qiymati tahlil qilinadi (bu holda u 1 bo'ladi). PRINT operatorida qo‘llaniladigan CAST funksiyasi Gres butun o‘zgaruvchisining qiymatini satr turiga aylantirish uchun ishlatiladi:
E'lon qiling 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Boshlangan yil yakuniy yildan kattaroq"
Chop etish "Kitoblar soni" + CAST (Gres as varchar (20))
Saqlangan protseduralar nafaqat jadvaldagi ma'lumotlarni o'qishi, balki ma'lumotlarni o'zgartirishi va hatto jadvallar va boshqa bir qator ma'lumotlar bazasi ob'ektlarini yaratishi mumkin.
Biroq, saqlangan protseduradan sxemalar, funktsiyalar, triggerlar, protseduralar va ko'rinishlarni yarata olmaysiz.
Quyidagi misolda bu imkoniyatlar ham, vaqtinchalik ob'ektlarning ko'lami ham ko'rsatilgan. Quyidagi saqlangan protsedura # thab2 vaqtinchalik jadvalining mavjudligini tekshiradi; agar bu jadval mavjud bo'lmasa, uni yaratadi. Shundan so'ng, ikkita ustunning qiymatlari # Tab2 jadvaliga kiritiladi va jadval mazmuni ko'rsatiladi. SELECT bayonoti:
PROC CREATE My_Procl (@id int, @name varchar (30))
AGAR OBJECT_ID ("tempdb.dbo. # Tab21) NULL bo'lsa
INSERT INTO dbo. # Tab2 (id, name) VALUES (0id, 0name)
dbo dan * ni tanlang. # Tab2 - №1
Saqlangan protseduraning birinchi qo'ng'irog'idan oldin biz unda ishlatiladigan vaqtinchalik # Tab2 jadvalini yaratamiz. EXEC operatoriga e'tibor bering. Oldingi misollarda parametrlar "pozitsiya bo'yicha" protsedurasiga o'tkazildi va bu holda parametrlarni o'tkazish uchun boshqa format qo'llaniladi - "nomi bo'yicha", parametr nomi va uning qiymati aniq ko'rsatilgan:
CREATE TABLE dbo. # Tab2 (id int, nomi varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
dbo dan * ni tanlang # Tab2; –№2
Yuqoridagi misolda SELECT operatori ikki marta bajariladi: birinchi marta - protsedura ichida, ikkinchi marta - chaqiruvchi kod fragmentidan ("# 2" izohi bilan belgilangan).
Jarayonning ikkinchi chaqiruvidan oldin biz vaqtinchalik jadvalni o'chirib tashlaymiz # Tab2. Keyin saqlangan protseduradan bir xil nomdagi vaqtinchalik jadval yaratiladi:
JADVALNI QILISH dbo # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
dbo dan * ni tanlang # Tab2; –№2
Bunday holda, faqat protsedura ichidagi SELECT operatori ("Xa 1" izohi bilan) ma'lumotlarni ko'rsatadi. SELECT “# 2” xatolikka olib keladi, chunki saqlangan protsedurada yaratilgan vaqtinchalik jadval protsedura qaytib kelganda tempdb dan allaqachon o'chirilgan.
DROP PROCEDURE bayonoti yordamida saqlangan protsedurani o'chirib qo'yishingiz mumkin. Uning formati quyida ko'rsatilgan. Bitta operator bir nechta saqlangan protseduralarni vergul bilan ajratib ko'rsatish orqali o'chirib tashlashi mumkin:
DROP (PROC I PROCEDURE) (protsedura) [
Masalan, avval yaratilgan protsedura summasini olib tashlaymiz:
DROP PROC summasi;
ALTER PROCEDURE bayonotidan foydalanib, mavjud protseduraga o'zgartirishlar kiritishingiz mumkin (va aslida uni bekor qilishingiz mumkin)
PROC qisqartmasi). ALTER kalit so'zidan tashqari, bayonot formati CREATE PROCEDURE formatiga o'xshaydi. Masalan, dbo protsedurasini o'zgartiramiz. rownum egasining xavfsizlik kontekstida bajarish variantini o'rnatish orqali:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner – o‘rnatish mumkin bo‘lgan variant
AGAR 0Birinchi Yil> 0O'tgan Yil QAYTIB BOSHLASA
SET 0result = (dbo.Bookl FROM COUNT (*) TANLANG
SFirsYear VA SLastYear O'rtasidagi QERDA);
Ba'zi hollarda buyruqni dinamik ravishda shakllantirish va uni ma'lumotlar bazasi serverida bajarish kerak bo'lishi mumkin. Bu vazifani EXEC operatori yordamida ham hal qilish mumkin. Quyidagi misol, Yil atributi o'zgaruvchi yordamida o'rnatilgan qiymatga teng bo'lishi sharti bilan Bookl jadvalidan yozuvlarni oladi:
E'lon qiling 0y int = 2000;
EXEC ("Tanlash * FROM dbo.Bookl WHERE =" +@y) ;
Dinamik ravishda yaratilgan ko'rsatmalarning bajarilishi amalga oshirish uchun zarur shart-sharoitlarni yaratadi kompyuter hujumlari"SQL injection" deb yozing (ing. SQL in'ektsiyasi). Hujumning mohiyati shundaki, tajovuzkor o'zining SQL kodini dinamik ravishda yaratilgan so'rovga kiritadi. Bu odatda foydalanuvchi kiritish natijalaridan almashtirilgan parametrlar olinganda sodir bo'ladi.
Oldingi misolni biroz o'zgartiramiz:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - biz buni foydalanuvchidan oldik
Agar biz SET bayonotida berilgan satr qiymatini foydalanuvchidan oldik deb faraz qilsak (qanday bo'lishidan qat'iy nazar, masalan, veb-ilova orqali), unda misol bizning kodimizning "muntazam" harakatini ko'rsatadi.
DECLARE 0y varchar (100);
SET 0y = "2000; dbo.Book2 dan o'chirish"; - in'ektsiya
EXEC ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
Bunday hollarda imkon qadar sp_executcsql tizimi saqlanadigan protseduradan foydalanish tavsiya etiladi, bu esa SQL in'ektsiyasi uchun to'siqlardan biri bo'lgan parametrlar turini boshqarish imkonini beradi. Uning formatini batafsil ko'rib chiqmasdan, keling, avval keltirilgan misolga o'xshash misolni tahlil qilaylik:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
Bu so'rovda ishlatiladigan parametr turini aniq belgilaydi va bajarish paytida SQL Server tomonidan nazorat qilinadi. Qo'shtirnoq oldidagi "N" harfi protsedura talab qilganidek, bu Unicode literal doimiysi ekanligini ko'rsatadi. Parametrga nafaqat doimiy qiymat, balki boshqa o'zgaruvchining qiymati ham berilishi mumkin.
Ushbu qo'llanma sizga qanday qilib ko'rsatib beradi SQL Serverda protseduralarni yaratish va tushirish(Transact-SQL) sintaksis va misollar bilan.
Tavsif
SQL da Server protsedurasi parametrlarni o'tkazishingiz mumkin bo'lgan saqlangan dasturdir. Funktsiya kabi qiymatni qaytarmaydi. Biroq, u muvaffaqiyatli / muvaffaqiyatsizlik holatini uni chaqirgan protseduraga qaytarishi mumkin.
Jarayon yaratish
SQL Serverda (Transact-SQL) o'zingizning saqlangan protseduralaringizni yaratishingiz mumkin. Keling, batafsil ko'rib chiqaylik.
Sintaksis
SQL Serverdagi (Transact-SQL) protseduralar sintaksisi:
YARAT (PROSEDUR | PROC) protsedura_nomi
[@parametr ma'lumotlar turi
[VARYING] [= default] [OUT | OUTPUT | FAQAT O'QISH]
, @parametr ma'lumotlar turi
[VARYING] [= default] [OUT | OUTPUT | FAQAT O'QISH]]
[BILAN (shifrlash | QAYTA TUZISH | bandi sifatida ISHLATISH)]
[REPLIKA UCHUN]
AS
BOSHLASH
bajariladigan_bo'lim
OXIRI;
Parametrlar yoki argumentlar
schema_name - saqlangan protsedura tegishli bo'lgan sxemaning nomi.
procedure_name - bu protsedurani SQL Serverda tayinlash uchun nom.
@parameter - protseduraga bir yoki bir nechta parametr uzatiladi.
type_schema_name - agar mavjud bo'lsa, ma'lumotlar turiga ega bo'lgan sxema.
Ma'lumotlar turi @parameter uchun ma'lumotlar turi.
VARYING - Natijalar to'plami chiqish parametri bo'lsa, kursor parametrlari uchun o'rnatiladi.
asl qiymati @parametrga tayinlanadigan standart qiymatdir.
OUT - Bu @parametr chiqish parametri degan ma'noni anglatadi.
OUTPUT - Bu @parameter chiqish parametri ekanligini bildiradi.
READONLY - Bu @parametrni saqlangan protsedura tomonidan qayta yozish mumkin emas degan ma'noni anglatadi.
Shifrlash - Bu saqlangan protseduraning manbasi SQL Server tizimi ko'rinishlarida oddiy matn sifatida saqlanmasligini anglatadi.
Qayta kompilyatsiya qilish - bu so'rov rejasi ushbu saqlangan protsedura uchun keshda saqlanmasligini anglatadi.
AS EXECUTE AS - saqlangan protsedurani bajarish uchun xavfsizlik kontekstini o'rnatadi.
REPLIKATSIYA UCHUN - Bu saqlangan protsedura faqat replikatsiya vaqtida bajarilishini bildiradi.
Misol
SQL Serverda (Transact-SQL) saqlangan protsedurani yaratish misolini ko'rib chiqamiz.
Quyida protseduraning oddiy misoli keltirilgan: