Pytanie Funkcja vs. procedura składowana w SQL Server


Uczę się funkcji i procedury przechowywanej przez dłuższy czas, ale nie wiem, dlaczego i kiedy powinienem użyć funkcji lub procedury przechowywanej. Wyglądają tak samo dla mnie, może dlatego, że jestem trochę początkująca.

Czy ktoś może mi powiedzieć, dlaczego?


658
2017-07-24 19:40


pochodzenie


venkatsqlinterview.blogspot.com/2011/05/... - Freelancer
wiki.answers.com/Q/... - Freelancer
searchsqlserver.techtarget.com/tip/... - Freelancer
co powiesz na prędkość? który z nich uruchamia to samo zapytanie szybciej? - AmiNadimi
@DineshDB - Edycje, które wprowadzają niepotrzebne formatowanie kodu na rzeczy, które nie są generalnie kodowane, nie są tutaj mile widziane. Proszę tego nie robić. Otrzymaliśmy skargi dotyczące tych zmian. - Brad Larson♦


Odpowiedzi:


Funkcje są obliczanymi wartościami i nie mogą wykonywać trwałych zmian środowiskowych na serwerze SQL Server (tzn. Nie można wprowadzać żadnych instrukcji INSERT ani UPDATE).

Funkcja może być używana inline w instrukcjach SQL, jeśli zwraca wartość skalarną, lub może być dołączona, jeśli zwraca zestaw wyników.

Punkt wart uwagi z komentarzy, które podsumowują odpowiedź. Dzięki @Sean K Anderson:

Funkcje są zgodne z definicją komputera, ponieważ MUSZĄ zwracać wartość i nie mogą zmieniać danych, które otrzymują jako parametry   (argumenty). Funkcje nie mogą niczego zmieniać, muszą   mieć przynajmniej jeden parametr i muszą zwracać wartość. Przechowywane   procsy nie muszą mieć parametru, mogą zmieniać obiekty bazy danych,   i nie muszą zwracać wartości.


555
2017-07-24 19:42



Zasadniczo DML nie jest dozwolony? - david blaine
Funkcje są zgodne z definicją komputera, ponieważ MUSZĄ zwracać wartość i nie mogą zmieniać danych, które otrzymują jako parametry (argumenty). Funkcje nie mogą niczego zmieniać, muszą mieć co najmniej jeden parametr i muszą zwracać wartość. Zapisane procsy nie muszą mieć parametru, mogą zmieniać obiektów bazy danych i nie muszą zwracać wartości. - Sean Anderson
W rzeczywistości można mieć instrukcje INSERT, UPDATE i DELETE w funkcji do modyfikowania lokalnych zmiennych tabel. - Ani
@Ani - Można utworzyć i zmodyfikować dowolną liczbę zmiennych lokalnych za pomocą funkcji, jednak nie można modyfikować niczego poza zakresem funkcji. - MyItchyChin
Funkcja @SeanKAnderson "musi mieć co najmniej jeden parametr" nie jest prawdziwa. - liang


Różnica między SP a UDF jest wymieniona poniżej:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

466
2017-10-15 17:00



Funkcje muszą zwracać jedną wartość lub zbiór. - Rafareino
Przyszło to 3 lata później, ale powinno być na wierzchu, ponieważ jest czytelne i obszerne. - DanteTheSmith


Funkcje i procedury przechowywane służą innym celom. Chociaż nie jest to najlepsza analogia, funkcje mogą być postrzegane dosłownie jak każda inna funkcja, której używałbyś w dowolnym języku programowania, ale przechowywane procy są bardziej podobne do pojedynczych programów lub skryptu wsadowego.

Funkcje zwykle mają wyjście i opcjonalnie wejścia. Dane wyjściowe można następnie wykorzystać jako dane wejściowe do innej funkcji (wbudowany program SQL Server, taki jak DATEDIFF, LEN itp.) Lub jako predykat zapytania SQL - np. SELECT a, b, dbo.MyFunction(c) FROM table lub SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Zapisane procy są używane do łączenia zapytań SQL w transakcję i interfejsu ze światem zewnętrznym. Ramy takie jak ADO.NET itp. Nie mogą bezpośrednio wywoływać funkcji, ale mogą wywoływać zapisany proces bezpośrednio.

Funkcje mają jednak ukryte niebezpieczeństwo: mogą być niewłaściwie używane i powodują raczej nieprzyjemne problemy z wydajnością: rozważ to zapytanie:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Gdzie MojaFunkcja jest zadeklarowana jako:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Co się dzieje tutaj, to funkcja MyFunction jest wywoływana dla każdego wiersza w tabeli MyTable. Jeśli MyTable ma 1000 wierszy, to jest to kolejne 1000 zapytań ad-hoc przeciwko bazie danych. Podobnie, jeśli funkcja zostanie wywołana, gdy jest określona w kolumnie spec, funkcja zostanie wywołana dla każdego wiersza zwróconego przez SELECT.

Musisz więc uważnie pisać funkcje. Jeśli wykonasz SELECT z tabeli w funkcji, musisz zadać sobie pytanie, czy można ją lepiej wykonać za pomocą JOIN w macierzystym zapisanym procrze lub w innym konstrukcie SQL (takim jak CASE ... WHEN ... ELSE ... KONIEC).


180
2017-07-24 20:06



Czy możesz rozwinąć "Frameworks takie jak ADO.NET, itp. Nie mogą bezpośrednio wywoływać funkcji"? Wykonałem funkcje z dostawcami danych ADO.NET bez żadnych problemów. - Ian Kemp
Musisz wywołać funkcję za pomocą instrukcji SELECT - funkcja nie może być wywołana jako niezależny fragment kodu sama w sobie - musi być wywołana jako część większej instrukcji SQL, nawet jeśli ta instrukcja SQL jest niczym więcej niż SELECT * from dbo.MyTableValuedFunction(). Z drugiej strony, Sprocs można wywołać bezpośrednio z ADO.NET przez ustawienie SqlCommand.CommandType do CommandType.StoredProcedure. - Chris J


Napisz funkcję zdefiniowaną przez użytkownika, gdy chcesz obliczyć i zwrócić wartość do wykorzystania w innych instrukcjach SQL; Napisz procedurę przechowywaną, gdy chcesz zamiast tego pogrupować możliwie złożony zestaw instrukcji SQL. W końcu są to dwa całkiem różne przypadki użycia!


52
2017-07-24 19:42



istnieją różne typy funkcji zdefiniowanych przez użytkownika. Skalarne zwracają tylko wartości; inne typy retrun zestawów wyników. - A-K


Różnice między procedurami przechowywanymi a funkcjami zdefiniowanymi przez użytkownika:

  • Procedury zapisane nie mogą być używane w instrukcjach Select.
  • Zapisane procedury obsługują funkcję odroczonego rozpoznawania nazw.
  • Procedury przechowywane są zwykle używane do wykonywania logiki biznesowej.
  • Procedury przechowywane mogą zwracać dowolny typ danych.
  • Procedury przechowywane mogą przyjmować większą liczbę parametrów wejściowych niż funkcje zdefiniowane przez użytkownika. Procedury przechowywane mogą mieć do 21 000 parametrów wejściowych.
  • Przechowywane procedury mogą wykonywać dynamiczny SQL.
  • Zapisane procedury obsługują obsługę błędów.
  • Niedeterministyczne funkcje mogą być używane w procedurach przechowywanych.

  • Funkcje zdefiniowane przez użytkownika mogą być używane w instrukcjach Select.
  • Funkcje zdefiniowane przez użytkownika nie obsługują odroczonego rozpoznawania nazw.
  • Zdefiniowane przez użytkownika funkcje są zwykle używane do obliczeń.
  • Funkcje zdefiniowane przez użytkownika powinny zwracać wartość.
  • Funkcje zdefiniowane przez użytkownika nie mogą zwracać obrazów.
  • Funkcje zdefiniowane przez użytkownika akceptują mniejszą liczbę parametrów wejściowych niż procedury przechowywane. Funkcje UDF mogą mieć do 1023 parametrów wejściowych.
  • Tabele tymczasowe nie mogą być używane w funkcjach zdefiniowanych przez użytkownika.
  • Funkcje zdefiniowane przez użytkownika nie mogą wykonywać dynamicznego SQL.
  • Funkcje zdefiniowane przez użytkownika nie obsługują obsługi błędów. RAISEERROR LUB @@ERROR nie są dozwolone w UDF.
  • Funkcje niedeterministyczne nie mogą być używane w UDF. Na przykład, GETDATE() nie mogą być używane w UDF.

51
2018-05-02 09:52



Aby zacytować @curiousBoy poniżej re. kolejną nieprzyznaną odpowiedź (przez @Ankit) (<- zobacz, jak to zrobiłem?;)): "Powinieneś podać referencję źródłową.blogs.msdn.microsoft.com/pradeepsvs/2014/10/08/...). Szanuj pracę, którą wykonują inni! " - Tom
Te blogi zostały napisane od 8 października 2014 r., A ta odpowiedź została napisana od 2 maja 2013 r. @Tom - Kumar Manish-PMP
@Code Rider: Ach, przepraszam! Nie mogę uwierzyć, że tego nie zauważyłem! Więc blog skopiował (lub kogoś innego) bez kredytu? - Tom


Podstawowa różnica

Funkcja musi zwrócić wartość, ale w procedurze przechowywanej jest opcjonalna (procedura może zwrócić zero lub n wartości).

Funkcje mogą mieć tylko parametry wejściowe, podczas gdy Procedury mogą mieć parametry wejściowe / wyjściowe.

Funkcja przyjmuje jeden parametr wejściowy jest obowiązkowy, ale procedura składowana może zająć o n parametrów wejściowych.

Funkcje można wywoływać z procedury, natomiast procedur nie można wywoływać z funkcji.

Advance Difference

Procedura pozwala na użycie instrukcji SELECT oraz DML (INSERT / UPDATE / DELETE), podczas gdy funkcja pozwala tylko na instrukcję SELECT.

Procedury nie mogą być wykorzystywane w instrukcji SELECT, podczas gdy funkcja może być osadzona w instrukcji SELECT.

Procedury przechowywane nie mogą być używane w instrukcjach SQL w dowolnym miejscu w sekcji WHERE / HAVING / SELECT, podczas gdy funkcja może być.

Funkcje zwracające tabele mogą być traktowane jako inny zestaw wierszy. Można go użyć w JOINach z innymi tabelami.

Funkcja Inline może być widokami, które pobierają parametry i mogą być używane w połączeniach JOIN i innych operacjach zestawu wierszy.

Wyjątkiem może być blok try-catch w procedurze, podczas gdy blok try-catch nie może być użyty w funkcji.

Możemy przejść do zarządzania transakcjami w procedurze, podczas gdy my nie możemy wejść do funkcji.

źródło


19
2018-02-28 10:55



Powinieneś podać referencję źródłową. To jest z dotnet-tricks.com/Tutorial/sqlserver/... . Szanuj pracę, którą wykonują inni! - curiousBoy
To nie powód, by nie podawać źródła. Możesz wspomnieć na końcu! - curiousBoy
Re. "Funkcja musi zwracać wartość, ale w procedurze przechowywanej jest opcjonalna ....": Chciałbym wyjaśnić, że: "Funkcje musi zwraca jedną i tylko jedną wartość (która musi być wykonana przez Returns słowo kluczowe i musi być skalarem lub typem tabeli), ale procedury przechowywane mogą opcjonalnie return: a) 1 Int wpisz Kod wyniku przez Return Oświadczenie i / lub b) 1+ Parametry (Włącznie. Cursor type) za pośrednictwem Output słowo kluczowe i / lub c) 1+ Zestaw wierszy przez Select Sprawozdania. Jeśli zostanie zwrócony tylko jeden zestaw wierszy, może on być użyty jako argument "execute_statement" instrukcji "Insert Into". " - Tom


funkcja zdefiniowana przez użytkownika jest ważnym narzędziem dostępnym dla programisty serwera sql. Możesz go użyć inline w takiej instrukcji SQL

SELECT a, lookupValue(b), c FROM customers 

gdzie lookupValue będzie UDF. Tego rodzaju funkcjonalność nie jest możliwa, gdy używana jest procedura składowana. W tym samym czasie nie możesz robić pewnych rzeczy w UDF. Podstawową rzeczą do zapamiętania tutaj jest to, że UDF:

  • nie może tworzyć stałych zmian
  • nie można zmienić danych

procedura składowana może zrobić te rzeczy.

Dla mnie wbudowane użycie UDF jest najważniejszym zastosowaniem UDF.


18
2017-07-24 20:04





Przechowywane procedury  są używane jako skrypty. Uruchamiają dla Ciebie szereg poleceń i możesz zaplanować ich uruchamianie o określonych porach.

Funkcje  są używane jako metody. Podajesz to coś i zwraca wynik. Powinien być mały i szybki - robi to w locie.


12
2018-03-13 19:55



To dobre podsumowanie tych dwóch, szybkich i brudnych sposobów myślenia o nich. - Eric Bishard
Rzeczywiście dobre podsumowanie. Inne odpowiedzi koncentrują się na teoretycznej różnicy dwóch, pozostawiając mi jednak niepewność, kiedy użyć tego, co w praktyce. - jf328


Procedura składowana:

  • Jest jak miniaturowy program w SQL Server.
  • Może być tak proste, jak instrukcja select, lub tak złożone, jak długie skrypt, który dodaje, usuwa, aktualizuje i / lub czyta dane z wielu tabele w bazie danych.
  • (Potrafi implementować pętle i kursory, z których obie pozwalają pracować mniejsze wyniki lub operacje wiersz po wierszu danych.)
  • Powinien zostać wywołany przy użyciu EXEC lub EXECUTE komunikat.
  • Zwraca zmienne tabeli, ale nie możemy ich użyć OUT parametr.
  • Obsługuje transakcje.

Funkcjonować:

  • Nie można używać do aktualizacji, usuwania ani dodawania rekordów do bazy danych.
  • Po prostu zwraca pojedynczą wartość lub wartość tabeli.
  • Może być używany tylko do wybierania rekordów. Jednak można go nazwać bardzo łatwo z poziomu standardowego SQL, takiego jak:

    SELECT dbo.functionname('Parameter1')
    

    lub

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
    
  • W przypadku prostych operacji wielokrotnego użytku, funkcje mogą uprościć kod. Tylko uważaj na siebie JOIN klauzule w twoich funkcjach. Jeżeli twój funkcja ma JOIN i wywołujesz go z innego wyboru instrukcja, która zwraca wiele wyników, to wywołanie funkcji będzie JOIN te tabele razem dla każdy linia zwrócona w zestawie wyników. Więc choć mogą one być pomocne w upraszczaniu niektórych elementów logiki, mogą być również wąskie gardło, jeśli nie są używane prawidłowo.

  • Zwraca wartości za pomocą OUT parametr.
  • Nie obsługuje transakcji.

6
2018-04-29 11:56