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

Лабораторная работа №9

Тема: Хранимые процедуры и операторы управления.

Цель: Научиться применять хранимые процедуры.

Задание:

1. Изучить теоретические сведения.

2. Определить хранимую процедуру.

3. Использовать различные операнды в процедурах.

4. Отображение и редактирование процедур.

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

6. Компиляция и автоматическая перекомпиляция процедур.

7. Дать понятие о завершающей команде GO.

Выполнение:

1. Определение хранимой процедуру.

· Процедура sp_help.

С помощью системной процедуры sp_help можно получить отчет о сохраненной процедуре. Например, пользователь может получить информацию о сохраненной процедуре из созданной базы данных с помощью следующей команды:

sp_help название_процедуры;

· Процедура sp_helptext.

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

sp_helptext название_процедуры;

2. Хранимые процедуры с одним и тем же именем, но с разными номерами версий, отображаются вместе и удаляются вместе. Инструкция Transact-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_, # и ##, можно определить создаваемую процедуру соответственно как системную или временную. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнять команду CREATE PROCEDURE в контексте этой базы данных. Переключение текущей базы данных выполняется с помощью команды USE.

Number – идентификационный номер хранимой процедуры, однозначно опре-деляющий ее в группе процедур.

parameter – имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Как и при работе с локальными переменными, имена параметров хранимой процедуры должны начинаться с символа @. В одной хранимой процедуре можно использовать множество параметров (до 1024). Для этого достаточно разделить их запятыми.

data_type — тип данных, который будет иметь соответствующий параметр хранимой процедуры. Для определения параметров можно использовать любые типы данных SQL Server, включая text, ntext и image, а также пользовательские типы данных. Однако заметим, что тип данных cursor может быть ис-пользован для параметров хранимых процедур только как выходной параметр, то есть с указанием ключевого слова OUTPUT.

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

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

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

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

FOR REPLICATION — это ключевое слово используется при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публика-цию. Когда процедура, созданная с параметром FOR REPLICATION, копируется механизмами репликации на подписчиков, она не может быть выполнена стандартными средствами. Ее вызов осуществляется только подсистемой репликации. Параметр FOR REPLICATION не может быть использован совместно с параметром RECOMPILE.

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

AS — это ключевое слово свидетельствует о начале собственно тела хранимой процедуры, то есть набора команд Transact-SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут использоваться практически все команды Transact-SQL, объявляться транзакции, устанавливаться блокировки и производиться вызовы других хранимых процедур. Для выхода из хранимой процедуры можно использовать команду RETURN.

3. Процедура для уменьшения цены товара первого сорта на 10%(без параметров):

CREATE PROC proc1

AS

UPDATE Товар SET Цена=Цена*0.9

WHERE Сорт=’первый’

Для обращения к процедуре можно использовать команды: EXEC proc1 или proc1

Для удаления существующей хранимой процедуры используется оператор DROP PROCEDURE (Удалить процедуру). С помощью одного такого оператора можно удалить несколько хранимых процедур. Для этого после ключевых слов DROP PROCEDURE нужно перечислить имена процедур. Синтаксис оператора DROP PROCEDURE выглядит следующим образом:

DROP PROCEDURE имя_процедуры_1, …, имя_процедуры_n

4. Компиляция процедуры.

Преимущество использования хранимой процедуры для выполнения набора операторов Transact-SQL состоит в том, что она компилируется при первом ее выполнении. Во время компиляции операторы Transact-SQL, содержащиеся в процедуре, преобразуются из первоначального символьного представления в двоичное. Кроме того, во время компиляции любые объекты, на которые есть ссылки в процедуре, преобразуются в альтернативную форму. Например, имена таблиц преобразуются в их объектные идентификаторы, а имена столбцов – в идентификаторы столбцов.

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

Автоматическая перекомпиляция процедур

Процедура всегда перекомпилируется при ее первом запуске, произошедшем с момента очередного перезапуска SQL-Server , который обычно происходит после перезагрузки операционной системы.

5. Команда GO используется для обозначения окончания набора операторов. При этом она помещается в отдельной строке.

Она не нужна также для наборов операторов Transact-SQL, которые выполняются внутри

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

триггеры или виды. В следующем примере команда GO сначала используется для

активизации выполнения команды USE, а затем для обозначения окончания второго

пакета, состоящего из одного оператора SELECT:

USE MyCompany

GO

SELECT * FROM Employee

GO

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

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

пример: допустим у нас есть таблица «Sotrydnik» БД “Ludi”. Необходимо извлечь информацию о сотрудниках, получающих зарплату более некоторого входного параметра рублей. Модифицируем предыдущую процедуру.

1. Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” - Programmability – Stored Procedures.

2. Шелкнуть правой кнопкой на хранимой процедуре dlastat1 и выбрать команду Modify.

3. Модифицируем ранее созданную процедуру таким образом:

CREATE PROCEDURE dlastat1

OKL int

AS

BEGIN

SELECT * from Sotrydnik

Where Oklad>=@okl

END

GO

где @OKL

входной параметр типа int

Использование выходных параметров.

Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” - Programmability – Stored Procedures.

2. Щелкаемправой кнопкой мышина папке Stored Procedures и выбираем команду New Stored Procedures.

3. Набираем код хранимой процедуры, который будем иметь вид:

CREATE PROCEDURE calc

@first [int],

@sec [int],

Ret [int] output

AS

BEGIN

SET @ret=@first+@sec

END

GO

Вывод: рассмотрел создание и структуру хранимых процедур, использование системных процедур: sp_help и sp_helptext.