Файл: Практикум направление подготовки 09. 03. 03 Прикладная информатика Уровень высшего образования Бакалавриат.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.03.2024
Просмотров: 50
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
СОДЕРЖАНИЕ
Практическое занятие 10. Хранимые процедуры
Практическое занятие 11. Использование триггеров. Агрегация данных с использованием триггеров
Практическое занятие 12. Наработка навыков создания хранимых процедур, функций и триггеров
Практическое занятие 13. Самостоятельная работа по написанию хранимых процедур, функций и триггеров
k_bill, содержащая внешний ключ.
Таблицы k_bill (Счета) и k_payment (Платежи) связаны связью «Произведена оплата счета» (см. рис. 10.1) с мощностью 1:М. Эта связь означает, что по одному счету может быть произведено несколько оплат, но каждая оплата относится к одному и только одному счету. В этой связи дочерней является таблица k_payment, содержащая внешний ключ. Для связывания используется идентифицирующая связь, включающая атрибут номер счета (bill_num) в состав атрибутов первичного ключа таблицы k_payment. Использование идентифицирующей связи означает, что для идентификации платежа недостаточно его номера, требуется указание номера счета, по которому произведен платеж.
3. SQL-скрипт для создания и заполнения БД «Продажи» (sales):
CREATE database sales;
USE sales;
CREATE TABLE k_staff (
staff_num INT NOT NULL AUTO_INCREMENT,
staff_name VARCHAR(45) NOT NULL,
staff_post VARCHAR(45) NULL,
staff_hiredate DATE NULL,
staff_termdate DATE NULL,
k_dept_dept_num INT NOT NULL,
PRIMARY KEY (staff_num));
CREATE TABLE k_dept (
dept_num INT NOT NULL AUTO_INCREMENT,
dept_full_name VARCHAR(45),
dept_short_name VARCHAR(10),
k_staff_staff_num INT NULL DEFAULT NULL,
PRIMARY KEY (dept_num));
alter table k_staff add CONSTRAINT fk_k_staff_k_dept1 FOREIGN KEY (k_dept_dept_num) REFERENCES k_dept (dept_num) ON DELETE NO ACTION ON UPDATE NO ACTION;
alter table k_dept add CONSTRAINT fk_k_dept_k_staff1 FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE TABLE k_firm (
firm_num INT NOT NULL AUTO_INCREMENT,
firm_name VARCHAR(45) NOT NULL,
firm_addr VARCHAR(45) NULL,
firm_phone VARCHAR(20) NULL,
PRIMARY KEY (firm_num));
CREATE TABLE k_contract (
contract_num INT NOT NULL AUTO_INCREMENT,
contract_date DATE NOT NULL,
contract_type ENUM('A','B','C') NOT NULL,
k_firm_firm_num INT NOT NULL,
k_staff_staff_num INT NOT NULL,
PRIMARY KEY (contract_num),
CONSTRAINT fk_k_kontrakt_k_firm1
FOREIGN KEY (k_firm_firm_num) REFERENCES k_firm (firm_num)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_kontrakt_k_staff1
FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_bill (
bill_num INT NOT NULL AUTO_INCREMENT,
bill_date DATE NOT NULL,
bill_sum DECIMAL(9,2) NOT NULL,
bill_term DATE NOT NULL,
bill_peni DECIMAL(6,2) NULL,
k_contract_contract_num INT NOT NULL,
PRIMARY KEY (bill_num),
CONSTRAINT fk_k_bill_k_contract1
FOREIGN KEY (k_contract_contract_num) REFERENCES k_contract (contract_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_payment (
payment_num INT NOT NULL,
payment_date DATE NOT NULL,
payment_sum DECIMAL(9,2) NOT NULL,
k_bill_bill_num INT NOT NULL,
PRIMARY KEY (payment_num, k_bill_bill_num),
CONSTRAINT fk_k_payment_k_bill1
FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_price (
price_num INT NOT NULL AUTO_INCREMENT,
price_name VARCHAR(45) NOT NULL,
price_sum DECIMAL(9,2) NULL,
price_type VARCHAR(1) NULL,
PRIMARY KEY (price_num));
CREATE TABLE k_protokol (
k_bill_bill_num INT NOT NULL,
k_price_price_num INT NOT NULL,
kolvo INT NOT NULL,
price_sum DECIMAL(9,2) NOT NULL,
PRIMARY KEY (K_bill_bill_num, k_price_price_num),
CONSTRAINT fk_k_bill_has_k_price_k_bill1
FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_bill_has_k_price_K_price1
FOREIGN KEY (k_price_price_num) REFERENCES k_price (price_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
# Заполнение
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Альфа', 'Москва');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Бета', 'Казань');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Гамма', 'Париж');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Дельта', 'Лондон');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Омега', 'Токио');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Sales', 'Отдел продаж');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Mart', 'Отдел маркетинга');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Cust', 'Отдел гарантийного обслуживания');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Петров', 2, '2010-10-13','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Сидоров', 3, '2005-12-01','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Семенов', 1, '1990-01-01','Директор');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Григорьев', 3, '2008-12-19','Программист');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-11-12', '2011-12-12', 1000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-12-12', '2012-01-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2012-01-12', '2012-02-12',2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2011-12-12', '2012-01-12', 6000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2012-01-12', '2012-02-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(3, '2012-01-12', '2012-02-12', 2500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(4, '2011-12-12', '2012-01-12', 1500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2011-12-12', '2012-01-12', 1200);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2012-01-12', '2012-02-12', 10000);
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (1, 'Материализация духов', 1000.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (2, 'Раздача слонов', 100.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (3, 'Слоновый бивень', 3000.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (4, 'Моржовый клык', 1500.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (5, 'Копыто пегаса', 5000.00, 'Т');
# Отключение режима safe mode (запрет изменений без указания первичного ключа)
# при помощи команды SQL
# Если режим safe mode включен, то MySQL-сервер выдает ошибку 1175
SET SQL_SAFE_UPDATES = 0;
UPDATE k_dept SET k_staff_staff_num=2
WHERE dept_short_name='Mart';
UPDATE k_dept SET k_staff_staff_num=3
WHERE dept_short_name='Cust';
UPDATE k_dept SET k_staff_staff_num=1
WHERE dept_short_name='Sales';
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (1, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (2, 1, 2, 1000.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (3, 2, 20, 100.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (4, 3, 2, 3000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 2, 10, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (6, 1, 2, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (6, 2, 5, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (7, 4, 1, 1500.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (8, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (8, 2, 2, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (9, 5, 2, 5000.00);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-15', 1000.00, 2);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-13', 1500.00, 3);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-12', 1000.00, 4);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-05', 100.00, 7);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-25', 1000.00, 8);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-15', 500.00, 3);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-12', 900.00, 7);
Задание
2.1 Создайте процедуру, которая в качестве параметра получает фамилию сотрудника и печатает список всех договоров, которые он курирует.
Для запуска этой процедуры нужно выполнить, например, команду
или
2.2 Создайте процедуру, которая получает номер месяца и номер года и печатает договоры за указанный период.
Выполним процедуру:
2.3 Создайте процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент.
Содержимое таблицы "Прайс-лист" до выполнения процедуры:
Для запуска этой процедуры нужно выполнить, например, команду:
Содержимое таблицы "Прайс-лист" после выполнения процедуры:
Как видим, товар с номером 4 в прайс-листе уценен на 10%.
А что произойдет, если в нашей базе данных есть несколько товаров, количество продаж которых минимально? К сожалению, в нашем случае процедура выдаст ошибку: Error Code: 1172, Result consisted of more than one row. Когда в команде SELECT выбирается сразу несколько значений поля k_price из таблицы k_protokol, невозможно присвоить эти несколько значений одной переменной p. Данную ситуацию можно обработать с помощью так называемых курсоров.
Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.
Для работы с курсорами существуют следующие команды.
Объявление курсора:
DECLARE имя_курсора CURSOR FOR SELECT текст_запроса;
Таким образом, любой курсор создается на основе некоторого оператора SELECT.
Открытие курсора:
OPEN имя_курсора;
Только после открытия курсора он становится активным, и из него можно читать строки.
Чтение значений из следующей строки курсора в набор переменных:
FETCH имя_курсора INTO список_переменных;
Переменные в списке должны иметь то же количество и тип, что и столбцы курсора.
Закрытие курсора:
CLOSE имя_курсора;
При переборе строк курсора возникает необходимость проверки, добрались ли мы до конца курсора или еще нет. В разных СУБД для этого могут быть предусмотрены разные средства. В СУБД MySQL назначается обработчик состояния “NOT FOUND”. Определять его нужно сразу же после описания структуры курсора:
DECLARE CONTINUE HANDLER FOR NOT FOUND оператор;
Например, этот обработчик может выглядеть так:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Таблицы k_bill (Счета) и k_payment (Платежи) связаны связью «Произведена оплата счета» (см. рис. 10.1) с мощностью 1:М. Эта связь означает, что по одному счету может быть произведено несколько оплат, но каждая оплата относится к одному и только одному счету. В этой связи дочерней является таблица k_payment, содержащая внешний ключ. Для связывания используется идентифицирующая связь, включающая атрибут номер счета (bill_num) в состав атрибутов первичного ключа таблицы k_payment. Использование идентифицирующей связи означает, что для идентификации платежа недостаточно его номера, требуется указание номера счета, по которому произведен платеж.
3. SQL-скрипт для создания и заполнения БД «Продажи» (sales):
CREATE database sales;
USE sales;
CREATE TABLE k_staff (
staff_num INT NOT NULL AUTO_INCREMENT,
staff_name VARCHAR(45) NOT NULL,
staff_post VARCHAR(45) NULL,
staff_hiredate DATE NULL,
staff_termdate DATE NULL,
k_dept_dept_num INT NOT NULL,
PRIMARY KEY (staff_num));
CREATE TABLE k_dept (
dept_num INT NOT NULL AUTO_INCREMENT,
dept_full_name VARCHAR(45),
dept_short_name VARCHAR(10),
k_staff_staff_num INT NULL DEFAULT NULL,
PRIMARY KEY (dept_num));
alter table k_staff add CONSTRAINT fk_k_staff_k_dept1 FOREIGN KEY (k_dept_dept_num) REFERENCES k_dept (dept_num) ON DELETE NO ACTION ON UPDATE NO ACTION;
alter table k_dept add CONSTRAINT fk_k_dept_k_staff1 FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num) ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE TABLE k_firm (
firm_num INT NOT NULL AUTO_INCREMENT,
firm_name VARCHAR(45) NOT NULL,
firm_addr VARCHAR(45) NULL,
firm_phone VARCHAR(20) NULL,
PRIMARY KEY (firm_num));
CREATE TABLE k_contract (
contract_num INT NOT NULL AUTO_INCREMENT,
contract_date DATE NOT NULL,
contract_type ENUM('A','B','C') NOT NULL,
k_firm_firm_num INT NOT NULL,
k_staff_staff_num INT NOT NULL,
PRIMARY KEY (contract_num),
CONSTRAINT fk_k_kontrakt_k_firm1
FOREIGN KEY (k_firm_firm_num) REFERENCES k_firm (firm_num)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_kontrakt_k_staff1
FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_bill (
bill_num INT NOT NULL AUTO_INCREMENT,
bill_date DATE NOT NULL,
bill_sum DECIMAL(9,2) NOT NULL,
bill_term DATE NOT NULL,
bill_peni DECIMAL(6,2) NULL,
k_contract_contract_num INT NOT NULL,
PRIMARY KEY (bill_num),
CONSTRAINT fk_k_bill_k_contract1
FOREIGN KEY (k_contract_contract_num) REFERENCES k_contract (contract_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_payment (
payment_num INT NOT NULL,
payment_date DATE NOT NULL,
payment_sum DECIMAL(9,2) NOT NULL,
k_bill_bill_num INT NOT NULL,
PRIMARY KEY (payment_num, k_bill_bill_num),
CONSTRAINT fk_k_payment_k_bill1
FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_price (
price_num INT NOT NULL AUTO_INCREMENT,
price_name VARCHAR(45) NOT NULL,
price_sum DECIMAL(9,2) NULL,
price_type VARCHAR(1) NULL,
PRIMARY KEY (price_num));
CREATE TABLE k_protokol (
k_bill_bill_num INT NOT NULL,
k_price_price_num INT NOT NULL,
kolvo INT NOT NULL,
price_sum DECIMAL(9,2) NOT NULL,
PRIMARY KEY (K_bill_bill_num, k_price_price_num),
CONSTRAINT fk_k_bill_has_k_price_k_bill1
FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_k_bill_has_k_price_K_price1
FOREIGN KEY (k_price_price_num) REFERENCES k_price (price_num)
ON DELETE NO ACTION ON UPDATE NO ACTION);
# Заполнение
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Альфа', 'Москва');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Бета', 'Казань');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Гамма', 'Париж');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Дельта', 'Лондон');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Омега', 'Токио');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Sales', 'Отдел продаж');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Mart', 'Отдел маркетинга');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Cust', 'Отдел гарантийного обслуживания');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Петров', 2, '2010-10-13','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Сидоров', 3, '2005-12-01','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Семенов', 1, '1990-01-01','Директор');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Григорьев', 3, '2008-12-19','Программист');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-11-12', '2011-12-12', 1000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-12-12', '2012-01-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2012-01-12', '2012-02-12',2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2011-12-12', '2012-01-12', 6000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2012-01-12', '2012-02-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(3, '2012-01-12', '2012-02-12', 2500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(4, '2011-12-12', '2012-01-12', 1500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2011-12-12', '2012-01-12', 1200);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2012-01-12', '2012-02-12', 10000);
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (1, 'Материализация духов', 1000.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (2, 'Раздача слонов', 100.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (3, 'Слоновый бивень', 3000.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (4, 'Моржовый клык', 1500.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (5, 'Копыто пегаса', 5000.00, 'Т');
# Отключение режима safe mode (запрет изменений без указания первичного ключа)
# при помощи команды SQL
# Если режим safe mode включен, то MySQL-сервер выдает ошибку 1175
SET SQL_SAFE_UPDATES = 0;
UPDATE k_dept SET k_staff_staff_num=2
WHERE dept_short_name='Mart';
UPDATE k_dept SET k_staff_staff_num=3
WHERE dept_short_name='Cust';
UPDATE k_dept SET k_staff_staff_num=1
WHERE dept_short_name='Sales';
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (1, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (2, 1, 2, 1000.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (3, 2, 20, 100.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (4, 3, 2, 3000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 2, 10, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (6, 1, 2, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (6, 2, 5, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (7, 4, 1, 1500.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (8, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (8, 2, 2, 100.00);
INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (9, 5, 2, 5000.00);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-15', 1000.00, 2);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-13', 1500.00, 3);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-12', 1000.00, 4);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-05', 100.00, 7);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-25', 1000.00, 8);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-15', 500.00, 3);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-12', 900.00, 7);
Задание
-
Создайте базу данных Sales (Продажи), используя приведенный выше SQL-скрипт. -
Реализуйте учебные примеры по написанию и запуску хранимых процедур к разработанной БД.
2.1 Создайте процедуру, которая в качестве параметра получает фамилию сотрудника и печатает список всех договоров, которые он курирует.
Для запуска этой процедуры нужно выполнить, например, команду
или
2.2 Создайте процедуру, которая получает номер месяца и номер года и печатает договоры за указанный период.
Выполним процедуру:
2.3 Создайте процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент.
Содержимое таблицы "Прайс-лист" до выполнения процедуры:
Для запуска этой процедуры нужно выполнить, например, команду:
Содержимое таблицы "Прайс-лист" после выполнения процедуры:
Как видим, товар с номером 4 в прайс-листе уценен на 10%.
А что произойдет, если в нашей базе данных есть несколько товаров, количество продаж которых минимально? К сожалению, в нашем случае процедура выдаст ошибку: Error Code: 1172, Result consisted of more than one row. Когда в команде SELECT выбирается сразу несколько значений поля k_price из таблицы k_protokol, невозможно присвоить эти несколько значений одной переменной p. Данную ситуацию можно обработать с помощью так называемых курсоров.
Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.
Для работы с курсорами существуют следующие команды.
Объявление курсора:
DECLARE имя_курсора CURSOR FOR SELECT текст_запроса;
Таким образом, любой курсор создается на основе некоторого оператора SELECT.
Открытие курсора:
OPEN имя_курсора;
Только после открытия курсора он становится активным, и из него можно читать строки.
Чтение значений из следующей строки курсора в набор переменных:
FETCH имя_курсора INTO список_переменных;
Переменные в списке должны иметь то же количество и тип, что и столбцы курсора.
Закрытие курсора:
CLOSE имя_курсора;
При переборе строк курсора возникает необходимость проверки, добрались ли мы до конца курсора или еще нет. В разных СУБД для этого могут быть предусмотрены разные средства. В СУБД MySQL назначается обработчик состояния “NOT FOUND”. Определять его нужно сразу же после описания структуры курсора:
DECLARE CONTINUE HANDLER FOR NOT FOUND оператор;
Например, этот обработчик может выглядеть так:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
