Funcții memorate transact-sql

Cu SQL Server, puteți crea propriile funcții care adaugă și extind funcțiile oferite de sistem. Funcțiile pot primi 0 sau mai mulți parametri și pot returna o valoare sau un tabel scalar. Parametrii de intrare pot fi de orice tip, cu excepția timestamp, cursor, tabel.

Serverul SQL suportă trei tipuri de funcții definite de utilizator:

1. Crearea unei funcții stocate

Funcția care returnează tabela:

2. Funcții scalare în Transact-SQL

Deci, să examinăm mai întâi codul pentru a crea o funcție scalară:

Este urmat cuvântul cheie RETURNS, urmat de o descriere a tipului de valoare returnată. Pentru o funcție scalară, atunci pot exista orice tipuri (șiruri, numere, date, etc.).

Următorul cod este executat între cuvintele cheie BEGIN și END:

Prima linie declară variabila @Summ. Este necesar să se stocheze rezultatul intermediar al calculelor. Apoi, se execută o interogare SELECT, în care se caută un șir după data și numele produsului în tabelul de produse. În linia găsită, câmpurile de preț și cantitate se înmulțesc, iar rezultatul este înscris la variabila @Summ.

Rețineți că la sfârșitul interogării există un punct și virgulă. Fiecare cerere trebuie să se încheie cu acest simbol, dar în majoritatea exemplelor am neglijat acest lucru, dar în absența caracterului ";" poate duce la o eroare.

În ultimul rând, vom returna rezultatul. Pentru a face acest lucru, trebuie să scrieți cuvântul cheie RETURN, după care scrieți valoarea returnată sau variabila. În acest caz, conținutul variabilei @Summ va fi returnat.

Deoarece funcția este scalară, valoarea returnată trebuie să fie de asemenea scalară și aceasta corespunde tipului descris după cuvântul RETURNS.

3. Utilizarea funcțiilor

Cum se efectuează această funcție? Da, precum și multe alte funcții ale sistemului (de exemplu, GETDATE ()). De exemplu, următorul exemplu folosește o funcție în instrucțiunea SELECT:

Rulați următoarea interogare și asigurați-vă că aceasta returnează același rezultat ca și funcția pe care am creat-o:

Funcțiile pot fi utilizate nu numai în instrucțiunea SELECT, ci și direct, atribuind valoarea variabilei. e exemplu:

În acest exemplu, am declarat variabila @Summ de tip numeric (10,2). Acesta este tipul pe care funcția o returnează. Următoarea linie a variabilei este atribuită rezultatului Summ, folosind SET.

Să vedem ce se întâmplă dacă treci funcții astfel de parametri, în care cererea funcției va reveni la mai mult de o linie. În tabelul nostru de bunuri, combinația datei și a denumirii nu dă unicitate, pentru că am încălcat-o. Cheia primară din tabel, de asemenea, nu există, iar printre produse am patru rânduri care au copiile lor exacte. e-ceva încalcă regula unicității liniilor din bazele de date relaționale, dar arată foarte clar că în viața reală nu poate fi încălcat.

Rezultatul este un singur număr, deși există două linii. eh ?? ce linie a doua a revenit serverul? Este imposibil să spun exact, pentru că sunt ambele la fel și fără o singură diferență. Prin urmare, serverul cel mai probabil a returnat prima linie.

4. O funcție care returnează o tabelă

În exemplul următor, vom crea o funcție care returnează un tabel ca rezultat. De exemplu, creați o funcție care va returna o tabelă de produse și pentru fiecare linie vom calcula produsul coloanelor și prețurilor de cantitate:

Această funcție este aceeași cu cea a funcției scalare - specificăm instrucțiunea CREATE FUNCTION și numele funcției. Am creat special această funcție fără parametri, așa că ați văzut cum sa făcut. Acest lucru se datorează faptului că nu există parametri, după nume ar trebui să fie paranteze, în care nu trebuie să scrie nimic. Dacă nu specificați paranteze, serverul va returna o eroare și funcția nu va fi creată.

Există, de asemenea, o procedură în secțiunea RETURNS, după care este indicat tipul TABLE, ceea ce indică necesitatea returnării tabelului. După aceasta apare cuvântul cheie AS și RETURN, după care valoarea de retur ar trebui să meargă. Pentru o funcție de acest tip în secțiunea RETURN, trebuie să specificați o interogare în paranteze, rezultatul căreia va fi returnat de funcție.

Când scrieți o interogare, toate câmpurile acesteia trebuie să conțină nume. Dacă unul dintre câmpuri nu are un nume, rezultatul executării instrucțiunii CREATE FUNCTION va fi o eroare. În exemplul nostru, ultimul câmp este rezultatul înmulțirii câmpurilor "Preț" și "Cantitate", iar astfel de câmpuri nu au un nume, așa că îl specificăm utilizând cuvântul cheie AS.

Să vedem cum puteți folosi această funcție cu instrucțiunea SELECT:

Deoarece folosim o simplă instrucțiune SELECT, putem limita de ieșire la anumite șiruri, folosind constrângerile în clauza WHERE. De exemplu, în următorul exemplu, selectăm din rezultatul funcției numai liniile în care câmpul "Cantitate" conține o valoare de 1:

Funcția returnează ca rezultat un tabel pe care îl puteți utiliza ca orice altă tabelă de baze de date. Să formăm un exemplu în care puteți vedea utilizarea funcției în legătură cu tabelele. În primul rând, vom crea o funcție care va returna ID-ul angajaților din tabelul tbPeoples și va fi combinată într-un singur câmp Nume:

De exemplu, funcția GetPeoples returnează toate rândurile din tabel. Pentru a obține doar numele de familie potrivit, trebuie să scrieți o interogare precum:

În acest caz, vor fi executate două interogări: una și una în interiorul funcției. e? Dacă treceți ultimul nume ca parametru la o funcție și faceți o clauză WHERE acolo, atunci puteți renunța la o singură interogare SELECT:

5. Funcția multi-operator care returnează o masă

Toate funcțiile create în secțiunea 3.3.5 pot returna un tabel generat de o singură instrucțiune SQL. eh ?? cum apoi puteți face posibilă efectuarea mai multor operațiuni? De exemplu, este posibil să doriți să efectuați verificări suplimentare asupra parametrilor de intrare pentru securitate. Verificările nu sunt inutile, mai ales datele de intrare și mai ales dacă aceste date de intrare sunt specificate de utilizator.

Următorul exemplu arată modul de creare a unei funcții care poate să returneze o tabelă ca rezultat, iar atunci când se întâmplă acest lucru, mai mulți operatori pot executa în corpul funcției:

Aici există o diferență față de funcțiile scalare - după RETURN am specificat numele variabilei a cărei valoare ar trebui să fie rezultatul. În acest caz, nu trebuie să specificați nimic. Am declarat deja o variabilă în secțiunea RETURNS și am descris formatul acestei variabile. În corpul unei funcții, putem și trebuie să umplem această variabilă cu valori și aceasta este cea care va cădea în rezultat.

Acum, să examinăm un exemplu de creare a unei funcții:

În acest exemplu, ca variabilă rezultat este declarată @ret, care este un tabel al celor două câmpuri „idPeoples“ int și „vcFIO“ tip varchar lungi de 50 de caractere. Corpul funcției în e ?? tabelul valorilor înregistrate tbPeoples de masă și execută RETURN declarația, de încheiere a funcției.

În uz, o astfel de funcție nu este diferită de cele considerate anterior. De exemplu, următoarea interogare selectează toate datele pe care funcția le întoarce:

6. Opțiuni de funcții

La crearea funcțiilor, următoarele opțiuni SCHEMABINDING pot fi folosite (legați la o schemă) și / sau ENCRYPTION (textul funcției de criptare). Dacă a doua opțiune cunoaștem deja a bobinei și a procedurilor (funcție vă permite să cripta codul sursă în tabelele de sistem), acesta din urmă este găsit pentru prima dată, dar uh ??, care oferă un mijloc convenabil de protecție a datelor.

Dacă funcția este proiectat pentru a SCHEMABINDING opțiune, obiectele bazei de date se face referire prin funcția nu poate fi schimbată (folosind instrucțiunea ALTER) sau îndepărtată (folosind o instrucțiune DROP). De exemplu, următoarea funcție folosește tabelul tbPeople și aceasta folosește opțiunea SCHEMABINDING:

O funcție poate fi asociată cu o schemă numai în cazul în care următoarele constrângeri de adevăr:

Creați o funcție și încercați după aceasta să eliminați tabelul tbPeoples.

Ca răspuns la aceasta, serverul va afișa un mesaj de eroare care să ateste că obiectul nu poate fi șters, datorită prezenței unei chei străine. Chiar dacă eliminați cheia, ștergerea nu va fi posibilă, deoarece tabelul este referit de o funcție legată de schemă.

Pentru a vedea mesajul fără a elimina cheia, să adăugăm o coloană în tabel și apoi să o ștergem:

Creația va avea succes, însă în timpul eliminării va exista o eroare, cu un mesaj care indică faptul că există o limită care depinde de coloană. Nu am creat restricții, ci am adăugat o coloană și am încercat să o ștergem. Restricția a existat mult timp, dar nu pe o coloană separată, ci pe toate coloanele tabelului și această restricție a fost creată de funcția GetPeoples2, care este asociată cu schema.

7. Schimbarea funcțiilor

Puteți schimba funcția utilizând instrucțiunea ALTER FUNCTION. Vederea generală pentru fiecare variantă a funcției este diferită. Să ne uităm la fiecare dintre ei.

1. Vedere generală a comenzii de schimbare a funcției scalare:

2. Vedere generală a schimbării funcției care returnează tabela:

3. Vedere generală a comenzii pentru schimbarea unei funcții cu setul de operatori care returnează o tabelă.

Următorul exemplu prezintă o versiune simplificată a comenzii care modifică funcția: