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

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

Чтобы создать сохраненную процедуру, синтаксис довольно прост:

CREATE PROCEDURE .

     <param> 

AS

Так, например:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

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

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

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

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

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

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

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

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

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

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

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

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

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

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

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

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

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

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

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

  Пример: sp_helptext [StoredProcedure_Name]

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

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

Обычно хранимая процедура - это «функция 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-инъекций.

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

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

ПРИМЕЧАНИЕ:

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

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

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

Хранящаяся процедура - это не что иное, как группа операторов SQL, скомпилированных в единый план выполнения.

1. Создайте один раз и назовите его n раз
2. Это уменьшает сетевой трафик

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

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

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

«Что такое сохраненная процедура» уже дан ответ в других постах здесь. То, что я опубликую, - это один из менее известных способов использования хранимой процедуры. Это «группировка хранимых процедур» или «нумерация хранимых процедур».

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

; число согласно 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'; 1 первым.

Ссылки :

1. КРИД ПРОЦЕДУРА с синтаксисом для числа
2. Пронумерованные хранимые процедуры в SQL Server - techie-friendly.blogspot.com
3. Сгруппировка хранимых процедур - sqlmag

ВНИМАНИЕ

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

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

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

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

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

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

В «Сложенных процедурах» заявления записываются только один раз и уменьшают сетевой трафик между клиентами и серверами. Мы также можем избежать Sql Injection Attacks.

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

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

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

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

2. Обеспечение целостности данных (данные будут вводиться последовательно) и

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

для простого

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

Каждая сохраненная программа содержит тело, состоящее из оператора 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    `*

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

create procedure_name (para1 int,para2 decimal)
as
select * from TableName