Что такое хранимая процедура?

Что такое хранимая процедура? Как они работают? Каков состав хранимой процедуры (вещи должны быть хранимой процедурой)?

Ответы

Ответ 1

Хранимые процедуры представляют собой пакет SQL-операторов, которые могут выполняться несколькими способами. Большинство основных DBM поддерживают хранимые процедуры; однако, не все. Вам необходимо будет проверить вашу конкретную справочную документацию по СУБД для уточнения. Поскольку я больше всего знаком с 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

Преимущество хранимых процедур заключается в том, что вы можете централизовать логику доступа к данным в одно место, которое затем легко оптимизировать DBA. Сохраненные процедуры также имеют преимущество безопасности, поскольку вы можете предоставить права выполнения хранимой процедуре, но пользователю не нужно иметь права на чтение и запись в базовых таблицах. Это хороший первый шаг против внедрения SQL.

Сохраненные процедуры имеют недостатки, в основном техническое обслуживание, связанное с вашей базовой операцией CRUD. Пусть говорят, что для каждой таблицы у вас есть Insert, Update, Delete и по крайней мере один выбор на основе первичного ключа, что означает, что каждая таблица будет иметь 4 процедуры. Теперь возьмите базу данных приличного размера из 400 таблиц, и у вас есть 1600 процедур! И это предполагает, что у вас нет дубликатов, которые вы, вероятно, будете делать.

Здесь используется ORM или какой-либо другой способ автоматического генерации ваших основных операций с CRUD, имеет массу преимуществ.

Ответ 2

Хранимая процедура представляет собой набор предварительно скомпилированных операторов 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)

Затем во-вторых, я вставляю значение в таблицу Employee

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).

Ответ 3

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

Пример создания хранимой процедуры

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

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

  • Хранимая процедура позволяет модульное программирование.

    Вы можете создать процедуру один раз, сохранить ее в базе данных и вызвать ее сколько угодно раз в вашей программе.

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

    Если для операции требуется большое количество SQL-кода, которое выполняется повторно, хранимые процедуры могут быть более быстрыми. Они анализируются и оптимизируются при первом их выполнении, а скомпилированная версия хранимой процедуры остается в кеше памяти для последующего использования. Это означает, что хранимая процедура не нуждается в повторном использовании и повторной оптимизации при каждом использовании, что приводит к значительному времени выполнения.

  • Хранимая процедура может снизить сетевой трафик.

    Операция, требующая сотни строк кода Transact-SQL, может выполняться через один оператор, который выполняет код в процедуре, а не путем отправки сотен строк кода по сети.

  • Хранимые процедуры обеспечивают лучшую безопасность ваших данных.

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

    В SQL Server у нас есть разные типы хранимых процедур:

    • Системные хранимые процедуры
    • Пользовательские хранимые процедуры
    • Расширенные хранимые процедуры

  • System хранимые процедуры хранятся в основной базе данных, и они начинаются с префикса sp_. Эти процедуры могут использоваться для выполнения множества задач для поддержки функций SQL Server для внешних вызовов приложений в системных таблицах.

    Пример: sp_helptext [StoredProcedure_Name]

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

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

Ответ 4

Как правило, хранимая процедура является "функцией 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-инъекций.

Ответ 5

Подумайте о такой ситуации,

  • У вас есть база данных с данными.
  • Существует множество различных приложений, необходимых для доступа к этой центральной базе данных, а также в будущем некоторые новые приложения.
  • Если вы хотите вставить встроенные запросы базы данных для доступа к центральной базе данных, внутри каждого кода приложения индивидуально, то, вероятно, вам придется дублировать один и тот же запрос снова и снова внутри кода разных приложений.
  • В такой ситуации вы можете использовать хранимые процедуры (SP). С помощью хранимых процедур вы записываете количество общих запросов (процедур) и храните их в центральной базе данных.
  • Теперь дублирование работы никогда не будет происходить по-прежнему, и доступ к данным и обслуживание будут выполняться централизованно.

Примечание:

  • В приведенной выше ситуации вы можете задаться вопросом: "Почему мы не можем ввести центральный сервер доступа к данным для взаимодействия со всеми приложениями? Да, это будет возможная альтернатива, но
  • Основным преимуществом SP для этого подхода является то, что в отличие от вашего кода доступа к данным с встроенными запросами, SP являются предварительно скомпилированными операциями, поэтому они будут выполняться быстрее. А стоимость связи (по сетям) будет минимальной.
  • Напротив, SP добавят немного нагрузки на сервер базы данных. Если это будет связано с ситуацией, лучшим выбором будет централизованный сервер доступа к данным с встроенными запросами.

Ответ 6

Хранимая процедура в основном используется для выполнения определенных задач в базе данных. Например

  • Получить базы данных на основе данных бизнес-логики.
  • Выполнять несколько операций с базой данных в одном вызове.
  • Используется для переноса данных из одной таблицы в другую.
  • Может быть вызван для других языков программирования, таких как Java.

Ответ 7

Хранимая процедура - это не что иное, как группа операторов 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

Ответ 8

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

Ответ 9

"Что такое хранимая процедура" уже ответил на другие сообщения здесь. То, что я опубликую, является менее известным способом использования хранимой процедуры. Это grouping stored procedures или numbering stored procedures.

Ссылка на синтаксис

введите описание изображения здесь

; number согласно this

Необязательное целое число, которое используется для группировки процедур с тем же именем. Эти сгруппированные процедуры можно сбрасывать вместе, используя один оператор DROP PROCEDURE

Пример

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, уровень 11, состояние 1, процедура SecondTest, строка 1 [строка запуска партии 3] Невозможно создать процедуру "SecondTest" с номером группы 2, потому что в базе данных в настоящее время не существует процедура с тем же именем и номером группы 1. Должен выполнить CREATE PROCEDURE "SecondTest", сначала первый.

Ссылки

Внимание

  • После группировки процедур вы не можете отбросить их по отдельности.
  • Эта функция может быть удалена в будущей версии Microsoft SQL Server.

Ответ 10

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

Ответ 11

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

  • Хранимая процедура должна выполняться автономно, используя EXEC

  • Хранимая процедура может возвращать несколько параметров

  • Хранимая процедура может использоваться для реализации транзакции

Ответ 12

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

Использование хранимой процедуры может быть полезно в

  • Предоставление контролируемого доступа к данным (конечные пользователи могут вводить или изменять данные, но не могут писать процедуры)

  • Обеспечение целостности данных (данные будут вводиться согласованным образом) и

  • Повышает производительность (инструкции хранимой процедуры нужно писать только один раз)

Ответ 13

для простого,

Сохраненная процедура Сохраненные программы. Программа/функция, хранящаяся в базе данных.

Каждая сохраненная программа содержит тело, состоящее из оператора SQL. Этот оператор может представлять собой составной оператор, состоящий из нескольких операторов, разделенных символами с запятой (;).

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

Ответ 14

Хранимые процедуры в 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 [email protected]     <<<---parameter used as criteria
end

Я узнал об этом от essential.com... это очень полезно.