Jakie są różnice między „procedurami przechowywanymi” a „funkcjami przechowywanymi”?

W komentarzu do tego pytania wspomniano, że istnieje niewielka różnica w „Stored Procedrues” i „Stored Funtions” w PostgreSQL.

Komentarz prowadzi do artykułu na Wikipedii, ale niektóre z nich wydają się nie mieć zastosowania (np. Że można ich użyć w SELECToświadczeniu).

Sama składnia wydaje się nieco myląca:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
       [...]
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Tworzysz, FUNCTIONale nazywaj to jako PROCEDURE.

Jaka jest różnica między tymi dwoma?

Oficjalnie PostgreSQL ma tylko „funkcje”. Funkcje wyzwalające są czasami nazywane „procedurami wyzwalającymi”, ale użycie to nie ma wyraźnego znaczenia. Wewnętrznie funkcje są czasami nazywane procedurami, na przykład w katalogu systemowym pg_proc. To pozostałość po PostQUEL. Wszelkie funkcje, które niektóre osoby (prawdopodobnie z doświadczeniem w różnych systemach baz danych) mogą skojarzyć z procedurami, takie jak ich znaczenie w zapobieganiu wstrzyknięciom SQL lub wykorzystaniu parametrów wyjściowych, mają również zastosowanie do funkcji istniejących w PostgreSQL.

Teraz, gdy ludzie ze społeczności PostgreSQL mówią o „procedurach przechowywanych” lub „prawdziwych procedurach przechowywanych”, często mają one na myśli hipotetyczną funkcję obiektu podobnego do funkcji, który może uruchamiać i zatrzymywać transakcje w jego ciele, coś, czego obecne funkcje nie mogą robić. W tym kontekście wydaje się, że użycie terminu „procedura składowana” jest analogiczne do innych produktów bazodanowych. Zobacz ten wątek listy mailingowej, aby uzyskać niejasny pomysł.

W praktyce jednak to rozróżnienie funkcji i procedury pod względem ich możliwości kontrolowania transakcji nie jest powszechnie akceptowane, a na pewno wielu programistów bez stronniczości bazy danych przyjmie interpretację procedury podobną do Pascala jako funkcję bez wartości zwracanej. (Wydaje się, że standard SQL ma środkowy punkt, ponieważ procedura domyślnie ma inne zachowanie transakcyjne niż funkcja, ale można to dostosować do obiektu.) Tak więc w każdym przypadku, a zwłaszcza, gdy patrzy się na pytania na temat wymiany stosów za pomocą bardzo zróżnicowana publiczność, należy unikać zbytniego zakładania i używać jaśniejszych terminów lub definiować oczekiwane właściwości.

Pod względem DDL Postgres nie ma obiektów procedur, tylko funkcje. Funkcje Postgres mogą zwracać wartości lub unieważniać, więc przyjmują role zarówno funkcji, jak i procedur w innych RDBMS. Słowo „procedura” w create triggerodnosi się do funkcji.

Pod względem dokumentacji Postgres'a „procedura” jest także synonimem obiektu bazy danych zwanego funkcją, np .: „ Procedura wyzwalająca jest tworzona za pomocą polecenia CREATE FUNCTION ”.

„Procedury” wyzwalacza mają określone reguły: należy je zadeklarować jako funkcję bez argumentów i typu wyzwalacza zwracanego . Przykład tutaj .

Terminy „procedura składowana” i „funkcja składowana” są używane zamiennie w PostgreSQL i ogólnie uważa się, że oznaczają to samo. Inne bazy danych mogą rozróżniać procedurę i funkcję (podobnie jak VB rozróżnia podprogramy i funkcje).

Tak długo, jak funkcja w PostgreSQL zwraca coś, co przypomina tabelę, możesz użyć wyniku tej funkcji tak, jakby to była tabela standardowa. CREATE TRIGGERSkładnia jest nieco mylące, ale podejrzewam, że może to być na miejscu przed standard ANSI została sfinalizowana. Mam tylko kopię SQL: 2003, więc nie mogę zrobić nic więcej niż spekulować, dlaczego nomenklatura jest dziwna.

Wersja TL; DR: z PostgreSQL „procedura” jest równoważna „funkcji”.

W MSSQL procedura składowana jest wstępnie skompilowanym zestawem poleceń SQL.
Procedura składowana:

 - może mieć wiele parametrów wejściowych i wyjściowych
 - może być użyty do modyfikacji tabel / struktur / danych bazy danych
 - zwykle nie są używane w instrukcjach wstawiania / aktualizacji / usuwania / wyboru

  - może mieć wiele parametrów wejściowych, ale zwraca tylko jedną wartość (tj. konkatenację łańcucha)
  - może zaakceptować zestaw jako dane wejściowe, zwrócić pojedynczą wartość (tj. dbo.FindLargestPig (ListOfPigs))
  - zwraca tabelę (tzn. wybierz * z dbo.ExplodeString („to jest lista słów”))
  - może być stosowany w instrukcjach wyboru / wstawiania / aktualizacji / usuwania
  - NIE MOŻNA używać do modyfikowania tabel / struktur / danych bazy danych

Krótka odpowiedź brzmi: funkcja zwraca wartość, ale procedura nie.

To rozróżnienie było obecne w modułach przechowywanych trwałych (SQL / PSM), które zostały zaproponowane dla SQL 1992. Nie wiem, czy SQL / PSM kiedykolwiek spełnił standardy.

Porównując przyjętą odpowiedź z abstrakcyjnego poziomu pojęciowego, rozumiem różnicę od funkcjonalności i perspektywy wejścia / wyjścia. Poniżej użyłem sp i f do przedstawienia odpowiednio procedury składowanej i funkcji.

1. Użyj w wyrażeniu: sp nie może być użyte w wyrażeniu, podczas gdy funkcja może, co oznacza, że ​​możesz użyć zwróconej wartości zf wewnątrz innych instrukcji, takich jak

   select * 
   from table 
   where col_a < (select col_A from f())

2. zwraca wartość: sp nie zwraca automatycznie wartości, chyba że określisz typ zwracany przez rekursor , otwórz i zwróć kursor; f zwraca wynik w ostatniej instrukcji, w której osadzona jest klauzula „return”, podobnie jak klauzula select .

3. zwraca pojedyncze / wielokrotne zestawy wyników: tutaj zestawy wyników odnoszą się do listy wyników, które mogą różnić się formatem, takich jak zestaw pojedynczej liczby całkowitej, tablicy tekstowej i dwóch tabel. sp może zwracać wiele zestawów, pod warunkiem, że określisz typ zwracany przez Refursor, otwieraj i zwracaj kursor. Jednak f może zwrócić tylko jeden typ zestawu.

Zwykle procedury przechowywane są używane do modyfikowania danych lub struktury bazy danych, w których wartość zwracana nie jest potrzebna, takich jak usuwanie, aktualizacja, upuszczanie itp .; lub sytuacje, w których wymaganych jest wiele zestawów wyników. Z drugiej strony funkcja jest wybierana głównie dla zwykłych zapytań.

Aby uzyskać więcej informacji na temat mojego wyjaśnienia, zapoznaj się z tym linkiem: Procedury i funkcje przechowywane w PostgreSQL