Funkcje w SQL Server to obiekty bazy danych zawierające plik zestaw instrukcji SQL do wykonania określonego zadania . Funkcja przyjmuje parametry wejściowe, wykonuje akcje, a następnie zwraca wynik. Należy zauważyć, że funkcje zawsze zwracają pojedynczą wartość lub tabelę. Głównym celem funkcji jest łatwe replikowanie typowego zadania. Funkcje możemy budować jednorazowo i używać ich w wielu lokalizacjach w zależności od naszych potrzeb. SQL Server nie pozwala na korzystanie z funkcji wstawiania, usuwania i aktualizacji rekordów w tabelach bazy danych.
rzutowanie typów i konwersja typów w Javie
Poniżej znajdują się zasady tworzenia funkcji SQL Server:
- Funkcja musi mieć nazwę, która nie może zaczynać się od znaku specjalnego, takiego jak @, $, # lub innych podobnych znaków.
- Instrukcje SELECT są jedynymi, które operują na funkcjach.
- Możemy użyć dowolnej funkcji, takiej jak AVG, COUNT, SUM, MIN, DATE i innych funkcji z zapytaniem SELECT w SQL.
- Ilekroć wywoływana jest funkcja, kompiluje się.
- Funkcje muszą zwracać wartość lub wynik.
- Funkcje korzystają wyłącznie z parametrów wejściowych.
- W funkcjach nie można używać instrukcji TRY i CATCH.
Rodzaje funkcji
SQL Server dzieli funkcje na dwa typy:
- Funkcje systemu
- Funkcje zdefiniowane przez użytkownika
Opiszemy szczegółowo oba typy.
Funkcje systemu
Funkcje zdefiniowane przez system nazywane są funkcjami systemowymi. Innymi słowy, wszystkie wbudowane funkcje obsługiwane przez serwer nazywane są funkcjami Systemu. Wbudowane funkcje oszczędzają nam czas podczas wykonywania konkretnego zadania. Funkcje tego typu zwykle współpracują z instrukcją SQL SELECT w celu obliczania wartości i manipulowania danymi.
Oto lista niektórych funkcji systemowych używanych w SQL Server:
- Funkcje łańcuchowe (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funkcje daty i godziny (datetime, datetime2, smalldatetime)
- Funkcje agregujące (COUNT, MAX, MIN, SUM, AVG)
- Funkcje matematyczne (ABS, MOC, PI, EXP, LOG)
- Funkcje rankingowe (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
Poniższy rysunek przedstawia wszystkie wbudowane funkcje bazodanowe używane w SQL Server:
Funkcje zdefiniowane przez użytkownika
Funkcje, które są utworzone przez użytkownika w bazie danych systemu lub w bazie danych zdefiniowanej przez użytkownika, nazywane są funkcjami zdefiniowanymi przez użytkownika. Funkcje UDF akceptują parametry, wykonują akcje i zwracają wynik. Funkcje te pomagają nam uprościć rozwój poprzez hermetyzację złożonej logiki biznesowej i udostępnienie jej do ponownego użycia w dowolnym miejscu w zależności od potrzeb. Funkcje zdefiniowane przez użytkownika znacznie ułatwiają pisanie kodu potrzebnego do wykonywania zapytań o dane. Poprawiają także czytelność i funkcjonalność zapytań, a także umożliwiają innym użytkownikom replikowanie tych samych procedur.
SQL Server dzieli funkcje zdefiniowane przez użytkownika głównie na dwa typy:
- Funkcje skalarne
- Funkcje o wartościach tabelarycznych
Poniżej znajdują się opisy tych funkcji UDF.
Funkcje skalarne
Funkcja skalarna w SQL Server zawsze akceptuje parametry, pojedyncze lub wielokrotne i zwraca pojedynczą wartość . Funkcje skalarne są przydatne w uproszczeniu naszego kodu. Załóżmy, że mamy złożone obliczenia pojawiające się w wielu zapytaniach. W takim przypadku możemy zbudować funkcję skalarną, która hermetyzuje formułę i używa jej w każdym zapytaniu, a nie w każdym zapytaniu.
Poniżej przedstawiono składnię ilustrującą tworzenie funkcji skalarnej w SQL Server:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Powyższe parametry składni opisano poniżej:
Najpierw zdefiniujemy nazwę funkcji po UTWÓRZ FUNKCJĘ słowa kluczowe. Nazwa schematu jest opcjonalna. Jeśli nie zdefiniujemy nazwy schematu, SQL Server użyje schematu domyślnego dbo . Następnie zdefiniujemy listę parametry ujęte w nawiasy. Po trzecie, napiszemy instrukcje dla funkcji, a następnie w ZWROTY instrukcji, zdefiniuj typ danych zwracanej wartości. Na koniec dodaliśmy instrukcję RETURN, która zwraca wartość wewnątrz treści funkcji.
Przykład
W tym przykładzie zostanie utworzona funkcja obliczyć sprzedaż netto na podstawie ilości, ceny i wartości rabatu:
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 możemy użyć tej funkcji do obliczenia sprzedaży netto dowolnego zamówienia sprzedaży w zdefiniowanej tabeli.
Poniższy obraz, na którym możemy znaleźć funkcje skalarne:
Funkcje skalarne możemy wywołać w taki sam sposób, jak funkcję wbudowaną w SQL Server. Na przykład możemy wywołać powyższą funkcję udfNet_Sales jak poniżej:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
Wykonanie tej funkcji zwróci sprzedaż netto:
SQL Server również nam na to pozwala aby zmodyfikować funkcję skalarną za pomocą ALTER słowo kluczowe. Oto składnia, aby to zrobić:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Możemy użyć poniższej instrukcji, aby usunąć funkcję skalarną z bazy danych SQL Server:
DROP FUNCTION [schema_name.]function_name;
Funkcje o wartościach tabelarycznych
Funkcje z wartościami przechowywanymi w tabeli w SQL Server to funkcja zdefiniowana przez użytkownika która zwraca dane typu tabela. Ponieważ ta funkcja typem zwracanym jest tabela , możemy go używać w taki sam sposób, jak używamy tabeli.
Funkcję zwracaną w tabeli możemy podzielić na dwa typy:
1. Wbudowane funkcje tabelaryczne
Ta funkcja UDF zwraca zmienną tabeli na podstawie akcji wykonanej przez tę funkcję. Do określenia wartości zmiennej tabeli należy użyć pojedynczej instrukcji SELECT.
Przykład
Poniższy przykład utworzy funkcję tabeli-wartości i pobierze dane z tabeli pracowników:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
W tej składni, TABELA ZWROTÓW określa, że funkcja zwróci tabelę. Ponieważ nie ma POCZĄTEK...KOŃC instrukcji, po prostu wysyła zapytanie o dane z tabeli pracowników. Ponadto, jeśli nie ma żadnych parametrów, zostanie wywołany bezpośrednio.
Funkcje z wartościami przechowywanymi w tabeli możemy wywoływać za pomocą metody Z klauzula z WYBIERAĆ zapytanie. Na przykład , możemy nazwać powyższym udf_GetEmployee działać jak poniżej:
przekonwertuj ciąg na znak
SELECT * FROM udf_GetEmployee();
Wykonanie tej funkcji zwróci poniższy wynik:
SQL Server pozwala nam także modyfikować funkcje zwracające wartości tabelowe za pomocą słowa kluczowego ALTER zamiast słowa kluczowego CREATE. Reszta scenariusza jest taka sama.
Funkcje z wartościami przechowywanymi w tabeli zawierające wiele instrukcji (MSTVF)
Ta funkcja UDF zwraca zmienną tabeli na podstawie akcji wykonywanej przez funkcję. Może zawierać jedną lub wiele instrukcji generujących wynik, a także jest funkcją zwracającą wynik wielu instrukcji w formie tabelarycznej. Jest to przydatne, ponieważ w tej funkcji możemy wykonać wiele instrukcji i uzyskać zagregowane wyniki w zwróconej tabeli. Możemy zdefiniować tę funkcję, używając zmiennej tabeli jako wartości zwracanej. Wewnątrz funkcji wykonujemy wiele zapytań i wstawiamy dane do tej zmiennej tabeli.
Poniższy przykład tworzy nazwę funkcji „WIELOWARTOŚCIOWE” to zwraca ' @Pracownik' tabela. Zawiera trzy pola o nazwach id, emp_name i wynagrodzenie z 'Pracownik' tabeli za pomocą instrukcji INSERT, a następnie za pomocą instrukcji UPDATE zaktualizować nazwisko pracownika.
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
Możemy wywołać wieloinstrukcyjne funkcje z wartościami przechowywanymi w tabeli, używając klauzuli FROM zapytania SELECT. Na przykład , możemy wywołać powyższą funkcję jak poniżej:
SELECT * FROM MULTIVALUED();
Wykonanie tej funkcji zwróci poniższy wynik:
Kiedy należy używać funkcji zwracających tabelę?
Funkcje z wartościami przechowywanymi w tabeli są powszechnie używane jako widoki sparametryzowane. Funkcje z wartościami przechowywanymi w tabelach są bardziej elastyczne niż procedury składowane, ponieważ można ich używać wszędzie tam, gdzie używane są tabele.
Wniosek
W tym artykule wyjaśniono pełny przegląd funkcji używanych w SQL Server. Tutaj poznaliśmy głównie dwa rodzaje funkcji używanych w SQL Server: funkcje systemowe i funkcje zdefiniowane przez użytkownika.