Procedură stocată - un obiect de bază de date, care este un set de instrucțiuni SQL care este compilat o dată și stocat pe server. Procedurile stocate sunt foarte asemănătoare cu procedurile obișnuite în limbaje de nivel înalt, pot avea parametri de intrare și ieșire și variabile locale, pot efectua calcule numerice și operații pe date simbolice, ale căror rezultate pot fi atribuite variabilelor și parametrilor. Procedurile stocate pot efectua operațiuni standard de baze de date (atât DDL, cât și DML). În plus, buclele și ramurile sunt posibile în procedurile stocate, adică pot utiliza instrucțiuni pentru a controla procesul de execuție.
Procedurile stocate sunt similare cu funcțiile definite de utilizator (UDF). Principala diferență este că UDF-urile pot fi utilizate ca orice altă expresie într-o interogare SQL, în timp ce procedurile stocate trebuie apelate folosind funcția CALL:
Procedura CALL (...)
EXECUTAȚI procedura (...)
Procedurile stocate pot returna mai multe rezultate, adică rezultatele unei interogări SELECT. Astfel de seturi de rezultate pot fi procesate folosind cursoare, alte proceduri stocate care returnează un indicator de set de rezultate sau prin aplicații. Procedurile stocate pot conține, de asemenea, variabile declarate pentru procesarea datelor și a cursorelor, care vă permit să faceți o buclă pe mai multe rânduri dintr-un tabel. Standardul SQL oferă IF, LOOP, REPEAT, CASE și multe alte expresii pentru a lucra. Procedurile stocate pot accepta variabile, returnează rezultate sau pot modifica și returna variabile, în funcție de locul în care este declarată variabila.
Implementarea procedurii stocate variază de la un SGBD la altul. Majoritatea furnizorilor principali de baze de date îi susțin într-o formă sau alta. În funcție de SGBD, procedurile stocate pot fi implementate în diferite limbaje de programare, cum ar fi SQL, Java, C sau C ++. Procedurile stocate care nu sunt scrise în SQL pot sau nu să execute interogări SQL pe cont propriu.
Pe
Partajarea logicii cu alte aplicații. Procedurile stocate încapsulează funcționalitatea; acest lucru asigură coerența în accesul și gestionarea datelor între diferite aplicații.
Izolarea utilizatorilor de tabelele bazei de date. Acest lucru vă permite să acordați acces la procedurile stocate, dar nu la datele reale ale tabelului.
Oferă un mecanism de apărare. Conform punctului anterior, dacă puteți accesa datele numai prin proceduri stocate, nimeni altcineva nu poate șterge datele dvs. prin comanda SQL DELETE.
Performanță îmbunătățită ca urmare a traficului de rețea redus. Cu procedurile stocate, mai multe interogări pot fi combinate.
Vs
Sarcină crescută pe serverul bazei de date datorită faptului că cea mai mare parte a muncii se face pe partea serverului și mai puțin pe partea clientului.
Sunt multe de învățat. Va trebui să învățați sintaxa expresiilor MySQL pentru a scrie procedurile stocate.
Duplicați logica aplicației dvs. în două locuri: cod server și cod pentru proceduri stocate, complicând astfel procesul de manipulare a datelor.
Migrarea de la un SGBD la altul (DB2, SQL Server etc.) poate duce la probleme.
Scopul și avantajele procedurilor stocate
Procedurile stocate îmbunătățesc performanța, sporesc programabilitatea și acceptă caracteristicile de securitate a datelor.
În loc să stocheze o interogare frecvent utilizată, clienții se pot referi la procedura stocată corespunzătoare. Când se apelează o procedură stocată, conținutul său este procesat imediat de server.
În plus față de execuția efectivă a interogării, procedurile stocate permit, de asemenea, efectuarea de calcule și manipularea datelor - schimbarea, ștergerea, executarea instrucțiunilor DDL (nu în toate DBMS-urile!) Și apelarea altor proceduri stocate, efectuarea unei logici tranzacționale complexe. Un singur operator vă permite să apelați un script complex conținut într-o procedură stocată, care evită trimiterea a sute de comenzi prin rețea și, în special, necesitatea de a transfera cantități mari de date de la client la server.
În majoritatea SGBD-urilor, prima dată când executați o procedură stocată, aceasta se compilează (analizează și generează un plan de acces la date). În viitor, prelucrarea sa se efectuează mai repede. Oracle DBMS interpretează codul procedural stocat stocat în dicționarul de date. Începând de la versiunea Oracle 10g, este acceptată așa-numita compilare nativă a codului procedural stocat în C și apoi în codul mașinii al mașinii țintă, după care, atunci când se apelează procedura stocată, codul său obiect compilat este executat direct.
Posibilități de programare
Odată creată, procedura stocată poate fi apelată în orice moment, ceea ce oferă modularitate și încurajează refolosirea codului. Acesta din urmă face mai ușoară întreținerea bazei de date deoarece devine izolată de modificarea regulilor de afaceri. Puteți modifica oricând procedura stocată în conformitate cu noile reguli. După aceea, toate aplicațiile care îl utilizează vor respecta automat noile reguli comerciale fără modificări directe.
Siguranță
Utilizarea procedurilor stocate vă permite să restricționați sau să excludeți complet accesul direct al utilizatorilor la tabelele bazei de date, lăsând utilizatorilor doar permisiunile de a executa proceduri stocate care oferă acces indirect și foarte reglementat la date. În plus, unele DBMS acceptă criptarea textului (împachetarea) procedurii stocate.
Aceste caracteristici de securitate vă permit să izolați structura bazei de date de utilizator, ceea ce asigură integritatea și fiabilitatea bazei de date.
Probabilitatea acțiunilor precum „injecția SQL” este redusă deoarece procedurile stocate bine scrise validează suplimentar parametrii de intrare înainte de a trimite o interogare la SGBD.
Implementarea procedurii stocate
Procedurile stocate sunt de obicei create folosind limbajul SQL sau o implementare specifică a acestuia în SGBD selectat. De exemplu, în aceste scopuri în Microsoft SQL Server DBMS există limbajul Transact-SQL, în Oracle - PL / SQL, în InterBase și Firebird - PSQL, în PostgreSQL - PL / pgSQL, PL / Tcl, PL / Perl, PL / Python, în IBM DB2 - SQL / PL (engleză), Informix - SPL. MySQL urmează standardul SQL: 2003 suficient de atent, limbajul său este similar cu SQL / PL.
În unele SGBD, este posibil să se utilizeze proceduri stocate scrise în orice limbaj de programare care poate crea fișiere executabile independente, de exemplu, în C ++ sau Delphi. În terminologia Microsoft SQL Server, astfel de proceduri se numesc proceduri stocate extinse și sunt pur și simplu funcții conținute într-o DLL Win32. De exemplu, Interbase și Firebird au un nume diferit pentru funcțiile apelate din DLL / SO - UDF (Funcție definită de utilizator). În MS SQL 2005, a devenit posibilă scrierea procedurilor stocate în orice limbaj .NET și este planificată abandonarea procedurilor stocate extinse în viitor. Oracle DBMS, la rândul său, permite scrierea procedurilor stocate în limbajul Java. În IBM DB2, scrierea procedurilor și funcțiilor stocate în limbaje de programare comune a fost modalitatea tradițională, acceptată de la început, iar extensia SQL procedurală a fost adăugată acestui DBMS doar în versiuni destul de târzii, după includerea sa în standardul ANSI. De asemenea, procedurile Java și C sunt acceptate de Informix.
În Oracle DBMS, procedurile stocate pot fi combinate în așa-numitele pachete. Un pachet constă din două părți - o specificație a pachetului, care specifică definiția unei proceduri stocate și un corp de pachet, unde se află implementarea acestuia. Astfel, Oracle vă permite să decuplați interfața de programare de implementare.
În IBM DB2, procedurile stocate pot fi combinate în module.
Sintaxă
CREAȚI PROCEDURA `p2` ()
DEFINITOR SQL SECURITY
COMENTARIE „O procedură”
SELECȚIONEAZĂ „Hello World!”;
Prima bucată de cod creează o procedură stocată. Următorul conține parametri opționali. Apoi vine numele și, în cele din urmă, corpul procedurii în sine.
4 caracteristici ale unei proceduri stocate:
Limbă: în scopul portabilității, SQL este specificat în mod implicit.
Deterministic: dacă procedura returnează același rezultat tot timpul și ia aceiași parametri de intrare. Aceasta este pentru procesul de replicare și înregistrare. Valoarea implicită NU este DETERMINISTICĂ.
Securitate SQL: În timpul apelului, sunt verificate drepturile utilizatorului. INVOKER este utilizatorul care apelează procedura stocată. DEFINER este „creatorul” procedurii. Valoarea implicită este DEFINER.
Comentariu: în scopuri de documentare, valoarea implicită este „”
Apel de procedură stocată
CALL nume_procedură_stocată (param1, param2, ....)
CALL procedure1 (10, "parametru șir", @parameter_var);
Modificarea unei proceduri stocate
MySQL oferă o instrucțiune ALTER PROCEDURE pentru modificarea procedurilor, dar este potrivită pentru schimbarea doar a câtorva caracteristici. Dacă trebuie să modificați parametrii sau corpul procedurii, trebuie să îl ștergeți și să-l creați din nou.
Se ștergestocatproceduri
PROCEDURA DE DROP DACĂ EXISTĂ p2;
Aceasta este o comandă simplă. Expresia IF EXISTS detectează o eroare dacă o astfel de procedură nu există.
Parametrii
CREATE PROCEDURE proc1 (): listă de parametri goi
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): un parametru de intrare. Cuvântul IN este opțional, deoarece parametrii impliciți sunt IN (de intrare).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): un parametru de returnare.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametru, atât de intrare cât și de revenire.
Sintaxa pentru declararea unei variabile arată astfel:
DECLARAți varname DATE-TIP DEFAULT valoare implicită;
scopul muncii- aflați cum să creați și să utilizați proceduri stocate pe serverul bazei de date.
1. Elaborarea tuturor exemplelor, analizarea rezultatelor execuției lor în utilitarul SQL Server Management Studio. Verificarea prezenței procedurilor create în baza de date curentă.
2. Finalizarea tuturor exemplelor și sarcinilor pe parcursul lucrărilor de laborator.
3. Implementarea sarcinilor individuale pe opțiuni.
Pentru a stăpâni programarea procedurilor stocate, folosim un exemplu de bază de date numită DB_Books, care a fost creat în lucrarea de laborator nr. 1. Când efectuați exemple și sarcini, acordați atenție corespondenței numelor bazei de date, a tabelelor și a altor obiecte ale proiectului.
Proceduri stocate sunt un set de comenzi, constând din una sau mai multe instrucțiuni sau funcții SQL, și stocate într-o bază de date într-o formă compilată.
Procedurile stocate în sistem sunt concepute pentru a efectua diverse acțiuni administrative. Aproape toate acțiunile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că procedurile stocate în sistem sunt interfața pentru lucrul cu tabelele de sistem. Procedurile stocate în sistem sunt prefixate cu sp_, sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
Procedurile personalizate stocate implementează anumite acțiuni. Procedurile stocate sunt un obiect complet al bazei de date. Ca urmare, fiecare procedură stocată este localizată într-o anumită bază de date, unde este executată.
Procedurile stocate temporar există doar pentru o perioadă scurtă de timp, după care sunt distruse automat de server. Acestea sunt împărțite în local și global. Procedurile stocate temporar local pot fi apelate numai de la conexiunea în care sunt create. Când creați o astfel de procedură, trebuie să i se dea un nume începând cu un caracter #. Ca toate obiectele temporare, procedurile stocate de acest tip sunt șterse automat atunci când utilizatorul deconectează, repornește sau oprește serverul. Procedurile globale stocate temporar sunt disponibile pentru orice conexiune de server care are aceeași procedură. Pentru a o defini, trebuie doar să îi dați un nume începând cu simbolurile ##. Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit sau când conexiunea în contextul căreia au fost create este închisă.
Crearea unei proceduri stocate implică rezolvarea următoarelor probleme: planificarea drepturilor de acces. Când creați o procedură stocată, rețineți că va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care a creat-o; definirea parametrilor unei proceduri stocate, procedurile stocate pot avea parametri de intrare și ieșire; elaborarea codului procedurii stocate. Codul de procedură poate conține orice succesiune de comenzi SQL, inclusiv apeluri către alte proceduri stocate.
Sintaxa pentru ca operatorul să creeze o nouă sau să modifice o procedură stocată existentă în notația MS SQL Server:
(CREATE | ALTER) PROC [EDURE] procedure_name [; number] [(@ parameter_name datatype) [VARYING] [= DEFAULT] [OUTPUT]] [, ... n] [WITH (RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)] [PENTRU REPLICAȚII] AS sql_operator [... n]Să luăm în considerare 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ă specificarea numelui proprietarului care va deține procedura creată, precum și numele bazei de date în care ar trebui să fie localizată. Prin urmare, pentru a plasa procedura stocată pe care o creați într-o anumită bază de date, trebuie să rulați comanda CREATE PROCEDURE în contextul acelei baze de date. Când faceți referire la obiecte ale aceleiași baze de date din corpul unei proceduri stocate, pot fi folosite nume prescurtate, adică fără a specifica numele bazei de date. Când trebuie să faceți referire la obiecte situate în alte baze de date, este necesară specificarea numelui bazei de date.
Numele parametrilor trebuie să înceapă cu caracterul @ pentru a transmite datele de intrare și ieșire procedurii stocate generate. Mai mulți parametri pot fi specificați într-o singură procedură stocată, separați prin virgule. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume sunt identice cu numele parametrilor acestei proceduri. Orice tip de date SQL, inclusiv definit de utilizator, este potrivit pentru definirea tipului de date al parametrilor unei proceduri stocate. Cu toate acestea, tipul de date CURSOR poate fi utilizat doar ca parametru de ieșire al unei proceduri stocate, adică specificând cuvântul cheie OUTPUT.
Prezența cuvântului cheie OUTPUT indică faptul că parametrul corespunzător este pentru returnarea datelor dintr-o procedură stocată. Cu toate acestea, acest lucru nu înseamnă că parametrul nu este potrivit pentru trecerea valorilor la o procedură stocată. Specificarea cuvântului cheie OUTPUT instruiește serverul, la ieșirea din procedura stocată, să atribuie valoarea curentă a parametrului variabilei locale care a fost specificată ca valoare a parametrului atunci când procedura a fost apelată. Rețineți că, atunci când este specificat cuvântul cheie OUTPUT, valoarea parametrului corespunzător la apelarea unei proceduri poate fi setată numai utilizând o variabilă locală. Nu aveți voie să utilizați expresii sau constante valabile pentru parametrii normali. Cuvântul cheie VARYING este utilizat împreună cu parametrul OUTPUT de tip CURSOR. Specifică faptul că parametrul de ieșire va fi setul de rezultate.
Cuvântul cheie DEFAULT este o valoare care va accepta parametrul implicit corespunzător. Astfel, atunci când apelați o procedură, nu este necesar să specificați în mod explicit valoarea parametrului corespunzător.
Deoarece serverul memorează în cache planul de execuție a interogării și codul compilat, următorul apel la procedură va utiliza valorile gata făcute. Cu toate acestea, în unele cazuri, trebuie totuși să recompilați codul de procedură. Specificarea cuvântului cheie RECOMPILE instruiește sistemul să genereze un plan de execuție pentru procedura stocată de fiecare dată când este apelat.
Parametrul FOR REPLICATION este necesar atunci când replicăm date și includem procedura stocată generată ca articol într-o publicație. Cuvântul cheie ENCRYPTION instruiește serverul să cripteze codul procedurii stocate, care poate oferi protecție împotriva algoritmilor de creație care implementează procedura stocată. Cuvântul cheie AS este plasat la începutul corpului efectiv al procedurii stocate. Corpul unei proceduri poate utiliza aproape toate comenzile SQL, poate declara tranzacții, poate achiziționa blocaje și poate apela alte proceduri stocate. Puteți ieși din procedura stocată cu comanda RETURN.
PROCEDURA DE DROP (nume_procedură) [, ... n]
Pentru a executa procedura stocată, utilizați comanda: [[EXEC [UTE] nume_procedură [; număr] [[@ nume_parametru =] (valoare | @ nume_variabilă) [IEȘIRE] | [DEFAULT]] [, ... n]
Dacă apelul către procedura stocată nu este singura comandă din pachet, atunci comanda EXECUTE trebuie să fie prezentă. Mai mult, această comandă este necesară pentru a apela o procedură din corpul altei proceduri sau declanșator.
Utilizarea cuvântului cheie OUTPUT într-un apel de procedură este permisă numai pentru parametrii care au fost declarați când procedura a fost creată cu cuvântul cheie OUTPUT.
Când cuvântul cheie DEFAULT este specificat pentru un parametru atunci când se apelează o procedură, va fi utilizată valoarea implicită. Bineînțeles, cuvântul DEFAULT specificat este permis numai pentru acei parametri pentru care este definită o valoare implicită.
Din sintaxa comenzii EXECUTE, puteți vedea că numele parametrilor pot fi omise atunci când se apelează o procedură. Cu toate acestea, în acest caz, utilizatorul trebuie să furnizeze valori pentru parametri în aceeași ordine în care au fost enumerați la crearea procedurii. Nu puteți atribui o valoare implicită unui parametru, omitându-l pur și simplu în timpul enumerării. Dacă trebuie să omiteți parametrii pentru care este definită o valoare implicită, este suficient să specificați în mod explicit numele parametrilor atunci când apelați procedura stocată. Mai mult, în acest fel puteți lista parametrii și valorile acestora în orice ordine.
Rețineți că atunci când se apelează o procedură, sunt specificate fie numele parametrilor cu valori, fie doar valorile fără un nume de parametru. Combinarea acestora nu este permisă.
Vă permite să ieșiți din procedură în orice moment în funcție de condiția specificată și vă permite, de asemenea, să transmiteți rezultatul procedurii ca un număr, prin care puteți judeca calitatea și corectitudinea procedurii. Un exemplu de creare a unei proceduri fără parametri:
CREAȚI PROCEDURĂ Count_Books AS SELECT COUNT (Code_book) FROM Books GOExercitiul 1.
EXEC Count_BooksVerificați rezultatul.
Un exemplu de creare a unei proceduri cu un parametru de intrare:
CREAȚI PROCEDURA Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages> = @Count_pages GOTema 2... Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Rulați-l cu comanda
EXEC Count_Books_Pages 100Verificați rezultatul.
Un exemplu de creare a unei proceduri cu parametri de intrare:
CREAȚI PROCEDURA Count_Books_Title @Count_pages AS INT, @Title AS CHAR (10) AS SELECT COUNT (Code_book) FROM Books WHERE Pages> = @Count_pages AND Title_book LIKE @Title GOSarcina 3. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Rulați-l cu comanda
EXEC Count_Books_Title 100, „P%”Verificați rezultatul.
Un exemplu de creare a unei proceduri cu parametri de intrare și un parametru de ieșire:
CREAȚI PROCEDURA Count_Books_Itogo @Count_pages INT, @Title CHAR (10), @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages> = @Count_pages AND Title_book LIKE @Title GOSarcina 4. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Rulați cu setul de comenzi:
SQL> Declare @q Ca int EXEC Count_Books_Itogo 100, "P%", ieșire @q selectați @q
Verificați rezultatul.
Un exemplu de creare a unei proceduri cu parametrii de intrare și RETURN:
CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM autori WHERE Code_author = @param) = "Pushkin A.S." RETURN 1 ALTE RETUR 2Sarcina 5. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Porniți-l cu comenzile:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_statusUn exemplu de creare a unei proceduri fără parametri pentru a crește valoarea unui câmp cheie din tabelul Achiziții de 2 ori:
CREATE PROC update_proc AS UPDATE Achiziții SET Code_purchase = Code_purchase * 2Sarcina 6. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Rulați-l cu comanda
EXEC update_procUn exemplu de procedură cu un parametru de intrare pentru a obține toate informațiile despre un anumit autor:
CREAȚI PROC select_author @k CHAR (30) AS SELECT * FROM Authors WHERE name_author = @kSarcina 7.
EXEC select_autor "Pushkin A.S." sau select_author @ k = "Pușkin A.S." sau EXEC select_author @ k = "Pușkin A.S."Un exemplu de creare a unei proceduri cu un parametru de intrare și o valoare implicită pentru a crește valoarea unui câmp cheie din tabelul Achiziții cu un număr specificat de ori (în mod implicit, de 2 ori):
CREARE PROC update_proc @p INT = 2 CA ACTUALIZARE Achiziții SET Code_purchase = Code_purchase * @pProcedura nu returnează date.
Sarcina 8. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Porniți-l cu comenzile:
EXEC update_proc 4 sau EXEC update_proc @p = 4 sau EXEC update_proc - va fi utilizată valoarea implicită.Un exemplu de creare a unei proceduri cu parametri de intrare și ieșire. Creați o procedură pentru a determina numărul de comenzi efectuate în perioada specificată:
CREAȚI PROC count_purchases @ d1 SMALLDATETIME, @ d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @ c = COUNT (Code_purchase) FROM Achiziții WHERE Date_order BETWEEN @ d1 AND @ d2 SET @c = ISNULL (@c, 0)Sarcina 9. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books utilizând utilitarul SQL Server Management Studio. Porniți-l cu comenzile:
DECLARE @ c2 INT EXEC count_purchases '01 - jun- 2006 ', '01 - iul- 2006', @ c2 OUTPUT SELECT @ c2Opțiuni pentru sarcini pentru lucrările de laborator nr. 4
Dispoziții generale. Creați o pagină nouă pentru cod în utilitarul SQL Server Management Studio (butonul Creare interogare). Activați programatic DB_Books create folosind instrucțiunea Use. Creați proceduri stocate utilizând instrucțiunile Create procedure și definiți-vă singur numele procedurilor. Fiecare procedură va executa o instrucțiune SQL care a fost efectuată în al doilea laborator. Mai mult, codul interogărilor SQL trebuie modificat astfel încât să fie posibilă transferarea valorilor câmpurilor prin care se efectuează căutarea în ele.
De exemplu, misiunea și interogarea inițiale din laboratorul nr. 2:
/ * Selectați din directorul furnizorilor (tabelul Livrări) numele companiilor, telefoanelor și TIN (câmpurile Name_company, Phone și INN), care au numele companiei (câmp Name_company) „OJSC MIR”.
SELECT Nume_companie, Telefon, INN DIN Livrări UNDE Nume_compania = "OJSC MIR"* / –În această lucrare, va fi creată o procedură:
CREATE PROC select_name_company @comp CHAR (30) AS SELECT Name_company, Phone, INN FROM Livrări WHERE Name_company = @comp–Pentru a începe procedura, utilizați comanda:
EXEC select_name_company "OJSC MIR"Lista sarcinilor
Creați un program nou în utilitarul SQL Server Management Studio. Activează programatic o bază de date individuală, creată în lucrările de laborator nr. 1, folosind operatorul Use. Creați proceduri stocate utilizând instrucțiunile Create procedure și definiți-vă singur numele procedurilor. Fiecare procedură va executa o interogare SQL, care este prezentată ca activități separate prin opțiuni.
Opțiunea 1
1. Afișați o listă a angajaților care au cel puțin un copil.
2. Afișați o listă a copiilor cărora li s-au oferit cadouri în perioada specificată.
3. Afișați o listă a părinților care au copii minori.
4. Afișați informații despre cadouri cu o valoare mai mare decât numărul specificat, sortate după dată.
Opțiunea 2
1. Afișați o listă de dispozitive cu tipul specificat.
2. Afișați numărul de dispozitive reparate și costul total al reparațiilor de la masterul specificat.
3. Afișați lista deținătorilor de instrumente și numărul de apeluri ale acestora, sortate după numărul de apeluri în ordine descrescătoare.
4. Afișați informații despre masterat cu un rang mai mare decât numărul specificat sau cu data angajării mai mică decât data specificată.
Opțiunea 3
2. Afișați o listă a codurilor de vânzare pentru care s-au vândut flori pentru o sumă care depășește numărul specificat.
3. Afișați data vânzării, suma, vânzătorul și floarea în conformitate cu codul de vânzare specificat.
4. Enumerați florile și o varietate pentru flori cu o înălțime mai mare decât numărul specificat sau înflorirea.
Opțiunea 4
1. Afișați o listă de medicamente cu indicația indicată pentru utilizare.
2. Afișați o listă a datelor de livrare pentru care a fost vândut mai mult decât numărul specificat al medicamentului cu același nume.
3. Afișați data livrării, suma, numele complet al managerului de la furnizor și numele medicamentului prin cod de primire mai mare decât numărul specificat.
Opțiunea 5
2. Afișați o listă de echipamente scoase din funcțiune din motivul specificat.
3. Afișați data primirii, numele echipamentului, numele persoanei responsabile și data anulării echipamentului anulat în perioada specificată.
4. Afișați o listă de echipamente cu tipul specificat sau cu data primirii mai mare decât o anumită valoare
Opțiunea 6
1. Afișați o listă de feluri de mâncare cu o greutate mai mare decât numărul specificat.
2. Afișați o listă de produse, al căror nume conține fragmentul de cuvânt specificat.
3. Afișați volumul produsului, numele vasului, numele produsului cu codul vasului de la valoarea inițială specificată la valoarea finală specificată.
4. Afișați ordinea de preparare a vasului și numele vasului cu cantitatea de carbohidrați mai mare decât o anumită valoare sau numărul de calorii mai mare decât valoarea specificată.
Opțiunea 7
1. Afișați o listă a angajaților cu funcția specificată.
3. Afișați data înregistrării, tipul documentului, numele complet al registratorului și numele organizației pentru documentele înregistrate în perioada specificată.
4. Afișați o listă de documente înregistrate cu un anumit tip de document sau cu o dată de înregistrare mai mare decât valoarea specificată.
Opțiunea 8
1. Afișați o listă a angajaților cu motivul specificat pentru concediere.
3. Afișați data înregistrării, motivul concedierii, numele complet al angajatului pentru documentele înregistrate în perioada specificată.
Opțiunea 9
1. Afișați o listă a angajaților care au luat concediu de tipul specificat.
2. Afișați o listă de documente cu data de înregistrare în perioada specificată.
3. Afișați data înregistrării, tipul vacanței, numele complet al angajatului pentru documentele înregistrate în perioada specificată.
4. Afișați o listă de documente înregistrate cu un cod de document în intervalul specificat.
Opțiunea 10
1. Afișați o listă a angajaților cu funcția specificată.
2. Afișați o listă de documente care conțin fragmentul de cuvânt specificat.
3. Afișați data înregistrării, tipul documentului, numele complet al expeditorului și numele organizației pentru documentele înregistrate în perioada specificată.
4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu un cod de document mai mic decât o anumită valoare.
Opțiunea 11
1. Afișați o listă a angajaților desemnați în funcția specificată.
2. Afișați o listă de documente cu data de înregistrare în perioada specificată.
3. Afișați data înregistrării, funcția, numele complet al angajatului pentru documentele înregistrate în perioada specificată.
4. Afișați o listă de documente înregistrate cu un cod de document în intervalul specificat.
Opțiunea 12
3. Afișați o listă a persoanelor care au închiriat echipamente și numărul apelurilor lor, sortate după numărul de apeluri în ordine descrescătoare.
Opțiunea 13
1. Afișați o listă de echipamente cu tipul specificat. 2. Afișați o listă de echipamente care au fost anulate de un anumit angajat.
3. Afișați cantitatea de echipamente scoase din funcțiune, grupate după tipul de echipament.
4. Afișați informații despre angajații cu o dată de angajare mai mare decât o anumită dată.
Opțiunea 14
1. Enumerați florile cu tipul de frunză specificat.
2. Afișați o listă de coduri de primire pentru care florile sunt vândute pentru sume mai mari decât o anumită valoare.
3. Afișați data primirii, suma, numele furnizorului și culorile pentru un anumit cod de furnizor.
4. Enumerați florile și o varietate pentru flori cu o înălțime mai mare decât un anumit număr sau înflorire.
Opțiunea 15
1. Afișați o listă a clienților care s-au cazat în camere în perioada specificată.
2. Afișați suma totală a plăților pentru camere pentru fiecare client.
3. Afișați data de check-in, tipul camerei, numele complet al clienților înregistrați în perioada specificată.
4. Afișați o listă a clienților înregistrați în camere de un anumit tip.
Opțiunea 16
1. Afișați o listă de echipamente cu tipul specificat.
2. Afișați o listă de echipamente închiriate de un anumit client.
3. Afișați o listă a persoanelor care au închiriat echipamente și numărul apelurilor lor, sortate după numărul de apeluri în ordine descrescătoare.
4. Afișați informații despre clienți sortați după adrese.
Opțiunea 17
1. Afișați o listă de valori cu o valoare de achiziție mai mare decât o anumită valoare sau o perioadă de garanție mai mare decât un număr specificat.
2. Afișați o listă a locațiilor activelor materiale, în numele cărora se găsește cuvântul specificat.
3. Afișați suma valorii obiectelor de valoare cu un cod în intervalul specificat.
4. Afișați o listă a persoanelor responsabile financiar cu data angajării în intervalul specificat.
Opțiunea 18
1. Afișați o listă de reparații efectuate de un anumit master.
2. Enumerați etapele de lucru incluse în lucrare, în titlul cărora apare cuvântul specificat.
3. Afișați suma costului etapelor lucrărilor de reparații pentru lucrări cu un cod în intervalul specificat.
4. Afișați o listă de masterat cu data angajării în intervalul specificat.
Opțiunea 19
1. Afișați o listă de medicamente cu o indicație specifică.
2. Afișați o listă cu numerele de verificare pentru care s-au vândut mai mult de un anumit număr de medicamente.
3. Afișați data vânzării, suma, numele complet al casierului și medicamentul pe chitanță cu numărul specificat.
4. Afișați o listă de medicamente și unități de măsură pentru medicamente cu o cantitate de pachet mai mare decât un număr specificat sau un cod de medicament mai mic decât o anumită valoare.
Opțiunea 20
1. Afișați o listă a angajaților cu funcția specificată.
2. Afișați o listă de documente care conțin fragmentul de cuvânt specificat.
3. Afișați data înregistrării, tipul documentului, numele complet al contractantului și faptul executării documentelor înregistrate în perioada specificată.
4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu un cod de document într-un anumit interval.
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 afaceri Procedurile stoiate vă permit să implementați o parte semnificativă a logicii aplicației la nivelul bazei de date și astfel să îmbunătățiți performanța întregului aplicație, centralizează procesarea datelor și reduce cantitatea de cod necesară pentru a finaliza sarcinile. Aproape orice aplicație de bază de date suficient de complexă necesită utilizarea procedurilor stocate.
În plus față de aceste binecunoscute avantaje ale utilizării procedurilor stocate, care sunt comune pentru majoritatea SGBD-urilor relaționale, procedurile stocate InterBase pot acționa ca seturi de date de aproape, ceea ce le permite să utilizeze rezultatele pe care le returnează în interogări SQL obișnuite.
Adesea dezvoltatorii începători se gândesc la procedurile stocate pur și simplu ca la un set de interogări SQL specifice care fac ceva în baza 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 la nivel înalt limba.
Deci, ce sunt procedurile stocate în InterBase?
O procedură stocată (SP) este o parte a metadatelor bazei de date, care este un subrutin compilat în reprezentarea InterBase internă, scris într-un limbaj special, al cărui compilator este încorporat în nucleul serverului InteiBase
Procedura stocată poate fi apelată din aplicații client, declanșatoare și alte proceduri stocate. O procedură stocată este executată în interiorul procesului serverului și poate manipula datele din baza de date, precum și să returneze rezultatele execuției sale clientului care a apelat-o (adică, declanșator, CP, aplicație)
Baza capabilităților puternice inerente SP-ului este un limbaj de programare procedurală care include atât instrucțiuni modificate ale SQL obișnuit, cum ar fi INSERT, UPDATE și SELECT, precum și instrumente de ramificare și buclare (IF, WHILE) și instrumente de gestionare a erorilor și excepții Limbajul procedurilor stocate vă permite să implementați algoritmi complexi pentru lucrul cu date și, datorită concentrării pe lucrul cu date relaționale, HP sunt mult mai compacte decât procedurile similare în 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 limbajului utilizat în declanșatoare și limbajul XPS sunt discutate în detaliu în capitolul „Declanșatoare” (partea 1).
Un exemplu de procedură simplă stocată
Acum este momentul să creați prima procedură stocată și să o utilizați pentru a studia procesul de creare a procedurilor stocate. Dar mai întâi, ar trebui spus câteva cuvinte despre cum să lucrați cu procedurile stocate. Faptul este că instrumentele standard extrem de slabe pentru dezvoltarea și depanarea procedurilor stocate își datorează gloria instrumentului obscur și incomod al SP. În documentația InterBase, se recomandă crearea de proceduri folosind fișiere script SQL care conțin textul SP, care sunt introduse la intrarea în interpretul isql și astfel se creează și se modifică SP. Dacă în acest script SQL se află la etapa compilarea textului procedurii în BLR Dacă apare o eroare, isql va afișa un mesaj pe care linie a fișierului script SQL a apărut această eroare. Corectați greșeala și repetați din nou. Depanarea în sensul modern al cuvântului, adică urmărirea execuției, cu capacitatea de a vedea valori intermediare ale variabilelor, nu este deloc în discuție. 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 procedurii stocate este descrisă după cum urmează:
CREAȚI numele PROCEDURII
[(paramet datatype [, param datatype ...])]]
)]
LA FEL DE
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARA VARIABLE var tip de date;
ÎNCEPE
< compound_statement>
[< compound_statement> ...]
SFÂRȘIT
< compound_statement> =
(
Arată destul de voluminoasă și poate fi chiar greoaie, dar de fapt totul este foarte simplu. Pentru a stăpâni treptat sintaxa, să ne uităm la exemple treptat mai complicate.
Deci, iată un exemplu de procedură stocată foarte simplă care ia două numere ca intrare, le adaugă și returnează rezultatul:
CREAȚI PROCEDURA SP_Add (first_arg DUBLĂ PRECIZIE,
second_arg PRECIZIE DUBLĂ)
RETURĂRI (rezultat DOUĂ 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 ai SP - first_arg și second_arg - sunt listate între paranteze, separate prin virgule, cu o indicație a tipurilor lor.
Lista parametrilor de intrare este o parte opțională a instrucțiunii CREATE PROCEDURE - există cazuri în care procedura primește toate datele pentru funcționarea sa prin interogări către tabelele din corpul procedurii.
Orice tip de date scalare InteiBase sunt utilizate în procedurile stocate Nu se pot utiliza tablouri și tipuri definite de utilizator - domenii
Urmează cuvântul cheie RETURNS, după care parametrii returnați sunt enumerați între paranteze, indicând tipurile lor - în acest caz, doar unul - Rezultat.
Dacă procedura nu ar trebui să returneze parametrii, atunci cuvântul RETURNE și lista parametrilor de retur lipsesc.
RETURNSQ este urmat de cuvântul cheie AS. Înainte de a merge cuvântul cheie AS antet,și după ea - techo proceduri.
Corpul unei proceduri stocate este o listă de descrieri ale variabilelor sale interne (locale) (dacă există, le vom discuta mai detaliat mai jos), separate printr-un punct și virgulă (;) și un bloc de afirmații încadrat în parantezele operatorului ÎNCEPE ÎNCHEIAT. În acest caz, corpul SP este foarte simplu - cerem să adăugăm două argumente de intrare și să atribuim rezultatul la ieșire și apoi apelăm comanda SUSPEND. Puțin mai târziu, vom explica esența funcționării acestei comenzi, dar deocamdată menționăm doar că este necesar să treci parametrii returnați de unde a fost apelată procedura stocată.
Separatori în proceduri stocate
Rețineți că o declarație din cadrul unei proceduri se încheie cu un punct și virgulă (;). După cum știți, punctul și virgula este un separator de comenzi standard în SQL - este un semnal către interpretul SQL că textul comenzii a fost introdus integral și este necesar să începeți procesarea acestuia. S-ar putea ca atunci când detectează un punct și virgulă în mijlocul SP, interpretul SQL consideră că comanda a fost introdusă în totalitate și încearcă să execute o parte din procedura stocată? Această presupunere are sens. Într-adevăr, dacă creați un fișier în care să scrieți exemplul de mai sus, adăugați o comandă pentru a vă conecta din baza de date și încercați să executați acest script SQL folosind interpretul isql, veți returna o eroare legată de neașteptat, în opinia interpretului, încetarea comenzii pentru a crea o procedură stocată. Dacă creați proceduri stocate folosind fișiere script SQL, fără a utiliza instrumente specializate pentru dezvoltatori InterBase, atunci înainte de fiecare comandă de creare CP (același lucru se aplică declanșatoarelor), schimbați separatorul de comenzi script cu un alt caracter decât un punct și virgulă și după textul HP pentru restaurare înapoi. Comanda isql care modifică separatorul de instrucțiuni SQL arată astfel:
SET TERMEN
Pentru un caz tipic de creare a unei proceduri stocate, arată astfel:
SET TERMEN ^;
CREAȚI PROCEDURA some_procedure
... . .
SFÂRȘIT
^
SET TERMEN; ^
Apel de procedură stocată
Revenim însă la procedura noastră stocată. Acum, când a fost creat, trebuie să fie numit cumva, parametrii trebuie să-i fie transferați și rezultatele să fie returnate. Este foarte ușor să faceți acest lucru - trebuie doar să scrieți o interogare SQL cu următoarea formă:
SELECTAȚI *
FROM Sp_add (181.35, 23.09)
Această interogare ne va întoarce un rând care conține un singur câmp Rezultat, care va conține suma numerelor 181.35 și 23.09, adică 204.44.
Astfel, procedura noastră poate fi utilizată în interogări SQL obișnuite executate atât în programe client, cât și în alte CP sau declanșatoare. Această utilizare a procedurii noastre a fost posibilă prin utilizarea comenzii SUSPEND la sfârșitul procedurii stocate.
Faptul 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 SP este că procedurile de preluare returnează de obicei multe seturi de parametri de ieșire, grupați rând cu rând, care sunt sub forma unui set de date, iar procedurile executate pot să nu returneze deloc parametri, sau returnează doar un singur set de parametri de ieșire. listat în Returnează, unde există o singură linie de parametri. Procedurile selectate sunt apelate în interogările SELECT, iar procedurile executabile sunt apelate utilizând comanda EXECUTE PROCEDURE.
Ambele tipuri de proceduri stocate au aceeași sintaxă de creare și nu sunt formal diferite, prin urmare, 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 vor comporta HP-urile cu diferite tipuri de apeluri. Cu alte cuvinte, diferența constă în proiectarea procedurii pentru un anumit tip de apel. Adică, o procedură selectivă este creată în mod specific pentru a fi apelată dintr-o interogare SELECT, iar o procedură executabilă este creată în mod specific pentru a fi apelată folosind EXECUTE PROCEDURE. Să aruncăm o privire la care sunt diferențele în designul acestor două tipuri de HP.
Pentru a înțelege cum funcționează procedura de eșantionare, trebuie să mergeți puțin mai adânc în teorie. Să ne imaginăm o interogare SQL simplă precum SELECT ID, NAME FROM Table_example. Ca rezultat al execuției sale, obținem la ieșire un tabel format din două coloane (ID și NAME) ș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 este numit și set de date SQL Să ne gândim la modul în care este format setul de date în timpul executării acestei interogări. Serverul, după ce a primit o interogare, determină la care tabele aparține, apoi află la ce subset de înregistrări din aceste tabele trebuie incluse în rezultatul interogării ... Apoi, serverul citește fiecare înregistrare care satisface rezultatele interogării, selectează câmpurile obligatorii din aceasta (în cazul nostru, acestea sunt ID-ul și NUMELE) și le trimite clientului. Apoi procesul se repetă din nou - și așa mai departe pentru fiecare intrare selectată.
Toată această digresiune este necesară pentru ca cititorul drag să înțeleagă că toate seturile de date SQL sunt formate rând cu rând, inclusiv în procedurile stocate! Iar principala diferență între procedurile de preluare și procedurile executabile este că primele sunt concepute pentru a returna mai multe rânduri, în timp ce cele din urmă sunt proiectate pentru unul singur. Prin urmare, acestea sunt utilizate în moduri diferite: o procedură de selecție este apelată folosind comanda SELECT, care „necesită” procedura pentru a da toate înregistrările pe care le poate returna. Procedura executabilă se numește folosind EXECUTE PROCEDURE, care „scoate” o singură linie din CP și ignoră restul (chiar dacă există!).
Să vedem un exemplu de procedură de preluare pentru a o clarifica. Pentru> iertare, să creăm o procedură stocată care funcționează exact ca SELECT ID, NAME FROM Table_Example interogare, adică doar preia câmpurile ID și NAME din întregul tabel. Iată acest exemplu:
CREAȚI PROCEDURA Simple_Select_SP
SE INTOARCE (
PROG INTEGER,
procNAME VARCHAR (80))
LA FEL DE
ÎNCEPE
PENTRU
SELECT ID, NAME FROM tabel_exemplu
ÎN: procID ,: procNAME
DO
ÎNCEPE
SUSPENDA;
SFÂRȘIT
SFÂRȘIT
Să parcurgem pașii acestei rutine numite Simple_Select_SP. După cum puteți vedea, nu are parametri de intrare și are doi parametri de ieșire - ID și NAME. Cel mai interesant lucru, desigur, constă în corpul procedurii. Construcția FOR SELECT este utilizată aici:
PENTRU
SELECT ID, NAME FROM tabel_exemplu
ÎN: 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 Table_example, puneți valorile selectate în variabilele procID și procName, apoi efectuați o acțiune cu aceste variabile.
S-ar putea să faceți o față surprinsă și să întrebați „Variabile? Ce alte variabile? 9” Este un fel de surpriză în acest capitol că putem folosi variabile în procedurile stocate. În limba XPS, puteți declara propriile variabile locale în cadrul unei proceduri sau puteți utiliza parametri de intrare și 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:
DECLARAȚI VARIABILĂ
De exemplu, pentru a declara o variabilă locală întreagă Mylnt, introduceți următoarea descriere între AS și BEGIN
DECLARAȚI VARIABLE Mylnt INTEGER;
Variabilele din exemplul nostru încep cu două puncte. Acest lucru se face deoarece acestea sunt accesate în interiorul comenzii SQL FOR SELECT, deci pentru a distinge între câmpurile din tabele care sunt utilizate în SELECT și variabile, trebuie să precedeți ultimul colon. La urma urmei, variabilele pot avea exact același nume ca și câmpurile din tabele!
Dar două puncte din fața numelui variabilei ar trebui să fie utilizate numai în interogările SQL. În afara textelor, accesul la o variabilă se face fără două puncte, de exemplu:
procName = "Un nume";
Dar înapoi la corpul procedurii noastre. Clauza FOR SELECT returnează date nu sub forma unui tabel - un set de date, ci o 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) procedure> p> folosind comanda SUSPEND
Astfel, comanda FOR SELECT ... DO parcurge înregistrările selectate în partea SELECT a acestei comenzi. În corpul buclei formate din partea DO, următoarea înregistrare generată este transferată către client utilizând comanda SUSPEND.
Deci, procedura de preluare este concepută pentru a returna unul sau mai multe rânduri, pentru care este organizată o buclă în interiorul corpului SP care umple parametrii variabilei rezultate. Și la sfârșitul corpului acestui ciclu există întotdeauna o comandă SUSPEND, care va returna următoarea linie de date către client.
Bucle și operatori de ramificare
În plus față de comanda FOR SELECT ... DO, care organizează o buclă peste înregistrările oricărei 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 CP folosind o buclă WHILE .. DO. Această rutină returnează pătratele numerelor între 0 și 99:
CREAȚI PROCEDJRE QUAD
RETURSURI (INTEGER QUADRAT)
LA FEL DE
DECLARA VARIABLE I INTEGER;
Î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 obține un tabel care conține o coloană QUADRAT, în care vor exista pătrate de numere întregi de la 1 la 99
În plus față de iterarea asupra rezultatelor unei selecții 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 unor \ cuvinte. Sintaxa sa este similar cu majoritatea operatorilor de ramificare în limbaje de programare la nivel înalt, cum ar fi Pascal și C.
Să aruncăm o privire la un exemplu mai complex de procedură stocată care face următoarele.
Mai întâi, să definim numele SP, precum și parametrii de intrare și ieșire. Toate acestea sunt scrise în antetul procedurii stocate.
CREAȚI PROCEDURA Creșteți prețurile (
Procent2lncrește DUBLĂ PRECIZIE)
RETURSURI (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 și 3 parametri de ieșire - ID, NAME și new_pnce. Rețineți că primii doi parametri de ieșire au aceleași nume ca și câmpurile din Table_example cu care urmează să lucrăm. Acest lucru este permis de regulile limbajului procedurii stocate.
Acum trebuie să declarăm o variabilă locală care va fi utilizată pentru a stoca valoarea medie. Această declarație va arăta astfel:
DECLARAȚI VARIABILĂ avg_price DUBLĂ PRECIZIE;
Acum să trecem la corpul procedurii stocate. Deschideți corpul HP
cuvântul cheie BEGIN.
În primul rând, trebuie să realizăm primul pas al algoritmului nostru - să calculăm prețul mediu. Pentru a face acest lucru, vom utiliza următoarea interogare:
SELECTARE AVG (Preț_l)
FROM Table_Example
ÎN: 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 pe întregul tabel Table_example. Valoarea returnată de interogare 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 interogare.
Particularitatea acestei cereri este că întotdeauna returnează exact o singură înregistrare. Astfel de interogări se numesc interogări singulare și numai astfel de selecții pot fi utilizate în procedurile stocate. Dacă interogarea returnează mai mult de un rând, atunci trebuie formatată ca o construcție FOR SELECT ... DO, care organizează o buclă pentru a procesa fiecare rând returnat
Deci, am obținut valoarea medie a prețului. Acum trebuie să parcurgeți întregul tabel, să comparați valoarea prețului din fiecare înregistrare cu prețul mediu și să luați măsurile corespunzătoare.
De la început, organizăm iterația pentru fiecare înregistrare din tabelul Table_example.
PENTRU
SELECTAȚI ID-ul, NUMELE, PRICE_1
FROM Table_Example
ÎN: ID ,: NUME ,: preț_ nou
DO
ÎNCEPE
/ * _ osifica fiecare înregistrare aici * /
SFÂRȘIT
Când această construcție este executată, datele vor fi scoase linie cu linie din Table_example și valorile câmpului din fiecare linie vor fi atribuite variabilelor ID, NAME și new_pnce. Vă veți aminti, desigur, că aceste variabile sunt declarate ca parametri out, dar nu trebuie să vă faceți griji că datele selectate vor fi returnate ca rezultate: faptul că ceva este atribuit parametrilor out nu înseamnă că clientul care apelează HP vor primi imediat aceste valori.! Parametrii sunt trecuți 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 interiorul corpului buclei BEGIN .. .END, putem procesa valorile fiecărei linii. După cum vă amintiți, trebuie să aflăm cum se compară prețul actual cu media și să luăm măsurile adecvate. Vom implementa această procedură de comparație folosind instrucțiunea IF:
IF (new_price> avg_price) THEN / * dacă prețul existent este mai mare decât prețul mediu * /
ÎNCEPE
/ * apoi setați un preț nou egal cu prețul mediu plus un procent fix * /
new_price = (avg_price + avg_price * (Procent2Creștere / 100));
ACTUALIZARE Tabel_exemplu
SET PRICE_1 =: new_price
UNDE ID =: ID;
SFÂRȘIT
ALTE
ÎNCEPE
/ * Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci setați prețul egal cu prețul anterior, plus jumătate din diferența dintre prețurile anterioare și medii * /
new_price = (new_pnce + ((avg_pnce new_price) / 2));
ACTUALIZARE Tabel_exemplu
SET PRICE_1 =: new_price
UNDE ID = .ID;
SFÂRȘIT
După cum puteți vedea, rezultatul este o construcție IF destul de mare, care ar fi dificil 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 vă permite să modificați înregistrările existente - una sau mai multe. Pentru a indica fără echivoc în ce înregistrare trebuie schimbat prețul, folosim câmpul cheie primară din clauza WHERE, comparându-l cu valoarea variabilei care stochează valoarea ID pentru înregistrarea curentă: ID =: ID. Rețineți că ID-ul variabilei este precedat de două puncte.
După executarea construcției IF ... THEN ... ELSE, variabilele ID, NAME și new_price conțin date pe care trebuie să le returnăm clientului \ apelantului procedurii. Pentru a face acest lucru, după IF, este necesar să introduceți comanda SUSPEND, care va trimite datele la locul de la care a fost apelat SP. În momentul transferului, procedura va fi suspendată și atunci când este înregistrată o nouă înregistrare necesar de la SP, va continua din nou, iar acest lucru va continua până când FOR SELECT ... DO nu va repeta toate înregistrările interogării sale.
Trebuie remarcat faptul că, pe lângă comanda SUSPEND, care suspendă doar acțiunea procedurii stocate, există o comandă EXIT, care termină procedura stocată după ce rândul a fost transferat. Cu toate acestea, comanda EXIT este utilizată rar, deoarece este necesară în principal pentru a întrerupe ciclul atunci când este atinsă o condiție.
În acest caz, în cazul în care procedura a fost apelată de instrucțiunea SELECT și finalizată de EXIT, ultimul rând recuperat nu va fi returnat. Adică, dacă trebuie să întrerupeți procedura și să obțineți> acest șir, trebuie să utilizați secvența
SUSPENDA;
IEȘIRE;
Scopul principal al EXIT este de a obține seturi de date singulare, de a returna parametrii, apelând prin EXECUTE PROCEDURE În acest caz, valorile parametrilor de ieșire sunt setate, dar setul de date SQL nu este format din acestea și procedura se încheie.
Să scriem întregul text al procedurii noastre stocate, astfel încât să-i putem surprinde logica dintr-o privire:
CREAȚI PROCEDURA Creșteți prețurile (
Procent2 Creșteți precizia dublă)
RETURSURI (ID INTEGER, NUME VARCHAR (80),
new_price DUBLĂ PRECIZIE) AS
DECLARAȚI VARIABILĂ avg_price DUBLĂ PRECIZIE;
ÎNCEPE
SELECTARE AVG (Preț_l)
FROM Table_Example
INTO: avg_price;
PENTRU
SELECTAȚI ID-ul, NUMELE, PRICE_1
FROM Table_Example
ÎN: ID ,: NUME ,: preț_ nou
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
/ * setați un preț nou egal cu prețul mediu plus un procent fix * /
new_price = (avg_price + avg_price * (Procent2lncrease / 100));
ACTUALIZARE Tabel_exemplu
SET PRICE_1 =: new_price
UNDE ID =: ID;
SFÂRȘIT
ALTE
ÎNCEPE
/ * Dacă prețul existent este mai mic sau egal cu prețul mediu, atunci stabilește prețul egal cu prețul anterior, plus jumătate din diferența dintre prețurile anterioare și medii * /
new_price = (new_price + ((avg_price - new_price) / 2));
ACTUALIZARE Tabel_exemplu
SET PRICE_1 =: new_price
UNDE ID =: ID;
SFÂRȘIT
SUSPENDA;
SFÂRȘIT
SFÂRȘIT
Acest exemplu de procedură stocată ilustrează utilizarea construcțiilor de bază a procedurii stocate și a declanșatorului. În continuare, vom analiza modul de utilizare a procedurilor stocate pentru a rezolva unele probleme comune.
Proceduri stocate recursive
Procedurile stocate InterBase pot fi recursive. Aceasta înseamnă că vă puteți apela dintr-o procedură stocată. Sunt permise până la 1000 de niveluri de cuibărire a procedurilor stocate, dar rețineți că resursele gratuite de pe server se pot epuiza înainte de atingerea cuibăririi maxime a HP.
Una dintre utilizările obișnuite pentru procedurile stocate este manipularea structurilor arborelui stocate într-o bază de date. Copacii sunt adesea utilizați în inventar, depozitare, resurse umane și alte aplicații comune.
Să vedem un exemplu de procedură stocată care selectează toate produsele de un anumit tip, începând de la un anumit nivel de cuibărire.
Să presupunem că avem următoarea afirmație de problemă: avem un director de produse cu o structură ierarhică de următorul tip:
Produse
- Aparate
- Frigidere
- Cu trei camere
- Două camere
- Cameră unică
- Mașini de spălat
- Vertical
- Frontal
- Clasic
- Îngust
- Tehnologia calculatoarelor
....
Această structură a directorului categoriilor de produse poate avea ramuri de diferite adâncimi. și, de asemenea, se acumulează în timp. Sarcina noastră este să ne asigurăm că toate elementele frunzei sunt selectate 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 - Front Classic
Mașini de spălat - față îngustă
Să definim structura tabelelor pentru stocarea informațiilor în catalogul de bunuri. Folosim o schemă simplificată pentru a organiza arborele într-un singur tabel:
CREAȚI TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
constrângere cheie primară pkGooci (ID_BUN));
Creăm un tabel GoodsTree, în care există doar 3 câmpuri: ID_GOOD - identificator inteligent de categorie, ID_PARENT_GOOD - identificator al orașului părinte pentru această categorie și GOOD_NAME - numele categoriei. Pentru a asigura integritatea datelor din acest tabel, impunem o constrângere de cheie externă asupra acestui tabel:
ALTER TABLE GoodsTree
ADĂUGAȚI CONSTRAINT FK_goodstree
CHEIE STRĂINĂ (ID_PARENT_GOOD)
REFERINȚE GOODSTPEE (ID__BUN)
Tabelul se referă la sine și cheia externă dată ține evidența acestui lucru. astfel încât să nu existe trimiteri la părinți inexistenți în tabel și, de asemenea, să împiedice încercările de ștergere a categoriilor de produse care au descendenți.
Să introducem următoarele date în tabelul nostru:
| ID_BUN 1
|
ID_PARENT_GOOD 0
|
BUN NUME BUNURI |
Acum că avem spațiu de stocare, putem începe să creăm o procedură stocată care afișează toate categoriile de produse „finale” într-o vizualizare „extinsă” - de exemplu, pentru categoria „Trei compartimente”, numele complet al categoriei ar fi arata ca "Electrocasnice Frigidere cu trei camere".
Procedurile stocate care procesează structuri asemănătoare copacilor au propria lor terminologie. Fiecare element al arborelui se numește nod; iar relația dintre noduri care se referă reciproc se numește relație părinte-copil. Nodurile care se află chiar la capătul copacului și nu au copii sunt numite „frunze”.
Procedura noastră stocată va avea un identificator de categorie ca parametru de intrare, de la care va trebui să începem desfășurarea. Procedura stocată va arăta astfel:
CREAȚI PROCEDURA GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNE (VARCHAR FULL_GOODS_NAME (1000),
ID_CHILD_GOOD INTEGER)
LA FEL DE
DECLARAȚI VARIABIL CURR_CHILD_NAME VARCHAR (80);
ÎNCEPE
/ * 0 organizează bucla exterioară FOR SELECT pe descendenții imediați ai produsului cu ID_GOOD = ID_GOOD2SHOW * /
PENTRU SELECT gtl.id_good, gtl.good_name
DE la GoodsTree gtl
UNDE gtl.id_parent_good =: ID_good2show
ÎN: ID_CHILD_GOOD ,: full_goods_name
DO
ÎNCEPE
/ "Verificarea cu 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 descendenți, atunci este o" frunză "a arborelui și intră în rezultate * /
DACĂ (NU EXISTĂ (
SELECTEAZĂ * din GoodsTree
UNDE GoodsTree.id_parent_good =: id_child_good))
ATUNCI
ÎNCEPE
/ * Treceți „frunza” arborelui la rezultate * /
SUSPENDA;
SFÂRȘIT
ALTE
/ * Pentru nodurile care au copii * /
ÎNCEPE
/ * stochează numele nodului părinte într-o variabilă temporară * /
CURR_CHILD_NAME = full_goods_name;
/ * rulați această rutină recursiv * /
PENTRU
SELECT ID_CHILD_GOOD, full_goods_name
DIN GETFULLNAME (: ID_CHILD_GOOD)
ÎN: ID_CHILD_GOOD ,: full_goods_name
ÎNCEPE
/ * adăugați numele nodului părinte la cel găsit., numele copilului folosind operația de concatenare șir || * /
full_goods_name = CURR_CHILD_NAME | "" | f ull_bunuri_nume, -
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 al categoriilor și am afișat numele complet al categoriilor „frunze” care se află chiar la vârfurile ramurilor.
Concluzie
Aceasta încheie revizuirea principalelor caracteristici ale limbajului procedurii stocate. Evident, este imposibil să stăpânim pe deplin dezvoltarea procedurilor stocate într-un singur capitol, dar aici am încercat să prezentăm și să explicăm conceptele de bază asociate cu procedurile stocate. Construcțiile CP descrise și tehnicile de proiectare pot fi aplicate în majoritatea aplicațiilor de baze de date.
Unele dintre problemele importante legate de dezvoltarea procedurilor stocate vor fi dezvăluite în capitolul următor - „Caracteristici avansate ale limbajului procedurilor stocate InterBase”, care este dedicat gestionării excepțiilor, gestionării erorilor în procedurile stocate și lucrul cu tablouri.
Este luată în considerare o situație în care procedurile stocate pot degrada performanța interogării.
La compilarea procedurilor stocate în MS SQL Server 2000, procedurile stocate sunt plasate în memoria cache procedurală, care poate îmbunătăți performanța în timpul execuției lor, eliminând necesitatea analizei, optimizării și compilării codului de procedură stocată.
Pe de altă parte, există capcane în stocarea codului compilat al unei proceduri stocate care poate avea efectul opus.
Faptul este că la compilarea unei proceduri stocate, planul de execuție al acelor operatori care alcătuiesc codul procedurii este compilat, respectiv, dacă procedura stocată compilată este memorată în cache, atunci planul său de execuție este, de asemenea, memorat în cache și, prin urmare, procedura stocată nu va fi să fie optimizat pentru o anumită situație și parametrii de interogare.
Voi face un mic experiment pentru a demonstra acest lucru.
PASUL 1... Crearea bazei de date.
Să creăm o bază de date separată pentru experiment.
CREAȚI BAZA DE DATE test_sp_perf
ACTIVAT (NAME = "test_data", FILENAME = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
LOG ON (NAME = "test_log", FILENAME = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
PASUL 2. Crearea unui tabel.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))
PASUL 3. Umplerea mesei cu linii de testare. Rândurile duplicate sunt adăugate în mod intenționat la tabel. 10.000 de linii numerotate de la 1 la 10.000 și 10.000 de linii numerotate 50.000.
DECLARA @i int
SET @ i = 1
In timp ce eu<10000)
ÎNCEPE
INSERT INTO sp_perf_test (column1, column2) VALUE (@i, "Test string #" + CAST (@i as char (8)))
INSERT INTO sp_perf_test (column1, column2) VALUE (50000, "Test string #" + CAST (@i as char (8)))
SET @ i = @ i + 1
SFÂRȘIT
SELECTAȚI COUNT (*) DIN sp_perf_test
MERGE
PASUL 4. Crearea unui index non-cluster. Deoarece planul de execuție este stocat în cache cu procedura, indexul va fi utilizat la fel pentru toate apelurile.
CREAȚI INDICE NECLUSIONATE CL_perf_test ON sp_perf_test (coloana1)
MERGE
PASUL 5. Crearea unei proceduri stocate. Procedura execută pur și simplu instrucțiunea SELECT condiționată.
CREAȚI PROC proc1 (@param int)
LA FEL DE
SELECTA coloana1, coloana2 DIN sp_perf_test UNDE [e-mail protejat]
MERGE
PASUL 6. Rularea unei proceduri stocate. Când începeți o procedură vulnerabilă, se utilizează în mod special un parametru selectiv. Ca rezultat al procedurii, obținem o linie. Planul de execuție indică utilizarea unui index non-cluster, deoarece interogarea este selectivă și este cel mai bun mod de a prelua un rând. O procedură optimizată pentru preluarea unui rând este stocată în memoria cache procedurală.
EXEC proc1 1234
MERGE
PASUL 7. Rularea unei proceduri stocate cu un parametru neselectiv. Valoarea de 50.000 este utilizată ca parametru. Liniile cu o astfel de valoare a primei coloane de aproximativ 10.000, respectiv, folosind un indice necluster și operațiunea de căutare a marcajelor este ineficientă, dar din moment ce codul compilat cu planul de execuție este stocat în cache procedural, va fi folosit. Planul de execuție arată acest lucru, precum și faptul că operațiunea de căutare a marcajelor a fost efectuată pentru 9999 de linii.
EXEC proc1 50.000
MERGE
PASUL 8. Preluarea rândurilor cu primul câmp egal cu 50 000. Când se execută o interogare separată, interogarea este optimizată și compilată cu valoarea specifică a primei coloane. Ca rezultat, optimizatorul de interogare detectează că câmpul este duplicat de mai multe ori și decide să utilizeze operația de scanare a tabelelor, care în acest caz este mult mai eficientă decât utilizarea unui index non-grupat.
SELECTează coloana1, coloana2 DIN sp_perf_test WHERE coloana1 = 50000
MERGE
Astfel, putem concluziona că utilizarea procedurilor stocate nu poate îmbunătăți întotdeauna performanța interogării. Ar trebui să fiți foarte atenți la procedurile stocate care funcționează pe rezultate cu un număr variabil de linii și să utilizeze diferite planuri de execuție.
Puteți utiliza scriptul pentru a repeta experimentul pe un server MS SQL nou.
