###
  • Programe
  • Siguranță
  • Știri
  • Interesant
  • Sfat
  • Știri
  • Recenzii

    • Proceduri stocate SQL: creare și utilizare. Proceduri stocate Proceduri stocate Sql server

    23.01.2022 Programe

    procedură stocată este posibilă numai dacă este efectuată în contextul bazei de date în care se află procedura.

    Tipuri de proceduri stocate

    SQL Server are mai multe tipuri proceduri stocate.

    • Sistem proceduri stocate concepute pentru a efectua diverse acţiuni administrative. Aproape toate activitățile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că sistemic proceduri stocate sunt o interfață care oferă lucru cu tabelele de sistem, care în cele din urmă se rezumă la modificarea, adăugarea, ștergerea și preluarea datelor din tabelele de sistem atât ale bazelor de date ale utilizatorilor, cât și ale sistemului. Sistem proceduri stocate au prefixul sp_, sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
    • Personalizat proceduri stocate implementează anumite acțiuni. Proceduri stocate– un obiect de bază de date cu drepturi depline. Drept urmare, fiecare procedură stocată se află într-o anumită bază de date unde este executat.
    • Temporar proceduri stocate există doar pentru o perioadă, după care sunt distruse automat de server. Ele sunt împărțite în locale și globale. Local temporar proceduri stocate pot fi apelate doar din conexiunea în care au fost create. Când creați o astfel de procedură, trebuie să îi dați un nume care începe cu un singur caracter #. Ca toate obiectele temporare, proceduri stocate de acest tip sunt șterse automat când utilizatorul se deconectează sau serverul este repornit sau oprit. Global temporar proceduri stocate sunt disponibile pentru orice conexiuni de la un server care are aceeași procedură. Pentru a-l defini, trebuie doar să îi dați un nume care începe cu caracterele ## . Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit, sau când conexiunea în contextul în care au fost create este închisă.

    Creați, modificați și ștergeți procedurile stocate

    Creare procedură stocată presupune rezolvarea următoarelor probleme:

    • determinarea tipului de creat procedură stocată: temporar sau personalizat. În plus, vă puteți crea propriul sistem procedură stocată, dându-i un nume prefixat cu sp_ și plasându-l în baza de date a sistemului. Această procedură va fi disponibilă în contextul oricărei baze de date de server local;
    • drepturi de acces de planificare. În timp ce creați procedură stocată trebuie avut în vedere faptul că va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care a creat-o;
    • definiție parametrii procedurii stocate. Similar cu procedurile incluse în majoritatea limbajelor de programare, proceduri stocate poate avea parametri de intrare și de ieșire;
    • dezvoltarea codului procedură stocată. Codul procedurii poate conține o secvență de orice comenzi SQL, inclusiv apeluri către alte comenzi proceduri stocate.

    Crearea unuia nou și schimbarea unuia existent procedură stocată realizat folosind următoarea comandă:

    <определение_процедуры>::= (CREATE | ALTER ) procedure_name [;număr] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

    Să ne uităm la parametrii acestei comenzi.

    Folosind prefixele sp_ ​​, # , ## , procedura creată poate fi definită ca sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului care va deține procedura creată, precum și numele bazei de date unde ar trebui să fie localizată. Astfel, pentru a plasa creatul procedură stocatăîntr-o anumită bază de date, trebuie să lansați comanda CREATE PROCEDURE în contextul acelei baze de date. Când te întorci de pe corp procedură stocată numele scurtate pot fi folosite pentru obiectele aceleiași baze de date, adică fără a specifica numele bazei de date. Când trebuie să accesați obiecte aflate în alte baze de date, specificarea numelui bazei de date este obligatorie.

    Numărul din nume este un număr de identificare procedură stocată, care îl identifică în mod unic într-un grup de proceduri. Pentru ușurința gestionării, procedurile sunt în mod logic de același tip proceduri stocate pot fi grupate dându-le același nume, dar numere de identificare diferite.

    Pentru a transfera date de intrare și de ieșire în formatul creat procedură stocată pot fi utilizați parametri, ale căror nume, ca și numele variabilelor locale, trebuie să înceapă cu simbolul @. unu procedură stocată Puteți specifica mai mulți parametri separați prin virgule. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume coincid cu numele parametrilor acestei proceduri.

    Pentru a determina tipul de date care corespunde parametrul procedurii stocate, toate tipurile de date SQL sunt potrivite, inclusiv cele definite de utilizator. Cu toate acestea, tipul de date CURSOR poate fi folosit doar ca parametrul de ieșire procedură stocată, adică indicând cuvânt cheie IEȘIRE.

    Prezența cuvântului cheie OUTPUT înseamnă că parametrul corespunzător este destinat să returneze date de la procedură stocată. Cu toate acestea, acest lucru nu înseamnă că parametrul nu este potrivit pentru transmiterea de valori către procedură stocată. Specificarea cuvântului cheie OUTPUT indică serverului să iasă procedură stocată atribuiți valoarea curentă a parametrului variabilei locale care a fost specificată la apelarea procedurii ca valoare a parametrului. Rețineți că atunci când specificați cuvântul cheie OUTPUT, valoarea parametrului corespunzător la apelarea procedurii poate fi setată numai folosind o variabilă locală. Orice expresii sau constante care sunt permise pentru parametrii obișnuiți nu sunt permise.

    Cuvântul cheie VARYING este folosit împreună cu

    Ultima actualizare: 14.08.2017

    Adesea, o operație de date reprezintă un set de instrucțiuni care trebuie executate în o anumită secvență. De exemplu, atunci când adăugați o achiziție de produs, trebuie să introduceți date în tabelul comenzilor. Cu toate acestea, înainte de a face acest lucru, trebuie să verificați dacă produsul pe care îl cumpărați este în stoc. Poate fi necesar să verificați o serie de condiții suplimentare. Adică, de fapt, procesul de achiziție a unui produs acoperă mai multe acțiuni care trebuie efectuate într-o anumită secvență. Și în acest caz, ar fi mai optim să încapsulăm toate aceste acțiuni într-un singur obiect - procedură stocată(procedură stocată).

    Adică, în esență, procedurile stocate sunt un set de instrucțiuni care sunt executate ca o singură unitate. Astfel, procedurile stocate fac posibilă simplificarea operațiilor complexe și plasarea lor într-un singur obiect. Procesul de achiziție a unui produs se va schimba; în consecință, va fi suficient să schimbați codul de procedură. Adică, procedura simplifică și gestionarea codului.

    Procedurile stocate vă permit, de asemenea, să restricționați accesul la datele din tabele și, prin urmare, să reduceți probabilitatea unor acțiuni nedorite intenționate sau neintenționate în legătură cu aceste date.

    Și un alt aspect important este performanța. Procedurile stocate se execută de obicei mai rapid decât instrucțiunile SQL obișnuite. Acest lucru se datorează faptului că codul de procedură este compilat o dată când este lansat pentru prima dată și apoi salvat în formă compilată.

    Pentru a crea o procedură stocată, utilizați comanda CREATE PROCEDURE sau CREATE PROC.

    Deci procedura stocată are trei caracteristici cheie: simplificare a codului, securitate și performanță.

    De exemplu, să presupunem că există un tabel în baza de date care stochează date despre produse:

    CREATE TABLE Produse (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

    Să creăm o procedură stocată pentru a prelua date din acest tabel:

    UTILIZAȚI produsedb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

    Deoarece comanda CREATE PROCEDURE trebuie apelată într-un pachet separat, comanda USE care setează baza de date curentă este urmată de o comandă GO pentru a defini un nou pachet.

    Numele procedurii trebuie urmat de cuvântul cheie AS.

    Pentru a separa corpul unei proceduri de restul scriptului, codul procedurii este adesea plasat într-un bloc BEGIN...END:

    UTILIZAȚI produsedb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

    După adăugarea procedurii, o putem vedea în nodul bazei de date din SQL Server Management Studio în subnodul Programabilitate -> Proceduri stocate:

    Și vom putea controla procedura și printr-o interfață vizuală.

    Executarea procedurii

    Pentru a executa o procedură stocată, apelați comanda EXEC sau EXECUTE:

    EXEC ProductSummary

    Eliminarea unei proceduri

    Pentru a elimina o procedură, utilizați comanda DROP PROCEDURE:

    PROCEDURA DE RĂDARE Rezumat produs

    MySQL 5 are multe caracteristici noi, dintre care una dintre cele mai semnificative este crearea de proceduri stocate. În acest tutorial, voi vorbi despre ce sunt acestea și despre cum vă pot face viața mai ușoară.

    Introducere

    O procedură stocată este o modalitate de a încapsula acțiuni repetitive. Procedurile stocate pot declara variabile, pot manipula fluxul de date și pot folosi alte tehnici de programare.

    Motivul creării lor este clar și este confirmat de utilizarea frecventă. Pe de altă parte, dacă vorbești cu cei care lucrează cu ei neregulat, părerile se vor împărți în două flancuri complet opuse. Nu uita asta.

    In spate

    • Partajarea logicii cu alte aplicații. Procedurile stocate încapsulează funcționalitatea; aceasta oferă conectivitate pentru accesul și gestionarea datelor în diferite aplicații.
    • Izolarea utilizatorilor de tabelele bazei de date. Acest lucru vă permite să acordați acces la procedurile stocate, dar nu și la datele tabelului în sine.
    • Oferă un mecanism de protecție. Conform punctului anterior, dacă puteți accesa datele numai prin proceduri stocate, nimeni altcineva nu vă poate șterge datele prin comanda SQL DELETE.
    • Execuție îmbunătățită ca urmare a reducerii traficului de rețea. Folosind proceduri stocate, mai multe interogări pot fi combinate.

    Împotriva

    • Încărcare crescută pe serverul bazei de date datorită faptului că cea mai mare parte a muncii este efectuată pe partea de server și mai puțin pe partea clientului.
    • Va trebui să înveți multe. Va trebui să învățați sintaxa expresiei MySQL pentru a vă scrie procedurile stocate.
    • Dublați logica aplicației în două locuri: codul serverului și codul pentru procedurile stocate, complicând astfel procesul de manipulare a datelor.
    • Migrarea de la un SGBD la altul (DB2, SQL Server etc.) poate duce la probleme.

    Instrumentul cu care lucrez se numește MySQL Query Browser, care este destul de standard pentru interacțiunea cu bazele de date. Instrument Linie de comanda MySQL este o altă alegere excelentă. Motivul pentru care vă spun acest lucru este că phpMyAdmin favorit al tuturor nu acceptă rularea procedurilor stocate.

    Apropo, folosesc o structură de bază de tabel pentru a vă facilita înțelegerea acestui subiect. Vorbesc despre procedurile stocate și sunt suficient de complexe pentru a necesita aprofundarea în structura greoaie a tabelului.

    Pasul 1: Plasați un limitator

    Un delimitator este un caracter sau șir de caractere care este folosit pentru a indica clientului MySQL că ați terminat de scris expresia SQL. De secole, punctul și virgulă a fost delimitătorul. Cu toate acestea, pot apărea probleme deoarece pot exista mai multe expresii într-o procedură stocată, fiecare dintre acestea trebuie să se termine cu punct și virgulă. În acest tutorial folosesc șirul „//” ca delimitator.

    Pasul 2: Cum să lucrați cu procedurile stocate

    Crearea unei proceduri stocate

    DELIMITER // CREATE PROCEDURE `p2` () LIMBA SQL DETERMINISTIC SQL DEFINITOR DE SECURITATE COMMENT "O procedură" BEGIN SELECT "Hello World!"; SFÂRŞIT//

    Prima parte a codului creează o procedură stocată. Următorul conține parametri opționali. Apoi urmează numele și, în sfârșit, corpul procedurii în sine.

    Numele procedurilor stocate sunt sensibile la majuscule. De asemenea, nu puteți crea mai multe proceduri cu același nume. Nu pot exista expresii în interiorul unei proceduri stocate care modifică baza de date în sine.

    4 caracteristici ale unei proceduri stocate:

    • Limbă: în scopuri de portabilitate, implicit este SQL.
    • Determinist: dacă procedura returnează întotdeauna același rezultat și ia aceiași parametri de intrare. Aceasta este pentru procesul de replicare și înregistrare. Valoarea implicită NU este DETERMINISTICĂ.
    • Securitate SQL: drepturile utilizatorului sunt verificate în timpul apelului. INVOKER este utilizatorul care apelează procedura stocată. DEFINER este „creatorul” procedurii. Valoarea implicită este DEFINER.
    • Comentariu: în scopuri de documentare, valoarea implicită este „”

    Apelarea unei proceduri stocate

    Pentru a apela o procedură stocată, trebuie să tastați cuvântul cheie CALL, urmat de numele procedurii, urmat de parametrii (variabile sau valori) din paranteze. Sunt necesare paranteze.

    CALL nume_procedură_stocat (param1, param2, ....) CALL procedure1(10 , "parametru șir" , @parametru_var);

    Modificarea unei proceduri stocate

    MySQL are o instrucțiune ALTER PROCEDURE pentru modificarea procedurilor, dar este potrivită doar pentru modificarea anumitor caracteristici. Dacă trebuie să modificați parametrii sau corpul unei proceduri, ar trebui să o ștergeți și să o recreați.

    Eliminarea unei proceduri stocate

    PROCEDURA DE REDARE DACĂ EXISTĂ p2;

    Aceasta este o comandă simplă. Instrucțiunea IF EXISTS prinde o eroare dacă o astfel de procedură nu există.

    Pasul 3: Opțiuni

    Să vedem cum putem trece parametrii unei proceduri stocate.

    • CREATE PROCEDURE proc1(): listă de parametri goală
    • CREATE PROCEDURE proc1 (IN varname DATA-TYPE): un parametru de intrare. Cuvântul IN este opțional deoarece parametrii impliciti sunt IN (in).
    • CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): a fost returnat un parametru.
    • CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametru, atât de intrare, cât și de returnat.

    Desigur, puteți specifica mai mulți parametri de diferite tipuri.

    Exemplu de parametru IN

    DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS rezultat; SFÂRŞIT//

    Exemplu de parametru OUT

    DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Acesta este un test"; SFÂRŞIT //

    Exemplu de parametru INOUT

    DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; SFÂRŞIT //

    Pasul 4: Variabile

    Acum vă voi învăța cum să creați variabile și să le stocați în cadrul procedurilor. Trebuie să le declarați în mod explicit la începutul blocului BEGIN/END, împreună cu tipurile lor de date. Odată ce ați declarat o variabilă, o puteți utiliza în același mod ca variabilele de sesiune, literalele sau numele de coloane.

    Sintaxa declarației variabilelor arată astfel:

    DECLARE varname DATA-TYPE DEFAULT valoare implicită;

    Să declarăm câteva variabile:

    DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

    Lucrul cu variabile

    Odată ce ați declarat o variabilă, puteți seta valoarea acesteia folosind comenzile SET sau SELECT:

    DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE astăzi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = „Sunt un șir”; SELECTARE CONCAT(str,paramstr), azi DIN tabelul2 WHERE b >=5; SFÂRŞIT //

    Pasul 5: Structuri de control al firelor

    MySQL acceptă constructele IF, CASE, ITERATE, LEAVE LOOP, WHILE și REPEAT pentru a controla firele într-o procedură stocată. Vom analiza cum să folosiți IF, CASE și WHILE, deoarece sunt cele mai frecvent utilizate.

    IF design

    Folosind constructul IF, putem efectua sarcini care conțin condiții:

    DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; DACA variabila1 = 0 THEN SELECT variabila1; ENDIF; IF param1 = 0 THEN SELECT "Valoarea parametrului = 0"; ELSE SELECT "Valoarea parametrului<>0"; END IF; END //

    Design CASE

    CASE este o altă metodă de testare a condițiilor și de selectare a unei soluții adecvate. Aceasta este o modalitate excelentă de a înlocui multe construcții IF. Construcția poate fi descrisă în două moduri, oferind flexibilitate în gestionarea mai multor expresii condiționate.

    DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; CAZ variabila1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variabila1); ELSE INSERT INTO table1 VALUES (99); SFÂRȘIT CAZ; SFÂRŞIT //

    DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variabila1 INT; SET variabila1 = param1 + 1; CAZUL CÂND variabila1 = 0 ATUNCI INSERT INTO table1 VALORI (param1); WHEN variabila1 = 1 ATUNCI INSERT INTO table1 VALUES (variabila1); ELSE INSERT INTO table1 VALUES (99); SFÂRȘIT CAZ; SFÂRŞIT //

    În timp ce design

    Din punct de vedere tehnic, există trei tipuri de bucle: bucla WHILE, bucla LOOP și bucla REPEAT. Puteți, de asemenea, să bucleți folosind tehnica de programare Darth Vader: instrucțiuni GOTO. Iată un exemplu de buclă:

    DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variabila1, variabila2 INT; SET variabila1 = 0; WHILE variabila1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

    Pasul 6: Cursore

    Cursorele sunt folosite pentru a parcurge setul de rânduri returnate de o interogare și pentru a procesa fiecare rând.

    MySQL acceptă cursoare în procedurile stocate. Iată o scurtă sintaxă pentru crearea și utilizarea unui cursor.

    DECLARE nume-cursor CURSOR FOR SELECT ...; /*Declararea unui cursor și completarea acestuia */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Ce să faci când nu mai există înregistrări*/ OPEN cursor-name; /*Deschide cursorul*/ FETCH cursor-name INTO variabila [, variabila]; /*Atribuiți o valoare unei variabile egală cu valoarea curentă a coloanei*/ CLOSE cursor-name; /*Închide cursorul*/

    În acest exemplu vom face câteva operatii simple folosind cursorul:

    DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUARE HANDLER PENTRU SET NU GĂSIT b = 1; DESCHIS cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; DACA b = 0 ATUNCI SETATI c = c + a; ENDIF; SFÂRȘIT CÂND; ÎNCHIS cur1; SET param1 = c; SFÂRŞIT //

    Cursoarele au trei proprietăți pe care trebuie să le înțelegeți pentru a evita obținerea de rezultate neașteptate:

    • Nesensibil: un cursor care se deschide o dată nu va reflecta modificările din tabel care apar mai târziu. În realitate, MySQL nu garantează că cursorul va fi actualizat, așa că nu vă bazați pe el.
    • Numai citire: cursoarele nu pot fi modificate.
    • Fără derulare înapoi: cursorul se poate mișca într-o singură direcție - înainte, nu veți putea sări peste linii fără a le selecta.

    Concluzie

    În acest tutorial, v-am prezentat elementele de bază ale lucrului cu procedurile stocate și unele dintre proprietățile specifice asociate acestora. Desigur, va trebui să vă aprofundați cunoștințele în domenii precum securitatea, expresiile SQL și optimizarea înainte de a deveni un adevărat guru al procedurilor MySQL.

    Ar trebui să calculați ce beneficii veți primi din utilizarea procedurilor stocate în dvs aplicație specifică, și abia apoi creați numai procedurile necesare. În general, folosesc proceduri; În opinia mea, merită implementate în proiecte datorită securității, întreținerii codului și designului general. De asemenea, rețineți că procedurile MySQL sunt încă în lucru. Așteptați-vă îmbunătățiri în ceea ce privește funcționalitatea și îmbunătățirile. Vă rugăm să nu ezitați să vă împărtășiți opiniile.

    Includeți o linie în proceduri - SET NOCOUNT ON:

    Cu fiecare expresie DML, serverul SQL ne returnează cu atenție un mesaj care conține numărul de înregistrări procesate. Aceasta informatie Ne poate fi util în timp ce depanăm codul, dar după aceea va fi complet inutil. Scriind SET NOCOUNT ON, dezactivăm această funcție. Pentru procedurile stocate care conțin mai multe expresii sau/sau bucle această acțiune poate oferi o creștere semnificativă a performanței, deoarece volumul de trafic va fi redus semnificativ.

    Transact-SQL

    Utilizați numele schemei cu numele obiectului:

    Ei bine, cred că este clar. Această operațiune îi spune serverului unde să caute obiecte și, în loc să scotoci aleatoriu prin coșurile sale, va ști imediat unde trebuie să meargă și ce să ia. Cu un număr mare de baze de date, tabele și proceduri stocate, ne poate economisi în mod semnificativ timpul și nervii.

    Transact-SQL

    SELECT * FROM dbo.MyTable --A face acest lucru este bine --În loc de SELECT * FROM MyTable --Și a face acest lucru este rău --Apelarea procedurii EXEC dbo.MyProc --Bine din nou --În loc de EXEC MyProc --Rău!

    Nu utilizați prefixul „sp_” în numele procedurilor dvs. stocate:

    Dacă numele procedurii noastre începe cu „sp_”, SQL Server va căuta mai întâi în baza de date principală. Faptul este că acest prefix este utilizat pentru procedurile interne stocate personale ale serverului. Prin urmare, utilizarea acestuia poate duce la costuri suplimentare și chiar la rezultate incorecte dacă în baza sa de date se găsește o procedură cu același nume cu al dumneavoastră.

    Folosiți IF EXISTS (SELECT 1) în loc de IF EXISTS (SELECT *):

    Pentru a verifica existența unei înregistrări într-un alt tabel, folosim instrucțiunea IF EXISTS. Această expresie returnează adevărat dacă cel puțin o valoare este returnată din expresia internă, nu contează „1”, toate coloanele sau tabelul. În principiu, datele returnate nu sunt utilizate în niciun fel. Astfel, pentru a comprima traficul în timpul transmisiei de date, este mai logic să folosiți „1”, așa cum se arată mai jos.

    Proceduri stocate

    Subiectul acestui capitol este unul dintre cele mai puternice instrumente oferite dezvoltatorilor de aplicații de baze de date InterBase pentru implementarea logicii de business.Procedurile stocate (engleză, stoied procedures) vă permit să implementați o parte semnificativă a logicii aplicației la nivel de bază de date și astfel să creșteți performanța întregii aplicații, centralizarea procesării datelor și reducerea cantității de cod necesare pentru a îndeplini sarcinile atribuite.Aproape orice aplicație de bază de date destul de complexă nu poate face fără utilizarea procedurilor stocate.
    Pe lângă aceste avantaje binecunoscute ale utilizării procedurilor stocate, comune majorității SGBD-urilor relaționale, procedurile stocate InterBase pot acționa ca seturi de date aproape complete, permițând ca rezultatele pe care le returnează să fie utilizate în interogări SQL obișnuite.
    Adesea, dezvoltatorii începători își imaginează procedurile stocate pur și simplu ca un set de interogări SQL specifice care fac ceva în interiorul bazei de date și există opinia că lucrul cu procedurile stocate este mult mai dificil decât implementarea aceleiași funcționalități într-o aplicație client în limbaj. nivel inalt
    Deci, ce sunt procedurile stocate în InterBase?
    O procedură stocată (SP) este o parte a metadatelor bazei de date, care este o subrutină compilată în reprezentarea internă a InterBase, scrisă într-un limbaj special, al cărui compilator este încorporat în nucleul serverului InteiBase.
    O procedură stocată poate fi apelată din aplicații client, de la declanșatoare și din alte proceduri stocate. Procedura stocată rulează în interiorul procesului serverului și poate manipula datele din baza de date, precum și poate returna rezultatele execuției sale către clientul care a apelat-o (adică declanșator, HP, aplicație)
    Baza capabilităților puternice inerente HP este un limbaj de programare procedural, care include atât instrucțiuni modificate ale SQL obișnuit, cum ar fi INSERT, UPDATE și SELECT, cât și instrumente pentru organizarea ramurilor și buclelor (IF, WHILE), precum și instrumente de tratare a erorilor și situații excepționale Limbajul procedurilor stocate vă permite să implementați algoritmi complecși pentru lucrul cu date și, datorită concentrării pe lucrul cu date relaționale, HP este mult mai compact decât procedurile similare din limbile tradiționale.
    Trebuie remarcat faptul că același limbaj de programare este utilizat pentru declanșatoare, cu excepția unui număr de caracteristici și limitări. Diferențele dintre subsetul de limbaj utilizat în declanșatoare și limbajul HP sunt discutate în detaliu în capitolul „Declanșatoare” (partea 1).

    Exemplu de procedură stocată simplă

    Este timpul să creați prima procedură stocată și să o utilizați ca exemplu pentru a învăța procesul de creare a procedurilor stocate. Dar mai întâi, ar trebui să spunem câteva cuvinte despre cum să lucrați cu procedurile stocate. Faptul este că HP își datorează reputația de instrument obscur și incomod instrumentelor standard extrem de slabe pentru dezvoltarea și depanarea procedurilor stocate. Documentația InterBase recomandă crearea de proceduri folosind fișiere de script SQL care conțin text HP, care sunt furnizate ca intrare pentru interpretul isql și, astfel, crearea și modificarea HP If în acest script SQL în etapa de compilare a textului procedurii în BLR (despre BLR , consultați Capitolul „Structura bazei de date InterBase” (Partea 4)) dacă apare o eroare, isql va afișa un mesaj despre linia pe care a apărut această eroare a fișierului script SQL. Corectați greșeala și repetați totul din nou. Nu se vorbește deloc despre depanare în sensul modern al cuvântului, adică despre urmărirea execuției, cu capacitatea de a vizualiza valorile intermediare ale variabilelor. Evident, această abordare nu contribuie la creșterea atractivității procedurilor stocate în ochii dezvoltatorului
    Cu toate acestea, pe lângă abordarea minimalistă standard a dezvoltării HP<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
    Sintaxa procedurilor stocate este descrisă după cum urmează:

    CREATE PROCEDURE nume
    [ (tip de date param [, tip de date param ...]) ]
    )]
    LA FEL DE
    ;
    < procedure_body> = []
    < block>
    < vanable_declaration_list> =
    DECLARE VARIABLE var tip de date;

    =
    ÎNCEPE
    < compound_statement>
    [< compound_statement> ...]
    Sfârşit
    < compound_statement> = (afirmație;)

    Pare destul de voluminos și poate chiar greoi, dar de fapt totul este foarte simplu.Pentru a stăpâni treptat sintaxa, să ne uităm la exemple treptat mai complexe.
    Deci, iată un exemplu de procedură stocată foarte simplă care ia două numere ca intrare, le adaugă și returnează rezultatul:

    CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
    second_arg DUBLĂ PRECIZIE)
    RETURNARE (Rezultat DUBĂ PRECIZIE)
    LA FEL DE
    ÎNCEPE
    Rezultat=first_arg+second_arg;
    SUSPENDA;
    Sfârşit

    După cum puteți vedea, totul este simplu: după comanda CREATE PROCEDURE este indicat numele procedurii nou create (care trebuie să fie unică în baza de date) - în acest caz SP_Add, apoi parametrii de intrare HP - first_arg și second_arg - sunt enumerate în paranteze, separate prin virgule, indicând tipurile acestora.
    Lista parametrilor de intrare este o parte opțională a instrucțiunii CREATE PROCEDURE - există cazuri când o procedură primește toate datele pentru funcționarea sa prin interogări către tabele din interiorul corpului procedurii.

    Procedurile stocate folosesc orice tip de date scalare InteiBase Nu permite utilizarea de matrice și tipuri definite de utilizator - domenii

    Urmează cuvântul cheie RETURNS, după care parametrii returnați sunt enumerați în paranteze, indicând tipurile acestora - în acest caz, doar unul - Rezultat.
    Dacă procedura nu trebuie să returneze parametrii, atunci cuvântul RETURNS și lista parametrilor returnați lipsesc.
    După RETURNSQ este specificat cuvântul cheie AS. Înainte ca cuvântul cheie AS să dispară titlu, si dupa aceea - techo proceduri.
    Corpul unei proceduri stocate este o listă de descrieri ale variabilelor sale interne (locale) (dacă există, le vom analiza mai detaliat mai jos), separate prin punct și virgulă (;) și un bloc de instrucțiuni cuprinse între paranteze operator. ÎNCEPUT Sfârșit. În acest caz, corpul HP este foarte simplu - cerem să adăugăm două argumente de intrare și să atribuim rezultatul acestora celui de ieșire, apoi apelăm comanda SUSPEND. Puțin mai târziu vom explica esența acțiunii acestei comenzi, dar deocamdată vom observa doar că este necesar să transferăm parametrii de returnare de unde a fost apelată procedura stocată.

    Delimitatori în procedurile stocate

    Rețineți că o instrucțiune dintr-o procedură se termină cu punct și virgulă (;). După cum știți, punctul și virgulă este un separator standard de comandă în SQL - este un semnal pentru interpretul SQL că textul comenzii a fost introdus în întregime și ar trebui să înceapă procesarea acestuia. Nu s-ar dovedi că dacă interpretul SQL găsește un punct și virgulă în mijlocul HP, va presupune că comanda a fost introdusă în întregime și va încerca să execute o parte din procedura stocată? Această presupunere nu este lipsită de merit. Într-adevăr, dacă creați un fișier în care să scrieți exemplul de mai sus, adăugați o comandă de conexiune din baza de date și încercați să executați acest script SQL folosind interpretul isql, va fi returnată o eroare din cauza neașteptății, în opinia interpretului, care se încheie a comenzii de creare a procedurii stocate. Dacă creați proceduri stocate folosind fișiere de script SQL, fără a utiliza instrumente specializate pentru dezvoltatori InterBase, atunci înainte de fiecare comandă de creare HP (același lucru se aplică declanșatorilor) trebuie să schimbați separatorul de comenzi de script cu un alt caracter, altul decât punct și virgulă, iar după textul HP pentru a o restabili. Comanda isql care modifică separatorul de clauze SQL arată astfel:

    SETĂ TERM

    Pentru un caz tipic de creare a unei proceduri stocate, arată astfel:

    SET TERM^;
    CREATE PROCEDURE some_procedure
    ... . .
    Sfârşit
    ^
    SETĂ TERM ;^

    Apelarea unei proceduri stocate

    Dar să revenim la procedura noastră stocată. Acum că a fost creat, trebuie să îl apelați cumva, să îi transmiteți parametri și să primiți rezultatele returnate. Acest lucru este foarte ușor de făcut - trebuie doar să scrieți o interogare SQL ca aceasta:

    SELECTAȚI *
    FROM Sp_add(181.35, 23.09)

    Această interogare ne va returna o linie care conține doar un câmp de rezultat, care va conține suma numerelor 181.35 și 23.09, adică 204.44.
    Astfel, procedura noastră poate fi folosită în mod obișnuit interogări SQL, executat atât în ​​programele client, cât și în alte HP sau declanșatoare. Această utilizare a procedurii noastre este posibilă prin utilizarea comenzii SUSPEND la sfârșitul procedurii stocate.
    Cert este că în InterBase (și în toate clonele sale) există două tipuri de proceduri stocate: proceduri selectabile și proceduri executabile. Diferența în funcționarea acestor două tipuri de HP este că procedurile de eșantionare returnează de obicei multe seturi de parametri de ieșire, grupați linie cu linie, care arată ca un set de date, iar procedurile executabile ar putea fie să nu returneze deloc parametri, fie să returneze doar un set de parametri de ieșire, listați în Returnări, unde o linie de parametri. Procedurile Select sunt apelate în interogările SELECT, iar procedurile executabile sunt apelate folosind comanda EXECUTE PROCEDURE.
    Ambele tipuri de proceduri stocate au aceeași sintaxă de creare și nu sunt în mod formal diferite, astfel încât orice procedură executabilă poate fi apelată într-o interogare SELECT și orice procedură de selecție poate fi apelată folosind EXECUTE PROCEDURE. Întrebarea este cum se va comporta HP când tipuri diferite apel. Cu alte cuvinte, diferența constă în proiectarea procedurii pentru un anumit tip de apel. Adică, procedura de selectare este creată special pentru a fi apelată dintr-o interogare SELECT, iar procedura executabilă este creată special pentru a fi apelată utilizând PROCEDURA EXECUTE. Să ne uităm la care sunt diferențele în designul acestor două tipuri de HP.
    Pentru a înțelege cum funcționează procedura de eșantionare, va trebui să aprofundați puțin mai adânc în teorie. Să ne imaginăm o interogare SQL obișnuită, cum ar fi SELECT ID, NAME FROM Table_example. În urma execuției sale, obținem un tabel format din două coloane (ID și NUME) și un anumit număr de rânduri (egal cu numărul de rânduri din tabelul Table_example). Tabelul returnat ca urmare a acestei interogări se mai numește și set de date SQL. Să ne gândim la modul în care se formează setul de date în timpul executării acestei interogări. Serverul, după ce a primit interogarea, determină la ce tabele se referă, apoi găsește a afla ce subset de înregistrări din aceste tabele trebuie inclus în rezultatul interogării. Apoi, serverul citește fiecare înregistrare care satisface rezultatele interogării, selectează câmpurile necesare din ea (în cazul nostru, ID și NUME) și le trimite clientului. Apoi procesul se repetă din nou - și așa mai departe pentru fiecare înregistrare selectată.
    Toată această digresiune este necesară pentru ca dragul cititor să înțeleagă că toate seturile de date SQL sunt generate rând cu rând, inclusiv în procedurile stocate! Iar principala diferență dintre procedurile de preluare și procedurile executabile este că primele sunt concepute pentru a returna multe rânduri, în timp ce ultimele sunt concepute pentru a returna doar unul. De aceea sunt folosite diferit: procedura de selectare este apelată cu ajutorul comenzii SELECT, care „cere” ca procedura să renunțe la toate înregistrările pe care le poate returna. Procedura executabilă este numită folosind EXECUTE PROCEDURE, care „scoate” doar o linie din HP și ignoră restul (chiar dacă există!).
    Să ne uităm la un exemplu de procedură de eșantionare pentru a fi mai clar. Pentru > iertare, să creăm o procedură stocată care funcționează exact ca interogare SELECT ID, NAME FROM Table_Example, adică selectează pur și simplu câmpurile ID și NAME din întregul tabel. Iată acest exemplu:

    CREATE PROCEDURE Simple_Select_SP
    SE INTOARCE (
    PROCID INTEGER,
    procNAME VARCHAR(80))
    LA FEL DE
    ÎNCEPE
    PENTRU
    SELECT ID, NAME FROM table_example
    INTO:procID, :procNAME
    DO
    ÎNCEPE
    SUSPENDA;
    Sfârşit
    Sfârşit

    Să ne uităm la pașii acestei proceduri, numite Simple_Select_SP. După cum puteți vedea, nu are parametri de intrare și are doi parametri de ieșire - ID și NUME. Cel mai interesant lucru, desigur, constă în corpul procedurii. Construcția FOR SELECT este folosită aici:

    PENTRU
    SELECT ID, NAME FROM table_example
    INTO:procID, :procNAME
    DO
    ÎNCEPE

    /*faceți ceva cu variabilele procID și procName*/

    Sfârşit

    Această bucată de cod înseamnă următoarele: pentru fiecare rând selectat din tabelul Table_example, puneți valorile selectate în variabilele procID și procName, apoi faceți ceva cu aceste variabile.
    S-ar putea să faci o față surprinsă și să întrebi: „Variabile? Ce alte variabile? 9” Este un fel de surpriză a acestui capitol că putem folosi variabile în procedurile stocate. În limbajul HP, puteți declara atât propriile variabile locale în cadrul unei proceduri și puteți utiliza parametrii de intrare și de ieșire ca variabile.
    Pentru a declara o variabilă locală într-o procedură stocată, trebuie să plasați descrierea acesteia după cuvântul cheie AS și înainte de primul cuvânt BEGIN. Descrierea unei variabile locale arată astfel:

    DECLARE VARIABIL ;

    De exemplu, pentru a declara o variabilă locală întreagă Mylnt, ar trebui să inserați următoarea declarație între AS și BEGIN

    DECLARE VARIABILĂ Mylnt INTEGER;

    Variabilele din exemplul nostru încep cu două puncte. Acest lucru se face deoarece acestea sunt accesate în cadrul comenzii SQL FOR SELECT, deci pentru a face distincția între câmpurile din tabelele care sunt utilizate în SELECT și variabile, acestea din urmă trebuie să fie precedate de două puncte. La urma urmei, variabilele pot avea exact același nume ca și câmpurile din tabele!
    Dar două puncte dinaintea unui nume de variabilă ar trebui folosite numai în interogările SQL. În afara textelor, se face referire la o variabilă fără două puncte, de exemplu:

    procName="Un nume";

    Dar să revenim la corpul procedurii noastre. Clauza FOR SELECT returnează date nu sub forma unui tabel - un set de date, ci un rând pe rând. Fiecare câmp returnat trebuie plasat în propria sa variabilă: ID => procID, NAME => procName. În partea DO, aceste variabile sunt trimise clientului care a apelat procedura folosind comanda SUSPEND
    Astfel, comanda FOR SELECT...DO trece în buclă prin înregistrările selectate în partea SELECT a comenzii. În corpul buclei formate din partea DO, următoarea înregistrare generată este transferată către client folosind comanda SUSPEND.
    Deci, procedura de selecție este concepută pentru a returna unul sau mai multe rânduri, pentru care este organizată o buclă în interiorul corpului HP care umple parametrii variabili rezultați. Și la sfârșitul corpului acestei bucle există întotdeauna o comandă SUSPEND, care va returna următorul rând de date către client.

    Bucle și instrucțiuni de ramuri

    Pe lângă comanda FOR SELECT...DO, care organizează o buclă prin înregistrările unei selecții, există un alt tip de buclă - WHILE...DO, care vă permite să organizați o buclă pe baza verificării oricăror condiții. Iată un exemplu de HP care utilizează bucla WHILE..DO. Această procedură returnează pătratele numerelor întregi de la 0 la 99:

    CREATE PROCEDJRE QUAD
    RETURNĂRI (INTEGR CADRAT)
    LA FEL DE
    DECLARE VARIABILĂ I ÎNTREG;
    ÎNCEPE
    I = 1;
    In timp ce eu<100) DO
    ÎNCEPE
    QUADRAT= I*I;
    I=I+1;
    SUSPENDA;
    Sfârşit
    Sfârşit

    Ca urmare a executării interogării SELECT FROM QUAD, vom primi un tabel care conține o coloană QUADRAT, care va conține pătratele numerelor întregi de la 1 la 99.
    Pe lângă iterarea rezultatelor unui eșantion SQL și a unei bucle clasice, limbajul procedurii stocate folosește operatorul IF...THEN..ELSE, care vă permite să organizați ramificarea în funcție de execuția oricăror condiții.Sintaxa sa este similară la majoritatea operatorilor de ramificare în limbaje de programare de nivel înalt, cum ar fi Pascal și C.
    Să ne uităm la un exemplu mai complex de procedură stocată care face următoarele.

    1. Calculează prețul mediu în tabelul Table_example (vezi capitolul „Tabelele Chei primare și generatoare”)
    2. Apoi, pentru fiecare intrare din tabel, efectuează următoarea verificare: dacă prețul existent (PREZUL) este mai mare decât prețul mediu, atunci stabilește un preț egal cu prețul mediu, plus un procent fix specificat
    3. Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci stabilește un preț egal cu prețul anterior, plus jumătate din diferența dintre prețul anterior și prețul mediu.
    4. Returnează toate rândurile modificate din tabel.

    Mai întâi, să definim numele HP, precum și parametrii de intrare și de ieșire.Toate acestea sunt scrise în antetul procedurii stocate.

    CREAȚI PROCEDURA Creșteți prețurile (
    Creștere cu 2 procente PRECIZIE DUBLĂ)
    RETURNĂRI (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
    PRECIZIE AS

    Procedura se va numi IncreasePrices, are un parametru de intrare Peiceni21nciease de tip DOUBLE PRECISION, si 3 parametri de iesire - ID, NAME si new_pnce. Rețineți că primii doi parametri de ieșire au aceleași nume ca și câmpurile din tabelul Table_example cu care vom lucra. Acest lucru este permis de regulile limbajului procedurii stocate.
    Acum trebuie să declarăm o variabilă locală care va fi folosită pentru a stoca valoarea medie. Declarația va arăta astfel:

    DECLARE VARIABILE avg_price DUBLĂ PRECIZIE;

    Acum să trecem la corpul procedurii stocate Deschideți corpul HP cuvântul cheie BEGIN.
    Mai întâi trebuie să efectuăm primul pas al algoritmului nostru - să calculăm prețul mediu. Pentru a face acest lucru, vom folosi următorul tip de interogare:

    SELECTAȚI AVG(Preț_l)
    FROM Tabel_Exemplu
    INTO:avg_price,-

    Această interogare folosește funcția de agregare AVG, care returnează media câmpului PRICE_1 dintre rândurile de interogare selectate — în cazul nostru, media PRICE_1 în întregul tabel Table_example. Valoarea returnată de cerere este plasată în variabila avg_price. Rețineți că variabila avg_pnce este precedată de două puncte pentru a o deosebi de câmpurile utilizate în cerere.
    Particularitatea acestei interogări este că returnează întotdeauna exact o singură înregistrare. Astfel de interogări sunt numite interogări singleton și numai astfel de selecții pot fi utilizate în procedurile stocate. Dacă o interogare returnează mai mult de un rând, atunci trebuie formatată ca construct FOR SELECT...DO, care organizează o buclă pentru a procesa fiecare rând returnat.
    Deci, avem prețul mediu. Acum trebuie să parcurgeți întregul tabel, să comparați valoarea prețului din fiecare intrare cu prețul mediu și să luați măsurile adecvate
    De la început, organizăm căutarea pentru fiecare înregistrare din tabelul Table_example

    PENTRU
    SELECT ID, NUME, PRICE_1
    FROM Tabel_Exemplu
    INTO:ID, :NAME, :new_price
    DO
    ÎNCEPE
    /*_aici descriem fiecare intrare*/
    Sfârşit

    Când se execută această construcție, datele vor fi extrase din tabelul Table_example rând cu rând și valorile câmpului din fiecare rând vor fi atribuite variabilelor ID, NAME și new_pnce. Vă amintiți, desigur, că aceste variabile sunt declarate ca parametri de ieșire, dar nu trebuie să vă faceți griji că datele selectate vor fi returnate ca rezultate: faptul că parametrilor de ieșire li se atribuie ceva nu înseamnă că clientul care apelează HP va primi imediat aceste valori! Parametrii sunt transmisi numai atunci când comanda SUSPEND este executată și înainte de aceasta putem folosi parametrii de ieșire ca variabile obișnuite - în exemplul nostru facem exact asta cu parametrul new_price.
    Deci, în corpul buclei BEGIN... END putem procesa valorile fiecărui rând. După cum vă amintiți, trebuie să ne dăm seama cum se compară prețul existent cu media și să luăm măsurile corespunzătoare. Implementăm această procedură de comparare folosind instrucțiunea IF:

    IF (new_price > avg_price) THEN /*dacă prețul existent este mai mare decât prețul mediu*/
    ÎNCEPE
    /*apoi instalează pret nou, egal cu prețul mediu, plus un procent fix */
    new_price = (preț_mediu + preț_mediu*(Procent2Creștere/100));
    UPDATE Tabel_exemplu
    SETARE PREȚ_1 = :preț_nou
    WHERE ID = :ID;
    Sfârşit
    ALTE
    ÎNCEPE
    /* Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci setați un preț egal cu prețul anterior, plus jumătate din diferența dintre prețul anterior și prețul mediu */
    new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
    UPDATE Tabel_exemplu
    SETARE PREȚ_1 = :preț_nou
    WHERE ID = .ID;
    Sfârşit

    După cum puteți vedea, rezultatul este un construct IF destul de mare, care ar fi greu de înțeles dacă nu ar fi comentariile incluse în simbolurile /**/.
    Pentru a modifica prețul în funcție de diferența calculată, vom folosi declarația UPDATE, care ne permite să modificăm înregistrările existente- una sau mai multe. Pentru a indica fără ambiguitate în ce înregistrare trebuie schimbat prețul, folosim câmpul cheie primară în condiția WHERE, comparându-l cu valoarea variabilei care stochează valoarea ID pentru înregistrarea curentă: ID=:ID. Rețineți că variabila ID este precedată de două puncte.
    După executarea constructului IF...THEN...ELSE, variabilele ID, NAME și new_price conțin date pe care trebuie să le returnăm clientului care a apelat procedura. Pentru a face acest lucru, după IF, trebuie să introduceți comanda SUSPEND, care va trimite datele de unde a fost apelat HP. În timpul transferului, procedura va fi suspendată și când este necesar HP intrare nouă, apoi va fi continuat din nou - și acest lucru va continua până când FOR SELECT...DO iterează prin toate înregistrările interogării sale.
    Trebuie remarcat faptul că pe lângă comanda SUSPEND, care suspendă doar procedura stocată, există o comandă EXIT care încheie procedura stocată după trecerea șirului. Cu toate acestea, comanda EXIT este folosită destul de rar, deoarece este necesară în principal pentru a întrerupe bucla atunci când este atinsă o condiție
    Totuși, în cazul în care procedura a fost apelată cu o instrucțiune SELECT și completată cu EXIT, ultimul rând preluat nu va fi returnat. Adică, dacă trebuie să întrerupeți procedura și tot > obțineți acest șir, trebuie să utilizați secvența

    SUSPENDA;
    IEȘIRE;

    Scopul principal al EXIT este de a primi seturi de date singleton, parametri returnați prin apelarea EXECUTE PROCEDURE. În acest caz, valorile parametrilor de ieșire sunt setate, dar setul de date SQL nu este generat din aceștia, iar execuția procedurii se încheie.
    Să notăm textul procedurii noastre stocate în întregime, astfel încât să putem surprinde logica acesteia dintr-o privire:

    CREAȚI PROCEDURA Creșteți prețurile (
    Procent2Crește PRECIZIUNEA DUBLĂ)
    RETURNĂ (ID INTEGER, NAME VARCHAR(80),
    new_price DUBLĂ PRECIZIE) AS
    DECLARE VARIABILE avg_price DUBLĂ PRECIZIE;
    ÎNCEPE
    SELECTAȚI AVG(Preț_l)
    FROM Tabel_Exemplu
    INTO:preț_mediu;
    PENTRU
    SELECT ID, NUME, PRICE_1
    FROM Tabel_Exemplu
    INTO:ID, :NAME, :new_price
    DO
    ÎNCEPE
    /*procesează fiecare înregistrare aici*/
    IF (new_pnce > avg_price) THEN /*dacă prețul existent este mai mare decât prețul mediu*/
    ÎNCEPE
    /*setează un preț nou egal cu prețul mediu plus un procent fix */
    preț_nou = (preț_mediu + preț_mediu*(Procent2lncrease/100));
    UPDATE Tabel_exemplu
    SETARE PREȚ_1 = :preț_nou
    WHERE ID = :ID;
    Sfârşit
    ALTE
    ÎNCEPE
    /* Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci stabilește un preț egal cu prețul anterior plus jumătate din diferența dintre prețul anterior și prețul mediu */
    nou_pret = (nou_pret + ((avg_price - new_price)/2));
    UPDATE Tabel_exemplu
    SETARE PREȚ_1 = :preț_nou
    WHERE ID = :ID;
    Sfârşit
    SUSPENDA;
    Sfârşit
    Sfârşit

    Acest exemplu de procedură stocată ilustrează utilizarea constructelor și declanșatorilor de limbaj de procedură stocată de bază. În continuare, vom analiza modalități de utilizare a procedurilor stocate pentru a rezolva unele probleme comune.

    Proceduri stocate recursive

    Procedurile stocate InterBase pot fi recursive. Aceasta înseamnă că o procedură stocată se poate autoinvoca. Sunt permise până la 1000 de niveluri de imbricare a procedurilor stocate, dar trebuie să ne amintim că resursele gratuite de pe server se pot epuiza înainte de a se atinge imbricarea maximă a HP.
    O utilizare comună a procedurilor stocate este procesarea structurilor arborescente stocate într-o bază de date. Copacii sunt adesea folosiți în compoziția produsului, depozit, personal și alte aplicații comune.
    Să ne uităm la un exemplu de procedură stocată care selectează toate produsele de un anumit tip, pornind de la un anumit nivel de imbricare.
    Să avem următoarea afirmație a problemei: avem un director de mărfuri cu structura ierarhica acest tip:

    Bunuri
    - Aparate
    - Frigidere
    - Cu trei camere
    - Cameră dublă
    - O singură cameră
    - Mașini de spălat
    - Verticală
    - Frontal
    - Clasic
    - Îngust
    - Tehnologia calculatoarelor
    ....

    Această structură a directorului de categorii de produse poate avea ramuri de diferite adâncimi. și, de asemenea, crește în timp. Sarcina noastră este să asigurăm selecția tuturor elementelor finite din director cu „extinderea numelui complet”, începând de la orice nod. De exemplu, dacă selectăm nodul „Mașini de spălat”, atunci trebuie să obținem următoarele categorii:

    Mașini de spălat - Verticale
    Mașini de spălat - Față Classic
    Mașini de spălat - Față îngustă

    Să definim structura tabelului pentru stocarea informațiilor din directorul de produse. Folosim o schemă simplificată pentru a organiza arborele într-un singur tabel:

    CREATE TABLE GoodsTree
    (ID_GOOD INTEGER NU NUL,
    ID_PARENT_GOOD INTEGER,
    GOOD_NAME VARCHAR(80),
    constrângere pkGooci cheie primară (ID_GOOD));

    Creăm un singur tabel GoodsTree, în care există doar 3 câmpuri: ID_GOOD - identificatorul inteligent al categoriei, ID_PARENT_GOOD - identificatorul companiei-mamă pentru această categorie și GOOD_NAME - numele categoriei. Pentru a asigura integritatea datelor din acest tabel, vom impune o constrângere de cheie străină pe acest tabel:

    ALTER TABLE GoodsTree
    ADĂUGAȚI CONSTRAINTĂ FK_goodstree
    CHEIE STRĂINĂ (ID_PARENT_GOOD)
    REFERINȚE GOODSTPEE (ID__GOOD)

    Tabelul se referă la el însuși și această cheie externă ține evidența acesteia. astfel încât tabelul să nu conțină referințe la părinți inexistenți și, de asemenea, să prevină încercările de ștergere a categoriilor de produse care au copii.
    Să introducem următoarele date în tabelul nostru:

    ID_BUN

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

    		ID_PARENT_BUN

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

    		BUN NUME

    BUNURI
    Aparate
    Calculatoare și componente
    Frigidere
    Mașini de spălat
    Cu trei camere
    Cameră dublă
    O singură cameră
    Vertical
    Frontal
    Îngust
    Clasic

    Acum că avem un loc pentru a stoca datele, putem începe să creăm o procedură stocată care va scoate toate categoriile de produse „finale” într-o formă „extinsă” - de exemplu, pentru categoria „Trei camere”, categoria completă. numele ar fi „Frigidere electrocasnice” cu trei camere”.
    Procedurile stocate care procesează structuri arborescente au propria terminologie. Fiecare element al arborelui este numit nod; iar relația dintre nodurile care se referă unul la altul se numește relație părinte-copil. Nodurile care se află la capătul copacului și nu au copii sunt numite „frunze”.
    Pentru această procedură stocată, parametrul de intrare va fi identificatorul categoriei, de la care va trebui să începem drilldown-ul. Procedura stocată va arăta astfel:

    CREATE PROCEDURA GETFULLNAME (ID_GOOD2SHOW INTEGER)
    RETURNĂRI (FULL_GOODS_NAME VARCHAR(1000),
    ID_CHILD_GOOD INTEGER)
    LA FEL DE
    DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
    ÎNCEPE
    /*0organize externe bucla FOR SELECTAȚI pe descendenții imediati ai produsului cu ID_GOOD=ID_GOOD2SHOW */
    FOR SELECT gtl.id_good, gtl.good_name
    DE LA GoodsTree gtl
    WHERE gtl.id_parent_good=:ID_good2show
    INTO:ID_CHILD_GOOD, :full_goods_name
    DO
    ÎNCEPE
    /"Verificați folosind funcția EXISTS, care returnează TRUE dacă interogarea dintre paranteze returnează cel puțin un rând. Dacă nodul găsit cu ID_PARENT_GOOD = ID_CHILD_GOOD nu are copii, atunci este o „frunză” a arborelui și este inclus în rezultate */
    DACĂ (NU EXISTĂ(
    SELECTAȚI * DIN GoodsTree
    WHERE GoodsTree.id_parent_good=:id_child_good))
    APOI
    ÎNCEPE
    /* Treceți „frunza” copacului la rezultate */
    SUSPENDA;
    Sfârşit
    ALTE
    /* Pentru nodurile care au copii*/
    ÎNCEPE
    /*salvează numele nodului părinte într-o variabilă temporară */
    CURR_CHILD_NAME=nume_complet_produs;
    /* rulează această procedură recursiv */
    PENTRU
    SELECT ID_CHILD_GOOD, nume_complet_produs
    DE LA GETFULLNAME (:ID_CHILD_GOOD)
    INTO:ID_CHILD_GOOD, :full_goods_name
    ÎNCEPEȚI
    /*adăugați numele nodului părinte la numele copilului găsit folosind operația de concatenare a șirurilor || */
    full_goods_name=CURR_CHILD_NAME| " " | Numele_complet al_marfurilor,-
    SUSPENDA; /* returnează numele complet al produsului*/
    Sfârşit
    Sfârşit
    Sfârşit
    Sfârşit

    Dacă executăm această procedură cu parametrul de intrare ID_GOOD2SHOW= 1, vom obține următoarele:

    După cum puteți vedea, folosind o procedură stocată recursivă, am parcurs întregul arbore de categorii și am afișat numele complet al categoriilor „frunze” care se află chiar la vârfurile ramurilor.

    Concluzie

    Aceasta încheie analiza noastră asupra principalelor caracteristici ale limbajului procedurii stocate. Evident, este imposibil să stăpânești pe deplin dezvoltarea procedurilor stocate într-un singur capitol, dar aici am încercat să introducem și să explicăm conceptele de bază asociate procedurilor stocate. Design-urile și tehnicile descrise pentru proiectarea HP pot fi aplicate în majoritatea aplicațiilor de baze de date
    Unele dintre problemele importante legate de dezvoltarea procedurilor stocate vor fi acoperite în următorul capitol - „Capacitățile avansate ale limbajului de proceduri stocate InterBase”, care este dedicat gestionării excepțiilor, rezolvării situațiilor de eroare în procedurile stocate și lucrului cu matrice.