Transact-sql, funcții definite de utilizator

În limbile de programare, există de obicei două tipuri de subrutine:

funcții definite de utilizator (UDF).

După cum sa discutat în articolul precedent, procedurile stocate constau din mai multe instrucțiuni și au zero până la câțiva parametri de intrare, dar de obicei nu revin nici un parametru. Spre deosebire de procedurile stocate, funcțiile întoarce întotdeauna o valoare. În această secțiune, vom examina crearea și utilizarea funcțiilor definite de utilizator (UDF).







Crearea și executarea funcțiilor definite de utilizator

Funcțiile definite de utilizator sunt create utilizând instrucțiunea CREATE FUNCTION. care are următoarea sintaxă:

Parametrul schema_name specifică numele schemei atribuite de proprietarul UDF creat, iar parametrul funcțional nume specifică numele funcției. Parametrul @param este parametrul de intrare al funcției (argument formal), al cărui tip de date este determinat de parametrul de tip. Parametrii funcției sunt valori care sunt transmise de către obiectul apelant la o funcție definită de utilizator pentru a fi utilizată în acesta. Parametrul implicit specifică valoarea implicită pentru parametrul funcției corespunzătoare. (Valoarea implicită poate fi, de asemenea, NULL.)

Clauza RETURNS specifică tipul de date al valorii returnate de UDF. Acesta poate fi aproape orice tip de date standard acceptat de sistemul de baze de date, inclusiv tipul de date TABLE. Singurul tip de date care nu poate fi specificat este tipul de date de timbru.

Funcțiile definite de utilizator pot fi scalare sau tabele. Funcțiile scalare returnează o valoare atomică (scalară). Aceasta înseamnă că în clauza RETURNS a unei funcții scalare este specificat unul dintre tipurile de date standard. Funcția este tabulară dacă clauza RETURNS returnează un set de rânduri.

Clauza alternativă WITH SCHEMABINDING leagă UDF de obiectele bazei de date pe care această funcție le accesează. După aceasta, orice încercare de a modifica obiectul bazei de date la care accesul funcției nu reușește. (Funcția de blocare pentru obiectele bazei de date la care se referă, se elimină numai atunci când schimbați funcția, și apoi opțiunea SCHEMABINDING nu mai este setat.)

Pentru a utiliza clauza SCHEMABINDING la crearea unei funcții, obiectele bazei de date la care se referă funcția trebuie să îndeplinească următoarele condiții:

toate vizualizările și alte UDF accesate de funcția definită trebuie să fie legate de schemă;

toate obiectele bazei de date (tabele, vizualizări și UDF) trebuie să fie în aceeași bază de date cu funcția definită.

Parametrul bloc specifică blocul BEGIN / END care conține implementarea funcției. Ultima instrucțiune a blocului trebuie să fie o instrucțiune RETURN cu un argument. (Valoarea argumentului este valoarea returnată de funcție.) Doar următoarele instrucțiuni sunt permise în interiorul blocului BEGIN / END:

instrucțiuni de asignare, cum ar fi SET;

instrucțiuni pentru controlul executării, cum ar fi WHILE și IF;

Declarațiile DECLARE declarând variabile locale;

Instrucțiuni SELECT conținând liste de coloane de selecție cu expresii ale căror valori sunt atribuite variabilelor care sunt locale pentru funcție;

INSERT, UPDATE și DELETE care modifică variabilele cu un tip de date TABLE care sunt locale pentru funcție.

În mod implicit, CREATE instrucțiune funcție poate fi utilizată numai membrii serverului fix sysadmin și fix bază de date rol db_owner sau db_ddladmin. Cu toate acestea, membrii acestor roluri pot atribui acest drept altor utilizatori prin intermediul instrucțiunii GRANT CREATE FUNCTION.

Următorul exemplu arată modul de creare a funcției ComputeCosts:

Funcția ComputeCosts calculează costurile suplimentare care apar atunci când bugetele proiectului sunt majorate. Singurul parametru de intrare, @percent, determină valoarea procentuală a creșterii bugetelor. În blocul BEGIN / END, pentru prima dată sunt declarate două variabile locale: @addCosts și @sumBudget, apoi utilizând instrucțiunea SELECT, suma totală a tuturor bugetelor este atribuită variabilei @sumBudget. După aceasta, funcția calculează costurile suplimentare totale și prin intermediul instrucțiunii RETURN returnează această valoare.

Apelarea funcției definite de utilizator

O funcție definită de utilizator poate fi apelată utilizând instrucțiuni Transact-SQL, cum ar fi SELECT, INSERT, UPDATE sau DELETE. Funcția se numește prin specificarea numelui acesteia cu o pereche de paranteze la sfârșit, în care pot fi specificate unul sau mai multe argumente. Argumentele sunt valori sau expresii care sunt transmise parametrilor de intrare, definiți imediat după numele funcției. Când se numește o funcție, atunci când parametrii nu sunt definiți în mod prestabilit, toți acești parametri trebuie să fie furnizați cu argumente în aceeași ordine în care acești parametri sunt definiți în instrucțiunea CREATE FUNCTION.

Următorul exemplu arată apelul la funcția ComputeCosts din instrucțiunea SELECT:

Instrucțiunea SELECT din exemplu afișează numele și numerele tuturor proiectelor ale căror bugete sunt mai mici decât costurile totale suplimentare pentru toate proiectele cu o creștere procentuală specificată.







Numele FUNCTION Transact-SQL trebuie să fie specificate folosind nume care constau din două părți: numele de schemă și numele funcției, astfel încât în ​​exemplul, am folosit schema prefix DBO.

Valori ale funcțiilor valorice în tabel

Așa cum am menționat mai devreme, funcția este o întoarcere în valoare de tabel dacă clauza RETURNS returnează un set de rânduri. În funcție de modul în care este definit corpul funcției, funcțiile evaluate în tabel sunt clasificate ca inline și multistate. Dacă cuvântul cheie TABLE este specificat în clauza RETURNS fără o listă de coloane însoțitoare, această funcție este încorporată. Instrucțiunea SELECT a unei funcții inline returnează un set de rezultate ca variabilă cu un tip de date TABLE.

Funcția de valoare în tabel multi-obiect returnează un nume care specifică o variabilă internă cu un tip de date TABLE. Acest tip de date este indicat de cuvântul cheie TABLE. care urmează numele variabilei. Rândurile selectate sunt inserate în această variabilă și servesc ca valoare de retur a funcției.

Crearea unei funcții valoroase este prezentată în exemplul de mai jos:

Funcția EmployeesInProject afișează numele tuturor angajaților care lucrează la un anumit proiect, numărul de care este definit de un @projectNumber parametru de intrare. Întrucât, în general, funcția returnează un set de rânduri, propunerea RETURNĂRI în definiția acestei funcții conține instrucțiunea TABLE cuvinte cheie, indicând faptul că funcția returnează o valoare tabelă. (Rețineți că în exemplul respectiv, blocul BEGIN / END trebuie să fie omis, iar clauza RETURN conține instrucțiunea SELECT.)

Utilizați funcția Employees_in_Project în exemplul de mai jos:

Transact-sql, funcții definite de utilizator

Funcții valoroase în tabel și instrucțiuni APPLY

Instrucțiunea relațională APPLY vă permite să apelați o funcție de valoare pentru fiecare rând dintr-o expresie de tabel. Această instrucțiune este specificată în clauza FROM a instrucțiunii SELECT corespunzătoare în același mod ca și instrucțiunea JOIN. Instrucțiunea APPLY poate fi combinată cu o funcție de tabelă pentru a produce un rezultat similar cu setul de rezultate al operației de conectare a celor două tabele. Există două forme ale instrucțiunii APPLY:

Instrucțiunea CROSS APPLY returnează acele rânduri din expresia de masă internă (stânga) care se potrivește cu expresia de masă externă (dreapta). Astfel, logic, instrucțiunea CROSS APPLY funcționează în același mod ca și instrucțiunea INNER JOIN.

Instrucțiunea OUTER APPLY returnează toate rândurile din expresia tabelului intern (stânga). (Pentru acele rânduri pentru care nu există nici un meci în expresie tabelă exterioară, conține valori NULL în coloanele expresiei tabel exterior.) In mod logic, instrucțiuni EXTERIOR APLICA este echivalentă cu LEFT OUTER JOIN declarație.

Aplicarea instrucțiunii APPLY este prezentată în exemplele de mai jos:

Funcția GetJob () returnează un set de rânduri din tabelul Works_on. În exemplul de mai jos, acest set de rezultate este "conectat" de clauza APPLY cu conținutul tabelului Angajat:

Rezultatul acestor două funcții va fi următoarele două tabele (afișate după executarea celei de-a doua funcții):

Transact-sql, funcții definite de utilizator

În prima interogare a exemplului, setul de rezultate al funcției de tabelă GetJob () este "conectat" la conținutul tabelului Angajat prin instrucțiunea CROSS APPLY. Funcția GetJob () joacă rolul de intrare corectă, iar tabelul Employee - stânga. Expresia corectă de intrare este calculată pentru fiecare linie a intrării din stânga, iar liniile rezultate sunt combinate, creând rezultatul final.

A doua interogare este similară celei dintâi (dar utilizează instrucțiunea OUTER APPLY), care corespunde în mod logic funcționării externe a celor două tabele.

Valorile parametrilor valorilor valorilor

Utilizarea unui parametru valoros în tabel este prezentată în exemplul de mai jos:

În acest exemplu, tipul de tabel departmentType este definit pentru prima dată. Aceasta înseamnă că acest tip este un tip de date TABLE, deci permite introducerea de rânduri. În procedura InsertProc, este declarată variabila @Moscow cu tipul de date departmentType. (Clauza READONLY specifică faptul că conținutul acestui tabel nu poate fi modificat.) Într-un pachet ulterior, datele sunt inserate în această variabilă tabelă, după care procedura este pornită. În timpul procesului de execuție, procedura introduce rândurile din variabila tabelă în tabelul temporar #moscowTable. Conținutul introdus al tabelului temporar este după cum urmează:

Utilizarea parametrului evaluat în tabel oferă următoarele avantaje:

simplifică modelul de programare al subrutinelor;

numărul de apeluri către server scade și se primesc răspunsurile corespunzătoare;

Tabelul cu rezultate poate avea un număr arbitrar de rânduri.

Schimbarea structurii instrucțiunilor definite de utilizator

Limba Transact-SQL acceptă, de asemenea, instrucțiunea ALTER FUNCTION. care modifică structura instrucțiunilor definite de utilizator (UDF). Această instrucțiune este de obicei folosită pentru a elimina o funcție legată de o schemă. Toți parametrii instrucțiunii ALTER FUNCTION au același înțeles cu instrucțiunile CREATE FUNCTION cu același nume.

Pentru a elimina UDF, utilizați instrucțiunea DROP FUNCTION. Numai proprietarul sau membrul rolului fix db_owner sau sysadmin poate șterge funcția.

Funcțiile definite de utilizator și CLR

În articolul precedent, am analizat modul de creare a procedurilor stocate din codul CLR gestionat în C #. Această abordare poate fi utilizată pentru funcțiile definite de utilizator (UDF), cu singura diferență că UDF pentru a salva un obiect bază de date, instrucțiunea CREATE FUNCTION, în loc de CREATE PROCEDURA. În plus, funcțiile definite de utilizator se aplică, de asemenea, într-un context diferit de procedurile stocate, deoarece UDF-urile întoarce întotdeauna o valoare.

Exemplul de mai jos arată codul sursă pentru funcțiile definite de utilizator (UDF) implementate în C #:

În codul sursă al funcțiilor definite de utilizator, exemplul calculează un nou buget al proiectului, mărind bugetul vechi cu un anumit număr de procente. Puteți utiliza instrucțiunea CREATE ASSEMBLY pentru a crea un ansamblu CLR în baza de date, așa cum se arată mai devreme. Dacă ați studiat exemplele din articolul precedent, și a adăugat CLRStoredProcedures de asamblare în baza de date, puteți actualiza adunarea, după recompilarea cu o nouă clasă (CLRStoredProcedures este numele meu proiect C # clase în care aș adăuga definiția procedurilor și funcțiile stocate, vă asamblarea poate fi numită altfel):

Instrucțiunea CREATE FUNCTION din exemplul de mai jos salvează metoda ComputeBudget ca obiect de bază de date, care poate fi utilizată mai târziu în instrucțiunile de manipulare a datelor.

Utilizând una dintre aceste instrucțiuni, instrucțiunea SELECT este prezentată în exemplul de mai jos:

O funcție definită de utilizator poate fi plasată în diferite locații în instrucțiunea SELECT. În exemplele de mai sus, a fost apelată în clasele WHERE, FROM și în lista selectată a instrucțiunii SELECT.







Articole similare

Trimiteți-le prietenilor: