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

Ответ 1

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

например. SELECT get_foo(myColumn) FROM mytable недействителен, если get_foo() - это процедура, но вы можете сделать это, если get_foo() - это функция. Цена заключается в том, что функции имеют больше ограничений, чем процедура.

Ответ 2

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

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

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

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

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

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

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

  • Задание параметра IN, OUT или INOUT действительна только для ПРОЦЕДУРЫ. Для функции FUNCTION параметры всегда считаются параметрами IN.

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

Чтобы определить хранимую процедуру или функцию, используйте 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) могут использоваться в хранимых процедурах, но не в хранимых функциях или триггерах. Таким образом, хранимые функции и триггеры не могут использовать Dynamic SQL (где вы строите операторы как строки, а затем выполняете их). (Динамический SQL в MySQL хранимых процедурах)

Еще несколько интересных отличий между FUNCTION и STORED PROCEDURE:

• (Этот пункт скопирован из blogpost.) Хранимая процедура представляет собой предварительно скомпилированный план выполнения, поскольку функции не являются. Функция Разработана и скомпилирована во время выполнения. Хранимые процедуры, сохраненные как псевдокод в базе данных, то есть скомпилированная форма.

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

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

• Функции Не могут повлиять на состояние базы данных (Заявления, которые делают явное или неявное совершение или откат, запрещены в функции) В то время как Хранимые процедуры могут влиять на состояние базы данных, используя фиксацию и т.д.
  refrence: J.1. Ограничения на хранимые процедуры и триггеры

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

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

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

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

Ответ 3

Одно существенное различие заключается в том, что вы можете включить function в свои 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)

Ответ 4

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

Ответ 5

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

Процедура выполняется с использованием запроса CALL.