Funkcije u SQL Serveru su objekti baze podataka koji sadrže a skup SQL naredbi za izvođenje određenog zadatka . Funkcija prihvaća ulazne parametre, izvodi akcije i zatim vraća rezultat. Treba imati na umu da funkcije uvijek vraćaju ili jednu vrijednost ili tablicu. Glavna svrha funkcija je jednostavno repliciranje zajedničkog zadatka. Funkcije možemo izgraditi jednom i možemo ih koristiti na više lokacija na temelju naših potreba. SQL Server ne dopušta korištenje funkcija za umetanje, brisanje ili ažuriranje zapisa u tablicama baze podataka.
Sljedeća su pravila za stvaranje funkcija SQL Servera:
- Funkcija mora imati naziv, a naziv ne može započeti posebnim znakom kao što su @, $, # ili drugi slični znakovi.
- SELECT naredbe su jedine koje rade s funkcijama.
- Možemo koristiti bilo gdje funkciju kao što su AVG, COUNT, SUM, MIN, DATE i druge funkcije s SELECT upitom u SQL-u.
- Kad god se funkcija pozove, ona se kompilira.
- Funkcije moraju vratiti vrijednost ili rezultat.
- Funkcije koriste samo ulazne parametre.
- Ne možemo koristiti naredbe TRY i CATCH u funkcijama.
Vrste funkcija
SQL Server kategorizira funkcije u dvije vrste:
niz.vrijednost
- Funkcije sustava
- Korisnički definirane funkcije
Opišimo obje vrste u detalje.
Funkcije sustava
Funkcije koje definira sustav poznate su kao funkcije sustava. Drugim riječima, sve ugrađene funkcije koje podržava poslužitelj nazivaju se funkcijama sustava. Ugrađene funkcije štede nam vrijeme dok obavljamo određeni zadatak. Ove vrste funkcija obično rade s SQL SELECT naredbom za izračunavanje vrijednosti i manipuliranje podacima.
Ovdje je popis nekih funkcija sustava koje se koriste u SQL Serveru:
- Funkcije niza (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funkcije datuma i vremena (datetime, datetime2, smalldatetime)
- Skupne funkcije (COUNT, MAX, MIN, SUM, AVG)
- Matematičke funkcije (ABS, POWER, PI, EXP, LOG)
- Funkcije rangiranja (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
Sljedeća slika prikazuje sve ugrađene funkcije baze podataka koje se koriste u SQL Serveru:
Korisnički definirane funkcije
Funkcije koje su kreiran od strane korisnika u bazi podataka sustava ili korisnički definiranoj bazi podataka poznate su kao korisnički definirane funkcije. UDF funkcije prihvaćaju parametre, izvode akcije i vraćaju rezultat. Ove nam funkcije pomažu da pojednostavimo naš razvoj tako što sažimamo složenu poslovnu logiku i činimo je dostupnom za ponovnu upotrebu bilo gdje na temelju potreba. Korisnički definirane funkcije znatno olakšavaju pisanje koda potrebnog za upit podataka. Oni također poboljšavaju čitljivost i funkcionalnost upita, kao i omogućuju drugim korisnicima ponavljanje istih postupaka.
SQL Server kategorizira korisnički definirane funkcije uglavnom u dvije vrste:
- Skalarne funkcije
- Funkcije s tabličnim vrijednostima
Ovdje su opisi ovih UDF funkcija.
Skalarne funkcije
Skalarna funkcija u SQL Serveru uvijek prihvaća parametre, bilo pojedinačne ili višestruke i vraća jednu vrijednost . Skalarne funkcije su korisne za pojednostavljenje našeg koda. Pretpostavimo da bismo mogli imati složeni izračun koji se pojavljuje u nizu upita. U tom slučaju možemo izgraditi skalarnu funkciju koja enkapsulira formulu i koristi je u svakom upitu umjesto u svakom upitu.
Sljedeća sintaksa ilustrira stvaranje skalarne funkcije u SQL Serveru:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Gore navedeni parametri sintakse opisani su u nastavku:
Prvo ćemo definirati naziv funkcije nakon STVORI FUNKCIJU ključne riječi. Naziv sheme nije obavezan. Ako nećemo definirati naziv sheme, SQL Server koristi zadanu shemu dbo . Zatim ćemo definirati popis parametri u zagradama. Treće, napisat ćemo izjave za funkciju, a zatim u POVRATAK naredbom, definirajte tip podataka povratne vrijednosti. Konačno, dodali smo naredbu RETURN za vraćanje vrijednosti unutar tijela funkcije.
Primjer
Ovaj primjer će stvoriti funkciju za izračunajte neto prodaju na temelju količine, cijene i vrijednosti popusta:
CREATE FUNCTION udfNet_Sales( @quantity INT, @price DEC(10,2), @discount DEC(3,2) ) RETURNS DEC(10,2) AS BEGIN RETURN @quantity * @price * (1 - @discount); END;
Sada možemo koristiti ovu funkciju za izračunavanje neto prodaje bilo kojeg prodajnog naloga u definiranoj tablici.
Sljedeći prikaz slike gdje možemo pronaći skalarne funkcije:
Skalarne funkcije možemo nazvati isto kao i ugrađenu funkciju u SQL Serveru. Na primjer, gornju funkciju udfNet_Sales možemo pozvati kao u nastavku:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
Izvršenje ove funkcije vratit će neto prodaju:
SQL Server nam također omogućuje za izmjenu skalarne funkcije korištenjem ALTER ključna riječ. Evo sintakse za to:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Možemo upotrijebiti naredbu u nastavku za uklanjanje skalarne funkcije iz baze podataka SQL Servera:
DROP FUNCTION [schema_name.]function_name;
Funkcije s tabličnim vrijednostima
Funkcije s tabličnim vrijednostima u SQL Serveru su korisnički definirana funkcija koji vraća podatke tipa tablice. Budući da je ova funkcija povratni tip je tablica , možemo ga koristiti na isti način kao što koristimo tablicu.
Funkciju tablične vrijednosti možemo kategorizirati u dvije vrste:
1. Umetnute funkcije tablice vrijednosti
Ova UDF funkcija vraća varijablu tablice na temelju radnje koju izvodi funkcija. Za određivanje vrijednosti varijable tablice trebala bi se koristiti jedna SELECT izjava.
Primjer
Primjer u nastavku stvorit će funkciju tablice vrijednosti i dohvatiti podatke tablice zaposlenika:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
U ovoj sintaksi, POVRATNA TABLICA navodi da će funkcija vratiti tablicu. Budući da nema POČETAK...KRAJ izjava, jednostavno traži podatke iz tablice zaposlenika. Također, ako nema nikakve parametre, bit će pozvan izravno.
Funkcije s tabličnim vrijednostima možemo pozvati pomoću IZ klauzula od IZABERI upit. Na primjer , možemo nazvati gore navedeno udf_GetEmployee funkcija kao ispod:
SELECT * FROM udf_GetEmployee();
Izvršenje ove funkcije vratit će rezultat u nastavku:
SQL Server nam također omogućuje izmjenu funkcija s tabličnim vrijednostima pomoću ključne riječi ALTER umjesto ključne riječi CREATE. Ostatak scenarija je isti.
Funkcije s tabličnim vrijednostima s više naredbi (MSTVF)
Ova UDF funkcija vraća varijablu tablice na temelju radnje koju obavlja funkcija. Može sadržavati jednu ili više izjava za dobivanje rezultata, a također je i funkcija koja vraća rezultat više izjava u tabličnom obliku. Korisno je jer možemo izvršiti više naredbi u ovoj funkciji i dobiti agregirane rezultate u vraćenu tablicu. Ovu funkciju možemo definirati korištenjem varijable tablice kao povratne vrijednosti. Unutar funkcije izvršavamo više upita i umećemo podatke u ovu varijablu tablice.
Sljedeći primjer stvara naziv funkcije 'VIŠE VRIJEDNOSTI' koji vraća ' @Zaposlenik' stol. Sadrži tri polja pod nazivom id, emp_name i plaća iz 'Zaposlenik' tablicu koristeći naredbu INSERT, a zatim koristi naredbu UPDATE za ažuriranje imena zaposlenika.
CREATE FUNCTION MULTIVALUED() RETURNS @Employee TABLE (id INT, emp_name VARCHAR(50), salary INT) AS BEGIN INSERT INTO @Employee SELECT E.id, E.emp_name, E.salary FROM Employee E; UPDATE @Employee SET emp_name = 'Graeme Smith' WHERE id = 3; RETURN END
Funkcije s tabličnim vrijednostima s više naredbi možemo pozvati pomoću klauzule FROM upita SELECT. Na primjer , gornju funkciju možemo pozvati na sljedeći način:
SELECT * FROM MULTIVALUED();
Izvršenje ove funkcije vratit će rezultat u nastavku:
Kada se trebaju koristiti funkcije s tabličnim vrijednostima?
Funkcije s tabličnim vrijednostima obično se koriste kao parametrizirani pogledi. Funkcije s tabličnim vrijednostima su fleksibilnije od pohranjenih procedura jer se mogu koristiti svugdje gdje se koriste tablice.
Zaključak
Ovaj članak objasnit će potpuni pregled funkcija koje se koriste u SQL Serveru. Ovdje smo naučili uglavnom dvije vrste funkcija koje se koriste u SQL Serveru: sistemske i korisnički definirane funkcije.