SQL saugomos procedūros: kūrimas ir naudojimas. Saugomos procedūros Sql serverio saugomos procedūros

Saugomų procedūrų tipai

SQL serveris yra kelių tipų saugomos procedūros.

• Sistema saugomos procedūros skirta įvairiems administraciniams veiksmams atlikti. Jų pagalba atliekama beveik visa serverio administravimo veikla. Galima sakyti, kad sisteminis saugomos procedūros yra sąsaja, suteikianti galimybę dirbti su sistemos lentelėmis, kurios galiausiai priklauso nuo duomenų keitimo, pridėjimo, ištrynimo ir gavimo iš vartotojų ir sistemos duomenų bazių sistemos lentelių. Sistema saugomos procedūros turi sp_ priešdėlį, yra saugomi sistemos duomenų bazėje ir gali būti iškviesti bet kurios kitos duomenų bazės kontekste.
• Pasirinktinis saugomos procedūrosįgyvendinti tam tikrus veiksmus. Saugomos procedūros– pilnavertis duomenų bazės objektas. Dėl to kiekvienas saugoma procedūra yra konkrečioje duomenų bazėje, kurioje jis vykdomas.
• Laikinas saugomos procedūros egzistuoja tik kurį laiką, po kurio serveris juos automatiškai sunaikina. Jie skirstomi į vietinius ir pasaulinius. Vietinis laikinas saugomos procedūros galima iškviesti tik iš ryšio, kuriame jie buvo sukurti. Kurdami tokią procedūrą, turite suteikti jai pavadinimą, kuris prasideda vienu simboliu #. Kaip ir visi laikini objektai, saugomos procedūrosšio tipo yra automatiškai ištrinami, kai vartotojas atsijungia arba serveris paleidžiamas iš naujo arba sustabdomas. Pasaulinis laikinas saugomos procedūros yra prieinami bet kokiems ryšiams iš serverio, kuriam taikoma ta pati procedūra. Norėdami jį apibrėžti, tiesiog suteikite pavadinimą, prasidedantį simboliais ## . Šios procedūros ištrinamos, kai serveris paleidžiamas iš naujo arba sustabdomas arba nutraukiamas ryšys kontekste, kuriame jos buvo sukurtos.

Kurti, keisti ir ištrinti išsaugotas procedūras

Kūrimas saugoma procedūra apima šių problemų sprendimą:

• nustatant sukurto tipą saugoma procedūra: laikinas arba pagal užsakymą. Be to, galite sukurti savo sistemą saugoma procedūra, suteikdami jam pavadinimą su priešdėliu sp_ ir įtraukdami jį į sistemos duomenų bazę. Ši procedūra bus prieinama bet kurios vietinės serverio duomenų bazės kontekste;
• planavimo prieigos teises. Kuriant saugoma procedūra reikia atsižvelgti į tai, kad jis turės tokias pat prieigos prie duomenų bazės objektų teises kaip ir jį sukūręs vartotojas;
• apibrėžimas išsaugotus procedūros parametrus. Panašiai kaip ir daugelyje programavimo kalbų, saugomos procedūros gali turėti įvesties ir išvesties parametrus;
• kodo kūrimas saugoma procedūra. Procedūros kode gali būti bet kokių SQL komandų seka, įskaitant iškvietimus į kitus saugomos procedūros.

Sukurti naują ir pakeisti esamą saugoma procedūra atlikta naudojant šią komandą:

<определение_процедуры>::= (CREATE | ALTER ) procedūros_pavadinimas [;numeris] [(@parametro_pavadinimas duomenų_tipas ) [=numatytasis] ][,...n] AS sql_operatorius [...n]

Pažvelkime į šios komandos parametrus.

Naudojant priešdėlius sp_, #, ##, sukurta procedūra gali būti apibrėžta kaip sisteminė arba laikina. Kaip matote iš komandos sintaksės, negalima nurodyti savininko, kuriam priklausys sukurta procedūra, vardo, taip pat duomenų bazės, kurioje ji turėtų būti, pavadinimo. Taigi, siekiant patalpinti sukurtą saugoma procedūra konkrečioje duomenų bazėje, tos duomenų bazės kontekste turite išleisti komandą CREATE PROCEDURE. Sukant nuo kūno saugoma procedūra sutrumpintus pavadinimus galima naudoti tos pačios duomenų bazės objektams, t.y., nenurodant duomenų bazės pavadinimo. Kai reikia pasiekti objektus, esančius kitose duomenų bazėse, būtina nurodyti duomenų bazės pavadinimą.

Skaičius pavadinime yra identifikavimo numeris saugoma procedūra, kuri vienareikšmiškai ją identifikuoja procedūrų grupėje. Kad būtų lengviau valdyti, procedūros logiškai yra tos pačios rūšies saugomos procedūros gali būti sugrupuoti suteikiant jiems tą patį pavadinimą, bet skirtingus identifikavimo numerius.

Norėdami perkelti įvesties ir išvesties duomenis į sukurtą saugoma procedūra gali būti naudojami parametrai, kurių pavadinimai, kaip ir vietinių kintamųjų pavadinimai, turi prasidėti @ simboliu. Vienas saugoma procedūra Galite nurodyti kelis parametrus, atskirtus kableliais. Procedūros turinys neturėtų naudoti vietinių kintamųjų, kurių pavadinimai sutampa su šios procedūros parametrų pavadinimais.

Norėdami nustatyti atitinkamą duomenų tipą išsaugotas procedūros parametras, tinka bet kokie SQL duomenų tipai, įskaitant vartotojo apibrėžtus. Tačiau duomenų tipą CURSOR galima naudoti tik kaip išvesties parametras saugoma procedūra, t.y. nurodant raktažodį IŠVADA.

Raktinio žodžio OUTPUT buvimas reiškia, kad atitinkamas parametras skirtas duomenims grąžinti saugoma procedūra. Tačiau tai nereiškia, kad parametras netinka reikšmėms perduoti saugoma procedūra. Nurodžius raktinį žodį OUTPUT, serveris nurodo išeiti saugoma procedūra priskirti esamą parametro reikšmę vietiniam kintamajam, kuris buvo nurodytas iškviečiant procedūrą kaip parametro reikšmę. Atkreipkite dėmesį, kad nurodant raktinį žodį OUTPUT, atitinkamo parametro reikšmę iškviečiant procedūrą galima nustatyti tik naudojant vietinį kintamąjį. Bet kokios išraiškos ar konstantos, leidžiamos įprastiniams parametrams, neleidžiamos.

Raktažodis VARYING naudojamas kartu su

Paskutinis atnaujinimas: 2017-08-14

Dažnai duomenų operacija reiškia instrukcijų rinkinį, kuris turi būti vykdomas tam tikra seka. Pavyzdžiui, pridedant prekės pirkimą, reikia įvesti duomenis į užsakymų lentelę. Tačiau prieš tai darydami turite patikrinti, ar perkamos prekės yra sandėlyje. Gali tekti patikrinti keletą papildomų sąlygų. Tai yra, iš tikrųjų prekės pirkimo procesas apima kelis veiksmus, kurie turi būti atliekami tam tikra seka. Ir šiuo atveju būtų optimaliau visus šiuos veiksmus sutalpinti į vieną objektą - saugoma procedūra(saugoma procedūra).

Tai yra, iš esmės saugomos procedūros yra komandų rinkinys, kuris vykdomas kaip vienas vienetas. Taigi, saugomos procedūros leidžia supaprastinti sudėtingas operacijas ir sudėti jas į vieną objektą. Keisis prekės įsigijimo procesas, atitinkamai pakaks pakeisti procedūros kodą. Tai yra, procedūra taip pat supaprastina kodo valdymą.

Saugomos procedūros taip pat leidžia apriboti prieigą prie duomenų lentelėse ir taip sumažinti tyčinių ar netyčinių nepageidaujamų veiksmų, susijusių su šiais duomenimis, tikimybę.

Ir dar vienas svarbus aspektas – našumas. Saugomos procedūros paprastai vykdomos greičiau nei įprasti SQL sakiniai. Taip yra todėl, kad procedūros kodas sukompiliuojamas vieną kartą, kai jis pirmą kartą paleidžiamas, o tada išsaugomas sukompiliuota forma.

Norėdami sukurti išsaugotą procedūrą, naudokite komandą CREATE PROCEDURE arba CREATE PROC.

Taigi saugoma procedūra turi tris Pagrindiniai bruožai: kodo supaprastinimas, saugumas ir našumas.

Pavyzdžiui, tarkime, kad duomenų bazėje yra lentelė, kurioje saugomi duomenys apie produktus:

KURTI LENTELĘ Produktai (ID INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Gamintojas NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Sukurkime saugomą procedūrą duomenims iš šios lentelės gauti:

NAUDOKITE produktusdb; EITI KURTI PROCEDŪRĄ Produkto santrauka AS PASIRINKTI Produkto pavadinimas AS Produktas, gamintojas, kaina nuo produktų

Kadangi komanda CREATE PROCEDURE turi būti iškviesta atskirame pakete, po komandos USE, kuri nustato esamą duomenų bazę, seka komanda GO, kad būtų apibrėžtas naujas paketas.

Po procedūros pavadinimo turi būti nurodytas AS raktinis žodis.

Norint atskirti procedūros turinį nuo likusio scenarijaus, procedūros kodas dažnai dedamas į BEGIN...END bloką:

NAUDOKITE produktusdb; EITI KURTI PROCEDŪRĄ ProductSummary AS PRADĖTI PASIRINKTI Produkto pavadinimas AS Produktas, gamintojas, kaina NUO Produktų PABAIGA;

Pridėjus procedūrą, ją matome duomenų bazės mazge, esančiame SQL Server Management Studio pomazge Programuojamumas -> Išsaugotos procedūros:

O procedūrą galėsime valdyti ir per vaizdinę sąsają.

Procedūros vykdymas

Norėdami atlikti saugomą procedūrą, iškvieskite komandą EXEC arba EXECUTE:

EXEC produkto santrauka

Procedūros pašalinimas

Norėdami pašalinti procedūrą, naudokite komandą DROP PROCEDURE:

NULEIDIMO PROCEDŪRA ProductSummary

MySQL 5 turi daug naujų funkcijų, iš kurių viena reikšmingiausių yra saugomų procedūrų kūrimas. Šioje pamokoje kalbėsiu apie tai, kas jie yra ir kaip jie gali palengvinti jūsų gyvenimą.

Įvadas

Išsaugota procedūra yra būdas įtraukti pasikartojančius veiksmus. Išsaugomos procedūros gali deklaruoti kintamuosius, manipuliuoti duomenų srautu ir naudoti kitus programavimo būdus.

Jų sukūrimo priežastis yra aiški ir tai patvirtina dažnas naudojimas. Kita vertus, jei kalbėsite su tais, kurie su jais dirba nereguliariai, nuomonės pasiskirstys į du visiškai priešingus šonus. Nepamiršk šito.

Už nugaros

• Dalijimasis logika su kitomis programomis. Saugomos procedūros apima funkcionalumą; Tai suteikia ryšį, leidžiantį pasiekti duomenis ir valdyti įvairias programas.
• Vartotojų izoliavimas iš duomenų bazių lentelių. Tai leidžia jums suteikti prieigą prie saugomų procedūrų, bet ne prie pačių lentelės duomenų.
• Suteikia apsaugos mechanizmą. Kaip nurodyta ankstesniame punkte, jei galite pasiekti duomenis tik naudodami saugomas procedūras, niekas kitas negali ištrinti jūsų duomenų per komandą SQL DELETE.
• Patobulintas vykdymas dėl sumažėjusio tinklo srauto. Naudojant išsaugotas procedūras, galima sujungti kelias užklausas.

Prieš

• Padidėjęs duomenų bazės serverio apkrovimas dėl to, kad didžioji dalis darbų atliekama serverio, o mažiau kliento pusėje.
• Turėsite daug išmokti. Norėdami parašyti saugomas procedūras, turėsite išmokti MySQL išraiškos sintaksę.
• Jūs dubliuojate savo taikomosios programos logiką dviejose vietose: serverio kodą ir saugomų procedūrų kodą, taip apsunkindami manipuliavimo duomenimis procesą.
• Perkėlimas iš vienos DBVS į kitą (DB2, SQL Server ir kt.) gali sukelti problemų.

Įrankis, su kuriuo dirbu, vadinamas MySQL užklausų naršykle, kuri yra gana standartinė sąveikai su duomenų bazėmis. Įrankis komandinė eilutė MySQL yra dar vienas puikus pasirinkimas. Priežastis, dėl kurios aš jums tai sakau, yra ta, kad visų mėgstama phpMyAdmin nepalaiko saugomų procedūrų vykdymo.

Beje, naudoju pagrindinę lentelės struktūrą, kad jums būtų lengviau suprasti šią temą. Kalbu apie saugomas procedūras, kurios yra pakankamai sudėtingos, kad tektų įsigilinti į sudėtingą lentelės struktūrą.

1 veiksmas: uždėkite ribotuvą

Skirstytuvas yra simbolis arba simbolių eilutė, naudojama MySQL klientui nurodyti, kad baigėte rašyti SQL išraišką. Ilgą laiką kabliataškis buvo skiriamoji priemonė. Tačiau gali kilti problemų, nes saugomoje procedūroje gali būti kelios išraiškos, kurių kiekviena turi baigtis kabliataškiu. Šioje pamokoje kaip skirtuką naudoju eilutę „//“.

2 veiksmas: kaip dirbti su saugomomis procedūromis

Išsaugotos procedūros kūrimas

DELIMITER // KURTI PROCEDŪRĄ `p2` () KALBA SQL DETERMINISTINIS SQL SAUGUMO APIBRĖŽTIS KOMENTARAS "Procedūra" BEGIN PASIRINKTI "Sveikas pasaulis!"; GALAS//

Pirmoji kodo dalis sukuria saugomą procedūrą. Kitame yra pasirenkami parametrai. Tada ateina pavadinimas ir galiausiai pačios procedūros turinys.

Išsaugomų procedūrų pavadinimuose skiriamos didžiosios ir mažosios raidės. Taip pat negalite sukurti kelių procedūrų tuo pačiu pavadinimu. Išsaugotoje procedūroje negali būti išraiškų, kurios modifikuotų pačią duomenų bazę.

4 saugomos procedūros savybės:

• Kalba: perkeliamumo tikslais numatytoji reikšmė yra SQL.
• Deterministinis: jei procedūra visada grąžina tą patį rezultatą ir naudoja tuos pačius įvesties parametrus. Tai skirta replikacijos ir registracijos procesui. Numatytoji reikšmė NĖRA DETERMINISTINĖ.
• SQL sauga: vartotojo teisės tikrinamos skambučio metu. INVOKER yra vartotojas, iškviečiantis išsaugotą procedūrą. DEFINER yra procedūros „sukūrėjas“. Numatytoji reikšmė yra DEFINER.
• Komentaras: dokumentacijos tikslais numatytoji reikšmė yra ""

Saugomos procedūros iškvietimas

Norėdami iškviesti išsaugotą procedūrą, turite įvesti raktinį žodį CALL, po kurio nurodomas procedūros pavadinimas, po kurio skliausteliuose nurodomi parametrai (kintamieji arba reikšmės). Skliausteliuose būtina.

CALL saugomos_procedūros_pavadinimas (param1, param2, ....) CALL procedura1(10 , "stygos parametras" , @parameter_var);

Išsaugotos procedūros keitimas

MySQL turi ALTER PROCEDURE sakinį, skirtą procedūrų keitimui, tačiau jis tinkamas tik tam tikroms charakteristikoms keisti. Jei reikia pakeisti procedūros parametrus arba turinį, turite jį ištrinti ir sukurti iš naujo.

Išsaugotos procedūros pašalinimas

NEMOKAMA PROCEDŪRA, JEI YRA, p2;

Tai paprasta komanda. Jei tokios procedūros nėra, sakinys IF EXISTS užfiksuoja klaidą.

3 veiksmas: parinktys

Pažiūrėkime, kaip galime perduoti parametrus išsaugotai procedūrai.

• KURTI PROCEDŪRĄ proc1(): tuščias parametrų sąrašas
• KURTI PROCEDŪRĄ proc1 (IN varname DATA-TYPE): vienas įvesties parametras. Žodis IN yra neprivalomas, nes numatytieji parametrai yra IN (in).
• KŪRIMO PROCEDŪRA proc1 (OUT varname DATA-TYPE): grąžintas vienas parametras.
• CREATE PROCEDŪRA proc1 (INOUT varname DATA-TYPE): vienas parametras, tiek įvestis, tiek grąžinimas.

Žinoma, galite nurodyti kelis skirtingų tipų parametrus.

IN parametro pavyzdys

DELIMITER // KURTI PROCEDŪRĄ „proc_IN“ (IN var1 INT) BEGIN SELECT var1 + 2 AS rezultatas; GALAS//

OUT parametro pavyzdys

DELIMITER // KURTI PROCEDŪRĄ `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Tai bandymas"; GALAS //

INOUT parametro pavyzdys

DELIMITER // KURTI PROCEDŪRĄ „proc_INOUT“ (OUT var1 INT) BEGIN SET var1 = var1 * 2; GALAS //

4 veiksmas: kintamieji

Dabar aš išmokysiu jus kurti kintamuosius ir saugoti juos procedūrose. Turite juos aiškiai nurodyti BEGIN/END bloko pradžioje kartu su duomenų tipais. Kai deklaruosite kintamąjį, galite jį naudoti taip pat, kaip seanso kintamuosius, literatus ar stulpelių pavadinimus.

Kintamojo deklaracijos sintaksė atrodo taip:

DEKLARUOTI varname DATA-TYPE DEFAULT numatytoji vertė;

Deklaruojame kai kuriuos kintamuosius:

DEKLARUOTI a, b INT NUMATYTOJI 5; DECLARE str VARCHAR(50); DEKLARUOTI šiandien TIMESTAMP NUMATYTOJI CURRENT_DATE; DEKLARUOTI v1, v2, v3 TINYINT;

Darbas su kintamaisiais

Kai deklaruosite kintamąjį, galite nustatyti jo reikšmę naudodami komandas SET arba SELECT:

DELIMITER // KURTI PROCEDŪRĄ `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT NUMATYTOJI 5; DECLARE str VARCHAR(50); DEKLARUOTI šiandien TIMESTAMP NUMATYTOJI CURRENT_DATE; DEKLARUOTI v1, v2, v3 TINYINT; INSERT INTO 1 lentelės VERTĖS (a); SET str = "Aš esu eilutė"; SELECT CONCAT(str,paramstr), šiandien FROM table2 WHERE b >=5; GALAS //

5 veiksmas: gijų valdymo struktūros

MySQL palaiko IF, CASE, ITERATE, LEAVE LOOP, WHILE ir REPEAT konstrukcijas, skirtas valdyti gijas saugomoje procedūroje. Pažiūrėsime, kaip naudoti IF, CASE ir WHILE, nes jie yra dažniausiai naudojami.

IF dizainas

Naudodami IF konstrukciją galime atlikti užduotis su sąlygomis:

DELIMITER // KURTI PROCEDŪRĄ „proc_IF“ (IN param1 INT) BEGIN DECLARE kintamasis1 INT; NUSTATYTI kintamąjį1 = param1 + 1; JEI kintamasis1 = 0 TAD PASIRINKITE kintamąjį1; ENDIF; JEI param1 = 0 TAI PASIRINKITE "Parametro reikšmė = 0"; ELSE SELECT "Parametro vertė<>0"; END IF; END //

CASE dizainas

CASE yra dar vienas sąlygų tikrinimo ir tinkamo sprendimo parinkimo būdas. Tai puikus būdas pakeisti daugelį IF konstrukcijų. Konstrukciją galima apibūdinti dviem būdais, suteikiant lankstumo tvarkant kelias sąlygines išraiškas.

DELIMITER // KURTI PROCEDŪRĄ „proc_CASE“ (IN param1 INT) BEGIN DECLARE kintamasis1 INT; NUSTATYTI kintamąjį1 = param1 + 1; CASE kintamasis1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); KAI 1 Tada INSERT INTO table1 VALUES (kintamasis1); ELSE INSERT INTO table1 VERSES (99); GALINĖ DĖLĖ; GALAS //

DELIMITER // KURTI PROCEDŪRĄ „proc_CASE“ (IN param1 INT) BEGIN DECLARE kintamasis1 INT; NUSTATYTI kintamąjį1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); KAI kintamasis1 = 1, tada INSERT INTO table1 VERTES (kintamasis1); ELSE INSERT INTO table1 VERSES (99); GALINĖ DĖLĖ; GALAS //

WHILE dizainas

Techniškai yra trijų tipų kilpos: WHILE ciklas, LOOP ciklas ir REPEAT ciklas. Taip pat galite atlikti kilpą naudodami Darth Vader programavimo techniką: GOTO teiginius. Štai ciklo pavyzdys:

DELIMITER // KURTI PROCEDŪRĄ „proc_WHILE“ (IN param1 INT) BEGIN DECLARE kintamasis1, kintamasis2 INT; NUSTATYTI kintamąjį1 = 0; WHILE kintamasis1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

6 veiksmas: žymekliai

Žymekliai naudojami užklausos grąžinamų eilučių rinkiniui pereiti ir kiekvienai eilutei apdoroti.

MySQL palaiko žymeklius saugomose procedūrose. Čia yra trumpa žymeklio kūrimo ir naudojimo sintaksė.

DECLARE kursoriaus pavadinimas CURSOR FOR SELECT ...; /*Žymeklio deklaravimas ir užpildymas */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Ką daryti, kai nebėra įrašų*/ OPEN kursoriaus pavadinimas; /*Atidaryti žymeklį*/ ATKESTI žymeklio pavadinimą Į kintamąjį [, kintamasis]; /*Kintamajam priskirti reikšmę, lygią esamai stulpelio reikšmei*/ UŽDARYTI žymeklio pavadinimą; /*Uždaryti žymeklį*/

Šiame pavyzdyje mes padarysime kai kuriuos paprastos operacijos naudojant žymeklį:

DELIMITER // KURTI PROCEDŪRĄ „proc_CURSOR“ (OUT param1 INT) PRADĖTI DEKLARUOTI a, b, c INT; DEKLARUOTI cur1 CURSOR FOR SELECT col1 FROM table1; DEKLARUOTI TĘSČIĄ DĖL NERASTA RINKINĮ b = 1; OPEN cur1; NUSTATYMAS b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 Į a; JEI b = 0 TAI NUSTATYTI c = c + a; ENDIF; PABAIGA KOL; UŽDARYTI cur1; NUSTATYTI param1 = c; GALAS //

Žymekliai turi tris savybes, kurias turite suprasti, kad išvengtumėte netikėtų rezultatų:

• Nejautri: vieną kartą atidarytas žymeklis neatspindės vėliau įvykusių lentelės pakeitimų. Tiesą sakant, MySQL negarantuoja, kad žymeklis bus atnaujintas, todėl nepasikliaukite juo.
• Tik skaityti: žymekliai negali būti keičiami.
• Jokio atsukimo atgal: žymeklis gali judėti tik viena kryptimi – pirmyn, negalėsite praleisti eilučių jų nepasirinkę.

Išvada

Šioje pamokoje supažindinau jus su darbo su saugomomis procedūromis pagrindais ir kai kuriomis specifinėmis su tuo susijusiomis savybėmis. Žinoma, prieš tapdami tikru MySQL procedūrų guru, turėsite pagilinti savo žinias tokiose srityse kaip saugumas, SQL išraiškos ir optimizavimas.

Turėtumėte apskaičiuoti, kokią naudą gausite naudodami saugomas procedūras savo konkretus pritaikymas, ir tik tada sukurkite tik būtinas procedūras. Apskritai naudoju procedūras; Mano nuomone, juos verta diegti į projektus dėl saugumo, kodų priežiūros ir bendro dizaino. Be to, atminkite, kad MySQL procedūros vis dar vyksta. Tikėtis funkcionalumo ir patobulinimų. Nedvejodami pasidalinkite savo nuomonėmis.

Į savo procedūras įtraukite eilutę – NUSTATYTI NOCOUNT ON:

Su kiekviena DML išraiška SQL serveris atidžiai grąžina mums pranešimą, kuriame nurodomas apdorotų įrašų skaičius. Ši informacija Mums tai gali būti naudinga derinant kodą, bet po to jis bus visiškai nenaudingas. Rašydami SET NOCOUNT ON, šią funkciją išjungiame. Išsaugotoms procedūroms, turinčioms kelias išraiškas arba/arba kilpas šis veiksmas gali žymiai padidinti našumą, nes srautas bus žymiai sumažintas.

Transact-SQL

Naudokite schemos pavadinimą su objekto pavadinimu:

Na, manau, aišku. Ši operacija nurodo serveriui, kur ieškoti objektų, ir užuot atsitiktinai knaisiojęsis po jo šiukšliadėžes, jis iš karto žinos, kur jam reikia eiti ir ką pasiimti. Turėdami daug duomenų bazių, lentelių ir saugomų procedūrų, tai gali žymiai sutaupyti mūsų laiką ir nervus.

Transact-SQL

SELECT * FROM dbo.MyTable --Tai daryti taip gerai -- Vietoj SELECT * FROM MyTable --Ir tai daryti taip yra blogai --Iškviesti EXEC dbo.MyProc procedūrą --Gerai dar kartą --Vietoje EXEC MyProc -- Blogai!

Nenaudokite priešdėlio „sp_“ saugomų procedūrų pavadinime:

Jei mūsų procedūros pavadinimas prasideda „sp_“, „SQL Server“ pirmiausia ieškos pagrindinėje duomenų bazėje. Faktas yra tas, kad šis priešdėlis naudojamas asmeninėms vidinėms serverio procedūroms. Todėl jos naudojimas gali sukelti papildomų išlaidų ir netgi neteisingų rezultatų, jei jos duomenų bazėje bus rasta procedūra tokiu pat pavadinimu kaip ir jūsų.

Naudokite JEI YRA (SELECT 1), o ne JEI YRA (SELECT *):

Norėdami patikrinti, ar kitoje lentelėje yra įrašas, naudojame teiginį IF EXISTS. Ši išraiška grąžina tiesa, jei iš vidinės išraiškos grąžinama bent viena reikšmė, nesvarbu „1“, visi stulpeliai ar lentelė. Grąžinti duomenys iš esmės niekaip nenaudojami. Taigi, norint suspausti srautą duomenų perdavimo metu, logiškiau naudoti „1“, kaip parodyta toliau.

Saugomos procedūros

Šio skyriaus tema yra viena iš galingiausių įrankių, siūlomų InterBase duomenų bazių programų kūrėjams verslo logikai diegti.Saugomos procedūros (anglų k., stoied proceduies) leidžia įdiegti reikšmingą programų logikos dalį duomenų bazės lygiu ir taip padidinti. visos programos našumą, centralizuoti duomenų apdorojimą ir sumažinti kodo kiekį, reikalingą priskirtoms užduotims atlikti.Beveik bet kuri gana sudėtinga duomenų bazės programa neapsieina be saugomų procedūrų.
Be šių gerai žinomų saugomų procedūrų naudojimo pranašumų, būdingų daugeliui reliacinių DBVS, „InterBase“ saugomos procedūros gali veikti kaip beveik užbaigti duomenų rinkiniai, leidžiantys jų grąžintus rezultatus naudoti įprastose SQL užklausose.
Dažnai pradedantieji kūrėjai saugomas procedūras įsivaizduoja tiesiog kaip konkrečių SQL užklausų, kurios kažką daro duomenų bazėje, rinkinį, ir yra nuomonė, kad dirbti su saugomomis procedūromis yra daug sunkiau nei įdiegti tas pačias funkcijas kliento programoje ta kalba. aukštas lygis
Taigi, kokios yra InterBase saugomos procedūros?
Saugoma procedūra (SP) yra duomenų bazės metaduomenų dalis, kuri yra paprogramė, sukompiliuota į vidinį InterBase atvaizdą, parašyta specialia kalba, kurios kompiliatorius yra integruotas į InteiBase serverio branduolį.
Išsaugotą procedūrą galima iškviesti iš kliento programų, iš trigerių ir kitų saugomų procedūrų. Išsaugota procedūra veikia serverio procese ir gali manipuliuoti duomenimis duomenų bazėje, taip pat grąžinti jos vykdymo rezultatus ją iškvietusiam klientui (t. y. trigeriui, HP, programai).
Galingų HP funkcijų pagrindas yra procedūrinė programavimo kalba, apimanti ir modifikuotus įprasto SQL sakinius, tokius kaip INSERT, UPDATE ir SELECT, tiek šakų ir kilpų organizavimo įrankius (IF, WHILE), taip pat klaidų apdorojimo įrankiai ir išskirtinės situacijos Saugomų procedūrų kalba leidžia įgyvendinti sudėtingus darbo su duomenimis algoritmus, o dėl dėmesio darbui su reliaciniais duomenimis HP yra daug kompaktiškesnis nei panašios procedūros tradicinėmis kalbomis.
Reikėtų pažymėti, kad trigeriams naudojama ta pati programavimo kalba, išskyrus keletą funkcijų ir apribojimų. Skirtumai tarp trigeriuose naudojamos kalbos poaibio ir HP kalbos yra išsamiai aptariami skyriuje „Trigeriai“ (1 dalis).

Paprastos saugomos procedūros pavyzdys

Atėjo laikas sukurti savo pirmąją saugomą procedūrą ir naudoti ją kaip pavyzdį, kad išmoktumėte saugomų procedūrų kūrimo procesą. Tačiau pirmiausia turėtume pasakyti keletą žodžių apie tai, kaip dirbti su saugomomis procedūromis. Faktas yra tas, kad HP kaip neaiškios ir nepatogios priemonės reputacija yra skolinga itin prastiems standartiniams saugomų procedūrų kūrimo ir derinimo įrankiams. InterBase dokumentacijoje rekomenduojama kurti procedūras naudojant SQL scenarijaus failus, kuriuose yra HP teksto, kurie pateikiami kaip įvestis į isql interpretatorių, ir tokiu būdu sukurti ir modifikuoti HP If šiame SQL scenarijuje procedūros teksto sudarymo BLR (apie BLR, žr. skyrių „InterBase duomenų bazės struktūra“ (4 dalis)), jei įvyksta klaida, isql parodys pranešimą, kurioje SQL scenarijaus failo eilutėje įvyko ši klaida. Ištaisykite klaidą ir pakartokite viską iš naujo. Apie derinimą šiuolaikine šio žodžio prasme, t.y. apie vykdymo sekimą, su galimybe peržiūrėti tarpines kintamųjų reikšmes, visiškai nekalbama. Akivaizdu, kad šis požiūris neprisideda prie saugomų procedūrų patrauklumo augimo kūrėjo akimis
Tačiau be standartinio minimalistinio požiūrio į HP kūrimą<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Saugomų procedūrų sintaksė aprašyta taip:

KURTI PROCEDŪROS pavadinimą
[ (paramo duomenų tipas [, parametro duomenų tipas ...]) ]
)]
AS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
PRADĖTI
< compound_statement>
[< compound_statement> ...]
GALAS
< compound_statement> = (pareiškimas ;)

Tai atrodo gana didelė ir gali būti sudėtinga, bet iš tikrųjų viskas yra labai paprasta. Norėdami palaipsniui įvaldyti sintaksę, pažvelkime į palaipsniui sudėtingesnius pavyzdžius.
Taigi, čia yra labai paprastos saugomos procedūros, kuri kaip įvestis paima du skaičius, juos prideda ir grąžina rezultatą, pavyzdys:

KURTI PROCEDŪRĄ SP_Add(first_arg DVIGUBAS TIKSLUMAS,
second_arg DVIGUBAS TIKSLUMAS)
GRĄŽINIMAI (rezultatas DVIGUBAS TIKSLUMAS)
AS
PRADĖTI
Rezultatas=pirmas_arg+antras_arg;
SUSTABDYTI;
GALAS

Kaip matote, viskas paprasta: po komandos CREATE PROCEDURE nurodomas naujai sukurtos procedūros pavadinimas (kuris turi būti unikalus duomenų bazėje) - šiuo atveju SP_Add, tada yra HP įvesties parametrai - first_arg ir second_arg. išvardyti skliausteliuose, atskirti kableliais, nurodant jų tipus.
Įvesties parametrų sąrašas yra pasirenkama CREATE PROCEDURE sakinio dalis – pasitaiko atvejų, kai procedūra gauna visus duomenis savo darbui per užklausas į lenteles procedūros korpuse.

Saugomose procedūrose naudojami bet kokie skaliariniai duomenų tipai InteiBase Tai neleidžia naudoti masyvų ir vartotojo nustatytų tipų – domenų

Toliau eina raktinis žodis RETURNS, po kurio skliausteliuose pateikiami grąžinami parametrai, nurodant jų tipus – šiuo atveju tik vienas – Rezultatas.
Jei procedūra neturėtų grąžinti parametrų, tada trūksta žodžio RETURNS ir grąžinamų parametrų sąrašo.
Po RETURNSQ nurodomas raktinis žodis AS. Prieš paleidžiant raktinį žodį AS titulas, ir po to - techo procedūras.
Išsaugotos procedūros turinys yra jos vidinių (vietinių) kintamųjų aprašymų sąrašas (jei tokių yra, mes juos išsamiau apžvelgsime toliau), atskirti kabliataškiu (;) ir teiginių bloku, esančiu operatorių skliausteliuose. PRADŽIA PABAIGA. Šiuo atveju HP turinys labai paprastas – prašome pridėti du įvesties argumentus ir priskirti jų rezultatą prie išvesties, o tada iškviečiame komandą SUSPEND. Šiek tiek vėliau paaiškinsime šios komandos veiksmo esmę, tačiau kol kas atkreipsime dėmesį tik į tai, kad ji reikalinga grąžinimo parametrams perkelti ten, iš kur buvo iškviesta saugoma procedūra.

Skirstytuvai saugomose procedūrose

Atminkite, kad procedūros teiginys baigiasi kabliataškiu (;). Kaip žinote, kabliataškis yra standartinis SQL komandų skyriklis – tai signalas SQL interpretatoriui, kad komandos tekstas įvestas visas ir turėtų pradėti jį apdoroti. Ar neišeitų taip, kad jei SQL interpretatorius ras HP viduryje kabliataškį, jis manys, kad komanda įvesta visa ir bandys įvykdyti dalį saugomos procedūros? Ši prielaida nėra be pagrindo. Iš tiesų, jei sukursite failą, kuriame rašysite aukščiau pateiktą pavyzdį, pridėsite prisijungimo komandą iš duomenų bazės ir bandysite vykdyti šį SQL scenarijų naudodami isql interpretatorių, bus grąžinta klaida dėl netikėto, interpretatoriaus nuomone, pabaigos. saugomos procedūros kūrimo komandos. Jei saugomas procedūras kuriate naudodami SQL scenarijaus failus, nenaudodami specializuotų „InterBase“ kūrėjo įrankių, tada prieš kiekvieną HP kūrimo komandą (tas pats galioja trigeriams) turite pakeisti scenarijaus komandų skyriklį į kitą simbolį, išskyrus kabliataškį, o po teksto HP norėdami jį atkurti. Isql komanda, pakeičianti SQL sąlygų skyriklį, atrodo taip:

NUSTATYTI TERMINĄ

Įprastas saugomos procedūros kūrimo atvejis atrodo taip:

NUSTATYTI TERMĄ^;
KURTI PROCEDŪRĄ some_procedure
... . .
GALAS
^
NUSTATYTI TERMINĄ ;^

Saugomos procedūros iškvietimas

Bet grįžkime prie mūsų išsaugotos procedūros. Dabar, kai jis sukurtas, reikia jį kažkaip iškviesti, perduoti jam parametrus ir gauti rezultatus. Tai padaryti labai paprasta – tiesiog parašykite tokią SQL užklausą:

PASIRINKTI*
FROM Sp_add(181.35, 23.09)

Ši užklausa mums pateiks vieną eilutę, kurioje bus tik vienas rezultato laukas, kuriame bus skaičių 181.35 ir 23.09 suma, t. y. 204.44.
Taigi, mūsų procedūra gali būti naudojama įprastai SQL užklausos, vykdomas tiek kliento programose, tiek kitose HP arba trigeriuose. Šis mūsų procedūros naudojimas yra įmanomas naudojant komandą SUSPEND išsaugotos procedūros pabaigoje.
Faktas yra tas, kad „InterBase“ (ir visuose jos klonuose) yra dviejų tipų saugomos procedūros: pasirenkamos procedūros ir vykdomosios procedūros. Šių dviejų tipų HP veikimo skirtumas yra tas, kad atrankos procedūros paprastai grąžina daugybę išvesties parametrų rinkinių, sugrupuotų po eilutės, kurie atrodo kaip duomenų rinkinys, o vykdomosios procedūros gali arba visai negrąžinti parametrų, arba grąžinti tik vienas išvesties parametrų rinkinys , nurodytas skiltyje Grąžinimai, kur viena parametrų eilutė. Pasirinkimo procedūros iškviečiamos SELECT užklausose, o vykdomosios procedūros – naudojant komandą EXECUTE PROCEDURE.
Abiejų tipų saugomos procedūros turi tą pačią kūrimo sintaksę ir formaliai nesiskiria, todėl bet kurią vykdomąją procedūrą galima iškviesti SELECT užklausoje, o bet kurią atrankos procedūrą galima iškviesti naudojant EXECUTE PROCEDURE. Kyla klausimas, kaip HP elgsis kada skirtingi tipai skambinti. Kitaip tariant, skirtumas yra tam tikro tipo skambučio procedūros sukūrimas. Tai yra, pasirinkimo procedūra yra specialiai sukurta iškviesti iš SELECT užklausos, o vykdomoji procedūra yra specialiai sukurta iškviesti naudojant EXECUTE PROCEDURE. Pažiūrėkime, kokie yra šių dviejų tipų HP dizaino skirtumai.
Norėdami suprasti, kaip veikia mėginių ėmimo procedūra, turėsite šiek tiek pasigilinti į teoriją. Įsivaizduokime įprastą SQL užklausą, pvz., SELECT ID, NAME FROM Table_example. Jo vykdymo rezultate gauname lentelę, susidedančią iš dviejų stulpelių (ID ir PAVADINIMAS) ir tam tikro skaičiaus eilučių (lygus eilučių skaičiui lentelėje Table_example). Dėl šios užklausos grąžinta lentelė dar vadinama SQL duomenų rinkiniu.Pagalvokime, kaip formuojamas duomenų rinkinys vykdant šią užklausą.Serveris, gavęs užklausą, nustato, į kurias lenteles ji nukreipia, tada suranda nustatyti, kuris šių lentelių įrašų poaibis turi būti įtrauktas į užklausos rezultatą. Toliau serveris nuskaito kiekvieną užklausos rezultatus atitinkantį įrašą, iš jo parenka reikiamus laukus (mūsų atveju ID ir NAME) ir išsiunčia klientui. Tada procesas kartojamas dar kartą – ir taip kiekvienam pasirinktam įrašui.
Visas šis nukrypimas būtinas, kad mielas skaitytojas suprastų, jog visi SQL duomenų rinkiniai generuojami eilė po eilutės, taip pat ir saugomose procedūrose! Ir pagrindinis skirtumas tarp gavimo procedūrų ir vykdomųjų procedūrų yra tas, kad pirmosios skirtos grąžinti daug eilučių, o antrosios skirtos grąžinti tik vieną. Štai kodėl jie naudojami skirtingai: pasirinkimo procedūra iškviečiama naudojant komandą SELECT, kuri „reikalauja“ procedūros atsisakyti visų įrašų, kuriuos ji gali grąžinti. Vykdomoji procedūra iškviečiama naudojant EXECUTE PROCEDURE, kuri „išima“ tik vieną eilutę iš HP, o likusias ignoruoja (net jei jos egzistuoja!).
Kad būtų aiškiau, pažvelkime į mėginių ėmimo procedūros pavyzdį. Norėdami > atleisti, sukurkime išsaugotą procedūrą, kuri veikia lygiai taip pat PASIRINKTI užklausą ID, PAVADINIMAS IŠ lentelės_Pavyzdys, tai yra, jis tiesiog parenka ID ir PAVADINIMO laukus iš visos lentelės. Štai šis pavyzdys:

KURTI PROCEDŪRĄ Simple_Select_SP
GRĄŽINA (
procID INTEGER,
procNAME VARCHAR (80))
AS
PRADĖTI
DĖL
PASIRINKITE ID, PAVADINIMAS IŠ lentelės_pavyzdys
INTO:procID, :procNAME
DARYK
PRADĖTI
SUSTABDYTI;
GALAS
GALAS

Pažvelkime į šios procedūros, vadinamos Simple_Select_SP, veiksmus. Kaip matote, jis neturi įvesties parametrų ir turi du išvesties parametrus - ID ir NAME. Įdomiausia, žinoma, slypi procedūros korpuse. Čia naudojama konstrukcija FOR SELECT:

DĖL
PASIRINKITE ID, PAVADINIMAS IŠ lentelės_pavyzdys
INTO:procID, :procNAME
DARYK
PRADĖTI

/*padarykite ką nors su kintamaisiais procID ir procName*/

GALAS

Ši kodo dalis reiškia: kiekvienoje eilutėje, pasirinktoje iš lentelės Table_example, įdėkite pasirinktas reikšmes į procID ir procName kintamuosius, tada atlikite ką nors su šiais kintamaisiais.
Galite nustebti ir paklausti: „Kintamieji? Kokie dar kintamieji? 9“ Šio skyriaus nuostaba, kad kintamuosius galime naudoti saugomose procedūrose. HP kalba galite deklaruoti tiek savo vietinius kintamuosius procedūroje, tiek naudoti įvesties ir išvesties parametrus kaip kintamuosius.
Norint deklaruoti vietinį kintamąjį saugomoje procedūroje, jo aprašymą reikia įdėti po AS raktažodžio ir prieš pirmąjį žodį BEGIN. Vietinio kintamojo aprašymas atrodo taip:

SKELBTI KINTAMĄJĮ ;

Pavyzdžiui, norėdami deklaruoti sveikąjį vietinį kintamąjį Mylnt, tarp AS ir BEGIN įterpkite šią deklaraciją

DECLARE VARIABLE Mylnt INTEGER;

Kintamieji mūsų pavyzdyje prasideda dvitaškiu. Tai daroma, nes jie pasiekiami naudojant komandą FOR SELECT SQL, todėl norint atskirti laukus lentelėse, kurie naudojami SELECT, ir kintamuosius, prieš pastarąjį turi būti dvitaškis. Juk kintamieji gali turėti lygiai tokį patį pavadinimą kaip ir lentelių laukai!
Tačiau dvitaškis prieš kintamojo pavadinimą turėtų būti naudojamas tik SQL užklausose. Už tekstų ribų kintamasis nurodomas be dvitaškio, pavyzdžiui:

procName="Kažkas vardas";

Bet grįžkime prie mūsų procedūros esmės. Sąlyga FOR SELECT pateikia duomenis ne lentelės pavidalu – duomenų rinkinį, o po vieną eilutę. Kiekvienas grąžinamas laukas turi būti įdėtas į savo kintamąjį: ID => procID, NAME => procName. Dalyje DO šie kintamieji siunčiami klientui, kuris iškvietė procedūrą naudojant komandą SUSPEND
Taigi komanda FOR SELECT...DO perkelia įrašus, pasirinktus komandos SELECT dalyje. Ciklo, kurį sudaro DO dalis, korpuse kitas sugeneruotas įrašas perduodamas klientui naudojant komandą SUSPEND.
Taigi, atrankos procedūra skirta grąžinti vieną ar daugiau eilučių, kurioms HP korpuse yra organizuojama kilpa, kuri užpildo gautus kintamuosius parametrus. O šio ciklo korpuso pabaigoje visada yra SUSPEND komanda, kuri klientui grąžins kitą duomenų eilutę.

Ciklos ir šakų teiginiai

Be komandos FOR SELECT...DO, kuri organizuoja kilpą per pasirinkimo įrašus, yra dar vienas ciklo tipas – WHILE...DO, leidžiantis organizuoti ciklą pagal bet kokių sąlygų patikrinimą. Štai pavyzdys, kaip HP naudoja WHILE..DO kilpą. Ši procedūra grąžina sveikųjų skaičių kvadratus nuo 0 iki 99:

KURTI PROCEDJRE QUAD
GRĄŽINIMAI (SVEIKI KVADRATO SKAIČIAI)
AS
DEKLARUOTI KINTAMĄJĮ I INTEGRUOTI SKAIČIUS;
PRADĖTI
I = 1;
Kol aš<100) DO
PRADĖTI
QUADRAT= I*I;
I=I+1;
SUSTABDYTI;
GALAS
GALAS

Vykdydami užklausą SELECT FROM QUAD, gausime lentelę su vienu QUADRAT stulpeliu, kuriame bus sveikųjų skaičių nuo 1 iki 99 kvadratai.
Be SQL pavyzdžio ir klasikinio ciklo rezultatų kartojimo, saugomų procedūrų kalba naudoja operatorių IF...THEN..ELSE, leidžiantį organizuoti šakojimą priklausomai nuo bet kokių sąlygų vykdymo. Jos sintaksė yra panaši daugeliui šakojančių operatorių aukšto lygio programavimo kalbomis, pvz., Pascal ir C.
Pažvelkime į sudėtingesnį saugomos procedūros, kuri atlieka toliau nurodytus veiksmus, pavyzdį.

1. Skaičiuoja vidutinę kainą lentelėje Table_example (žr. skyrių „Lentelių pirminiai raktai ir generatoriai“)
2. Tada kiekvienam lentelės įrašui jis atlieka tokį patikrinimą: jei esama kaina (PRICE) yra didesnė už vidutinę kainą, tada ji nustato kainą, lygią vidutinei kainai, pridėjus nurodytą fiksuotą procentą.
3. Jei esama kaina yra mažesnė arba lygi vidutinei kainai, tada nustatoma kaina, lygi ankstesnei kainai, pridėjus pusę skirtumo tarp ankstesnės ir vidutinės kainos.
4. Grąžina visas pakeistas lentelės eilutes.

Pirmiausia apibrėžkime HP pavadinimą, taip pat įvesties ir išvesties parametrus Visa tai parašyta saugomos procedūros antraštėje.

KURTI PROCEDŪRĄ Padidinkite kainas (
2 proc. padidės DVIGUBAS TIKSLUMAS)
GRĄŽIA (ID INTEGRUS, PAVADINIMAS VARCHAR(SO), new_price DOUBLE
TIKSLUMAS AS

Procedūra vadinsis IncreasePrices, ji turi vieną DOUBLE PRECISION tipo įvesties parametrą Peiceni21nciease ir 3 išvesties parametrus – ID, NAME ir new_pnce. Atkreipkite dėmesį, kad pirmieji du išvesties parametrai turi tokius pačius pavadinimus kaip ir lentelės Table_example laukai, su kuriais ketiname dirbti.Tai leidžia saugomos procedūrų kalbos taisyklės.
Dabar turime deklaruoti vietinį kintamąjį, kuris bus naudojamas vidutinei vertei saugoti. Deklaracija atrodys taip:

DEKLARUOTI KINTAMĄ Avg_price DVIGUBAS TIKSLUMAS;

Dabar pereikime prie saugomos procedūros korpuso.Atidarykite HP korpusą raktinis žodis BEGIN.
Pirmiausia turime atlikti pirmąjį mūsų algoritmo žingsnį – apskaičiuoti vidutinę kainą. Norėdami tai padaryti, naudosime tokio tipo užklausą:

PASIRINKTI AVG (kaina_l)
FROM Table_Example
INTO:vidutinė_kaina,-

Ši užklausa naudoja AVG agregavimo funkciją, kuri grąžina PRICE_1 lauko vidurkį tarp pasirinktų užklausos eilučių – mūsų atveju PRICE_1 vidurkį visoje lentelėje Table_example. Užklausos grąžinta reikšmė įdedama į kintamąjį avg_price. Atminkite, kad prieš kintamąjį avg_pnce rašomas dvitaškis, kad jis būtų atskirtas nuo užklausoje naudojamų laukų.
Šios užklausos ypatumas yra tas, kad ji visada pateikia tiksliai vieną įrašą. Tokios užklausos vadinamos viengubomis užklausomis.Ir tik tokios parinktys gali būti naudojamos saugomose procedūrose. Jei užklausa pateikia daugiau nei vieną eilutę, ji turi būti suformatuota kaip FOR SELECT...DO konstrukcija, kuri sutvarko ciklą, kad apdorotų kiekvieną grąžintą eilutę.
Taigi, gavome vidutinę kainą. Dabar reikia peržiūrėti visą lentelę, palyginti kiekvieno įrašo kainos vertę su vidutine kaina ir imtis atitinkamų veiksmų
Nuo pat pradžių organizuojame kiekvieno įrašo paiešką iš lentelės Table_example

DĖL
PASIRINKITE ID, PAVADINIMAS, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DARYK
PRADĖTI
/*_čia aprašome kiekvieną įrašą*/
GALAS

Kai ši konstrukcija bus vykdoma, duomenys iš lentelės Table_example bus išgaunami iš lentelės eilutės po eilutės ir kiekvienoje eilutėje esančios lauko reikšmės bus priskirtos kintamiesiems ID, NAME ir new_pnce. Žinoma, atminkite, kad šie kintamieji yra deklaruojami kaip išvesties parametrai, tačiau nereikia jaudintis, kad pasirinkti duomenys bus grąžinti kaip rezultatai: tai, kad išvesties parametrams yra kažkas priskirtas, nereiškia, kad klientas skambina HP iš karto gaus šias vertes! Parametrai perduodami tik tada, kai vykdoma komanda SUSPEND, o prieš tai išvesties parametrus galime naudoti kaip įprastus kintamuosius – mūsų pavyzdyje kaip tik tai darome su parametru new_price.
Taigi, kilpos BEGIN... END turinyje galime apdoroti kiekvienos eilutės reikšmes. Kaip prisimenate, turime išsiaiškinti, kaip esama kaina lyginama su vidutine, ir imtis atitinkamų veiksmų. Šią palyginimo procedūrą įgyvendiname naudodami IF teiginį:

IF (new_price > avg_price) THEN /*jei esama kaina yra didesnė už vidutinę kainą*/
PRADĖTI
/*tada įdiekite nauja kaina, lygi vidutinei kainai, pridėjus fiksuotą procentą */
nauja_kaina = (vid._kaina + vid._kaina*(Procentai2padidėjimas/100));
ATNAUJINTI Table_example
NUSTATYTI KAINA_1 = :nauja_kaina
KUR ID = :ID;
GALAS
KITAS
PRADĖTI
/* Jei esama kaina yra mažesnė arba lygi vidutinei kainai, tada nustatykite kainą, lygią ankstesnei kainai, pridėjus pusę skirtumo tarp ankstesnės ir vidutinės kainos */
nauja_kaina = (naujas_pnce + ((vid._pnce new_price)/2)) ;
ATNAUJINTI Table_example
NUSTATYTI KAINA_1 = :nauja_kaina
WHERE ID = .ID;
GALAS

Kaip matote, rezultatas yra gana didelė IF konstrukcija, kurią būtų sunku suprasti, jei ne /**/ simboliuose esantys komentarai.
Norėdami pakeisti kainą pagal apskaičiuotą skirtumą, naudosime UPDATE teiginį, kuris leidžia keisti esamus įrašus- vienas ar daugiau. Norėdami vienareikšmiškai nurodyti, kuriame įraše reikia keisti kainą, sąlygoje WHERE naudojame pirminio rakto lauką, lygindami jį su kintamojo, kuriame saugoma esamo įrašo ID reikšmė: ID=:ID reikšme. Atminkite, kad prieš ID kintamąjį rašomas dvitaškis.
Įvykdžius konstrukciją IF...THEN...ELSE, ID, NAME ir new_price kintamuosiuose yra duomenų, kuriuos turime grąžinti klientui, kuris iškvietė procedūrą. Tam po IF reikia įterpti komandą SUSPEND, kuri išsiųs duomenis iš kur buvo iškviesta HP Perkėlimo metu procedūra bus sustabdyta, o kai reikės HP naujas įėjimas, tada jis bus tęsiamas dar kartą – ir tai tęsis tol, kol FOR SELECT...DO pakartos visus savo užklausos įrašus.
Pažymėtina, kad be komandos SUSPEND, kuri tik sustabdo saugomą procedūrą, yra komanda EXIT, kuri nutraukia saugomą procedūrą perdavus eilutę. Tačiau komanda EXIT naudojama gana retai, nes ji daugiausia reikalinga ciklui nutraukti, kai pasiekiama sąlyga
Tačiau tuo atveju, kai procedūra buvo iškviesta naudojant SELECT sakinį ir baigta naudojant EXIT, paskutinė gauta eilutė nebus grąžinta. Tai yra, jei jums reikia nutraukti procedūrą ir vis tiek gauti šią eilutę, turite naudoti seką

SUSTABDYTI;
IŠĖJIMAS;

Pagrindinis EXIT tikslas yra gauti pavienius duomenų rinkinius, grąžintus parametrus skambinant EXECUTE PROCEDURE. Tokiu atveju nustatomos išvesties parametrų reikšmės, tačiau iš jų nesugeneruojamas SQL duomenų rinkinys, o procedūros vykdymas baigiamas.
Užsirašykime visą saugomos procedūros tekstą, kad galėtume vienu žvilgsniu užfiksuoti jos logiką:

KURTI PROCEDŪRĄ Padidinkite kainas (
2 procentas, padidinkite DVIGUBĄ TIKSLUMĄ)
GRĄŽINIMAI (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DEKLARUOTI KINTAMĄ Avg_price DVIGUBAS TIKSLUMAS;
PRADĖTI
PASIRINKTI AVG (kaina_l)
FROM Table_Example
INTO:vidutinė_kaina;
DĖL
PASIRINKITE ID, PAVADINIMAS, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DARYK
PRADĖTI
/*čia apdorokite kiekvieną įrašą*/
IF (new_pnce > avg_price) THEN /*jei esama kaina yra didesnė už vidutinę kainą*/
PRADĖTI
/*nustatykite naują kainą, lygią vidutinei kainai pridėjus fiksuotą procentą */
nauja_kaina = (vid._kaina + vid._kaina*(Procentai2lnpadidėjimas/100));
ATNAUJINTI Table_example
NUSTATYTI KAINA_1 = :nauja_kaina
KUR ID = :ID;
GALAS
KITAS
PRADĖTI
/* Jei esama kaina yra mažesnė arba lygi vidutinei kainai, tada nustatoma kaina, lygi ankstesnei kainai, pridėjus pusę skirtumo tarp ankstesnės ir vidutinės kainos */
nauja_kaina = (nauja_kaina + ((vid._kaina - nauja_kaina)/2));
ATNAUJINTI Table_example
NUSTATYTI KAINA_1 = :nauja_kaina
KUR ID = :ID;
GALAS
SUSTABDYTI;
GALAS
GALAS

Šis saugomos procedūros pavyzdys iliustruoja pagrindinių saugomų procedūrų kalbos konstrukcijų ir trigerių naudojimą. Toliau apžvelgsime būdus, kaip naudoti saugomas procedūras sprendžiant kai kurias įprastas problemas.

Rekursyvios saugomos procedūros

InterBase saugomos procedūros gali būti rekursinės. Tai reiškia, kad išsaugota procedūra gali išsikviesti save. Leidžiama iki 1000 saugomų procedūrų įdėjimo lygių, tačiau turime atsiminti, kad laisvi resursai serveryje gali baigtis dar nepasiekus maksimalaus HP įdėjimo.
Vienas iš dažniausiai naudojamų saugomų procedūrų yra apdoroti duomenų bazėje saugomas medžio struktūras. Medžiai dažnai naudojami gaminių sudėties, sandėlio, personalo ir kitose įprastose srityse.
Pažiūrėkime į saugomos procedūros, kuri parenka visus tam tikro tipo produktus, pradedant nuo tam tikro lizdo lygio, pavyzdį.
Pateikiame tokį problemos teiginį: turime prekių katalogą su hierarchinė struktūraŠis tipas:

Prekės
- Prietaisai
- Šaldytuvai
- Trijų kamerų
- Dvikamera
- Vienos kameros
- Skalbimo mašinos
- Vertikalus
- Priekinė
- Klasika
- Siauras
- Kompiuterinė technologija
....

Šioje produktų kategorijų katalogo struktūroje gali būti įvairaus gylio šakų. ir laikui bėgant didėja. Mūsų užduotis yra užtikrinti visų baigtinių elementų pasirinkimą iš katalogo su "išplečiant visą pavadinimą", pradedant nuo bet kurio mazgo. Pavyzdžiui, jei pasirenkame mazgą „Skalbimo mašinos“, turime gauti šias kategorijas:

Skalbimo mašinos – Vertikalios
Skalbimo mašinos - Front Classic
Skalbimo mašinos - Front Narrow

Apibrėžkime lentelės struktūrą produktų katalogo informacijai saugoti. Mes naudojame supaprastintą schemą, kad sutvarkytume medį vienoje lentelėje:

KURTI LENTELĘ GoodsTree
(ID_GOOD SVEIKASIS SKAIČIUS NE NULIS,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
apribojimas pkGooci pirminis raktas (ID_GOOD));

Sukuriame vieną lentelę GoodsTree, kurioje yra tik 3 laukai: ID_GOOD – išmanusis kategorijos identifikatorius, ID_PARENT_GOOD – pagrindinės šios kategorijos įmonės identifikatorius ir GOOD_NAME – kategorijos pavadinimas. Siekdami užtikrinti šios lentelės duomenų vientisumą, šiai lentelei taikysime išorinio rakto apribojimą:

ALTER TABLE GoodsTree
PRIDĖTI APRIBOJIMĄ FK_goodstree
UŽSIENIS RAKTAS (ID_PARENT_GOOD)
NUORODOS GOODSTEE (ID__GOOD)

Lentelė nurodo save, o šis išorinis raktas tai stebi. kad lentelėje nebūtų nuorodų į neegzistuojančius tėvus, taip pat būtų išvengta bandymų ištrinti produktų kategorijas, kurios turi vaikų.
Į savo lentelę įveskime šiuos duomenis:

Dabar, kai turime vietą duomenims saugoti, galime pradėti kurti saugomą procedūrą, kuri išves visas „galutines“ produktų kategorijas „išplėstoje“ formoje – pavyzdžiui, kategorijai „Trijų kamerų“, visa kategorija. pavadinimas būtų „Buitinė technika šaldytuvai“ Trijų kamerų“.
Saugomos procedūros, apdorojančios medžio struktūras, turi savo terminiją. Kiekvienas medžio elementas vadinamas mazgu; o ryšys tarp mazgų, nurodančių vienas į kitą, vadinamas tėvų ir vaikų ryšiu. Mazgai, esantys pačiame medžio gale ir neturintys vaikų, vadinami „lapais“.
Šios saugomos procedūros įvesties parametras bus kategorijos identifikatorius, nuo kurio turėsime pradėti gilinimą. Išsaugota procedūra atrodys taip:

KURTI PROCEDŪRĄ GETFULLNAME (ID_GOOD2RODYTI SVEIKIĄ SKAIČIŲ)
GRĄŽINIMAI (FULL_GOODS_NAME VARCHAR (1000),
ID_CHILD_GOOD INTEGER)
AS
DEKLARUOTI KINTAMĄJĮ CURR_CHILD_NAME VARCHAR(80);
PRADĖTI
/*0organizuoti išorę FOR kilpa PASIRINKITE tiesioginius produkto palikuonis su ID_GOOD=ID_GOOD2SHOW */
PASIRINKIANT gtl.id_good, gtl.good_name
IŠ GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DARYK
PRADĖTI
/"Patikrinkite naudodami funkciją EXISTS, kuri grąžina TRUE, jei užklausa skliausteliuose pateikia bent vieną eilutę. Jei rastas mazgas su ID_PARENT_GOOD = ID_CHILD_GOOD neturi antrinių, tai yra medžio „lapas“ ir įtraukiamas į rezultatus. */
IF (NE EXISTS(
PASIRINKTI * IŠ GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
TADA
PRADĖTI
/* Perduokite medžio „lapą“ prie rezultatų */
SUSTABDYTI;
GALAS
KITAS
/* Mazgams, turintiems vaikų*/
PRADĖTI
/*išsaugokite pirminio mazgo pavadinimą laikinajame kintamajame */
CURR_CHILD_NAME=visas_prekių_vardas;
/* paleisti šią procedūrą rekursyviai */
DĖL
SELECT ID_CHILD_GOOD, full_goods_name
IŠ GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
PRADĖKITE
/*pridėkite pirminio mazgo pavadinimą prie rasto antrinio vardo, naudodami eilutės sujungimo operaciją || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSTABDYTI; /* grąžinti visą prekės pavadinimą*/
GALAS
GALAS
GALAS
GALAS

Jei atliksime šią procedūrą su įvesties parametru ID_GOOD2SHOW= 1, gausime:

Kaip matote, naudodami rekursyvią saugomą procedūrą, perėjome visą kategorijų medį ir parodėme pilną „lapų“ kategorijų, esančių pačiuose šakų galiukuose, pavadinimą.

Išvada

Tai užbaigia mūsų pagrindinių saugomos procedūros kalbos ypatybių svarstymą. Akivaizdu, kad neįmanoma visiškai įsisavinti saugomų procedūrų kūrimo tik viename skyriuje, tačiau čia mes bandėme pristatyti ir paaiškinti pagrindines sąvokas, susijusias su saugomomis procedūromis. Apibūdinti HP projektavimo projektai ir metodai gali būti taikomi daugumoje duomenų bazių programų
Kai kurie svarbūs klausimai, susiję su saugomų procedūrų kūrimu, bus aptarti kitame skyriuje – „Išplėstinės InterBase saugomų procedūrų kalbos galimybės“, kuris skirtas išimčių tvarkymui, saugomų procedūrų klaidų situacijų sprendimui ir darbui su masyvais.