Procedura składowana MySQL a funkcja, których użyć, kiedy?

Patrzę na procedury i funkcje składowane MySQL. Jaka jest prawdziwa różnica?

Wydają się być podobne, ale funkcja ma więcej ograniczeń.

Prawdopodobnie się mylę, ale wygląda na to, że procedura składowana może zrobić wszystko, a więcej funkcja składowana. Dlaczego / kiedy miałbym używać procedury, a nie funkcji?

Nie możesz mieszać procedur składowanych ze zwykłym SQL, podczas gdy w przypadku funkcji składowanych możesz.

np. SELECT get_foo(myColumn) FROM mytablenie jest poprawne, jeśli get_foo()jest procedurą, ale możesz to zrobić, jeśli get_foo()jest funkcją. Cena jest taka, że ​​funkcje mają więcej ograniczeń niż procedura.

Najbardziej ogólna różnica między procedurami i funkcjami polega na tym, że są one wywoływane w różny sposób i do różnych celów:

1. Procedura nie zwraca wartości. Zamiast tego jest wywoływany za pomocą instrukcji CALL w celu wykonania operacji, takiej jak modyfikacja tabeli lub przetwarzanie pobranych rekordów.
2. Funkcja jest wywoływana w wyrażeniu i zwraca pojedynczą wartość bezpośrednio do obiektu wywołującego, który ma zostać użyty w wyrażeniu.
3. Nie można wywołać funkcji za pomocą instrukcji CALL ani nie można wywołać procedury w wyrażeniu.

Składnia tworzenia rutynowych procedur różni się nieco w przypadku procedur i funkcji:

1. Parametry procedury można zdefiniować jako tylko wejściowe, tylko wyjściowe lub oba. Oznacza to, że procedura może przekazać wartości z powrotem do obiektu wywołującego przy użyciu parametrów wyjściowych. Dostęp do tych wartości można uzyskać w instrukcjach następujących po instrukcji CALL. Funkcje mają tylko parametry wejściowe. W rezultacie, chociaż zarówno procedury, jak i funkcje mogą mieć parametry, deklaracja parametrów procedury różni się od deklaracji funkcji.

2. Funkcje zwracają wartość, więc w definicji funkcji musi znajdować się klauzula RETURNS wskazująca typ danych wartości zwracanej. Ponadto w treści funkcji musi znajdować się co najmniej jedna instrukcja RETURN, aby zwrócić wartość do obiektu wywołującego. RETURNS i RETURN nie pojawiają się w definicjach procedur.

   • Aby wywołać procedurę składowaną, użyj CALL statement. Aby wywołać zapisaną funkcję, odwołaj się do niej w wyrażeniu. Funkcja zwraca wartość podczas obliczania wyrażenia.

   • Procedura jest wywoływana za pomocą instrukcji CALL i może przekazywać wartości tylko przy użyciu zmiennych wyjściowych. Funkcję można wywołać z wnętrza instrukcji, tak jak każdą inną funkcję (to znaczy wywołując nazwę funkcji) i może zwrócić wartość skalarną.

   • Określenie parametru jako IN, OUT lub INOUT jest ważne tylko dla PROCEDURY. W przypadku FUNKCJI parametry są zawsze traktowane jako parametry IN.

   Jeśli przed nazwą parametru nie podano słowa kluczowego, domyślnie jest to parametr IN. Parametry zapisanych funkcji nie są poprzedzone przez IN, OUT ani INOUT. Wszystkie parametry funkcji są traktowane jako parametry IN.

Aby zdefiniować procedurę składowaną lub funkcję, użyj odpowiednio opcji CREATE PROCEDURE lub CREATE FUNCTION:

CREATE PROCEDURE proc_name ([parameters])
 [characteristics]
 routine_body


CREATE FUNCTION func_name ([parameters])
 RETURNS data_type       // diffrent
 [characteristics]
 routine_body

Rozszerzenie MySQL dla procedury składowanej (nie funkcji) polega na tym, że procedura może generować zestaw wyników lub nawet wiele zestawów wyników, które obiekt wywołujący przetwarza w taki sam sposób, jak wynik instrukcji SELECT. Jednak zawartości takich zestawów wyników nie można używać bezpośrednio w wyrażeniu.

Procedury składowane (odnoszące się zarówno do procedur składowanych, jak i funkcji składowanych) są powiązane z określoną bazą danych, podobnie jak tabele lub widoki. Usunięcie bazy danych powoduje również usunięcie wszystkich procedur przechowywanych w bazie danych.

Procedury i funkcje składowane nie współużytkują tej samej przestrzeni nazw. W bazie danych można mieć procedurę i funkcję o tej samej nazwie.

W procedurach składowanych można używać dynamicznego języka SQL, ale nie w funkcjach lub wyzwalaczach.

Instrukcje przygotowane w języku SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) mogą być używane w procedurach składowanych, ale nie w funkcjach składowanych ani wyzwalaczach. W związku z tym przechowywane funkcje i wyzwalacze nie mogą używać dynamicznego języka SQL (w którym instrukcje są konstruowane jako łańcuchy, a następnie wykonywane). (Dynamiczny SQL w procedurach przechowywanych w MySQL)

Kilka bardziej interesujących różnic między PROCEDURĄ FUNKCJONALNĄ a ZAPISANĄ:

1. ( Ten punkt jest kopiowany z postu na blogu ) . Procedura składowana jest prekompilowanym planem wykonania, gdzie funkcje nie są. Funkcja przeanalizowana i skompilowana w czasie wykonywania. Procedury składowane, przechowywane jako pseudokod w bazie danych, czyli w formie skompilowanej.

2. ( Nie jestem pewien do tego punktu. )
   Procedura składowana ma zabezpieczenia i zmniejsza ruch w sieci, a także możemy wywołać procedurę składowaną w dowolnym nie. aplikacji naraz. odniesienie

3. Funkcje są zwykle używane do obliczeń, w których procedury są zwykle używane do wykonywania logiki biznesowej.

4. Funkcje nie mogą wpływać na stan bazy danych (instrukcje, które wykonują jawne lub niejawne zatwierdzanie lub wycofywanie zmian, są w funkcji niedozwolone) Zważywszy, że procedury składowane mogą wpływać na stan bazy danych za pomocą zatwierdzania itp.
   Odniesienie: J.1. Ograniczenia dotyczące procedur przechowywanych i wyzwalaczy

5. Funkcje nie mogą używać instrukcji FLUSH , podczas gdy procedury składowane mogą to robić.

6. Funkcje składowane nie mogą być cykliczne, podczas gdy procedury składowane mogą. Uwaga: Rekurencyjne procedury składowane są domyślnie wyłączone, ale można je włączyć na serwerze, ustawiając zmienną systemową serwera max_sp_recursion_depth na wartość różną od zera. Aby uzyskać więcej informacji, zobacz Sekcja 5.2.3, „Zmienne systemowe” .

7. W ramach funkcji przechowywanej lub wyzwalacza nie można modyfikować tabeli, która jest już używana (do odczytu lub zapisu) przez instrukcję, która wywołała funkcję lub wyzwalacz. Dobry przykład: Jak zaktualizować tę samą tabelę podczas usuwania w MYSQL?

Uwaga : chociaż niektóre ograniczenia zwykle dotyczą funkcji składowanych i wyzwalaczy, ale nie procedur składowanych, ograniczenia te dotyczą procedur składowanych, jeśli są wywoływane z funkcji składowanej lub wyzwalacza. Na przykład, chociaż można użyć FLUSH w procedurze składowanej, takiej procedury składowanej nie można wywołać z funkcji składowanej lub wyzwalacza.

Jedną istotną różnicą jest to, że możesz uwzględnić funkcję w zapytaniach SQL, ale procedury składowane mogą być wywoływane tylko za pomocą CALLinstrukcji:

Przykład UDF:

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE names (id int, name varchar(20));
INSERT INTO names VALUES (1, 'Bob');
INSERT INTO names VALUES (2, 'John');
INSERT INTO names VALUES (3, 'Paul');

SELECT hello(name) FROM names;
+--------------+
| hello(name)  |
+--------------+
| Hello, Bob!  |
| Hello, John! |
| Hello, Paul! |
+--------------+
3 rows in set (0.00 sec)

Przykład Sproc:

delimiter //

CREATE PROCEDURE simpleproc (IN s CHAR(100))
BEGIN
   SELECT CONCAT('Hello, ', s, '!');
END//
Query OK, 0 rows affected (0.00 sec)

delimiter ;

CALL simpleproc('World');
+---------------------------+
| CONCAT('Hello, ', s, '!') |
+---------------------------+
| Hello, World!             |
+---------------------------+
1 row in set (0.00 sec)

W zapytaniu można używać funkcji przechowywanej. Możesz następnie zastosować go do każdego wiersza lub w ramach klauzuli WHERE.

Procedura jest wykonywana przy użyciu zapytania CALL.

Procedurę składowaną można wywołać rekurencyjnie, ale funkcji składowanej nie