MySQL хранимая процедура vs функция, которую я буду использовать, когда?

наиболее общее различие между процедурами и функциями состоит в том, что они вызываются по-разному и для разных целей:

1. процедура не возвращает значение. Вместо этого он вызывается с помощью инструкции CALL для выполнения такой операции, как изменение таблицы или обработка полученных записей.
2. функция вызывается в выражении и возвращает одно значение непосредственно вызывающему объекту, который будет использоваться в выражении. выражение.
3. вы не можете вызвать функцию с оператором вызова, а также не можете вызвать процедуру в выражении.

синтаксис для рутинного создания несколько отличается для процедур и функций:

1. параметры процедуры могут быть определены как только для ввода, только для вывода или оба. Это означает, что процедура может передавать значения обратно с помощью выходных параметров. Эти значения можно найти в инструкции по оператор Call. Функции имеют только входные параметры. В результате, хотя и процедуры, и функции могут иметь параметры, объявление параметров процедуры отличается от объявления параметров для функций.

2. функции возвращают значение, поэтому в определении функции должно быть предложение RETURNS для указания типа данных возвращаемого значения. Кроме того, в теле функции должен быть хотя бы один оператор RETURN для возврата значения вызывающему объекту. Возврат и возврат не отображаются в определение процедуры.

   • чтобы вызвать хранимую процедуру, используйте CALL statement. Чтобы вызвать сохраненную функцию, обратитесь к ней в выражении. Функция возвращает значение во время вычисления выражения.

   • процедура вызывается с помощью оператора CALL и может передавать значения только с помощью выходных переменных. Функция может быть вызвана изнутри оператора так же, как и любая другая функция (то есть путем вызова имени функции), и может возвращать a скалярное значение.

   • указание параметра как IN, OUT или INOUT допустимо только для процедуры. Для функции параметры всегда рассматриваются как в параметрах.

   Если перед именем параметра не задано ключевое слово, то по умолчанию это параметр IN. параметры для сохраненных функций не предшествуют IN, OUT или INOUT. все параметры функции обрабатываются как в параметрах.

To определите хранимую процедуру или функцию, используйте CREATE PROCEDURE или CREATE FUNCTION соответственно:

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


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

расширение MySQL для хранимой процедуры (не функции) заключается в том, что процедура может генерировать результирующий набор или даже несколько результирующих наборов, которые вызывающий обрабатывает так же, как результат инструкции SELECT. Однако содержимое таких результирующих наборов не может использоваться непосредственно в выражении.

сохраненные подпрограммы (имея в виду как хранить процедуры и сохраненные функции)связаны с определенной базой данных, так же, как таблицы или представления. при удалении базы данных все сохраненные в ней процедуры также удаляются.

хранимые процедуры и функции не имеют общего пространства имен. в базе данных можно иметь процедуру и функцию с одинаковым именем.

в хранимых процедурах можно использовать динамический SQL, но не в функциях или триггерах.

подготовленные операторы SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) могут использоваться в хранимых процедурах, но не в сохраненных функциях или триггерах. Таким образом, сохраненные функции и триггеры не могут использовать динамический SQL (где вы строите операторы в виде строк, а затем выполняете их). (динамический SQL в сохраненных подпрограммах MySQL)

еще несколько интересных различий между функцией и сохраненными Процедура:

1. (этот пункт скопировано с блога.) Хранимая процедура-это скомпилированный план выполнения, где в качестве функции нет. Функция разбирается и компилируется во время выполнения. Хранимые процедуры, хранящиеся в виде псевдокода в базе данных т. е. скомпилированной форме.

2. (я не уверен в этом пункте.)
   Хранимая процедура имеет безопасность и уменьшает сеть трафик, а также мы можем позвонить хранимая процедура в любом. из приложения за один раз. ссылка

3. функции обычно используются для вычислений, где в качестве процедур обычно используются для выполнения бизнес-логики.

4. функции не могут влиять на состояние базы данных (Операторы, которые выполняют явную или неявную фиксацию или откат, запрещены в функции) Поскольку Хранимые процедуры могут влиять на состояние базы данных с помощью commit и т. д.
   справочные: J. 1. Ограничения на сохраненные подпрограммы и триггеры

5. функции не могут использовать FLUSH заявления, в то время как хранимые процедуры могут сделать.

6. хранимые функции не могут быть рекурсивными, в то время как хранимые процедуры могут быть. Примечание: рекурсивные хранимые процедуры по умолчанию отключены, но могут быть включены на сервере путем установки системной переменной max_sp_recursion_depth сервера в ненулевое значение. Смотрите 5.2.3, "Системные Переменные", для получения дополнительной информации.

7. в пределах сохраненной функции или триггера не разрешается изменять таблицу, которая уже используется (для чтения или записи) оператором, который вызвал функцию или триггер. Хороший Пример:Как обновить ту же таблицу при удалении в MYSQL?

Примечание: хотя некоторые ограничения обычно применяются к сохраненным функциям и триггерам, но не к хранимые процедуры, эти ограничения применяются к хранимым процедурам, если они вызываются из хранимой функции или триггера. Например, хотя вы можете использовать flush в хранимой процедуре, такая хранимая процедура не может быть вызвана из сохраненной функции или триггера.

одно существенное различие заключается в том, что вы можете включить функции в ваших SQL запросах, но хранимых процедур можно вызвать только с помощью CALL о себе:

пример 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)

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)

хранимая функция может быть использована в запросе. Затем вы можете применить его к каждой строке или в предложении WHERE.

процедура выполняется с помощью запроса вызова.

хранимая процедура может быть вызвана рекурсивно, но сохраненная функция не может