Proceduri stocate în T-SQL - creare, modificare, ștergere. Proceduri stocate SQL: creare și utilizare
În Microsoft SQL Server pentru a implementa și automatiza propriile algoritmi ( calculele) puteți folosi proceduri stocate, așa că astăzi vom vorbi despre cum sunt create, modificate și șterse.
Dar mai întâi, puțină teorie pentru a înțelege ce sunt procedurile stocate și de ce sunt necesare în T-SQL.
Notă! Pentru programatorii începători, recomand următoarele materiale utile pe T-SQL:
- Pentru un studiu mai detaliat al limbajului T-SQL, vă recomand să citiți și cartea - The T-SQL Programmer's Path. Tutorial limbajul Transact-SQL.
Ce sunt procedurile stocate în T-SQL?
Proceduri stocate– acestea sunt obiecte de bază de date care conțin un algoritm sub forma unui set de instrucțiuni SQL. Cu alte cuvinte, putem spune că procedurile stocate sunt programe din interiorul unei baze de date. Procedurile stocate sunt folosite pentru a stoca cod reutilizabil pe server, de exemplu, ați scris un anumit algoritm, calcul secvenţial sau instrucţiune SQL în mai multe etape, iar pentru a nu executa de fiecare dată toate instrucţiunile incluse în acest algoritm, îl puteți formata. ca procedură stocată. În același timp, atunci când creați o procedură SQL, serverul compilează codul și apoi, de fiecare dată când rulați această procedură SQL, serverul nu o va recompila.
Pentru a rula o procedură stocată în SQL Server, trebuie să scrieți comanda EXECUTE înaintea numelui acesteia, este de asemenea posibil să prescurtați această comandă ca EXEC. Apelarea unei proceduri stocate într-o instrucțiune SELECT, de exemplu, ca o funcție nu va mai funcționa, de exemplu. procedurile sunt lansate separat.
În procedurile stocate, spre deosebire de funcții, este deja posibilă efectuarea operațiunilor de modificare a datelor precum: UNSERT, UPDATE, DELETE. De asemenea, puteți utiliza instrucțiuni SQL de aproape orice tip în proceduri, de exemplu, CREATE TABLE pentru a crea tabele sau EXECUTE, de exemplu. apelând la alte proceduri. Excepția fac mai multe tipuri de instrucțiuni, cum ar fi: crearea sau modificarea funcțiilor, vizualizărilor, declanșatorilor, crearea de scheme și alte câteva instrucțiuni similare, de exemplu, de asemenea, nu puteți comuta contextul conexiunii la baza de date (USE) într-o procedură stocată.
O procedură stocată poate avea parametri de intrare și parametri de ieșire, poate returna date tabelare sau nu poate returna nimic, executa doar instrucțiunile conținute în ea.
Procedurile stocate sunt foarte utile, ne ajută să automatizăm sau să simplificăm multe operațiuni, de exemplu, trebuie să generați în mod constant diverse rapoarte analitice complexe folosind tabele pivot, de ex. operator PIVOT. Pentru a facilita formularea interogărilor cu acest operator ( după cum știți, sintaxa lui PIVOT este destul de complexă), Puteți scrie o procedură care va genera în mod dinamic rapoarte rezumative pentru dvs., de exemplu, materialul „Dynamic PIVOT în T-SQL” oferă un exemplu de implementare a acestei caracteristici sub forma unei proceduri stocate.
Exemple de lucru cu proceduri stocate în Microsoft SQL Server
Date sursă pentru exemple
Toate exemplele de mai jos vor fi rulate în Microsoft SQL Server 2016 Express. Pentru a demonstra cum funcționează procedurile stocate cu date reale, avem nevoie de aceste date, să le creăm. De exemplu, să creăm un tabel de testare și să adăugăm câteva înregistrări la el, să spunem că va fi un tabel care va conține o listă de produse cu prețurile lor.
Instrucțiune pentru crearea unui tabel CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instrucțiune pentru adăugarea datelor INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , „Mouse”, 100), (1, „Keyboard”, 200), (2, „Phone”, 400) GO --Selectează interogarea SELECT * FROM TestTable
Avem datele, acum să trecem la crearea procedurilor stocate.
Crearea unei proceduri stocate în T-SQL - instrucțiunea CREATE PROCEDURE
Procedurile stocate sunt create folosind o instrucțiune CREAȚI PROCEDURA, după această instrucțiune trebuie să scrieți numele procedurii dvs., apoi, dacă este necesar, să definiți parametrii de intrare și de ieșire între paranteze. După aceasta, scrieți cuvântul cheie AS și deschideți blocul de instrucțiuni cu cuvântul cheie BEGIN, închideți acest bloc cu cuvântul END. În interiorul acestui bloc, scrii toate instrucțiunile care implementează algoritmul tău sau un fel de calcul secvenţial, cu alte cuvinte, programezi în T-SQL.
De exemplu, să scriem o procedură stocată care va adăuga o nouă înregistrare, de exemplu. produs nou pe masa noastră de testare. Pentru a face acest lucru, vom defini trei parametri de intrare: @CategoryId – identificatorul categoriei de produs, @ProductName – numele produsului și @Preț – prețul produsului, acest parametru va fi opțional, adică; nu va fi necesar să-l treci la procedură ( de exemplu, nu știm încă prețul), în acest scop vom seta o valoare implicită în definiția acesteia. Acești parametri se află în corpul procedurii, de exemplu. în blocul BEGIN…END pot fi folosite, la fel ca variabilele obișnuite ( După cum știți, variabilele sunt notate cu semnul @). Dacă trebuie să specificați parametrii de ieșire, atunci după numele parametrului indicați cuvântul cheie OUTPUT ( sau OUT pe scurt).
În blocul BEGIN...END vom scrie o instrucțiune de adăugare a datelor, precum și o instrucțiune SELECT la finalul procedurii, astfel încât procedura stocată să ne returneze date tabelare despre produsele din categoria specificată, luând în considerare contează produsul nou, tocmai adăugat. Tot în această procedură stocată am adăugat procesarea parametrului de intrare și anume eliminarea spațiilor suplimentare la începutul și la sfârșitul șirului de text pentru a elimina situațiile în care mai multe spații au fost introduse accidental.
Iată codul pentru această procedură ( Am comentat si eu).
Creați o procedură CREATE PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și la sfârșitul liniei de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Rularea unei proceduri stocate în T-SQL - comanda EXECUTE
Puteți rula o procedură stocată, așa cum am menționat deja, folosind comanda EXECUTE sau EXEC. Parametrii primiți sunt trecuți procedurilor prin simpla listă a acestora și specificând valorile corespunzătoare după numele procedurii ( pentru parametrii de ieșire trebuie să specificați și comanda OUTPUT). Cu toate acestea, este posibil ca numele parametrilor să nu fie specificate, dar în acest caz este necesar să se urmeze succesiunea de specificare a valorilor, adică. specificați valorile în ordinea în care sunt definiți parametrii de intrare ( acest lucru se aplică și parametrilor de ieșire).
Parametrii care au valori implicite nu trebuie să fie specificați, aceștia sunt așa-numiții parametri opționali.
Iată câteva moduri diferite, dar echivalente, de a rula procedurile stocate, în special procedura noastră de testare.
1. Apelați procedura fără a specifica prețul EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Apelați procedura indicând prețul EXEC TestProcedure @CategoryId = 1, @ProductName = „Testați produsul 2”, @Price = 300 --3. Apelați procedura fără a specifica numele parametrilor EXEC TestProcedure 1, "Test product 3", 400
Schimbarea unei proceduri stocate în instrucțiunea T-SQL - ALTER PROCEDURE
Puteți face modificări ale algoritmului procedurii folosind instrucțiunile PROCEDURA DE ALTERARE. Cu alte cuvinte, pentru a schimba o procedură deja existentă, trebuie doar să scrieți ALTER PROCEDURE în loc de CREATE PROCEDURE și să modificați orice altceva după cum este necesar.
Să presupunem că trebuie să facem modificări procedurii noastre de testare, să spunem parametrul @Price, de exemplu. preț, îl vom face obligatoriu, pentru aceasta vom elimina valoarea implicită și, de asemenea, ne imaginăm că nu mai trebuie să obținem setul de date rezultat, pentru aceasta vom elimina pur și simplu instrucțiunea SELECT din procedura stocată.
Schimbăm procedura ALTER PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și sfârșitul liniilor de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Ștergerea unei proceduri stocate în instrucțiunea T-SQL - DROP PROCEDURE
Dacă este necesar, puteți șterge procedura stocată, acest lucru se face folosind instrucțiunile PROCEDURA DE RĂDARE.
De exemplu, să ștergem procedura de testare pe care am creat-o.
DROP PROCEDURE TestProcedure
La ștergerea procedurilor stocate, merită să ne amintim că, dacă procedura este referită prin alte proceduri sau instrucțiuni SQL, după ștergerea acesteia, acestea vor eșua cu o eroare, deoarece procedura la care se referă nu mai există.
Atât am, sper că materialul ți-a fost interesant și util, pa!
Când lucrează cu SQL Server, utilizatorii își pot crea propriile proceduri care implementează anumite acțiuni. Procedurile stocate sunt obiecte de bază de date cu drepturi depline și, prin urmare, fiecare dintre ele este stocată într-o anumită bază de date. Un apel direct la o procedură stocată este posibil numai dacă se face în contextul bazei de date în care se află procedura.
Tipuri de proceduri stocate
SQL Server are mai multe tipuri de proceduri stocate.
Procedurile stocate de sistem sunt concepute pentru a efectua diverse acțiuni administrative. Aproape toate activitățile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că procedurile stocate de sistem 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. Procedurile stocate de 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 stocate personalizate implementează anumite acțiuni. Procedurile stocate sunt un obiect de bază de date cu drepturi depline. 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. Ele sunt împărțite în locale și globale. Procedurile stocate temporare locale pot fi apelate numai de la conexiunea în care sunt 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, procedurile stocate de acest tip sunt șterse automat când utilizatorul se deconectează sau serverul este repornit sau oprit. Procedurile stocate temporare globale sunt disponibile pentru orice conexiune 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ă.
Declanșatoare
Declanșatoare sunt un tip de procedură stocată. Ele sunt executate atunci când un operator de limbaj de manipulare a datelor (DML) este executat pe tabel. Declanșatorii sunt utilizați pentru a verifica integritatea datelor și, de asemenea, pentru a anula tranzacțiile.
Trigger este o procedură SQL compilată, a cărei execuție este condiționată de apariția anumitor evenimente în baza de date relațională. Utilizarea declanșatorilor este, în cea mai mare parte, foarte convenabilă pentru utilizatorii bazei de date. Cu toate acestea, utilizarea lor implică adesea supraîncărcare I/O suplimentară. Atunci când aceleași rezultate (cu mult mai puțină suprasarcină) pot fi obținute folosind proceduri stocate sau programe de aplicație, utilizarea declanșatorilor nu este practică.
Declanșatoare este un instrument special de server SQL folosit pentru a menține integritatea datelor într-o bază de date. Este posibil ca constrângerile de integritate, regulile și valorile implicite să nu atingă întotdeauna nivelul dorit de funcționalitate. Este adesea necesar să se implementeze algoritmi complecși de verificare a datelor pentru a asigura fiabilitatea și realitatea acestora. În plus, uneori trebuie să monitorizați modificările valorilor din tabel, astfel încât datele asociate să poată fi modificate după cum este necesar. Declanșatoarele pot fi considerate ca un fel de filtre care intră în vigoare după ce toate operațiunile au fost finalizate în conformitate cu regulile, valorile standard etc.
Trigger este un tip special de procedură stocată care este lansată automat de către server atunci când se încearcă modificarea datelor din tabelele cărora sunt asociate declanșatorii. Fiecare Trigger este legat de un anumit tabel. Toate modificările de date pe care le face sunt considerate ca o singură tranzacție. Dacă este detectată o eroare sau o încălcare a integrității datelor, tranzacția este anulată. Prin urmare, modificările sunt interzise. Orice modificări deja făcute de declanșator sunt, de asemenea, anulate.
Creează declanșatorul numai proprietarul bazei de date. Această restricție vă permite să evitați modificări accidentale ale structurii tabelelor, modalități de conectare a altor obiecte la acestea etc.
Trigger Este un remediu foarte util și în același timp periculos. Deci, dacă logica funcționării sale este incorectă, puteți distruge cu ușurință o întreagă bază de date, așa că declanșatoarele trebuie depanate cu mare atenție.
Spre deosebire de o subrutină obișnuită, declanșatorul este executat implicit ori de câte ori are loc un eveniment de declanșare și nu are argumente. Activarea acestuia se numește uneori declanșarea declanșatorului. Folosind declanșatoarele, sunt atinse următoarele obiective:
Validarea corectitudinii datelor introduse și aplicarea unor constrângeri complexe de integritate a datelor care sunt dificil, dacă nu imposibil, de menținut folosind constrângerile de integritate stabilite pe un tabel;
emiterea de avertismente care vă reamintesc să efectuați anumite acțiuni atunci când actualizați un tabel implementat într-un anumit mod;
acumularea de informații de audit prin înregistrarea informațiilor despre modificările efectuate și despre acele persoane care le-au efectuat;
suport pentru replicare.
Formatul de bază al comenzii CREATE TRIGGER este prezentat mai jos:
<Определение_триггера>::=
CREATE TRIGGER nume_declanșator
ÎNAINTE | DUPĂ<триггерное_событие>
PE<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Evenimentele de declanșare constau în inserarea, ștergerea și actualizarea rândurilor dintr-un tabel. În acest din urmă caz, puteți specifica nume specifice de coloane de tabel pentru evenimentul declanșator. Momentul declanșării este determinat folosind cuvintele cheie BEFORE ( Trigger rulează înainte ca evenimentele asociate acestuia să fie executate) sau AFTER (după ce sunt executate).
Acțiunile efectuate de declanșator sunt specificate pentru fiecare rând (PENTRU FIECARE RÂND) acoperit de eveniment, sau doar o singură dată pentru fiecare eveniment (PENTRU FIECARE Declarație).
Declanșatoarele scrise incorect pot duce la probleme grave, cum ar fi blocaje. Declanșatoarele pot bloca multe resurse pentru perioade lungi de timp, așa că ar trebui să se acorde o atenție deosebită minimizării conflictelor de acces.
Trigger poate fi creat doar în baza de date curentă, dar este posibil să accesați și alte baze de date în cadrul declanșatorului, inclusiv cele situate pe un server la distanță.
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. performanța întregii aplicații, centralizați procesarea datelor și reduceți cantitatea de cod necesară pentru a finaliza 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ă o părere că lucrul cu proceduri stocate este mult mai dificil decât implementarea aceleiași funcționalități într-o aplicație client, într-o aplicație de înaltă calitate. limbaj de nivel
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 valori 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> =
(
Pare destul de voluminos și poate fi chiar greoi, dar de fapt totul este foarte simplu Pentru a stăpâni treptat sintaxa, să ne uităm treptat la exemple 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 lor - î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ă acestea există, le vom analiza mai detaliat mai jos), separate prin punct și virgulă (;) și un bloc de instrucțiuni incluse în operator. paranteze BEGIN END. În acest caz, corpul HP este foarte simplu - cerem să adăugăm două argumente de intrare și să atribuim rezultatul acestora la ieșire, apoi să 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 interogări SQL obișnuite executate 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 în cazul diferitelor tipuri de apeluri. 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 rezultat al acestei interogări este numit și un set de date SQL. Să ne gândim la modul în care se formează setul de date în timpul execuției acestei interogări. Serverul, după ce a primit interogarea, determină la ce tabele se referă a afla ce subset de înregistrări din aceste tabele ar trebui să fie incluse î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! Și 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 o 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 înaintea primului cuvânt BEGIN. Descrierea variabilei locale arată astfel:
DECLARE VARIABIL
De exemplu, pentru a declara o variabilă locală întreagă Mylnt, trebuie 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, prin urmare, 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ă datele nu ca un 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 de procedură stocată 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.
- Calculează prețul mediu în tabelul Table_example (vezi capitolul „Tabelele Chei primare și generatoare”)
- 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
- 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.
- 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 procentuală 2ln 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 multe rânduri, atunci aceasta trebuie formatată ca o construcție 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 vom stabili un nou preț 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 conformitate cu 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ă, iar atunci când este necesară o nouă înregistrare de la HP va fi continuat din nou - și acest lucru va continua până când FOR SELECT...DO nu va itera prin toate înregistrările din interogarea sa.
De remarcat 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 întregul text al procedurii noastre stocate, 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 formulare a problemei: avem un director de mărfuri cu o structură ierarhică de următorul 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 finale 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
|
ID_PARENT_BUN 0
|
BUN NUME BUNURI |
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
/*0organizați bucla exterioară FOR SELECT în funcție de 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 capitolul următor - „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.
Procedurile stocate SQL sunt module de program executabile care pot fi stocate sub formă de diferite obiecte. Cu alte cuvinte, este un obiect care conține instrucțiuni SQL. Aceste proceduri stocate pot fi executate în clientul aplicației pentru a obține performanțe bune. În plus, astfel de obiecte sunt adesea numite din alte scripturi sau chiar din altă secțiune.
Introducere
Mulți oameni cred că sunt similare cu diferite proceduri (respectiv, cu excepția MS SQL). Poate că acest lucru este adevărat. Au parametri similari si pot produce valori similare. Mai mult, în unele cazuri se ating. De exemplu, acestea sunt combinate cu baze de date DDL și DML, precum și cu funcții de utilizator (nume de cod UDF).
În realitate, procedurile stocate SQL au o gamă largă de avantaje care le deosebesc de procesele similare. Securitate, flexibilitate de programare, productivitate - toate acestea atrage din ce în ce mai mulți utilizatori care lucrează cu baze de date. Popularitatea maximă a procedurilor a avut loc în 2005-2010, când a fost lansat un program de la Microsoft numit „SQL Server Management Studio”. Cu ajutorul acestuia, lucrul cu bazele de date a devenit mult mai ușor, mai practic și mai convenabil. De la an la an, acesta a câștigat popularitate în rândul programatorilor. Astăzi este un program absolut familiar, care pentru utilizatorii care „comună” cu bazele de date este la egalitate cu Excel.
Când o procedură este apelată, aceasta este procesată instantaneu de către server însuși, fără procese inutile sau intervenția utilizatorului. După aceasta, puteți efectua orice ștergere, execuție sau modificare. De toate acestea este operatorul DDL, care singur efectuează cele mai complexe acțiuni de procesare a obiectelor. Mai mult, toate acestea se întâmplă foarte repede, iar serverul nu este încărcat efectiv. Această viteză și performanță vă permit să transferați foarte rapid cantități mari de informații de la utilizator la server și invers.
Pentru a implementa această tehnologie de lucru cu informații, există mai multe limbaje de programare. Acestea includ, de exemplu, PL/SQL de la Oracle, PSQL în sistemele InterBase și Firebird, precum și clasicul Microsoft Transact-SQL. Toate sunt concepute pentru crearea și executarea procedurilor stocate, ceea ce permite procesoarelor mari de baze de date să folosească propriii algoritmi. Acest lucru este necesar și pentru ca cei care gestionează astfel de informații să poată proteja toate obiectele de accesul neautorizat al terților și, în consecință, de crearea, modificarea sau ștergerea anumitor date.
Productivitate
Aceste obiecte de bază de date pot fi programate într-o varietate de moduri. Acest lucru permite utilizatorilor să aleagă tipul de metodă utilizată cel mai potrivit, economisind efort și timp. În plus, procedura este procesată în sine, ceea ce evită timpul uriaș petrecut cu comunicarea dintre server și utilizator. De asemenea, modulul poate fi reprogramat și schimbat în direcția dorită în orice moment. Este de remarcat în special viteza cu care este lansată procedura stocată SQL: acest proces are loc mai rapid decât altele similare cu acesta, ceea ce îl face convenabil și universal.
Siguranță
Acest tip de prelucrare a informațiilor diferă de procesele similare prin faptul că garantează o securitate sporită. Acest lucru este asigurat de faptul că accesul la proceduri de către alți utilizatori poate fi complet exclus. Acest lucru va permite administratorului să efectueze operațiuni cu aceștia în mod independent, fără teama de interceptare a informațiilor sau acces neautorizat la baza de date.
Transfer de date
Relația dintre procedura stocată SQL și aplicația client este utilizarea parametrilor și a valorilor returnate. Acesta din urmă nu trebuie să treacă datele în procedura stocată, dar aceste informații (în principal la cererea utilizatorului) sunt procesate pentru SQL. După ce procedura stocată și-a încheiat activitatea, trimite pachete de date înapoi (dar din nou opțional) către aplicația care a apelat-o, folosind diverse metode care pot fi folosite fie pentru a apela procedura stocată SQL, fie pentru a returna, de exemplu:
Transferarea datelor utilizând un parametru de tip de ieșire;
Transmiterea datelor folosind operatorul de retur;
Transmiterea datelor utilizând operatorul de selectare.
Acum să ne dăm seama cum arată acest proces din interior.
1. Creați o procedură stocată EXEC în SQL
Puteți crea o procedură în MS SQL (Managment Studio). După ce procedura este creată, aceasta va fi listată în nodul programabil al bazei de date, în care procedura de creare este executată de către operator. Pentru a se executa, procedurile stocate SQL folosesc un proces EXEC care conține numele obiectului însuși.
Când creați o procedură, numele acesteia apare mai întâi, urmat de unul sau mai mulți parametri atribuiți acesteia. Parametrii pot fi opționali. După ce parametrul(ii), adică corpul procedurii, au fost scrise, trebuie efectuate unele operații necesare.
Ideea este că un corp poate avea variabile locale localizate în el, iar aceste variabile sunt, de asemenea, locale în raport cu procedurile. Cu alte cuvinte, acestea pot fi vizualizate numai în corpul unei proceduri Microsoft SQL Server. În acest caz, procedurile stocate sunt considerate locale.
Deci, pentru a crea o procedură, avem nevoie de numele procedurii și de cel puțin un parametru ca corp al procedurii. Rețineți că o opțiune excelentă în acest caz este să creați și să executați o procedură cu numele schemei în clasificator.
Corpul procedurii poate fi de orice fel, cum ar fi crearea unui tabel, inserarea unuia sau mai multor rânduri ale unui tabel, stabilirea tipului și naturii bazei de date și așa mai departe. Cu toate acestea, organul de procedură restricționează anumite operațiuni să fie efectuate în cadrul acestuia. Unele dintre limitările importante sunt enumerate mai jos:
Organismul nu ar trebui să creeze nicio altă procedură stocată;
Corpul nu trebuie să creeze o impresie falsă asupra obiectului;
Corpul nu ar trebui să creeze niciun declanșator.
2. Setarea unei variabile în corpul procedurii
Puteți face variabile locale în corpul procedurii și apoi vor locui exclusiv în corpul procedurii. Este o practică bună să creați variabile la începutul corpului procedurii stocate. Dar, de asemenea, puteți seta variabile oriunde în corpul unui obiect dat.
Uneori puteți observa că mai multe variabile sunt setate pe o singură linie și fiecare parametru variabil este separat prin virgulă. De asemenea, rețineți că variabila este prefixată cu @. În corpul procedurii, puteți seta variabila oriunde doriți. De exemplu, variabila @NAME1 poate fi declarată aproape de sfârșitul corpului procedurii. Pentru a atribui o valoare unei variabile declarate, se utilizează un set de date personale. Spre deosebire de situația în care pe aceeași linie sunt declarate mai multe variabile, în această situație se utilizează un singur set de date personale.
Utilizatorii pun adesea întrebarea: „Cum să atribuiți mai multe valori într-o singură declarație în corpul unei proceduri?” Bine. Este o întrebare interesantă, dar este mult mai ușor de făcut decât crezi. Răspuns: Folosind perechi precum „Select Var = value”. Puteți folosi aceste perechi separându-le cu virgulă.
O varietate de exemple arată oameni care creează o procedură stocată simplă și o execută. Cu toate acestea, o procedură poate accepta parametri astfel încât procesul care o apelează să aibă valori apropiate de ea (dar nu întotdeauna). Dacă acestea coincid, în interiorul corpului încep procesele corespunzătoare. De exemplu, dacă creați o procedură care va accepta un oraș și o regiune de la apelant și va returna date despre câți autori aparțin orașului și regiunii corespunzătoare. Procedura va interoga tabelele de autori ale bazei de date, cum ar fi Pubs, pentru a efectua această numărare de autori. Pentru a obține aceste baze de date, de exemplu, Google descarcă scriptul SQL de pe pagina SQL2005.
În exemplul anterior, procedura ia doi parametri, care în engleză se vor numi în mod convențional @State și @City. Tipul de date se potrivește cu tipul definit în aplicație. Corpul procedurii are variabile interne @TotalAuthors, iar această variabilă este folosită pentru a afișa numărul de autori. Urmează secțiunea de selecție a interogărilor, care calculează totul. În cele din urmă, valoarea calculată este tipărită în fereastra de ieșire folosind instrucțiunea print.
Cum se execută o procedură stocată în SQL
Există două moduri de a efectua procedura. Prima modalitate arată, prin trecerea parametrilor, cum este executată o listă separată prin virgulă după numele procedurii. Să presupunem că avem două valori (ca în exemplul anterior). Aceste valori sunt colectate folosind variabilele parametrilor procedurii @State și @City. În această metodă de transmitere a parametrilor, ordinea este importantă. Această metodă se numește trecere ordinală de argumente. În a doua metodă, parametrii sunt deja alocați direct, iar în acest caz ordinea nu este importantă. Această a doua metodă este cunoscută ca transmiterea de argumente numite.
Procedura se poate abate ușor de la cea tipică. Totul este la fel ca în exemplul anterior, dar doar aici parametrii sunt deplasați. Adică, @City este stocat primul, iar @State este stocat lângă valoarea implicită. Parametrul implicit este de obicei evidențiat separat. Procedurile stocate SQL sunt transmise doar ca parametri. În acest caz, cu condiția, parametrul „UT” înlocuiește valoarea implicită „CA”. În a doua execuție, este transmisă o singură valoare a argumentului pentru parametrul @City, iar parametrul @State preia valoarea implicită „CA”. Programatorii cu experiență sfătuiesc ca toate variabilele să fie localizate la sfârșitul listei de parametri în mod implicit. În caz contrar, execuția nu este posibilă și atunci trebuie să lucrați cu transmiterea de argumente numite, care este mai lungă și mai complexă.
4. Proceduri stocate SQL Server: Metode de returnare
Există trei moduri importante de a trimite date într-o procedură numită stocată. Acestea sunt enumerate mai jos:
Returnează valoarea unei proceduri stocate;
Ieșirea parametrilor procedurii stocate;
Selectarea uneia dintre procedurile stocate.
4.1 Returnarea valorilor din procedurile stocate SQL
În această tehnică, o procedură atribuie o valoare unei variabile locale și o returnează. O procedură poate returna direct o valoare constantă. În exemplul următor, am creat o procedură care returnează numărul total de autori. Dacă comparați această procedură cu cele anterioare, puteți vedea că valoarea de imprimare este inversată.
Acum să vedem cum să executăm o procedură și să tipărim valoarea returnată a acesteia. Executarea procedurii necesită setarea unei variabile și imprimarea, care se efectuează după întregul proces. Rețineți că în loc de o instrucțiune de tipărire, puteți utiliza o instrucțiune Select, cum ar fi Select @RetValue, precum și OutputValue.
4.2 Ieșirea parametrului procedurii stocate SQL
Valoarea răspunsului poate fi folosită pentru a returna o singură variabilă, ceea ce am văzut în exemplul anterior. Utilizarea parametrului Ieșire permite unei proceduri să trimită una sau mai multe valori variabile apelantului. Parametrul de ieșire este desemnat exact prin acest cuvânt cheie „Ieșire” atunci când se creează o procedură. Dacă un parametru este dat ca parametru de ieșire, atunci obiectul procedură trebuie să îi atribuie o valoare. Proceduri stocate SQL, exemple ale cărora pot fi văzute mai jos, în acest caz revin cu informații rezumative.
În exemplul nostru vor exista două nume de ieșire: @TotalAuthors și @TotalNoContract. Ele sunt indicate în lista de parametri. Aceste variabile atribuie valori în corpul procedurii. Când folosim parametrii de ieșire, apelantul poate vedea valoarea setată în corpul procedurii.
De asemenea, în scenariul anterior, două variabile sunt declarate pentru a vedea valorile pe care procedurile MS SQL Server le-au stabilit în parametrul de ieșire. Apoi procedura se efectuează prin furnizarea valorii normale a parametrului „CA”. Următorii parametri sunt parametri de ieșire și, prin urmare, variabilele declarate sunt transmise în ordinea specificată. Rețineți că atunci când treceți variabile, cuvântul cheie de ieșire este de asemenea setat aici. După finalizarea cu succes a procedurii, valorile returnate de parametrii de ieșire sunt afișate în fereastra de mesaje.
4.3 Selectarea uneia dintre procedurile SQL stocate
Această tehnică este utilizată pentru a returna un set de valori ca un tabel de date (RecordSet) la procedura stocată de apelare. În acest exemplu, procedura stocată SQL cu parametrii @AuthID interogează tabelul Authors prin filtrarea înregistrărilor returnate folosind acel parametru @AuthId. Declarația Select decide ce ar trebui returnat apelantului procedurii stocate. Când procedura stocată este executată, AuthId-ul este transmis înapoi. Această procedură de aici returnează întotdeauna o singură înregistrare sau deloc. Dar o procedură stocată nu are restricții privind returnarea mai multor înregistrări. Nu este neobișnuit să vedem exemple în care returnarea datelor cu ajutorul parametrilor selectați care implică variabile calculate are loc prin furnizarea de totaluri multiple.
In cele din urma
O procedură stocată este un modul de program destul de serios care revine sau trece și setează, de asemenea, variabilele necesare datorită aplicației client. Deoarece procedura stocată rulează singură pe server, cantități uriașe de schimb de date între server și aplicația client (pentru unele calcule) pot fi evitate. Acest lucru vă permite să reduceți sarcina pe serverele SQL, ceea ce, desigur, este în beneficiul proprietarilor lor. Unul dintre subtipuri este procedurile stocate T SQL, dar studiul lor este necesar pentru cei care creează baze de date impresionante. Există, de asemenea, un număr mare, chiar uriaș de nuanțe, care pot fi utile atunci când studiază procedurile stocate, dar acest lucru este necesar mai mult pentru cei care intenționează să se implice în programare, inclusiv profesional.
