1 что такое хранимая процедура

Хранимые процедуры

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (stored procedure).

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

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

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

Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC .

Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.

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

CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );

Создадим хранимую процедуру для извлечения данных из этой таблицы:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN. END:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures :

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

Выполнение процедуры

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :

EXEC ProductSummary

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE :

DROP PROCEDURE ProductSummary

SQL-Ex blog

Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще

Добавил Sergey Moiseenko on Суббота, 25 февраля. 2023

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

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

Что необходимо для выполнения примеров

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

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

В простейшем виде хранимая процедура — это не более чем набор операторов Transact-SQL (T-SQL), который хранится в базе данных и может выполняться при вызове хранимой процедуры по имени. Имя присваивается хранимой процедуре при ее создании. Для краткости хранимую процедуру часто называют хп (SP).

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

• Хранимые процедуры могут принимать входные параметры.
• Хранимые процедуры могут возвращать выходные параметры.
• Хранимые процедуры содержат программные операторы.
• Хранимые процедуры могут возвращать значение состояния, показывающее успешность или сбой (и почему он произошел).
• Нет ограничения на размер хранимой процедуры
• Имеется четыре типа хранимых процедур:
  1. Определяемые пользователем
  2. Временные
  3. Системные
  4. Расширенные определяемые пользователем

Определения четырех типов хранимых процедур

Определяемая пользователем хранимая процедура

Наиболее часто используются пользовательские хранимые процедуры. Такая процедура может создаваться в пользовательской базе данных или в любой системной базе данных за исключением базы данных Resource. Мы подробней поговорим о пользовательских хранимых процедурах позже в этой статье и рассмотрим ряд примеров.

Временная хранимая процедура

Временная хранимая процедура — это вид пользовательской хранимой процедуры, которая очень похожа на временные таблицы. Временные хранимые процедуры хранятся в базе данных «tempdb», видной на вкладке «System Databases». Эти временные хранимые процедуры могут использоваться как локальные или глобальные.

Временные локальные хранимые процедуры видны только на текущем подключении пользователя и всегда будут иметь префикс # (знак фунта или хэштега).

Временные глобальные хранимые процедуры видны на любом пользовательском подключении и имеют префикс ## (двойной знак фунта).

Поскольку они являются «временными», эти хранимые процедуры пропадают, когда закрывается подключение SQL.

Системные хранимые процедуры

Вы обнаружите, что довольно часто используете системные хранимые процедуры. Полезно познакомиться с ними, чтобы не изобретать колесо. Эти хранимые процедуры включены по умолчанию в установку SQL Server и всегда будут начинаться с префикса «sys.sp_». Вам могут пригодиться самые распространенные системные хранимые процедуры, включая sys.sp_addUser, sys.sp_addLogin, sys.sp_addRole, sys.sp_change_users_login и т.д.

1. Databases (базы данных)
2. System Databases (системные базы данных)
3. Tempdb
4. Programmability (программирование)
5. Stored Procedures (хранимые процедуры)
6. System Stored Procedures (системные хранимые процедуры)

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

Расширенные хранимые процедуры пользователя используются для создания внешних процедур на языках типа C, C#, VB и т.д. Они реализуются как DLL, которые SQL Server может загрузить и выполнять динамически.

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

Зачем использовать хранимые процедуры

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

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

Мы можем предоставить пользователям разрешение на использование (вызов) хранимой процедуры или же запретить пользователям ее вызывать. Замечание: пользователь не должен иметь разрешение или доступ к таблице, чтобы вызвать хранимую процедуру, которая будет менять данные в этой таблице. Это позволит пользователям добавлять данные в таблицу для создания записей данных, не имея к ним доступа на просмотр, удаление или изменение любых данных в таблице, помимо тех, на которые вы дали им разрешения.

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

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

Теперь, когда мы знаем немного больше о преимуществах хранимых процедур, давайте создадим базовый тестовый пример для работы. В коде примера, приведенного ниже, мы создаем хранимую процедуру с именем uspGetEmployees. Я уверен, что вы можете догадаться, что подразумевает часть «Get Employees», но что такое «usp» в имени? Вы можете именовать хранимые процедуры как вам нравится, но отраслевым стандартом является использование соглашения, которое мы здесь применили. «usp» — это сокращение от «User Stored Procedure» (пользовательская хранимая процедура).

USE [AdventureWorks2019] 
GO 
CREATE PROCEDURE uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person; 
GO

Видно, что блок кода выше очень похож на создание представления (VIEW) в SQL Server. Вы просто предваряете оператор SELECT текстом «CREATE PROCEDURE «.

Замечание. При создании хранимой процедуры вы можете сократить слово «PROCEDURE» до «PROC»; любой вариант будет работать.

Выполнение хранимых процедур

При выполнении хранимой процедуры вам просто нужно напечатать команду EXECUTE (или EXEC) с последующим именем хранимой процедуры, как в примере ниже.

EXEC uspGetEmployees;

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

Изменение/модификация хранимой процедуры

Вы можете использовать команду «ALTER PROC» или «ALTER PROCEDURE» для изменения функциональности хранимой процедуры. Скажем, мы хотим также получать отчество из таблицы «Person.Person» в базе данных AdventureWorks. Вместо создания новой хранимой процедуры мы можем модифицировать имеющуюся, как в примере ниже.

ALTER PROC uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, MiddleName 
, LastName 
FROM Person.Person; 
GO

Давайте выполним хранимую процедуру снова и посмотрим на результаты.

EXEC uspGetEmployees;

Видно, что единственным отличием в результатах этого множества и предыдущего является наличие отчества.

ОК, довольно просто. Давайте продолжим. Теперь мы добавим предложение WHERE и будем использовать предикат IS NOT NULL для фильтрации сотрудников, у которых присутствует отчество (не стоит NULL).

ALTER PROC uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, MiddleName 
, LastName 
FROM Person.Person 
WHERE MiddleName IS NOT NULL; 
GO

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

EXEC uspGetEmployees;

Удаление хранимой процедуры

Удаление хранимой процедуры в SQL подобно удалению таблицы, представления и т.д. Вы просто вызываете команду «DROP PROCEDURE» или «DROP PROC» с именем процедуры.

DROP PROC uspGetEmployees;

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

Входные параметры хранимой процедуры

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

Когда мы добавляем входной параметр в хранимую процедуру, она помещается после команды «CREATE PROC» и выше команды «AS». И, как и для любой переменной, имя параметра должно начинаться с символа @ и последующим типом данных. В следующем примере мы изменяем нашу хранимую процедуру, добавляя переменную и присваивая ей значение по умолчанию.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) = 'abel' 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName = @lastname; 
GO

Давайте выполним хранимую процедуру и посмотрим на результаты.

EXEC uspGetEmployees;

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

EXEC uspGetEmployees 'Akers';

Что если вы не уверены, как пишется имя? Хорошо, тогда опять изменим нашу хранимую процедуру. Теперь мы заменим равенство » sql»>ALTER PROCEDURE uspGetEmployees
@lastname VARCHAR(40) = ‘abel’
AS
SELECT
BusinessEntityID
, FirstName
, LastName
FROM Person.Person
WHERE LastName LIKE @lastname;
GO

Это позволит нам использовать подстановочные знаки в параметре и вернуть, например, каждого, чья фамилия начинается с «Ab».

EXEC uspGetEmployees 'Ab%';

Хранимые процедуры с несколькими входными параметрами

Добавление нескольких параметров следует тем же принципам, что и команда с единственным параметром, только они должны разделяться запятой. В примере ниже мы добавляем параметр «FirstName» и ссылку на него в предложении WHERE. Мы начинаем с удаления значений по умолчанию, которые присваивались параметрам в предыдущих примерах. Значения параметров будут передаваться пользователем при вызове хранимой процедуры.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) 
, @firstname VARCHAR(40) 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName LIKE @lastname AND FirstName LIKE @firstname; 
GO

Теперь при выполнении хранимой процедуры нам нужно включать в вызов значения обоих параметров. В этом примере мы вернем все строки, у которых фамилия начинается с «Ab», а имя — с «K».

EXEC uspGetEmployees 'Ab%', 'K%';

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

EXEC uspGetEmployees @FirstName = 'Kim', @LastName = 'Ab%';

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

Выходные параметры хранимой процедуры

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

1) CREATE PROC myRowCount 
2) @lname VARCHAR(40), 
3) @numrows INT = 0 OUTPUT 
4) AS 
5) SELECT LastName 
6) FROM Person.Person 
7) WHERE LastName LIKE @lname 
 SET @numrows = @@ROWCOUNT;

1. Мы создаем новую хранимую процедуру с именем «myRowCount».
2. Добавляем входной параметр с именем «@lname» и типом данных «VARCHAR(40)».
3. Теперь давайте добавим выходной параметр с именем «@numrows», т.к. мы хотим подсчитать число строк, возвращаемых хранимой процедурой. Тип данных переменной — INT, значение по умолчанию — 0, и нам необходимо указать, что это выходной параметр, с помощью ключевого слова OUTPUT. Это даст знать SQL, что эта переменная будет использоваться для вывода данных.
4. Ключевое слово «AS» говорит создать хранимую процедуру, содержащую последующий код.
5. Наш основной оператор SELECT.
6. Предложение FROM указывает таблицы, откуда будут извлекаться данные.
7. Предложение WHERE устанавливает фильтры для оператора SELECT.
8. На этой строке мы устанавливаем (присваиваем) для «@numrows» значение «@@ROWCOUNT». Когда вы видите двойной символ @, это признак системной переменной, которых имеется несколько. Познакомиться с системными переменными можно в документации. Поскольку @@ROWCOUNT получает значение при каждом выполнении запроса, мы захватим число, сгенерированное при выполнении нашей хранимой процедуры, и загрузим его в выходной параметр @numrows.

1) DECLARE @retrows INT 
2) EXEC myRowCount 'B%', @numrows = @retrows OUTPUT 
3) SELECT @retrows AS 'Rows';

1. Здесь мы объявляем переменную, чтобы принять значение из выходного параметра процедуры.
   В этом примере мы называем новую переменную «@retrows», сокращение от «return rows». Мы должны назначить ей тип данных, который соответствует выходной переменной, которую мы создали в хранимой процедуре.
2. Здесь мы выполняем нашу хранимую процедуру с помощью команды «EXEC myRowCount», и нам нужно предоставить значение входному параметру. В данном примере мы хотим вернуть все строки, для которых фамилия начинается с «B». Далее на той же строке мы хотим получить значение в нашу переменную «@numrows», которую мы создали в хранимой процедуре, и передать это значение в нашу новую переменную, которую мы описали в операторе DECLARE выше. Опять же нам нужно добавить ключевое слово OUTPUT, чтобы напомнить компилятору об этой переменной.
3. Теперь мы создаем последний оператор SELECT, в котором получаем значение (число строк) из переменной «@retrows» и именуем результирующий набор «Rows».

Обработка ошибок в хранимых процедурах

Здесь мы опять изменим хранимую процедуру uspGetEmployees. С помощью функции @@ROWCOUNT, о которой говорилось в предыдущем разделе, мы будем возвращать сообщение «No Records Found», если ничего не будет возвращено. Это упрощенный, но эффективный взгляд на обработку ошибок в хранимых процедурах.

Здесь мы выполним хранимую процедуру дважды. Сначала вернем все строки, фамилии которых начинаются с «Ak»; потом выполним хранимую процедуру, которая вернет сообщение об ошибке. Но первое, что нужно сделать, — это изменить процедуру.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName LIKE @lastname 
IF @@ROWCOUNT < 1
BEGIN 
RAISERROR('No Records Found', 10,1) 
END; 
GO

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

EXEC uspGetEmployees 'Ak%';

Теперь выполним ту же процедуру с именем, которого нет в таблице Person.Person.

EXEC uspGetEmployees 'zz%';

Несмотря на то, что это был примитивный пример, он даст вам направление работы в тестовом и рабочем окружении. Экспериментируйте и вносите изменения. Делайте обработку ошибок интуитивно понятной.

Безопасность хранимых процедур

• Абстракция: Пользователи могут выполнять сложные запросы, не зная структуры лежащих в основе таблиц или других объектов базы данных.
• Шифрование: Мы можете зашифровать код в хранимой процедуре после ее создания.
• Предотвращение SQL-инъекции: Все передаваемые в хранимую процедуру параметры могут быть проверены до передачи их в таблицу или другой объект базы данных.
• Согласованное манипулирование данными: Хранимая процедура выполняет всякий раз один и тот же код в одном и том же порядке.
• Контроль выполнения: Вы можете установить разрешения на хранимую процедуру, позволяя только определенным пользователям ли группам выполнять ее.
• Обработка ошибок: Хранимые процедуры обеспечивают возможность использовать последовательную и эффективную обработку ошибок и создание отчетов.

Ссылки по теме:

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Хранимые процедуры: описание, лимиты и примеры

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

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

Хранимая процедура — это заданный алгоритм, позволяющий Вам реализовать быстрый вызов сразу нескольких методов API, аналогично принципу работы метода execute, но без передачи кода по сети (в запросе необходимо указывать только имя процедуры и необходимые параметры).

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

Код для хранимых процедур пишется на VKScript языке.

VKScript для метода execute получается не всегда маленький, и передавать его в запросе очень жирно. Так же для хранимых процедур реализован удобный клиент прямо в Вконтакте. Дергать процедуру по ее названию, в этом случае передавать code уже не нужно. Не используя хранимые процедуры при отправке запросов с клиента, ваш код отправляемый к API, можно посмотреть.

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

Лимиты

Макс. запросов в секунду	3
Макс. размер ответа	5 МБ
Макс. кол-во обращение внутри процедуры к методам API	25
Макс. кол-во операций внутри процедуры	1000

Мои процедуры

Умный поиск людей, без даты рождения и точного города — не важно указан у пользователя в профиле город или дата рождения.

execute.userSearch

• Шаг 1
  1. ФИО, дата рождения, город. По данным полям ищем совпадения среди аккаунтов сети Вконтакте, у которых есть фото. В случае, если находим 2 и более аккаунтов используем тот, у которого дата последнего входа максимальна. В случае, если ничего не найдено переходим на Шаг 2.

• Шаг 2
  1. Используя данные по фамилии и имени, дате рождения производим повторный поиск.
     В случае, если находим 2 и более аккаунтов, переходим на Шаг 3.2. В случае, если ничего не найдено переходим на Шаг 3.3.

• Шаг 3
  1. Ищем 10 самых популярных групп выбранного города Вконтакте.
  2. В этих группах ищем по ФИ, Дате рождения, страна Россия, есть фото. Если что-то нашлось, то сохраняем текущий результат, иначе идем в шаг 3.3.
  3. По группам ищем по ФИ, страна Россия, есть фото. Если что-то нашлось сохраняем результат, иначе «пусто».

var name = Args.fullname; var birth_day = Args.birth_day; var birth_month = Args.birth_month; var birth_year = Args.birth_year; var city = Args.city; var fields = "photo_id, sex, bdate, city, country, home_town, photo_max_orig, contacts, site, education, universities, schools, status, last_seen, followers_count, common_count, occupation, nickname, relatives, relation, personal, connections, exports, activities, interests, music, movies, tv, books, games, about, quotes, can_post, can_see_all_posts, can_see_audio, can_write_private_message, can_send_friend_request, is_favorite, is_hidden_from_feed, timezone, screen_name, maiden_name, career, military"; var countGroups = 10; var res = []; var item = <>; var city_id = 0; if (city != "") < city_id = API.database.getCities().items@.id[0]; > if (birth_day != "" && city_id > 0) < item = API.users.search(< "q": name, "country":1, "city": city_id, "has_photo":1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year >).items; var i = 0; while (i < item.length) < var item_ = item[i]; item_.criterion = "Дата рождения, ФИО, Город"; item_.step = "1.1"; res.push(item_); i = i +1; >> else < countGroups = countGroups + 1; >if (item.length > 0) < return res; >if (birth_day != "") < item = API.users.search(< "q": name, "country": 1, "has_photo": 1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year >).items; var i = 0; while (i < item.length) < var item_ = item[i]; item_.criterion = "Дата рождения, ФИО"; item_.step = "2.1"; res.push(item_); i = i +1; >> if (item.length > 0) < return res; >if (city.length > 0) < var groupsIDs = API.groups.search(< "q": city, "sort": 6, "type": "page", "count": countGroups >).items@.id; var count = 0; while (count < groupsIDs.length) < var item2 = <>; if (birth_day != "") < item2 = API.users.search(< "q": name, "country":1, "has_photo":1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year, "group_id": groupsIDs[count] >).items; > if (item2.length > 0) < var i = 0; while (i < item2.length) < var item_ = item2[i]; item_.criterion = "Дата рождения, ФИО, Состоит в пабликах города"; item_.step = "3.2"; item_.groupID = groupsIDs[count]; res.push(item_); i = i +1; >> else < item2 = API.users.search(< "q": name, "country":1, "has_photo":1, "fields": fields, "count": 5, "group_id": groupsIDs[count] >).items; var i = 0; while (i < item2.length) < var item_ = item2[i]; item_.criterion = "ФИО, Состоит в пабликах города"; item_.step = "3.3"; item_.groupID = groupsIDs[count]; res.push(item_); i = i +1; >> count = count + 1; > > return res;

execute.getFriendsBDates

 var response = ""; var bdates = API.friends.get().items@.bdate; response = response + bdates; if (!bdates.length || bdates[0].length == 5000) < response = response + API.friends.get().items@.bdate; > return response; 

execute.getMembers

 var members = API.groups.getMembers().items; // делаем первый запрос и создаем массив var offset = 1000; // это сдвиг по участникам группы while (offset < 25000 && (offset + Args.offset) < Args.total_count) // пока не получили 20000 и не прошлись по всем участникам < members = members + "," + API.groups.getMembers().items; // сдвиг участников на offset + мощность массива offset = offset + 1000; // увеличиваем сдвиг на 1000 >; return members; 

execute.getFriends

 var user_ids = Args.user_ids.split(','); var friends = API.friends.get().items; var i = 1; while (user_ids.length > i && i < 25) < friends = friends + "," + API.friends.get().items; i = i + 1; > return friends; 

execute.isMemberGroups

 var groups = API.groups.search().items; var members = []; var i = 0; while (groups.length > i) < var groupIsMember = []; groupIsMember.members = API.groups.isMember(); groupIsMember.group_id = groups[i].id; members.push(groupIsMember); i = i + 1; > return members; 

SQL — Урок 15. Хранимые процедуры. Часть 1.

Как правило, мы в работе с БД используем одни и те же запросы, либо набор последовательных запросов. Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере. Это очень удобный инструмент, и сейчас вы в этом убедитесь. Начнем с синтаксиса:

CREATE PROCEDURE имя_процедуры (параметры) begin операторы end

Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы — это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. В уроке 10, когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:

INSERT INTO customers (name, email) VALUE (‘Иванов Сергей’, ‘sergo@mail.ru’);

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

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end

Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на «//», чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :

Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end //

Итак, процедура создана. Теперь, когда нам понадобится ввести нового покупателя нам достаточно ее вызвать, указав необходимые параметры. Для вызова хранимой процедуры используется оператор CALL , после которого указывается имя процедуры и ее параметры. Давайте добавим нового покупателя в нашу таблицу Покупатели (customers):

call ins_cust(‘Сычов Валерий’, ‘valera@gmail.ru’)//

Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):

Появился, процедура работает, и будет работать всегда, пока мы ее не удалим с помощью оператора DROP PROCEDURE название_процедуры .

Как было сказано в начале урока, процедуры позволяют объединить последовательность запросов. Давайте посмотрим, как это делается. Помните в уроке 11 мы хотели узнать, на какую сумму нам привез товар поставщик «Дом печати»? Для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. А если мы захотим узнать, на какую сумму нам привез товар другой поставщик? Придется составлять новые запросы, объединения и т.д. Проще один раз написать хранимую процедуру для этого действия.

Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; end //

Но так процедура работать не будет. Все дело в том, что в представлениях не могут использоваться параметры . Поэтому нам придется несколько изменить последовательность запросов. Сначала мы создадим представление, которое будет выводить идентификатор поставщика (id_vendor), идентификатор продукта (id_product), количество (quantity), цену (price) и сумму (summa) из трех таблиц Поставки (incoming), Журнал поставок (magazine_incoming), Цены (prices):

CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming;

А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:

SELECT SUM(summa) FROM report_vendor WHERE id_vendor=2;

Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Проверим работу процедуры, с разными входными параметрами:

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.

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

DROP PROCEDURE sum_vendor// DROP VIEW report_vendor// CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming// CREATE PROCEDURE sum_vendor(i INT) begin SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

Второй вариант — прямо в процедуре дописать команду, которая будет удалять представление, если оно существует:

CREATE PROCEDURE sum_vendor(i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу:

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

Научись программировать на Python прямо сейчас!

• Научись программировать на Python прямо сейчас
• Бесплатный курс

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.