Для чего используются хранимые процедуры. Как правильно писать хранимые процедуры в SQL Server. Работа с переменными
В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
Введение
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
За
- Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
- Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
- Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
- Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
Шаг 1: Ставим ограничитель
Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
Шаг 2: Как работать с хранимыми процедурами
Создание хранимой процедуры
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
- Language: в целях обеспечения переносимости, по умолчанию указан SQL.
- Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
- SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
- Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Шаг 3: Параметры
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
- CREATE PROCEDURE proc1 (): пустой список параметров
- CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.
Естественно, вы можете задавать несколько параметров разных типов.
Пример параметра IN
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
Пример параметра OUT
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
Пример параметра INOUT
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //
Шаг 4: Переменные
Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Давайте объявим несколько переменных:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Работа с переменными
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //
Шаг 5: Структуры управления потоками
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //
Конструкция CASE
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
Конструкция WHILE
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Шаг 6: Курсоры
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/
В этом примере мы проведем кое-какие простые операции с использованием курсора:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
- Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
- Доступен только для чтения: курсоры нельзя изменять.
- Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
Заключение
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
Хранимая процедура - это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.
Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.
При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.
Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.
Хранимые процедуры можно также использовать для следующих целей:
для создания журнала логов о действиях с таблицами баз данных.
Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.
Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.
Создание и исполнение хранимых процедур
Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE , которая имеет следующий синтаксис:
CREATE PROC proc_name [({@param1} type1 [ VARYING] [= default1] )] {, …} AS batch | EXTERNAL NAME method_name Соглашения по синтаксису
Параметр schema_name определяет имя схемы, которая назначается владельцем созданной хранимой процедуры. Параметр proc_name определяет имя хранимой процедуры. Параметр @param1 является параметром процедуры (формальным аргументом), чей тип данных определяется параметром type1. Параметры процедуры являются локальными в пределах процедуры, подобно тому, как локальные переменные являются локальными в пределах пакета. Параметры процедуры - это значения, которые передаются вызывающим объектом процедуре для использования в ней. Параметр default1 определяет значение по умолчанию для соответствующего параметра процедуры. (Значением по умолчанию также может быть NULL.)
Опция OUTPUT указывает, что параметр процедуры является возвращаемым, и с его помощью можно возвратить значение из хранимой процедуры вызывающей процедуре или системе.
Как уже упоминалось ранее, предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Если же по каким-либо причинам хранимую процедуру требуется компилировать при каждом ее вызове, при объявлении процедуры используется опция WITH RECOMPILE . Использование опции WITH RECOMPILE сводит на нет одно из наиболее важных преимуществ хранимых процедур: улучшение производительности благодаря одной компиляции. Поэтому опцию WITH RECOMPILE следует использовать только при частых изменениях используемых хранимой процедурой объектов базы данных.
Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.
По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE .
В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:
USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;
Как говорилось ранее, для разделения двух пакетов используется инструкция GO . Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.
Хранимые процедуры могут обращаться к несуществующим таблицам. Это свойство позволяет выполнять отладку кода процедуры, не создавая сначала соответствующие таблицы и даже не подключаясь к конечному серверу.
В отличие от основных хранимых процедур, которые всегда сохраняются в текущей базе данных, возможно создание временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Одним из поводов для создания временных хранимых процедур может быть желание избежать повторяющегося исполнения определенной группы инструкций при соединении с базой данных. Можно создавать локальные или глобальные временные процедуры. Для этого имя локальной процедуры задается с одинарным символом # (#proc_name), а имя глобальной процедуры - с двойным (##proc_name).
Локальную временную хранимую процедуру может выполнить только создавший ее пользователь и только в течение соединения с базой данных, в которой она была создана. Глобальную временную процедуру могут выполнять все пользователи, но только до тех пор, пока не завершится последнее соединение, в котором она выполняется (обычно это соединение создателя процедуры).
Жизненный цикл хранимой процедуры состоит из двух этапов: ее создания и ее выполнения. Каждая процедура создается один раз, а выполняется многократно. Хранимая процедура выполняется посредством инструкции EXECUTE пользователем, который является владельцем процедуры или обладает правом EXECUTE для доступа к этой процедуре. Инструкция EXECUTE имеет следующий синтаксис:
[] [@return_status =] {proc_name | @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT}.. Соглашения по синтаксису
За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.
Предложение DEFAULT предоставляет значения по умолчанию для параметра процедуры, которое было указано в определении процедуры. Когда процедура ожидает значение для параметра, для которого не было определено значение по умолчанию и отсутствует параметр, либо указано ключевое слово DEFAULT, то происходит ошибка.
Когда инструкция EXECUTE является первой инструкцией пакета, ключевое слово EXECUTE можно опустить. Тем не менее будет надежнее включать это слово в каждый пакет. Использование инструкции EXECUTE показано в примере ниже:
USE SampleDb; EXECUTE IncreaseBudget 10;
Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.
В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:
Процедура ModifyEmpId в примере иллюстрирует использование хранимых процедур, как часть процесса обеспечения ссылочной целостности (в данном случае между таблицами Employee и Works_on). Подобную хранимую процедуру можно использовать внутри определения триггера, который собственно и обеспечивает ссылочную целостность.
В примере ниже показано использование в хранимой процедуре предложения OUTPUT:
Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @ OUTPUT; PRINT N"Удалено сотрудников: " + convert(nvarchar(30), @quantityDeleteEmployee);
Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.
Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.
Предложение WITH RESULTS SETS инструкции EXECUTE
В SQL Server 2012 для инструкции EXECUTE вводится предложение WITH RESULTS SETS , посредством которого при выполнении определенных условий можно изменять форму результирующего набора хранимой процедуры.
Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:
Процедура EmployeesInDept - это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:
USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Фамилия] CHAR(20) NOT NULL));
Результат выполнения хранимой процедуры, вызванной таким способом, будет следующим:
Как можно видеть, запуск хранимой процедуры с использованием предложения WITH RESULT SETS в инструкции EXECUTE позволяет изменить наименования и тип данных столбцов результирующего набора, выдаваемого данной процедурой. Таким образом, эта новая функциональность предоставляет большую гибкость в исполнении хранимых процедур и помещении их результатов в новую таблицу.
Изменение структуры хранимых процедур
Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.
Компонент Database Engine поддерживает тип данных CURSOR . Этот тип данных используется для объявления курсоров в хранимых процедурах. Курсор - это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.
Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE . Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.
Хранимые процедуры и среда CLR
SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.
Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure , которая запускается на выполнение инструкцией RECONFIGURE . В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:
USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:
Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.
Используя инструкцию CREATE ASSEMBLY , создать соответствующий выполняемый файл.
Выполнить процедуру, используя инструкцию EXECUTE.
На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.
Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.
В примере ниже показан исходный код хранимой процедуры на языке C#:
Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures { public static int CountEmployees() { int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; } }
В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure , который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection . Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd , которому передается нужная SQL-команда.
В следующем фрагменте кода:
Cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee";
используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.
Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).
USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE
Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM {dll_file} Соглашения по синтаксису
В параметре assembly_name указывается имя сборки. В необязательном предложении AUTHORIZATION указывается имя роли в качестве владельца этой сборки. В предложении FROM указывается путь, где находится загружаемая сборка.
Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.
Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.
Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.
В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:
USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees
Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:
assembly_name.class_name.method_name
assembly_name - указывает имя сборки;
class_name - указывает имя общего класса;
method_name - необязательная часть, указывает имя метода, который задается внутри класса.
Выполнение процедуры CountEmployees показано в примере ниже:
USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Вернет 7
Инструкция PRINT возвращает текущее количество строк в таблице Employee.
Цель работы – научиться создавать и использовать хранимые процедуры на сервере БД.
1. Проработка всех примеров, анализ результатов их выполнения в утилите SQL Server Management Studio. Проверка наличия созданных про- цедур в текущей БД.
2. Выполнение всех примеров и заданий по ходу лабораторной работы.
3. Выполнение индивидуальных заданий по вариантам.
Пояснения к выполнению работы
Для освоения программирования хранимых процедур используем при- мер базы данных c названием DB_Books , которая была создана в лабора- торной работе №1. При выполнении примеров и заданий обращайте вни- мание на соответствие названий БД, таблиц и других объектов проекта.
Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде.
Типы хранимых процедур
Системные хранимые процедуры предназначены для выполнения раз- личных административных действий. Практически все действия по адми- нистрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные дейст- вия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе дан- ных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на ло- кальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При созда- нии такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
Создание, изменение хранимых процедур
Создание хранимой процедуры предполагает решение следующих за- дач: планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров храни- мой процедуры, хранимые процедуры могут обладать входными и выход- ными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.
Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:
{ CREATE | ALTER } PROC[ EDURE] имя_процедуры [ ;номер] [ { @имя_параметра тип_данных } [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION] AS sql_оператор [ ... n]Рассмотрим параметры данной команды.
Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.
Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных параметров хранимой процедуры подходят любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.
Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.
Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра.
Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.
Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.
Удаление хранимой процедуры
DROP PROCEDURE { имя_процедуры} [ ,... n]
Выполнение хранимой процедуры
Для выполнения хранимой процедуры используется команда: [ [ EXEC [ UTE] имя_процедуры [ ;номер] [ [ @имя_параметра= ] { значение | @имя_переменной} [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]
Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.
Использование ключевого слова OUTPUT при вызове процедуры раз- решается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естест- венно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же тре- буется опустить параметры, для которых определено значение по умолча- нию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.
Отметим, что при вызове процедуры указываются либо имена пара- метров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.
Использование RETURN в хранимой процедуре
Позволяет выйти из процедуры в любой точке по указанному условию, а также позволяет передать результат выполнения процедуры числом, по которому можно судить о качестве и правильности выполнения процеду- ры. Пример создания процедуры без параметров:
CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GOЗадание 1.
EXEC Count_BooksПроверьте результат.
Пример создания процедуры c входным параметром:
CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GOЗадание 2 . Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды
EXEC Count_Books_Pages 100Проверьте результат.
Пример создания процедуры c входными параметрами:
CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOЗадание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды
EXEC Count_Books_Title 100 , "П%"Проверьте результат.
Пример создания процедуры c входными параметрами и выходным параметром:
CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOЗадание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд:
Sql> Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q
Проверьте результат.
Пример создания процедуры c входными параметрами и RETURN:
CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкин А.С." RETURN 1 ELSE RETURN 2Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_statusПример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:
CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды
EXEC update_procПример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @kЗадание 7.
EXEC select_author "Пушкин А.С." или select_author @k= "Пушкин А.С." или EXEC select_author @k= "Пушкин А.С."Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раза (по умолчанию в 2 раза):
CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @pПроцедура не возвращает никаких данных.
Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:
EXEC update_proc 4 или EXEC update_proc @p = 4 или EXEC update_proc --будет использовано значение по умолчанию.Пример создания процедуры с входным и выходным параметрами. Создать процедуру для определения количества заказов, совершенных за указанный период:
CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:
DECLARE @c2 INT EXEC count_purchases ’01- jun- 2006 ’, ’01- jul- 2006 ’, @c2 OUTPUT SELECT @c2Варианты заданий к лабораторной работе №4
Общие положения. В утилите SQL Server Management Studio создать новую страницу для кода (кнопка «Создать запрос»). Программно сделать активной созданную БД DB_Books с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно было передавать значения полей, по которым осуществляется поиск.
Например, исходное задание и запрос в лабораторной работе №2:
/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком- паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) „ОАО МИР“.
SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ОАО МИР"*/ –В данной работе будет создана процедура:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp–Для запуска процедуры используется команда:
EXEC select_name_company "ОАО МИР"Список заданий
В утилите SQL Server Management Studio создать новую программу. Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.
Вариант 1
1. Вывести список сотрудников, у которых есть хотя бы один ребенок.
2. Вывести список детей, которым выдали подарки в указанный период.
3. Вывести список родителей, у которых есть несовершеннолетние дети.
4. Вывести информацию о подарках со стоимостью больше указанного числа, отсортированных по дате.
Вариант 2
1. Вывести список приборов с указанным типом.
2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.
3. Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.
4. Вывести информацию о мастерах с разрядом больше указанного числа или с датой приема на работу меньше указанной даты.
Вариант 3
2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.
3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.
4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.
Вариант 4
1. Вывести список лекарств с указанным показанием к применению.
2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.
3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.
Вариант 5
2. Вывести список списанного оборудования по указанной причине.
3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный период.
4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения
Вариант 6
1. Вывести список блюд с весом больше указанного числа.
2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.
3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.
4. Вывести порядок приготовления блюда и название блюда с количеством углеводов больше определенного значения или количеством калорий больше указанного значения.
Вариант 7
1. Вывести список сотрудников с указанной должностью.
3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с определенным типом документа или с датой регистрации больше указанного значения.
Вариант 8
1. Вывести список сотрудников с указанной причиной увольнения.
3. Вывести дату регистрации, причину увольнения, ФИО сотрудника для документов, зарегистрированных в указанный период.
Вариант 9
1. Вывести список сотрудников, бравших отпуск указанного типа.
2. Вывести список документов с датой регистрации в указанный период.
3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.
Вариант 10
1. Вывести список сотрудников с указанной должностью.
2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.
3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.
Вариант 11
1. Вывести список сотрудников, назначенных на указанную должность.
2. Вывести список документов с датой регистрации в указанный период.
3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.
Вариант 12
3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированный по количеству обращений по убыванию.
Вариант 13
1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.
3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.
4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.
Вариант 14
1. Вывести список цветков с указанным типом листа.
2. Вывести список кодов поступлений, по которым продано цветов на суммы больше определенного значения.
3. Вывести дату поступления, сумму, названия поставщика и цветов по определенному коду поставщика.
4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.
Вариант 15
1. Вывести список клиентов, заехавших в номера в указанный период.
2. Вывести общую сумму оплат за номера для каждого клиента.
3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных клиентов в номерах определенного типа.
Вариант 16
1. Вывести список оборудования с указанным типом.
2. Вывести список оборудования, которое брал в прокат определенный клиент.
3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированных по количеству обращений по убыванию.
4. Вывести информацию о клиентах, отсортированных по адресам.
Вариант 17
1. Вывести список ценностей с закупочной стоимостью больше определенного значения или сроком гарантии больше указанного числа.
2. Вывести список мест нахождения материальных ценностей, в названии которых встречается указанное слово.
3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.
4. Вывести список материально ответственных лиц с датой приема на работу в указанном диапазоне.
Вариант 18
1. Вывести список ремонтных работ, выполненных определенным мастером.
2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.
3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.
4. Вывести список мастеров с датой приема на работу в указанном диапазоне.
Вариант 19
1. Вывести список лекарств с определенным показанием.
2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.
3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.
4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.
Вариант 20
1. Вывести список сотрудников с указанной должностью.
2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.
3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт исполнения для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.
SQL - Урок 15. Хранимые процедуры. Часть 1.
Как правило, мы в работе с БД используем одни и те же запросы, либо набор последовательных запросов. Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере. Это очень удобный инструмент, и сейчас вы в этом убедитесь. Начнем с синтаксиса:CREATE PROCEDURE имя_процедуры (параметры) begin операторы end
Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы - это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. В уроке 10 , когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:
INSERT INTO customers (name, email) VALUE ("Иванов Сергей", "[email protected]");
Т.к. подобный запрос мы будем использовать каждый раз, когда нам необходимо будет добавить нового покупателя, то вполне уместно оформить его в виде процедуры:
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("Сычов Валерий", "[email protected]")//
Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (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:
Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (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 //
Проверим работу процедуры, с разными входными параметрами:
Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление 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, а затем проверить работу:
Как видите, сложные запросы или их последовательность действительно проще один раз оформить в хранимую процедуру, а дальше просто обращаться к ней, указывая необходимые параметры. Это значительно сокращает код и делает работу с запросами более логичной.
В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов ) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
- Для более подробного изучения языка T-SQL также рекомендую почитать книгу — Путь программиста T-SQL. Самоучитель по языку Transact-SQL;
- Профессиональные онлайн-курсы по T-SQL
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT , например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: UNSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен ), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL » представлен пример реализации данной возможности в виде хранимой процедуры.
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express . Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
Инструкция создания таблицы CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Инструкция добавления данных INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1, "Мышь", 100), (1, "Клавиатура", 200), (2, "Телефон", 400) GO --Запрос на выборку SELECT * FROM TestTable
Данные есть, теперь давайте переходить к созданию хранимых процедур.
Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE , после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.
Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену ), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @ ). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT ).
В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.
Вот код данной процедуры (его я также прокомментировал ).
Создаем процедуру CREATE PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Возвращаем данные SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Запуск хранимой процедуры на T-SQL – команда EXECUTE
Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT ). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам ).
Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.
Вот несколько разных, но эквивалентных способов запуска хранимых процедур, в частности нашей тестовой процедуры.
1. Вызываем процедуру без указания цены EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 1" --2. Вызываем процедуру с указанием цены EXEC TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 2", @Price = 300 --3. Вызываем процедуру, не указывая название параметров EXEC TestProcedure 1, "Тестовый товар 3", 400
Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE . Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.
Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.
Изменяем процедуру ALTER PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE .
Например, давайте удалим созданную нами тестовую процедуру.
DROP PROCEDURE TestProcedure
При удалении хранимых процедур стоит помнить о том, что, если на процедуру будут ссылаться другие процедуры или SQL инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
