Funkcie v SQL Server sú databázové objekty, ktoré obsahujú a súbor príkazov SQL na vykonanie konkrétnej úlohy . Funkcia akceptuje vstupné parametre, vykoná akcie a potom vráti výsledok. Mali by sme si uvedomiť, že funkcie vždy vracajú buď jednu hodnotu alebo tabuľku. Hlavným účelom funkcií je jednoducho replikovať bežnú úlohu. Funkcie môžeme zostaviť raz a môžeme ich použiť na viacerých miestach podľa našich potrieb. SQL Server neumožňuje používanie funkcií na vkladanie, mazanie alebo aktualizáciu záznamov v databázových tabuľkách.
Nasledujú pravidlá pre vytváranie funkcií SQL Server:
- Funkcia musí mať názov a názov nemôže začínať špeciálnym znakom, ako napríklad @, $, # alebo inými podobnými znakmi.
- Príkazy SELECT sú jediné, ktoré pracujú s funkciami.
- Funkciu môžeme použiť kdekoľvek ako AVG, COUNT, SUM, MIN, DATE a ďalšie funkcie s dotazom SELECT v SQL.
- Kedykoľvek je funkcia zavolaná, skompiluje sa.
- Funkcie musia vrátiť hodnotu alebo výsledok.
- Funkcie používajú iba vstupné parametre.
- Vo funkciách nemôžeme použiť príkazy TRY a CATCH.
Typy funkcií
SQL Server kategorizuje funkcie do dvoch typov:
nekonečná slučka
- Systémové funkcie
- Používateľom definované funkcie
Poďme si oba typy podrobne popísať.
Systémové funkcie
Funkcie, ktoré sú definované systémom, sú známe ako systémové funkcie. Inými slovami, všetky vstavané funkcie podporované serverom sa označujú ako systémové funkcie. Vstavané funkcie nám šetria čas pri vykonávaní konkrétnej úlohy. Tieto typy funkcií zvyčajne pracujú s príkazom SQL SELECT na výpočet hodnôt a manipuláciu s údajmi.
Tu je zoznam niektorých systémových funkcií používaných v SQL Server:
- Funkcie reťazca (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funkcie dátumu a času (datetime, datetime2, smalldatetime)
- Súhrnné funkcie (COUNT, MAX, MIN, SUM, AVG)
- Matematické funkcie (ABS, POWER, PI, EXP, LOG)
- Hodnotiace funkcie (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
Nasledujúci obrázok zobrazuje všetky vstavané databázové funkcie používané v SQL Server:
Používateľom definované funkcie
Funkcie, ktoré sú vytvorené používateľom v systémovej databáze alebo užívateľom definovanej databáze sú známe ako užívateľom definované funkcie. Funkcie UDF prijímajú parametre, vykonávajú akcie a vracajú výsledok. Tieto funkcie nám pomáhajú zjednodušiť náš vývoj zapuzdrením komplexnej obchodnej logiky a jej sprístupnením na opätovné použitie kdekoľvek na základe potrieb. Používateľom definované funkcie značne zjednodušujú písanie kódu potrebného na dopytovanie údajov. Zlepšujú tiež čitateľnosť a funkčnosť dotazov a umožňujú ostatným používateľom replikovať rovnaké postupy.
SQL Server kategorizuje užívateľom definované funkcie hlavne do dvoch typov:
- Skalárne funkcie
- Funkcie s tabuľkovou hodnotou
Tu sú popisy týchto funkcií UDF.
Skalárne funkcie
Skalárna funkcia v SQL Server vždy akceptuje parametre, buď jeden alebo viacero, a vráti jednu hodnotu . Skalárne funkcie sú užitočné pri zjednodušení nášho kódu. Predpokladajme, že by sme mohli mať zložitý výpočet, ktorý sa objaví v množstve dopytov. V takom prípade môžeme vytvoriť skalárnu funkciu, ktorá zapuzdruje vzorec a používa ho v každom dotaze namiesto v každom dotaze.
Nasledujúca syntax ilustruje vytvorenie skalárnej funkcie na serveri SQL Server:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Vyššie uvedené parametre syntaxe sú popísané nižšie:
Najprv zadefinujeme názov funkcie za VYTVORIŤ FUNKCIU Kľúčové slová. Názov schémy je voliteľný. Ak nebudeme definovať názov schémy, SQL Server použije predvolenú schému dbo . Ďalej definujeme zoznam parametre v zátvorkách. Po tretie, napíšeme príkazy pre funkciu a potom v VRÁTKY definujte dátový typ návratovej hodnoty. Nakoniec sme pridali príkaz RETURN, ktorý vráti hodnotu v tele funkcie.
Príklad
Tento príklad vytvorí funkciu pre vypočítať čisté tržby na základe množstva, ceny a hodnoty zľavy:
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;
Teraz môžeme použiť túto funkciu na výpočet čistého predaja akejkoľvek zákazky odberateľa v definovanej tabuľke.
Nasledujúci obrázok zobrazuje, kde môžeme nájsť skalárne funkcie:
Skalárne funkcie môžeme nazvať rovnako ako vstavanú funkciu v SQL Serveri. Napríklad, môžeme zavolať vyššie uvedenú funkciu udfNet_Sales, ako je uvedené nižšie:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
Vykonaním tejto funkcie sa vrátia čisté tržby:
SQL Server nám tiež umožňuje na úpravu skalárnej funkcie pomocou ALTER kľúčové slovo. Tu je syntax, ako to urobiť:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Na odstránenie skalárnej funkcie z databázy SQL Server môžeme použiť nasledujúci príkaz:
DROP FUNCTION [schema_name.]function_name;
Funkcie s tabuľkovou hodnotou
Tabuľkové funkcie v SQL Server sú užívateľsky definovaná funkcia ktorý vracia údaje typu tabuľky. Keďže táto funkcia je návratový typ je tabuľka , môžeme ho použiť rovnakým spôsobom, ako používame tabuľku.
Tabuľkovú funkciu môžeme kategorizovať do dvoch typov:
1. Funkcie Inline Table-Values
Táto funkcia UDF vracia premennú tabuľky na základe akcie vykonanej funkciou. Na určenie hodnoty premennej tabuľky by sa mal použiť jeden príkaz SELECT.
Príklad
Nasledujúci príklad vytvorí funkciu tabuľkových hodnôt a načíta údaje tabuľky zamestnancov:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
V tejto syntaxi je TABUĽKA VRÁTENIA určuje, že funkcia vráti tabuľku. Keďže neexistuje žiadna ZAČIATOK...KONIEC výpis, jednoducho si vyžiada údaje z tabuľky zamestnancov. Taktiež, ak nemá žiadne parametre, zavolá sa priamo.
Funkcie s tabuľkovou hodnotou môžeme zavolať pomocou OD doložka z SELECT dopyt. Napríklad , môžeme nazvať vyššie udf_GetEmployee fungovať ako nižšie:
arp - príkaz
SELECT * FROM udf_GetEmployee();
Vykonaním tejto funkcie sa vráti výsledok uvedený nižšie:
SQL Server nám tiež umožňuje upravovať funkcie s hodnotou tabuľky pomocou kľúčového slova ALTER namiesto kľúčového slova CREATE. Zvyšok scenára je rovnaký.
Funkcie s tabuľkovou hodnotou s viacerými príkazmi (MSTVF)
Táto funkcia UDF vráti premennú tabuľky na základe činnosti vykonávanej funkciou. Môže obsahovať jeden alebo viacero príkazov na vytvorenie výsledku a je to tiež funkcia, ktorá vracia výsledok viacerých príkazov v tabuľkovej forme. Je to užitočné, pretože v tejto funkcii môžeme vykonať viacero príkazov a získať agregované výsledky do vrátenej tabuľky. Túto funkciu môžeme definovať pomocou premennej tabuľky ako návratovej hodnoty. Vo vnútri funkcie vykonáme viacero dotazov a vložíme údaje do tejto premennej tabuľky.
Nasledujúci príklad vytvorí názov funkcie „MULTIHODNOTA“ ktorý vráti ' @Employee' tabuľky. Obsahuje tri polia s názvom id, emp_name a plat z 'zamestnanec' pomocou príkazu INSERT a potom pomocou príkazu UPDATE aktualizujte meno zamestnanca.
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
Pomocou klauzuly FROM v dotaze SELECT môžeme volať funkcie s tabuľkovou hodnotou s viacerými príkazmi. Napríklad , môžeme zavolať vyššie uvedenú funkciu, ako je uvedené nižšie:
SELECT * FROM MULTIVALUED();
Vykonaním tejto funkcie sa vráti výsledok uvedený nižšie:
Kedy by sa mali používať funkcie s tabuľkovou hodnotou?
Funkcie s tabuľkovou hodnotou sa bežne používajú ako parametrizované zobrazenia. Funkcie s hodnotou tabuľky sú flexibilnejšie ako uložené procedúry, pretože ich možno použiť všade, kde sa používajú tabuľky.
Záver
Tento článok vysvetlí úplný prehľad funkcií používaných v SQL Server. Tu sme sa naučili hlavne dva typy funkcií používaných v SQL Server: systémové a užívateľom definované funkcie.