Файл: Методические указания к практическим занятиям для студентов направления подготовки 230100. 68.pdf

41 пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. При- своить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного ука- зания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произ- вольном порядке.
Отметим, что при вызове процедуры указываются либо имена па- ра- метров со значениями, либо только значения без имени параметра.
Их комбинирование не допускается.
Использование 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
Pag- es
>=
@Count_pages
Go

42
Задание 2. Запуск процедуры Count_Books_Pages
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
Pag- es
>=
@Count_pages
AND
Title_book
LIKE
@Title
Go
Задание 3. Запуск процедуры Count_Books_Title
EXEC Count_Books_Title
100
,
'П%'
Пример создания процедуры с входными параметрами и выходным параметром:
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. Запуск процедуры с помощью набора команд T-SQL sql>
Declare @q As int
EXEC Count_Books_Itogo 100, 'П%', @q output select @q
Пример создания процедуры c входными параметрами

43 и RETURN:
CREATE
PROCEDURE checkname @param int
AS
IF
(
SELECT
Name_author
FROM
authors
WHERE
Code_author
=
@param
)
=
'Пушкин А.С.'
RETURN
1
ELSE
RETURN
2
Задание 5. Запуск процедуры с помощью набора команд T-SQL
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. Запуск процедуры
EXEC update_proc
Пример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:
CREATE
PROC select_author @k CHAR
(
30
)
AS
SELECT
*
FROM
Authors
WHERE
name_author
=
@k
Задание 7. Запуск процедуры с помощью набора команд

44
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. Запуск процедуры с помощью набора команд:
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
)

45
Задание 9. Запуск процедуры с помощью набора команд:
DECLARE @c2 INT
EXEC count_purchases ’01
- jun
-
2006
’
,
’01
- jul
-
2006
’
,
@c2 OUTPUT
SELECT
@c2
Задания для самостоятельного выполнения
Задание 1. Создать два – три представления из одной и несколь- ких таблиц используемой базы данных.
Задание 2. Создать хранимые процедуры для добавления, удале- ния и корректировки данных одной таблицы.
Задание 3. Создать хранимую процедуру, использующую цикл
WHILE с принудительным выходом из цикла, а также с приостанов- кой выполнения программы.
Задание 4. Создать хранимую процедуру, выполняющую добав- ление записей в родительскую и дочернюю таблицы.
Задание 5. Создать хранимую процедуру, удаляющую запись из ро- дительской таблицы и все связанные с ней записи из дочерних таблиц.
Индивидуальные задания
Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каж- дая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.
Вариант 1. БД «Учет выданных подарков несовершеннолетним детям сотрудников предприятия»
Код сотрудника
Код сотрудника Код ребенка
Фамилия
Имя ребенка
Стоимость подарка
Имя
Дата рождения Дата выдачи подарка
Отчество
Код ребенка
Код выдачи
Должность
Подразделение
Дата приема на работу

46 1. Вывести список сотрудников, у которых есть хотя бы один ре- бенок.
2. Вывести список детей, которым выдали подарки в указанный период.
3. Вывести список родителей, у которых есть несовершеннолет- ние дети.
4. Вывести информацию о подарках со стоимостью больше ука- занного числа, отсортированных по дате.
Вариант 2. БД «Учет выполненных ремонтных работ»
Код прибора в ремонте Код прибора
Код мастера
Название прибора
Код мастера
Фамилия мастера
Тип прибора
ФИО владельца прибора Имя мастера
Дата производства
Дата приема в ремонт
Отчество мастера
Вид поломки
Разряд мастера
Стоимость ремонта
Дата приема на работу код ремонта
1. Вывести список приборов с указанным типом.
2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.
3. Вывести список владельцев приборов и количество их обраще- ний, отсортированный по количеству обращений по убыванию.
4. Вывести информацию о мастерах с разрядом больше указанно- го числа или с датой приема на работу меньше указанной даты.
Вариант 3. БД «Продажа цветов»
Код цветка
Код цветка
Код продавца
Название цветка
Дата продажи
Фамилия
Сорт цветка
Цена продажи
Имя
Средняя высота
Код продавца
Отчество
Тип листа
Код продажи
Разряд
Цветущий
Оклад
Дополнительные сведения
Дата приема на работу

47 1. Вывести список цветков с указанным типом листа.
2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.
3. Вывести дату продажи, сумму, продавца и цветок по указанно- му коду продажи.
4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.
Вариант 4. БД «Поступление лекарственных средств»
Код лекарства
Код лекарства
Код поставщика
Название лекарства
Код поставщика
Сокращенное название
Показания к применению
Дата поставки
Полное название
Единица измерения
Цена за единицу
Юридический адрес
Количество в упаковке
Количество
Телефон
Название производителя
Код поступления
ФИО руководителя
1. Вывести список лекарств с указанным показанием к примене- нию.
2. Вывести список дат поставок, по которым продано больше ука- занного числа одноименного лекарства.
3. Вывести дату поставки, сумму, ФИО руководителя от постав- щика и название лекарства по коду поступления больше указанного числа.
4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекар- ства меньше определенного значения.
Вариант 5. БД «Списание оборудования»
Код оборудования
Код оборудования
Код сотрудника
Название оборудования
Причина списания
Фамилия
Тип оборудования
Дата списания
Имя
Дата поступления
Код сотрудника
Отчество
ФИО ответственного
Код списания
Должность
Место установки
Подразделение
Дата приема на работу

48 1. Вывести список сотрудников с указанной должностью.
2. Вывести список списанного оборудования по указанной причине.
3. Вывести дату поступления, название оборудования, ФИО от- ветственного и дату списания для оборудования, списанного в указан- ный период.
4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения
Вариант 6. БД «Поваренная книга»
Код блюда
Код блюда
Код продукта
Тип блюда
Код продукта
Название продукта
Вес блюда
Объем продукта
Ед измерения
Порядок приготовления
Количество калорий
Количество углеводов
1. Вывести список блюд с весом больше указанного числа.
2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.
3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.
4. Вывести порядок приготовления блюда и название блюда с ко- личеством углеводов больше определенного значения или количе- ством калорий больше указанного значения.
Вариант 7. БД «Регистрация входящей документации»
Код регистратора
Код документа
Код организации- отправителя
Фамилия
Номер документа
Сокращенное название
Имя
Дата регистрации
Полное название
Отчество
Краткое содержание доку- мента
Юридический адрес
Должность
Тип документа
Телефон
Дата приема на ра- боту
Код организации- отправителя
ФИО руководителя
Код регистратора

49 1. Вывести список сотрудников с указанной должностью.
2. Вывести список документов, в содержании которых встречает- ся указанный фрагмент слова.
3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в ука- занный период.
4. Вывести список зарегистрированных документов с определенным типом документа или с датой регистрации больше указанного значения.
Вариант 8. БД «Увольнение сотрудника»
Код сотрудника
Код документа
Код статьи увольнения
Фамилия
Номер документа
Название статьи увольнения
Имя
Дата регистрации
Причина увольнения
Отчество
Дата увольнения
Номер статьи увольнения
Должность
Код статьи увольне- ния
Номер пункта/подпункта уволь- нения
Подразделение
Код сотрудника
Дата приема на ра- боту
Денежная компенса- ция
1. Вывести список сотрудников с указанной причиной увольнения.
2. Вывести список документов с датой регистрации в указанный период.
3. Вывести дату регистрации, причину увольнения, ФИО сотруд- ника для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с кодом до- кумента в указанном диапазоне.
Вариант 9. БД «Приказ на отпуск»
Код сотрудника
Код документа
Код отпуска
Фамилия
Номер документа
Тип отпуска
Имя
Дата регистрации
Оплата отпуска
Отчество
Дата начала отпуска
Льготы по опуску
Должность
Дата окончания отпуска
Подразделение
Код сотрудника
Дата приема на работу
Код отпуска

50 1. Вывести список сотрудников, бравших отпуск указанного типа.
2. Вывести список документов с датой регистрации в указанный период.
3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с кодом до- кумента в указанном диапазоне.
Вариант 10. БД «Регистрация выходящей документации»
Код отправителя
Код документа
Код организации- получателя
Фамилия
Номер документа
Сокращенное название
Имя
Дата регистрации
Полное название
Отчество
Краткое содержание доку- мента
Юридический адрес
Должность
Тип документа
Телефон
Дата приема на ра- боту
Код организации- получателя
ФИО руководителя
Код отправителя
1. Вывести список сотрудников с указанной должностью.
2. Вывести список документов, в содержании которых встречает- ся указанный фрагмент слова.
3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указан- ный период.
4. Вывести список зарегистрированных документов с указанным ти- пом документа или с кодом документа меньше определенного значения.
Вариант 11. БД «Назначение на должность»
Код сотрудника
Код документа
Код должности
Фамилия
Номер документа Название должности
Имя
Дата регистрации Льготы по должности
Отчество
Дата назначения
Требования к квалификации
Дата приема на работу
Код сотрудника
Дата рождения
Код должности
Пол

51 1. Вывести список сотрудников, назначенных на указанную должность.
2. Вывести список документов с датой регистрации в указанный период.
3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.
4. Вывести список зарегистрированных документов с кодом до- кумента в указанном диапазоне.
Вариант 12. БД «Выдача оборудования в прокат»
Код клиента
Код выдачи
Код оборудования
Фамилия
Номер документа
Название оборудования
Имя
Дата начала проката
Тип оборудования
Отчество
Дата окончания проката Дата поступления в прокат
Адрес
Код оборудования
Телефон
Код клиента
Серия и номер паспорта Стоимость
1. Вывести список оборудования с указанным типом.
2. Вывести список оборудования, которое брал в прокат опреде- ленный клиент.
3. Вывести список лиц, бравших оборудование в прокат и коли- чество их обращений, отсортированный по количеству обращений по убыванию.
4. Вывести информацию о клиентах, отсортированных по адресам.
Вариант 13. БД «Списание оборудования из проката»
Код оборудования
Код оборудования
Код сотрудника
Название оборудования
Причина списания
Фамилия
Тип оборудования
Дата списания
Имя
Дата поступления в прокат
Код сотрудника
Отчество
Номер документа
Должность
Дата регистрации
Дата приема на работу
Код списания

52 1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.
3. Вывести количество списанного оборудования, сгруппирован- ного по типам оборудования.
4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.
Вариант 14. БД «Прием цветов в магазин»
Код цветка
Код цветка
Код поставщика
Название цветка
Дата поступления
Сокращенное название
Сорт цветка
Цена за единицу
Полное название
Средняя высота
Код поставщика
Юридический адрес
Тип листа
Код поступления
Телефон
Цветущий
Количество
ФИО руководителя
Дополнительные сведения
1. Вывести список цветков с указанным типом листа.
2. Вывести список кодов поступлений, по которым продано цве- тов на суммы больше определенного значения.
3. Вывести дату поступления, сумму, названия поставщика и цве- тов по определенному коду поставщика.
4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.
Вариант 15. БД «Регистрация клиентов гостиницы»
Код номера
Код регистрации
Код клиента
Тип номера
Код номера
Фамилия
Перечень удобств
Дата заезда
Имя
Цена за сутки
Дата выезда
Отчество
Стоимость
Адрес
Код клиента
Телефон
Серия и номер паспорта