Я изучаю функции и хранимые процедуры уже довольно давно, но я не знаю, почему и когда я должен использовать функцию или хранимую процедуру. Для меня они выглядят одинаково, возможно, потому что я в этом деле новичок.
Может ли кто-нибудь объяснить мне, почему?
Функции являются вычисляемыми значениями и не могут выполнять постоянные изменения среды SQL Server (т.е. не допускаются операторы INSERT или UPDATE).
Функцию можно использовать в SQL-операторах, если она возвращает скалярное значение, или можно присоединить к ней, если она возвращает набор результатов.
Примечание из комментариев, которое подытоживает ответ. Спасибо @Sean K Anderson:.
Функции следуют определению компьютерной науки в том, что они ДОЛЖНЫ возвращать значение и не могут изменять данные, которые они получают в качестве параметров. (аргументы). Функциям не разрешается изменять что-либо, они должны иметь хотя бы один параметр и должны возвращать значение. Хранимые процедуры не обязательно должны иметь параметр, могут изменять объекты базы данных, и не должны возвращать значение.
Разница между SP и UDF перечислены ниже:
в
+---------------------------------+----------------------------------------+
| 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. | |
+---------------------------------+----------------------------------------+
Функции и хранимые процедуры служат разных целей. Хотя он's не лучшая аналогия, функции можно рассматривать буквально, как и любые другие функции вы'd с помощью на любом языке программирования, но хранимых процедур, больше похожи на отдельные программы или пакетного сценария.
Функции, как правило, имеют выход и опционально входы. Затем данные могут быть использованы в качестве входных данных для другой функции (например, SQL сервер встроенный, таких как функция datediff, лен и т. д.), Либо как предикат в SQL-запрос - например, выберите, б, ДБО.Myfunction в консоли(C) из таблицы " или " выбрать А, B, C из таблицы, где = ДБО.MyFunc(с)
.
Хранимые процедуры используются, чтобы связать воедино запросы SQL в транзакции, и интерфейс с внешним миром. Структур, таких как ADO.NET и т. д. может'т вызвать функцию напрямую, но они могут вызвать хранимую proc и напрямую.
Функции есть скрытые опасности, хотя они могут быть использованы и вызывают довольно неприятные проблемы с производительностью: рассмотрим следующий запрос:
SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)
Где myfunction в консоли объявлен как:
CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
DECLARE @retval INTEGER
SELECT localValue
FROM dbo.localToNationalMapTable
WHERE nationalValue = @someValue
RETURN @retval
END
То, что происходит здесь заключается в том, что функции myfunction вызывается для каждой строки в таблице mytable. Если таблица mytable имеет 1000 строк, то, что'ы еще 1000 нерегламентированных запросов к базе данных. Аналогично, если функция вызывается, когда указанным в спецификации столбца, то функция будет вызываться для каждой строки, возвращаемой на выбор.
Поэтому вы должны быть осторожны написание функций. Если вы выберите из таблицы в функцию, вы должны спросить себя, Может ли он быть лучше выполняется вступить в родительский хранимой proc или некоторые другие SQL построить (например, случай ... когда ... иначе ... конец).
Различия между хранимыми процедурами и пользовательскими функциями:
<ч/>
RAISEERROR
или @@ошибке
не допускается в пользовательские функции.Напишите определяемую пользователем функцию, когда вы хотите вычислить и вернуть значение для использования в других SQL-запросах; напишите хранимую процедуру, когда вы хотите сгруппировать возможно сложный набор SQL-запросов. В конце концов, это два совершенно разных случая использования!
STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION)
* Procedure can return 0, single or | * Function can return only single value
multiple values. |
|
* Procedure can have input, output | * Function can have only input
parameters. | parameters.
|
* Procedure cannot be called from | * Functions can be called from
function. | procedure.
|
* Procedure allows select as well as | * Function allows only select statement
DML statement in it. | in it.
|
* Exception can be handled by | * Try-catch block cannot be used in a
try-catch block in a procedure. | function.
|
* We can go for transaction management| * We can't go for transaction
in procedure. | management in function.
|
* Procedure cannot be utilized in a | * Function can be embedded in a select
select statement | statement.
|
* Procedure can affect the state | * Function can not affect the state
of database means it can perform | of database means it can not
CRUD operation on database. | perform CRUD operation on
| database.
|
* Procedure can use temporary tables. | * Function can not use
| temporary tables.
|
* Procedure can alter the server | * Function can not alter the
environment parameters. | environment parameters.
|
* Procedure can use when we want | * Function can use when we want
instead is to group a possibly- | to compute and return a value
complex set of SQL statements. | for use in other SQL
| statements.
Основные Отличия
Функция должна возвращать значение, но в хранимой процедуре это необязательно( процедура может возвращать ноль или N значений).
Функции могут иметь только входные параметры для нее, тогда как процедуры могут иметь входные/выходные параметры .
Функция принимает один входной параметр является обязательным, но хранимая процедура может занять o для n входных параметров.
Функции могут вызываться из процедуры, в то время как процедур может быть вызван из функции.
Предварительная Разница
Процедура позволяет выбрать так же, как и DML(вставить/обновить/удалить) заявление, в то время как функция позволяет выбрать только заявление в ней.
Процедуры не могут использоваться в инструкции Select, в то время как функция может быть встроена в инструкции Select.
Хранимые процедуры не могут быть использованы в SQL-операторы в любой точке, где/имеющие/Выберите раздел, в то время как функция может быть.
Функции, возвращающие таблицы можно рассматривать как еще один набор строк. Это может быть использовано в соединениях с другими таблицами.
Встроенная функция может рассматриваться как вид, который принимает параметры и может быть использовано в соединениях и другие операции набора.
Исключение может быть обработано конструкцией try-catch блок в порядке, а попробовать-catch блок не может быть использован в функции.
Мы можем пойти для управления транзакциями в порядок, тогда как мы можем'т пойти в функцию.
пользовательская функция-это важный инструмент, доступный программисту SQL-сервера. Вы можете использовать встроенные в SQL-оператор, как так
SELECT a, lookupValue(b), c FROM customers
где lookupValue будет ОДС. Такой функционал не возможен при использовании хранимой процедуры. В то же время вы не можете делать определенные вещи внутри ОДС. Основная вещь, чтобы помнить здесь является то, что ОДС'ы:
хранимая процедура может сделать эти вещи.
Для меня рядный использование UDF является наиболее важным использованием пользовательской функции.
Хранимые процедуры используются в качестве скриптов. Они запускают серию команд для вас, и вы можете запланировать их выполнение в определенное время.
Функции используются в качестве методов. Вы передадите ему что-то и возвращает результат. Должен быть маленький и быстрый - это на лету.
Хранимая процедура:
метод exec
или выполнить
заявление.Функции:
Выберите dbo.имя_функции('Параметр1')
или
Выберите имя, ДБО.Имя_функции('Параметр1') из таблице sysobjects
выход
.Функции SQL-сервера, такие как курсоры, предназначены для использования в качестве последнего оружия! У них действительно есть проблемы с производительностью и, следовательно, используя табличное значение функции следует избегать, насколько это возможно. Про производительность говорить о столе с более чем 1,000,000 записей, размещенные на сервере на оборудовании среднего класса; в противном случае вы не'т нужно беспокоиться о производительности, вызванной функции.
для получения дополнительной информации см.: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html
Чтобы решить, когда что использовать, могут помочь следующие пункты...
Хранимые процедуры не могут возвращать табличную переменную, в то время как функции могут это делать.
Вы можете использовать хранимые процедуры для изменения параметров окружения сервера, а функции - нет.
спасибо
Начнем с функции, которые возвращают одно значение. Приятно то, вы можете поместить часто используемые код в функцию и возвращать их в виде столбца в результирующем наборе.
Затем, вы можете использовать функцию параметризованного списка городов. ДБО.GetCitiesIn (с"Нью-Йорк" У), которая возвращает таблицу, которая может использоваться как присоединиться.
Это'ы способ организации кода. Зная, когда что-то многоразовые, а когда это пустая трата времени что-то приобрел только путем проб и ошибок и опыта.
Кроме того, функции хорошая идея в SQL сервере. Они быстрее и может быть довольно мощным. Встроенные и прямые выбирает. Осторожны, не злоупотребляйте.
Здесь'ы практическая причина предпочесть функций хранимых процедур. Если у вас есть хранимая процедура, которая нуждается в результатах другой хранимой процедуры, вы должны использовать команду вставка-инструкции exec. Это означает, что вы должны создать временную таблицу и использовать метод exec
заявление, чтобы вставить результаты хранимой процедуры во временную таблицу. Это'ы грязный. Одна проблема с этим является то, что вставка-боссы не могут быть вложенными.
Если вы'вновь застрял с хранимыми процедурами, вызывать другие хранимые процедуры, вы можете столкнуться с этим. Если вложенные хранимые процедуры просто возвращает набор данных, он может быть заменен на функцию, возвращающую табличное значение, и вы'больше не будете получать эту ошибку.
(это еще одна причина, по которой мы должны держать бизнес-логику из базы данных)
Пользовательские Функции.
Хранимая Процедура
Функции могут использоваться в инструкции Select, где в качестве процедуры не могут.
Хранимая процедура принимает как входные и выходные параметры, а функции принимает только входные параметры.
Функции не могут возвращать значения типа text, типа ntext, изображения & метки времени, где в качестве процедуры.
Функции могут быть использованы как пользовательские типы данных, создавать таблицы, но процедуры не могут.
***Например:-создать таблицу <имятаблицы>(имя, тип varchar(10),зарплата getsal(имя))`
Здесь getsal это определяемая пользователем функция, которая возвращает зарплату типа, когда таблица создается не для хранения, отведенных для заработной платы тип, и getsal функция также не выполняется, но, когда мы отнесем некоторые значения из этой таблицы, getsal функции выполняется и возврат Тип возвращаются в результирующем наборе.
Я понимаю, что это очень старый вопрос, но я не'т см. Одним из важнейших аспектов, упомянутых в любой из ответов: встраивание в план запроса.
Функции могут быть...
Создать функция ... возвращает scalar_type как начать ... конец`
Создать функция ... возвращает @таблица р(...) как начать ... конец`
СОЗДАЕМ ФУНКЦИЮ ... ТАБЛИЦЫ ВОЗВРАЩАЕТ КАК ВЕРНУТЬ ВЫБЕРИТЕ ...
Третий вид (встроенной возвращающей табличное значение) обрабатываются оптимизатором запросов по существу (параметризованных) вид, который означает, что ссылка на функцию из вашего запроса аналогично скопировать-вставить функцию'ы в SQL тела (без копировать-вставить), что приводит к следующие преимущества:
Вышеперечисленное может привести к значительному снижению производительности, особенно при объединении нескольких уровнях функций.
Примечание: похоже, что SQL-сервер 2019 будет представить некоторые формы скалярной функции подстановкой, а также.
В SQL сервере, функции и хранимые процедуры являются двумя разными типами сущностей.
Функции: в базе данных SQL Server функции используются, чтобы выполнить некоторые действия и действия немедленно возвращает результат. Функции бывают двух типов:
Системные
Пользовательские
Хранимые процедуры: в SQL-сервера, хранимые процедуры хранятся на сервере и его можно вернуть ноль, один, так и несколько значений. Хранимые процедуры бывают двух типов: