Что такое хранимая процедура?
Что такое "хранимая процедура" и как они работают?
Каков состав хранимой процедуры (каждый должен должен быть хранимой процедурой)?
Ответы 17
Обычно хранимая процедура - это «функция SQL». Они имеют:
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
Это пример, ориентированный на T-SQL. Хранимые процедуры могут выполнять большинство операторов SQL, возвращать скалярные и табличные значения и считаются более безопасными, поскольку предотвращают атаки с использованием SQL-инъекций.
Хранимые процедуры - это пакет операторов SQL, которые можно выполнить двумя способами. Большинство основных СУБД поддерживают хранимые процедуры; однако не все. Вам нужно будет свериться со справочной документацией вашей конкретной СУБД для уточнения деталей. Поскольку я больше всего знаком с SQL Server, я буду использовать его в качестве примеров.
Синтаксис для создания хранимой процедуры довольно прост:
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
Так например:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Преимущество хранимых процедур состоит в том, что вы можете централизовать логику доступа к данным в одном месте, которое администраторы баз данных могут легко оптимизировать. Сохраненные процедуры также имеют преимущество безопасности, поскольку вы можете предоставить права на выполнение хранимой процедуре, но пользователю не нужно иметь разрешения на чтение / запись для базовых таблиц. Это хороший первый шаг против SQL-инъекции.
Сохраненные процедуры имеют недостатки, в основном обслуживание, связанное с вашей базовой операцией CRUD. Скажем, для каждой таблицы у вас есть Insert, Update, Delete и по крайней мере один выбор на основе первичного ключа, это означает, что каждая таблица будет иметь 4 процедуры. Теперь возьмите базу данных приличного размера из 400 таблиц, и у вас будет 1600 процедур! И это при условии, что у вас нет дубликатов, которые, вероятно, будут.
Вот где использование ORM или какого-либо другого метода для автоматической генерации ваших основных операций CRUD имеет массу достоинств.
Да, это один из возможных подходов. Мы используем ORM для всего, кроме отчетов
Хранимая процедура - это набор предварительно скомпилированных операторов SQL, которые используются для выполнения специальной задачи.
Пример: если у меня есть таблица Employee
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Сначала я извлекаю таблицу Employee:
Create Procedure Employee details
As
Begin
Select * from Employee
End
Чтобы запустить процедуру на SQL Server:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Затем, во-вторых, я вставляю значение в таблицу сотрудников.
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Чтобы запустить параметризованную процедуру на SQL Server:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Пример: @Name Varchar(30)
В таблице Employee размер столбца Name должен быть varchar(30).
пример, который легко понять
Хранимая процедура в основном используется для выполнения определенных задач в базе данных. Например
- Получите наборы результатов базы данных из некоторой бизнес-логики для данных.
- Выполнение нескольких операций с базой данных за один вызов.
- Используется для переноса данных из одной таблицы в другую.
- Может вызываться для других языков программирования, например Java.
Хранимая процедура используется для извлечения данных, изменения данных и удаления данных в таблице базы данных. Вам не нужно писать всю команду SQL каждый раз, когда вы хотите вставить, обновить или удалить данные в базе данных SQL.
Хранимая процедура - это именованная коллекция операторов SQL и процедурной логики, то есть скомпилированная, проверенная и хранимая в базе данных сервера. Хранимая процедура обычно обрабатывается так же, как и другие объекты базы данных, и контролируется механизмом безопасности сервера.
Хранимая процедура - это группа операторов SQL, которые были созданы и хранятся в базе данных. Хранимая процедура будет принимать входные параметры, так что одна процедура может использоваться в сети несколькими клиентами, использующими разные входные данные. Хранимые процедуры уменьшат сетевой трафик и увеличат производительность. Если мы изменим хранимую процедуру, все клиенты получат обновленную хранимую процедуру.
Пример создания хранимой процедуры
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
Преимущества использования хранимых процедур
Хранимая процедура допускает модульное программирование.
Вы можете создать процедуру один раз, сохранить ее в базе данных и вызывать ее любое количество раз в своей программе.
Хранимая процедура обеспечивает более быстрое выполнение.
Если операция требует большого количества кода SQL, который выполняется повторно, хранимые процедуры могут быть быстрее. Они анализируются и оптимизируются при первом запуске, а скомпилированная версия хранимой процедуры остается в кэше памяти для дальнейшего использования. Это означает, что хранимую процедуру не нужно повторно анализировать и повторно оптимизировать при каждом использовании, что значительно сокращает время выполнения.
Хранимая процедура может уменьшить сетевой трафик.
Операция, требующая сотен строк кода Transact-SQL, может быть выполнена с помощью одного оператора, который выполняет код в процедуре, а не путем отправки сотен строк кода по сети.
Хранимые процедуры обеспечивают лучшую безопасность ваших данных
Пользователям может быть предоставлено разрешение на выполнение хранимой процедуры, даже если у них нет разрешения на выполнение операторов процедуры напрямую.
В SQL Server у нас есть разные типы хранимых процедур:
- Системные хранимые процедуры
- Пользовательские хранимые процедуры
- Расширенные хранимые процедуры
Хранимые процедуры Система хранятся в базе данных master и начинаются с префикса
sp_. Эти процедуры можно использовать для выполнения различных задач по поддержке функций SQL Server для вызовов внешних приложений в системных таблицах.Пример: sp_helptext [StoredProcedure_Name]
Хранимые процедуры Определяемые пользователем обычно хранятся в пользовательской базе данных и обычно предназначены для выполнения задач в пользовательской базе данных. При кодировании этих процедур не использовать префикс
sp_, потому что, если мы сначала используем префиксsp_, он проверит основную базу данных, а затем перейдет к пользовательской базе данных.Хранимые процедуры Расширенный - это процедуры, вызывающие функции из файлов DLL. В настоящее время расширенные хранимые процедуры не рекомендуются по той причине, что было бы лучше избегать использования расширенных хранимых процедур.
Хранимая процедура - это не что иное, как группа операторов SQL, скомпилированных в единый план выполнения.
- Создайте один раз и назовите его n раз
- Уменьшает сетевой трафик
Пример: создание хранимой процедуры
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
Изменить или изменить хранимую процедуру:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
Отбросьте или удалите хранимую процедуру:
DROP PROCEDURE GetEmployee
Подумайте о такой ситуации,
- У вас есть база данных с данными.
- Для доступа к этой центральной базе данных требуется ряд различных приложений, а в будущем и некоторые новые приложения.
- Если вы собираетесь вставлять встроенные запросы к базе данных для доступа к центральной базе данных, внутри кода каждого приложения индивидуально, то, вероятно, вам придется снова и снова дублировать один и тот же запрос внутри кода разных приложений.
- В такой ситуации вы можете использовать хранимые процедуры (SP). С помощью хранимых процедур вы пишете ряд общих запросов (процедур) и сохраняете их в центральной базе данных.
- Теперь дублирование работы никогда не произойдет, как раньше, а доступ к данным и обслуживание будут осуществляться централизованно.
ПРИМЕЧАНИЕ:
- В приведенной выше ситуации вы можете спросить: «Почему мы не можем ввести центральный сервер доступа к данным для взаимодействия со всеми приложениями? Да. Это будет возможной альтернативой. Но,
- Основное преимущество SP перед этим подходом состоит в том, что, в отличие от вашего кода доступа к данным со встроенными запросами, SP представляют собой предварительно скомпилированные операторы, поэтому они будут выполняться быстрее. А расходы на связь (по сети) будут минимальными.
- Напротив, SP добавят дополнительную нагрузку на сервер базы данных. Если это будет проблемой в зависимости от ситуации, лучше выбрать централизованный сервер доступа к данным со встроенными запросами.
Хранимая процедура - это предварительно скомпилированный набор из одного или нескольких операторов SQL, которые выполняют определенную задачу.
Хранимая процедура должна выполняться автономно с использованием
EXEC.Хранимая процедура может возвращать несколько параметров
Хранимая процедура может использоваться для реализации транзакции.
В СУБД хранимая процедура - это набор операторов SQL с назначенным именем, который хранится в базе данных в скомпилированной форме, так что он может использоваться несколькими программами.
Использование хранимой процедуры может быть полезно в
Предоставление контролируемого доступа к данным (конечные пользователи могут только вводить или изменять данные, но не могут писать процедуры)
Обеспечение целостности данных (данные будут вводиться единообразно) и
Повышает производительность (инструкции хранимой процедуры нужно писать только один раз)
«Что такое хранимая процедура» уже ответили в других сообщениях здесь. Я опубликую еще один менее известный способ использования хранимой процедуры. Это grouping stored procedures или numbering stored procedures.
Справочник по синтаксису
; number согласно это
An optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement
Пример
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
Использовать
exec FirstTest 10
exec FirstTest;2 20,30
Результат
Еще одна попытка
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
Результат
Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3] Cannot create procedure 'SecondTest' with a group number of 2 because a procedure with the same name and a group number of 1 does not currently exist in the database. Must execute CREATE PROCEDURE 'SecondTest';1 first.
Рекомендации:
- СОЗДАТЬ ПРОЦЕДУРУ с синтаксисом для числа
- Нумерованные хранимые процедуры в SQL Server - techie-friendly.blogspot.com
- Группирование хранимых процедур - sqlmag
ОСТОРОЖНОСТЬ
- После того, как вы сгруппируете процедуры, их нельзя отбросить по отдельности.
- Эта функция может быть удалена в будущей версии Microsoft SQL Server.
для простого,
Хранимая процедура - это Сохраненные программы, программа / функция, хранящаяся в базе данных.
Каждая сохраненная программа содержит тело, состоящее из оператора SQL. Этот оператор может быть составным оператором, состоящим из нескольких операторов, разделенных символами точки с запятой (;).
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
Хранимые процедуры в SQL Server могут принимать входные параметры и возвращать несколько значений выходных параметров; в SQL Server хранимые процедуры программные операторы для выполнения операций в базе данных и возврата значения состояния вызывающей процедуре или пакету.
Преимущества использования хранимых процедур в SQL Server
Они позволяют модульное программирование. Они позволяют более быстрое выполнение. Они могут уменьшить сетевой трафик. Их можно использовать как механизм безопасности.
Вот пример хранимой процедуры, которая принимает параметр, выполняет запрос и возвращает результат. В частности, хранимая процедура принимает BusinessEntityID в качестве параметра и использует его для сопоставления первичного ключа таблицы HumanResources.Employee для возврата запрошенного сотрудника.
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end
Я узнал об этом на сайте essential.com ... это очень полезно.
Хранимая процедура поможет вам сделать код на сервере. Вы можете передать параметры и найти результат.
create procedure_name (para1 int,para2 decimal)
as
select * from TableName
В хранимых процедурах операторы записываются только один раз и сокращают сетевой трафик между клиентами и серверами. Мы также можем избежать атак с использованием Sql-инъекций.
- Если вы используете стороннюю программу в своем приложении для обработки платежей, здесь база данных должна отображать только информация, которая ему нужна, и действия, которые эта третья сторона была авторизовано, тем самым мы можем добиться конфиденциальности данных, установив разрешения с использованием хранимых процедур.
- Обновление таблицы должно выполняться только для таблицы, на которую она нацелена. но он не должен обновлять какую-либо другую таблицу, с помощью которой мы можем добиться целостность данных с использованием обработки транзакций и обработки ошибок.
- Если вы хотите вернуть один или несколько элементов с типом данных, то это лучше использовать выходной параметр.
- В хранимых процедурах мы используем выходной параметр для всего, что необходимо вернуть. Если вы хотите вернуть только один товар только с целочисленный тип данных, тогда лучше использовать возвращаемое значение. Собственно возвращаемое значение предназначено только для информирования об успехе или неудаче сохраненного Процедура.
Предисловие: В 1992 году был создан стандарт SQL92, популяризируемый БД Firebase. Этот стандарт ввел «хранимую процедуру».
** Сквозной запрос: строка (обычно объединяемая программно), которая вычисляет синтаксически правильный SQL-запрос, обычно генерируемый на уровне сервера (на таких языках, как PHP, Python, PERL и т. д.). Затем эти операторы передаются в базу данных. **
** Триггер: фрагмент кода, предназначенный для запуска в ответ на событие базы данных (обычно событие DML), часто используемый для обеспечения целостности данных. **
Лучший способ объяснить, что такое хранимая процедура, - это объяснить устаревший способ выполнения логики БД (то есть без использования хранимой процедуры).
Унаследованный способ создания систем заключался в использовании «сквозного запроса» и, возможно, наличия триггеров в БД. Практически любой, кто не использует хранимые процедуры, использует вещь, называемую «сквозным запросом».
В соответствии с современным соглашением о хранимых процедурах триггеры стали унаследованными вместе с «сквозными запросами».
Преимущества хранимых процедур:
- Их можно кэшировать как физический текст хранимой процедуры. никогда не меняется.
- У них есть встроенные механизмы против вредоносного SQL. инъекция.
- Только параметры нужно проверять на наличие вредоносного SQL инъекция экономит много ресурсов процессора.
- Самая современная база данных движки фактически компилируют хранимые процедуры.
- Они увеличивают степень абстракции между уровнями.
- Они происходят в одном и том же процесс в качестве базы данных, что обеспечивает большую оптимизацию и пропускная способность.
- Можно протестировать весь рабочий процесс серверной части без кода на стороне клиента. (например, команда "Выполнить" в Transact SQL или команда CALL в MySQL).
- Их можно использовать для повышают безопасность, поскольку их можно использовать, чтобы запретить доступ к базе данных должен осуществляться способом, несовместимым с тем, как система предназначена для работы. Это делается через пользователя базы данных механизм разрешения. Например, вы можете дать пользователям только права для ВЫПОЛНЕНИЯ хранимых процедур, а не для SELECT, UPDATE и т. д. привилегии.
- Нет необходимости в слое DML, связанном с триггерами. ** Использование всего одного триггера открывает слой DML, который очень удобен. загрузка процессора **
Таким образом, при создании новой системы баз данных SQL нет веского оправдания для использования сквозных запросов.
Также следует отметить, что использование хранимых процедур в устаревших системах, в которых уже используются триггеры или сквозные запросы, совершенно безопасно; Это означает, что переход от устаревших процедур к хранимым процедурам очень прост, и такая миграция не обязательно должна приводить к отключению системы надолго, если вообще.
Вы имеете в виду хранимую процедуру для чтения данных и
ORM, напримерEntity Framework, для выполнения операцийCRUD?