Создание процедуры в обозревателе объектов

1. В обозревателе объектов подключитесь к экземпляру компонента Компонент DatabaseEngine и разверните его.

2. Последовательно разверните узел Базы данных, базу данных AdventureWorks2012 и узел Программирование.

3. Щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Создать хранимую процедуру.

4. В меню Запрос выберите пункт Задание значений для параметров шаблона.

5. В диалоговом окне Задание значений для параметров шаблона введите для показанных параметров следующие значения.

6. Нажмите кнопку ОК.

7. В редакторе запросов замените инструкцию SELECT следующей инструкцией:

Transact-SQL

SELECT FirstName, LastName, Department

FROM HumanResources.vEmployeeDepartmentHistory

WHERE FirstName = @FirstName AND LastName = @LastName

AND EndDate IS NULL;

8. Для проверки синтаксиса выберите пункт Синтаксический анализ в меню Запрос. Если возвращается сообщение об ошибке, сравните инструкции с приведенными выше и при необходимости внесите исправления.

9. Чтобы создать процедуру, в меню Запрос выберите пункт Выполнить. Процедура создается как объект в базе данных.

10. Чтобы увидеть процедуру в обозревателе объектов, щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Обновить.

11. Чтобы выполнить процедуру, в обозревателе объектов щелкните правой кнопкой мыши имя хранимой процедуры HumanResources.uspGetEmployeesTest и выберите пункт Выполнение хранимой процедуры.

12. В окне Выполнение процедуры введите Margheim в качестве значения для параметра @LastName и Diane в качестве значения для параметра @FirstName.

«Если подробней что-то нужно будет дописать»

Хранимая процедура (storedprocedure) – это именованный набор команд Transact-SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базу данных. Она существует независимо от таблиц или каких-либо других объектов баз данных. Хранимая процедура может быть вызвана клиентской программой, другой хранимой процедурой или триггером. Возможно управлять правами доступа пользователей к хранимы процедурам. Прежде чем выполнить хранимую процедуру, сервер генерирует для неё так называемый план исполнения (executionplan), выполняет её оптимизацию и компиляцию. Выполняется кэширование плана исполнения процедуры, а также оптимизированного компилирования кода. Использование хранимых процедур реализует принцип модульного проектирования.

Системные хранимые процедуры (systemstoredprocedures) – это хранимые процедуры, поставляемые в составе MySQL. Предназначены для выполнения различных административных действий. Такие процедуры имеют префикс sp_ . Они хранятся в базе данных master.

Пользовательские хранимые процедуры (user-definedstoredprocedures) – это процедуры созданные пользователями, реализующие те или иные действия (полноценный объект баз данных). Следствием этого является то, что каждая хранимая процедура хранится в конкретной базе данных.

Временные хранимые процедуры (temporarystoredprocedures) – эти процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Бывают локальные и глобальные.

Локальные временные хранимые процедуры(localtemporarystoredprocedure) – могут быть вызваны только из того соединения, в котором они были созданы. При создании такой процедуры необходимо дать ей имя, начинающееся символом #. Они хранятся в базе данных tempdb и автоматически удаляются при отключении пользователя.

Глобальные временные хранимые процедуры (globaltemporarystoredprocedure) – доступны для любых соединений сервера, на котором была создана соответствующая глобальная временная хранимая процедура. При создании такой процедуры необходимо дать ей имя, начинающееся символом ## и удаляются при перезапуске или остановке сервера, а также при закрытии соединения в контексте которого были созданы.

Для создания хранимой процедуры на языке Transact-SQL используется SQL-оператор CREATE PROCEDURE. Синтаксисданногооператорадля MS SQL:

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement[ ...n ]

Procedure_name – имя создаваемой процедуры. Используются префиксы sp_, #, ## (значение указано выше). Как видно из синтаксиса, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Чтобы разместить хранимую процедуру в конкретной базе данных нужно создать её в контексте этой базы данных.

Number – идентификационный номер хранимой процедуры, однозначно определяющий её в группе процедур. На пример: orderproc;1, orderproc;2. Процедура orderproc объединяет в себе две процедуры. При вызове DROP PROCEDURE orderproc, будут удалены все процедуры этой группы;

@Parameter – имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Имена параметров должны начинаться с символа @. В одной хранимой процедуре может использоваться до 1024 параметров.

Data_type – тип данных, который будет иметь соответствующий параметр хранимой процедуры. Можно использовать все типы данных, включая text, ntext и image и пользовательские типа данных. Однако, тип данных cursor может использоваться только как выходной параметр (с указанием ключевого слова OUTPUT).

VARYING – ключевое слово, которое используется совместно с параметром OUTPUT, имеющим тип данных cursor. В качестве выходного параметра будет представлено результирующее множество.

Default – значение, которое будет принимать соответствующий параметр по умолчанию. При вызове процедуры, явно можно будет не указывать значение соответствующего параметра. Будет использовано значение, созданное с помощью этого параметра.

OUTPUT – его наличие указывает, что параметр предназначается для возвращения данных из хранимой процедуры. Но этот параметр также может использоваться и для передачи значений в процедуру. Значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Нельзя использовать выражения и константы, допустимые для обычных параметров.

n – количество определённых параметров.

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

Синтаксис оператора:

[ [ EXEC [ UTE ] ]

{

[ @return_status = ]

{procedure_name[ ;number ] | @procedure_name_var

}

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] }]

[ ,…n ]

[ WITH RECOMPILE]

Более короткий вариант:

EXEC procedure_name

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } ]

[ ,…n]

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

Триггер – поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.

Синтаксис создания триггера

CREATE [DEFINER = { имя_ пользователя | CURRENT_USER }]TRIGGERимя_триггеравремя_триггерасобытие_срабатывания_триггера ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

Время_триггера - определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.

Событие_срабатывания_триггера:

- INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE);

- UPDATE: когда сущность (строка) ;

- DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE).