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

Looplar va tarmoqlanuvchi operatorlar
Tanlov yozuvlari ustidan siklni tashkil qiluvchi FOR SELECT ... DO buyrug'idan tashqari yana bir turdagi sikl mavjud - WHILE ... DO, bu siklni istalgan shartlarni tekshirish asosida tashkil qilish imkonini beradi. WHILE .. DO siklidan foydalangan holda CP ga misol keltiramiz. Ushbu tartib 0 dan 99 gacha bo'lgan butun sonlarning kvadratlarini qaytaradi:
PROCEDJRE QUAD YARATING
QAYTIRISH (QUADRAT BUTUN SAYI)
AS
VARIABLE I INTEGERNI E'lon qilish;
BOSHLASH
I = 1;
WHILE (i<100) DO
BOSHLASH
QUADRAT = I * I;
I = I + 1;
TO'XTIRISH;
OXIRI
OXIRI
SELECT FROM QUAD so'rovini bajarish natijasida biz bitta QUADRAT ustunini o'z ichiga olgan jadvalni olamiz, unda 1 dan 99 gacha butun sonlar kvadratlari bo'ladi.
Saqlangan protseduralar tili SQL tanlovi va klassik sikl natijalarini takrorlashdan tashqari, ba'zi \ so'zlarning bajarilishiga qarab tarmoqlanishni tashkil qilish imkonini beruvchi IF ... THEN..ELSE operatoridan foydalanadi.Uning sintaksisi: Paskal va C kabi yuqori darajali dasturlash tillaridagi ko‘pchilik tarmoqlanuvchi operatorlarga o‘xshash.
Keling, quyidagi amallarni bajaradigan saqlangan protseduraning murakkabroq misolini ko'rib chiqaylik.

1. 
   Table_example jadvalidagi o'rtacha narxni hisoblaydi ("Birlamchi kalitlar va generatorlar jadvallari" bo'limiga qarang)
   
2. 
   Keyin, jadvaldagi har bir yozuv uchun u quyidagi tekshiruvni amalga oshiradi, agar mavjud narx (PRICE) o'rtacha narxdan katta bo'lsa, u holda u o'rtacha narx qiymatiga teng narxni, shuningdek, belgilangan belgilangan foizni belgilaydi.
   
3. 
   Agar mavjud narx o'rtacha narxdan past yoki unga teng bo'lsa, u holda oldingi narxga teng narxni, shuningdek, oldingi va o'rtacha narxlar o'rtasidagi farqning yarmini qo'shib qo'yadi.
   
4. 
   Jadvaldagi barcha o'zgartirilgan qatorlarni qaytaradi.
   

Birinchidan, SP nomini, shuningdek, kirish va chiqish parametrlarini aniqlaymiz.Bularning barchasi saqlanadigan protsedura sarlavhasida yozilgan.
TARTIBI YARAT Narxlarni oshirish (
2 LINDAN IKKIRTTA ANQLIKni oshirish)
QAYTISHLAR (ID INTEGER, NOMI VARCHAR (SO), yangi_narx DOUBLE
PRECISION) AS
Jarayon IncreasePrices deb nomlanadi, u DOUBLE PRECISION tipidagi Peiceni21nciease bitta kirish parametriga va 3 chiqish parametriga ega - ID, NAME va new_pnce. E'tibor bering, birinchi ikkita chiqish parametrlari biz ishlayotgan Table_misolidagi maydonlar bilan bir xil nomga ega.Bu saqlangan protsedura tilining qoidalari bilan ruxsat etiladi.
Endi biz o'rtacha qiymatni saqlash uchun ishlatiladigan mahalliy o'zgaruvchini e'lon qilishimiz kerak. Bu deklaratsiya quyidagicha ko'rinadi:
O'ZGARCHI avg_price DOUBLE ANIKLIGINI E'lon qilish;
Endi saqlangan protsedura tanasiga o'tamiz.HP korpusini oching BEGIN kalit so'zi.
Birinchidan, biz algoritmimizning birinchi bosqichini bajarishimiz kerak - o'rtacha narxni hisoblang. Buning uchun biz quyidagi shakldagi so'rovdan foydalanamiz:
AVG TANLASH (Price_l)
Jadvaldan_Misol
INTO: avg_price, -
Bu soʻrovda tanlangan soʻrovlar qatorlari orasidagi PRICE_1 maydonining oʻrtacha qiymatini qaytaruvchi AVG yigʻma funksiyasidan foydalaniladi — bizning holatlarimizda, butun jadval_jadvalidagi oʻrtacha PRICE_1. So'rov tomonidan qaytarilgan qiymat avg_price o'zgaruvchisiga joylashtiriladi. E'tibor bering, avg_pnce o'zgaruvchisi oldida ikki nuqta qo'yilgan - uni so'rovda ishlatiladigan maydonlardan ajratish uchun.
Ushbu so'rovning o'ziga xosligi shundaki, u har doim aynan bitta va yagona yozuvni qaytaradi. Bunday so'rovlar singleton so'rovlar deb ataladi.Va faqat shunday tanlovlar saqlanadigan protseduralarda qo'llanilishi mumkin. Agar so'rov bir nechta satrni qaytarsa, u har bir qaytarilgan qatorni qayta ishlash uchun tsiklni tashkil qiluvchi FOR SELECT ... DO tuzilmasi ko'rinishida formatlanishi kerak.
Shunday qilib, biz narxning o'rtacha qiymatini oldik. Endi siz butun jadvalni ko'rib chiqishingiz, har bir yozuvdagi narx qiymatini o'rtacha narx bilan solishtirishingiz va tegishli choralarni ko'rishingiz kerak.
Boshidan boshlab biz Table_example jadvalidagi har bir yozuv bo'yicha takrorlashni tashkil qilamiz.
UCHUN
TANLANGAN ID, NAME, PRICE_1
Jadvaldan_Misol
INTO: ID,: NAME,: new_price
QILING
BOSHLASH
/ * _ bu yerda har bir yozuvni ossifikatsiya qiling * /
OXIRI
Ushbu qurilish amalga oshirilganda, ma'lumotlar jadval_misolidan satr bo'yicha olinadi va har bir satrdagi maydon qiymatlari ID, NAME va new_pnce o'zgaruvchilariga tayinlanadi. Albatta, siz ushbu o'zgaruvchilar tashqi parametrlar sifatida e'lon qilinganligini eslaysiz, lekin tanlangan ma'lumotlar natijalar sifatida qaytarilishidan xavotirlanmang: chiqish parametrlariga biror narsa tayinlanganligi mijozning HPga qo'ng'iroq qilayotganini anglatmaydi. bu qiymatlarni darhol qabul qiladi.! Parametrlar faqat to'xtatib turish buyrug'i bajarilganda o'tkaziladi va undan oldin biz chiqish parametrlarini oddiy o'zgaruvchilar sifatida ishlatishimiz mumkin - bizning misolimizda biz buni new_price parametri bilan qilamiz.
Shunday qilib, BEGIN .. .END tsiklining tanasi ichida biz har bir satrning qiymatlarini qayta ishlashimiz mumkin. Esingizda bo'lsa, biz joriy narx o'rtacha narx bilan qanday solishtirganini aniqlashimiz va tegishli choralarni ko'rishimiz kerak. Ushbu taqqoslash jarayonini IF iborasi yordamida amalga oshiramiz:
AGAR (yangi_narx> avg_narx) KEYIN / * agar mavjud narx o'rtacha narxdan yuqori bo'lsa * /
BOSHLASH
/ * keyin o'rtacha narxga va belgilangan foizga teng yangi narxni belgilang * /
yangi_narx = (o'rtacha_narx + o'rtacha_narx * (2 foizga o'sish / 100));
Jadval_misolini YANGILASH
PRICE_1 =: yangi_narx
WHERE ID =: ID;
OXIRI
BOSHQA
BOSHLASH
/ * Agar mavjud narx o'rtacha narxdan past yoki unga teng bo'lsa, u holda narxni oldingi narxga teng qilib, oldingi va o'rtacha narxlar o'rtasidagi farqning yarmini qo'shing * /
new_price = (new_pnce + ((avg_pnce new_price) / 2));
Jadval_misolini YANGILASH
PRICE_1 =: yangi_narx
WHERE ID = .ID;
OXIRI
Ko'rib turganingizdek, natijada juda katta IF konstruktsiyasi paydo bo'ldi, agar buni / ** / belgilariga kiritilgan izohlar bo'lmaganida tushunish qiyin bo'lar edi.
Hisoblangan farqga ko'ra narxni o'zgartirish uchun biz mavjud yozuvlarni - bir yoki bir nechtasini o'zgartirishga imkon beruvchi UPDATE bayonotidan foydalanamiz. Qaysi yozuvda narxni o'zgartirish kerakligini aniq ko'rsatish uchun biz WHERE bandidagi asosiy kalit maydonidan foydalanamiz, uni joriy yozuv uchun ID qiymatini saqlaydigan o'zgaruvchining qiymati bilan taqqoslaymiz: ID =: ID. E'tibor bering, o'zgaruvchi identifikatoridan oldin ikki nuqta qo'yilgan.
IF ... THEN ... ELSE konstruktsiyasi bajarilgandan so'ng, ID, NAME va new_price o'zgaruvchilari mijozga \ protsedurani chaqiruvchiga qaytarishimiz kerak bo'lgan ma'lumotlarni o'z ichiga oladi. Buning uchun IF dan keyin ma'lumotlarni SP chaqirilgan joyga yuboradigan TO'XTIRISH buyrug'ini kiritish kerak.O'tkazish vaqtida protsedura to'xtatiladi va yangi yozuv talab qilinganda. SP, u yana davom etadi va bu FOR SELECT ... DO o'z so'rovining barcha yozuvlarini takrorlamaguncha davom etadi.
Shuni ta'kidlash kerakki, faqat saqlangan protsedurani to'xtatib turuvchi TO'XTARISH buyrug'iga qo'shimcha ravishda EXIT buyrug'i mavjud bo'lib, u satr o'tkazilgandan keyin saqlangan protsedurani tugatadi. Biroq, EXIT buyrug'i kamdan-kam qo'llaniladi, chunki u asosan shartga erishilganda tsiklni to'xtatish uchun kerak bo'ladi.
Bunday holda, protsedura SELECT operatori tomonidan chaqirilgan va EXIT orqali yakunlangan bo'lsa, oxirgi olingan qator qaytarilmaydi. Ya'ni, agar siz protsedurani to'xtatib, hali> ushbu satrni olishingiz kerak bo'lsa, ketma-ketlikni ishlatishingiz kerak
TO'XTIRISH;
CHIQISH;
EXIT ning asosiy maqsadi EXECUTE PROCEDURE orqali qo'ng'iroq qilish orqali yagona ma'lumotlar to'plamini, parametrlarni qaytarishdir. Bunday holda, chiqish parametrlarining qiymatlari o'rnatiladi, lekin SQL ma'lumotlar to'plami ulardan shakllanmaydi va protsedura tugaydi.
Keling, bir qarashda uning mantig'ini tushunishimiz uchun saqlangan protseduramizning butun matnini yozamiz:
TARTIBI YARAT Narxlarni oshirish (
Foiz 2 IKKITA ANQLIKni oshirish)
Qaytariladi (ID INTEGER, VARCHAR nomi (80),
yangi_narx EKTA ANIQLIK) AS
O'ZGARCHI avg_price DOUBLE ANIKLIGINI E'lon qilish;
BOSHLASH
AVG TANLASH (Price_l)
Jadvaldan_Misol
INTO: avg_price;
UCHUN
TANLANGAN ID, NAME, PRICE_1
Jadvaldan_Misol
INTO: ID,: NAME,: new_price
QILING
BOSHLASH
/ * har bir yozuvni shu yerda qayta ishlang * /
AGAR (new_pnce> avg_price) KEYIN / * agar mavjud narx o'rtacha narxdan yuqori bo'lsa * /
BOSHLASH
/ * o'rtacha narxga va belgilangan foizga teng yangi narxni belgilash * /
yangi_narx = (o'rtacha_narx + o'rtacha_narx * (2 foiz o'sish / 100));
Jadval_misolini YANGILASH
PRICE_1 =: yangi_narx
WHERE ID =: ID;
OXIRI
BOSHQA
BOSHLASH
/ * Agar mavjud narx o'rtacha narxdan past yoki unga teng bo'lsa, u holda narxni avvalgi narxga, shuningdek oldingi va o'rtacha narxlar o'rtasidagi farqning yarmiga tenglashtiradi * /
yangi_narx = (yangi_narx + ((o'rtacha_narx - yangi_narx) / 2));
Jadval_misolini YANGILASH
PRICE_1 =: yangi_narx
WHERE ID =: ID;
OXIRI
TO'XTIRISH;
OXIRI
OXIRI
Ushbu saqlangan protsedura namunasi asosiy saqlangan protsedura va trigger tili konstruktsiyalaridan foydalanishni ko'rsatadi. Keyinchalik, ba'zi umumiy muammolarni hal qilish uchun saqlangan protseduralardan qanday foydalanishni ko'rib chiqamiz.
Rekursiv saqlanadigan protseduralar
InterBase saqlanadigan protseduralar rekursiv bo'lishi mumkin. Bu shuni anglatadiki, siz o'zingizni saqlangan protsedura ichidan chaqirishingiz mumkin. Saqlangan protseduralarni joylashtirishning 1000 tagacha darajasiga ruxsat beriladi, lekin esda tutingki, serverdagi bo'sh resurslar maksimal HP joylashtirish darajasiga yetguncha tugashi mumkin.
Saqlangan protseduralarning keng tarqalgan qo'llanilishidan biri bu ma'lumotlar bazasida saqlangan daraxt tuzilmalarini manipulyatsiya qilishdir. Daraxtlar ko'pincha inventarizatsiya, omborxona, HR va boshqa keng tarqalgan ilovalarda qo'llaniladi.
Keling, ma'lum bir turdagi barcha mahsulotlarni ma'lum bir joylashtirish darajasidan boshlab tanlaydigan saqlangan protsedura misolini ko'rib chiqaylik.
Aytaylik, bizda quyidagi muammoli bayonot mavjud: bizda quyidagi turdagi ierarxik tuzilishga ega mahsulot katalogi mavjud:
Tovarlar
- Maishiy texnika
- Muzlatgichlar
- Uch kamerali
- Ikki kamerali
- Yagona kamerali
- Kir yuvish mashinalari
- Vertikal
- Frontal
- Klassik
- Tor
- Kompyuter texnologiyasi
....
Mahsulot toifalari ma'lumotnomasining ushbu tuzilmasi turli xil chuqurlikdagi filiallarga ega bo'lishi mumkin. va shuningdek, vaqt o'tishi bilan quriladi. Bizning vazifamiz har qanday tugundan boshlab "to'liq ismni kengaytirish" bilan katalogdan barcha barg elementlari tanlanganligini ta'minlashdir. Masalan, agar biz "Kir yuvish mashinalari" tugunini tanlasak, unda quyidagi toifalarni olishimiz kerak:
Kir yuvish mashinalari - Vertikal
Kir yuvish mashinalari - Front Classic
Kir yuvish mashinalari - Old tor
Tovarlar katalogidagi ma'lumotlarni saqlash uchun jadvallar tuzilishini aniqlaymiz. Daraxtni bitta jadvalda tashkil qilish uchun biz soddalashtirilgan sxemadan foydalanamiz:
JADVAL YARATING GoodsTree
(ID_GOOD INTEGER NULL EMAS,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
cheklash pkGooci asosiy kaliti (ID_GOOD));
Biz bitta GoodsTree jadvalini yaratamiz, unda faqat 3 ta maydon mavjud: ID_GOOD - aqlli toifa identifikatori, ID_PARENT_GOOD - ushbu toifa uchun asosiy shahar identifikatori va GOOD_NAME - toifa nomi. Ushbu jadvaldagi ma'lumotlarning yaxlitligini ta'minlash uchun biz ushbu jadvalga tashqi kalit cheklovini qo'yamiz:
ALTER TABLE GoodsTree
CHEKLASH FK_goodstree qo'shing
CHET KALİT (ID_PARENT_GOOD)
MA'LUMOTLAR GOODSTPEE (ID__GOOD)
Jadval o'ziga va berilganlarga ishora qiladi tashqi kalit shunga amal qiladi. Shunday qilib, jadvalda mavjud bo'lmagan ota-onalarga havolalar yo'q, shuningdek, avlodlari bo'lgan mahsulot toifalarini o'chirishga urinishlarning oldini oladi.
Keling, quyidagi ma'lumotlarni jadvalimizga kiritamiz:

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GOOD_NAME BULAR Maishiy texnika Kompyuterlar va aksessuarlar Muzlatgichlar Kir yuvish mashinalari Uch kamerali Ikki palatali Yagona kamerali Vertikal Frontal Tor Klassik

Endi bizda biroz saqlash joyi bor, biz barcha "yakuniy" mahsulot toifalarini "kengaytirilgan" ko'rinishda ko'rsatadigan saqlanadigan protsedurani yaratishni boshlashimiz mumkin - masalan, "Uch bo'lim" toifasi uchun toifaning to'liq nomi "Maishiy texnika Sovutgichlar Uch kamerali" kabi ko'rinadi.
Daraxtga o'xshash tuzilmalarni qayta ishlovchi saqlangan protseduralar o'z terminologiyasiga ega. Daraxtning har bir elementi tugun deb ataladi; va bir-biriga havola qiluvchi tugunlar orasidagi munosabat ota-ona munosabatlari deb ataladi. Daraxtning eng oxirida joylashgan va bolalari bo'lmagan tugunlar "barglar" deb ataladi.
Bizning saqlangan protseduramiz kirish parametri sifatida toifa identifikatoriga ega bo'ladi, biz undan ochishni boshlashimiz kerak. Saqlangan protsedura quyidagicha ko'rinadi:
GETFULLNAME (ID_GOOD2SHOW INTEGER) PROSEDURASI YARATING
Qaytariladi (FULL_GOODS_NAME VARCHAR (1000),
ID_CHILD_GOOD INTEGER)
AS
CURR_CHILD_NAME VARCHAR (80) VARIABLENI E'lon qilish;
BOSHLASH
/ * 0 ID_GOOD = ID_GOOD2SHOW * / bilan mahsulotning bevosita avlodlari uchun tashqi FOR SELECT halqasini tashkil qiling.
UCHUN SELECT gtl.id_good, gtl.good_name
GoodsTree gtl.dan
QAYERDA gtl.id_parent_good =: ID_good2show
INTO: ID_CHILD_GOOD,: to'liq_tovar_nomi
QILING
BOSHLASH
/ "EXISTS funktsiyasi bilan tekshirish, agar qavs ichidagi so'rov kamida bitta qatorni qaytarsa, TRUE qaytaradi. Agar ID_PARENT_GOOD = ID_CHILD_GOOD bo'lgan topilgan tugunning avlodlari bo'lmasa, u daraxtning "bargi" bo'lib, natijalarga kiradi * /
AGAR (mavjud EMAS (
GoodsTree dan * TANLANG
QAYERDA GoodsTree.id_parent_good =: id_child_good))
KEYIN
BOSHLASH
/ * Daraxtning "bargini" natijalarga o'tkazing * /
TO'XTIRISH;
OXIRI
BOSHQA
/ * Bolalari bo'lgan tugunlar uchun * /
BOSHLASH
/ * asosiy tugun nomini vaqtinchalik o'zgaruvchida saqlang * /
CURR_CHILD_NAME = to'liq_tovar_nomi;
/ * bu tartibni rekursiv bajaring * /
UCHUN
ID_CHILD_GOOD, to'liq_tovar_nomini tanlang
GETFULLNAMEDAN (: ID_CHILD_GOOD)
INTO: ID_CHILD_GOOD,: to'liq_tovar_nomi
BOSHLAING
/ * topilgan tugunga ota-ona tugunining nomini qo'shing., satrni birlashtirish operatsiyasidan foydalangan holda bolaning nomi || * /
to'liq_goods_name = CURR_CHILD_NAME | "" | f ull_goods_name, -
TO'XTIRISH; / * mahsulotning to'liq nomini qaytaring * /
OXIRI
OXIRI
OXIRI
OXIRI
Agar biz ushbu protsedurani ID_GOOD2SHOW = 1 kiritish parametri bilan bajarsak, biz quyidagilarni olamiz:
Ko'rib turganingizdek, rekursiv saqlangan protseduradan foydalanib, biz butun toifalar daraxti bo'ylab yurdik va filiallarning eng uchida joylashgan "barg" toifalarining to'liq nomini ko'rsatdik.
Xulosa
Bu saqlangan protseduralar tilining asosiy xususiyatlarini ko'rib chiqishni yakunlaydi. Shubhasiz, bir bobda saqlangan protseduralarni ishlab chiqishni to'liq o'zlashtirish mumkin emas, lekin bu erda biz saqlangan protseduralar bilan bog'liq asosiy tushunchalarni taqdim etishga va tushuntirishga harakat qildik. Ta'riflangan CP konstruktsiyalari va dizayn texnikasi ko'pchilik ma'lumotlar bazasi ilovalarida qo'llanilishi mumkin.
Saqlangan protseduralarni ishlab chiqish bilan bog'liq ba'zi muhim masalalar keyingi bobda - "InterBase saqlangan protseduralar tilining ilg'or xususiyatlari"da ochib beriladi, bu istisnolar bilan ishlash, saqlangan protseduralardagi xatolarni bartaraf etish va massivlar bilan ishlashga bag'ishlangan.
Kengaytirilgan saqlangan protseduralarni dasturlash uchun Microsoft ODS (Ochiq ma'lumotlar xizmati) API-ni MS SQL Server 2000 funksionalligini kengaytiruvchi server ilovalarini yaratish uchun foydalaniladigan makrolar va funktsiyalar to'plami bilan ta'minlaydi.
Kengaytirilgan saqlangan protseduralar - bu ODS API va WIN32 API yordamida C / C ++ tilida yozilgan oddiy funktsiyalar bo'lib, dinamik havolalar kutubxonasi (dll) sifatida yaratilgan va men aytganimdek, SQL serverining funksionalligini kengaytirish uchun mo'ljallangan. ODS API dasturchiga mijozga har qanday tashqi ma'lumotlar manbasidan olingan ma'lumotlarni oddiy yozuvlar to'plami shaklida uzatish imkonini beruvchi boy funktsiyalar to'plamini taqdim etadi. Shuningdek, kengaytirilgan saqlangan protsedura unga berilgan parametr (OUTPUT parametri) orqali qiymatlarni qaytarishi mumkin.