Практическое занятие по МДК 02.02. Технология разработки и защиты баз данных на тему Создание, изменение и удаление хранимых процедур. Применение параметров в хранимых процедурах

Практическое занятие №12
Создание, изменение и удаление хранимых процедур. Применение параметров в хранимых процедурах
Цель занятия: Научиться создавать, изменять и удалять хранимые процедуры. Применять параметры и использовать выходные параметры в хранимых процедурах.
Теоретические сведения
Хранимые процедуры представляют собой группы связанных между собой операторов SQL, применение которых делает работу программиста более легкой и гибкой, поскольку выполнить хранимую процедуру часто оказывается гораздо проще, чем последовательность отдельных операторов SQL. Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:
необходимые операторы уже содержатся в базе данных;
все они прошли этап синтаксического анализа и находятся в исполняемом формате; перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию;
хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
хранимые процедуры могут вызывать другие хранимые процедуры и функции;
хранимые процедуры могут быть вызваны из прикладных программ других типов;
как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;
хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.
Создание хранимой процедуры предполагает решение следующих задач:
определение типа создаваемой хранимой процедуры: временная или пользовательская.
планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.
Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:
<определение_процедуры>::=
{CREATE | ALTER } [PROCEDURE] имя_процедуры [{@имя_параметра тип_данных }
[=default][OUTPUT] ][,...n]
AS
sql_оператор [...n]
Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров.
Удаление хранимой процедуры осуществляется командой:
DROP PROCEDURE {имя_процедуры} [,...n]
Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] имя_процедуры
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]
Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера. Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.
Ход работы
Вариант 1
Создать хранимую процедуру для выполнения запроса, который выбирает фамилию студента, его адрес и телефон из таблицы Студенты. Вызвать ее на выполнение.
Изменить хранимую процедуру так чтобы в запрос включалась еще и группа, в которой учиться студент. Вызвать ее на выполнение.
Создать хранимую процедуру, которая выводила бы первые три значения из таблицы студенты.
Создать хранимую процедуру, которая выводила бы все поля таблицы Студенты, содержащие в поле Фамилия значение «Иванова».
Создать хранимую процедуру, которая подсчитывала бы все записи таблицы Студенты.
Создать хранимую процедуру, которая извлекала бы записи из таблицы Студенты с заданной Фамилией.
Пр. На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице "Туристы" по заданной фамилии создаем следующую процедуру:
create proc proc_p1
@Фамилия nvarchar(50)
asselect *
from Туристы
where Фамилия=@Фамилия
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля "Фамилия" установлен этот тип.
Запустим процедуру так:
exec proc_p1 'Андреева'
В результате выводится запись, соответствующая фамилии "Андреева".
Создать хранимую процедуру, которую можно использовать для вставки в эту таблицу данных о новом студенте. Вызвать ее на выполнение с новыми параметрами. Вызвать на исполнение первую хранимую процедуру, чтобы ознакомиться с произошедшими изменениями.
Подготовить еще один вариант хранимой процедуры, предназначенной для вставки данных о студентах. Обозначить параметр, соответствующий номеру телефона, как необязательный.
Создать хранимую процедуру, которая выводила бы фамилию студента по заданному коду.
Пр. Процедуры с выходными параметрами позволяют возвращать значения, получаемые в результате обработки SQL-конструкции при подаче определенного параметра. Представим, что нам нужно получать фамилию туриста по его коду (полю "Кодтуриста"). Создадим следующую процедуру:
create proc proc_po1
@TouristID int,
@LastName nvarchar(60) output
asselect @LastName = Фамилия from Туристы where Кодтуриста = @TouristIDОператор output указывает на то, что выходным параметром здесь будет @LastName. Запустим эту процедуру, извлекая фамилию туриста, значение поля "Кодтуриста" которого равно "4":
declare @LastName nvarchar(60)
exec proc_po1 '4',
@LastName outputselect @LastNameОператор declare нужен для объявления поля, в которое будет выводиться значение. Получаем фамилию туриста
Для задания названия столбца можно применить псевдоним:
declare @LastName nvarchar(60)
exec proc_po1 '4',
@LastName output
select @LastName as 'Фамилия туриста'
Создать хранимую процедуру, которая подсчитывала бы количество студентов с телефонами имеющими сочетание цифр 65.
Вариант 2
Создать хранимую процедуру для выполнения запроса, который выбирает все записи из таблицы Кадровый состав. Вызвать ее на выполнение.
Изменить хранимую процедуру так чтобы в запрос включались только Фамилия, адрес и телефон преподавателя. Вызвать ее на выполнение.
Создать хранимую процедуру, которая выводила бы первых пять значений из таблицы Кадровый состав.
Создать хранимую процедуру, которая выводила бы все поля таблицы Кадровый состав, содержащие в поле Должность значение «профессор».
Создать хранимую процедуру, которая подсчитывала бы все записи таблицы Кадровый состав, содержащие в поле Должность значение «профессор».
Создать хранимую процедуру, которая извлекала бы записи из таблицы Кадровый состав с заданным Именем.
Создать хранимую процедуру, которую можно использовать для вставки в таблицу Кадровый состав данных о новом преподавателе. Вызвать ее на выполнение с новыми параметрами. Вызвать на исполнение первую хранимую процедуру, чтобы ознакомиться с произошедшими изменениями.
Подготовить еще один вариант хранимой процедуры, предназначенной для вставки данных о преподавателях. Обозначить параметр, соответствующий адресу и номеру телефона, как необязательный.
Создать хранимую процедуру, которая выводила бы фамилию преподавателя по заданному коду.
Создать хранимую процедуру, которая подсчитывала бы количество преподавателей живущих по улице Садовая.
Контрольные вопросы:
Что представляет собой хранимая процедура? Чем она отличается от сценариев?
Запишите полный синтаксис создания хранимой процедуры.
Как вызвать на выполнение хранимую процедуру?
В чем различия, возникающие при использовании ALTER PROC и CREATE PROC при изменении процедуры?
Запишите полный синтаксис объявления параметра.
Для чего используются выходные параметры? Как указать выходные параметры?