Инфоурок / Другое / Другие методич. материалы / Практическое занятие по МДК 02.02. Технология разработки и защиты баз данных на тему "Создание, изменение и удаление хранимых процедур. Применение параметров в хранимых процедурах"
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

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

Только сейчас Вы можете пройти дистанционное обучение прямо на сайте "Инфоурок" со скидкой 40% по курсу повышения квалификации "Организация работы с обучающимися с ограниченными возможностями здоровья (ОВЗ)" (72 часа). По окончании курса Вы получите печатное удостоверение о повышении квалификации установленного образца (доставка удостоверения бесплатна).

Автор курса: Логинова Наталья Геннадьевна, кандидат педагогических наук, учитель высшей категории. Начало обучения новой группы: 27 сентября.

Подать заявку на этот курс    Смотреть список всех 216 курсов со скидкой 40%

Практическое занятие по МДК 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

  1. Создать хранимую процедуру для выполнения запроса, который выбирает фамилию студента, его адрес и телефон из таблицы Студенты. Вызвать ее на выполнение.

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

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

  4. Создать хранимую процедуру, которая выводила бы все поля таблицы Студенты, содержащие в поле Фамилия значение «Иванова».

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

  6. Создать хранимую процедуру, которая извлекала бы записи из таблицы Студенты с заданной Фамилией.

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

create proc proc_p1

@Фамилия nvarchar(50)

as

select *

from Туристы

where Фамилия=@Фамилия


После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля "Фамилия" установлен этот тип.


Запустим процедуру так:

exec proc_p1 'Андреева'

В результате выводится запись, соответствующая фамилии "Андреева".

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

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

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

Пр. Процедуры с выходными параметрами позволяют возвращать значения, получаемые в результате обработки SQL-конструкции при подаче определенного параметра. Представим, что нам нужно получать фамилию туриста по его коду (полю "Кодтуриста"). Создадим следующую процедуру:

create proc proc_po1

@TouristID int,

@LastName nvarchar(60) output

as

select @LastName = Фамилия from Туристы where Кодтуриста = @TouristID


Оператор output указывает на то, что выходным параметром здесь будет @LastName. Запустим эту процедуру, извлекая фамилию туриста, значение поля "Кодтуриста" которого равно "4":

declare @LastName nvarchar(60)

exec proc_po1 '4',

@LastName output

select @LastName


Оператор declare нужен для объявления поля, в которое будет выводиться значение. Получаем фамилию туриста


Для задания названия столбца можно применить псевдоним:

declare @LastName nvarchar(60)

exec proc_po1 '4',

@LastName output

select @LastName as 'Фамилия туриста'

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


Вариант 2

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

  2. Изменить хранимую процедуру так чтобы в запрос включались только Фамилия, адрес и телефон преподавателя. Вызвать ее на выполнение.

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

  4. Создать хранимую процедуру, которая выводила бы все поля таблицы Кадровый состав, содержащие в поле Должность значение «профессор».

  5. Создать хранимую процедуру, которая подсчитывала бы все записи таблицы Кадровый состав, содержащие в поле Должность значение «профессор».

  6. Создать хранимую процедуру, которая извлекала бы записи из таблицы Кадровый состав с заданным Именем.

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

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

  9. Создать хранимую процедуру, которая выводила бы фамилию преподавателя по заданному коду.

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

Контрольные вопросы:

  1. Что представляет собой хранимая процедура? Чем она отличается от сценариев?

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

  3. Как вызвать на выполнение хранимую процедуру?

  4. В чем различия, возникающие при использовании ALTER PROC и CREATE PROC при изменении процедуры?

  5. Запишите полный синтаксис объявления параметра.

  6. Для чего используются выходные параметры? Как указать выходные параметры?



Самые низкие цены на курсы переподготовки

Специально для учителей, воспитателей и других работников системы образования действуют 50% скидки при обучении на курсах профессиональной переподготовки.

После окончания обучения выдаётся диплом о профессиональной переподготовке установленного образца с присвоением квалификации (признаётся при прохождении аттестации по всей России).

Обучение проходит заочно прямо на сайте проекта "Инфоурок", но в дипломе форма обучения не указывается.

Начало обучения ближайшей группы: 27 сентября. Оплата возможна в беспроцентную рассрочку (10% в начале обучения и 90% в конце обучения)!

Подайте заявку на интересующий Вас курс сейчас: https://infourok.ru

Общая информация

Номер материала: ДБ-392094

Похожие материалы

2017 год объявлен годом экологии и особо охраняемых природных территорий в Российской Федерации. Министерство образования и науки рекомендует в 2017/2018 учебном году включать в программы воспитания и социализации образовательные события, приуроченные к году экологии.

Учителям 1-11 классов и воспитателям дошкольных ОУ вместе с ребятами рекомендуем принять участие в международном конкурсе «Законы экологии», приуроченном к году экологии. Участники конкурса проверят свои знания правил поведения на природе, узнают интересные факты о животных и растениях, занесённых в Красную книгу России. Все ученики будут награждены красочными наградными материалами, а учителя получат бесплатные свидетельства о подготовке участников и призёров международного конкурса.

Конкурс "Законы экологии"