Представления, хр. процедуры, триггера и курсоры

Имя таблицы	Имя колонки	Расшифровка
employees		Таблица служащих
	id	Идентификатор служащего
	first_name	Имя
	pather_name	Отчество
	last_name	Фамилия
	position	Должность
	salary	Зарплата в месяц
departments		Таблица отделов
	id	Идентификатор товара
	name	Наименование отдела
projects		Таблица проектов
	id	Идентификатор записи
	name	Наименование проекта
	cost	Стоимость проекта
	department_id	Отдел, работающий над проектом
	date_beg	Дата начала работы с проектом
	date_end	Дата предполагаемого окончания работы с проектом
	date_end_real	Реальная дата окончания проекта
departments_employees		Привязка служащих к отделам
	id	Идентификатор записи
	department_id	Отдел
	employee_id	Служащий этого отдела

Соединения

Название	Primary Key	Foreign Key
fk_depts_employees_employees	employees.id	departments_employees.employee_id
fk_projects_departments	departments.id	projects.department_id
fk_deps_employees_depts	departments.id	departments_employees.department_id

Представления, хр. процедуры, триггера и курсоры

Представления

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

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

Hints: select, where, count, max, group by, having, like, create view, drop view

Хранимые процедуры

· без параметров:

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

· с входными параметрами:

1. Создать хранимую процедуру, имеющую два параметра «служащий1» и «служащий2». Она должна возвращать проекты, в которых эти два служащих работали одновременно.

· с выходными параметрами:

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

Hints: select, where, count, max, group by, having, create procedure, drop procedure

Триггера

· Триггера на вставку:

1. Создать триггер, который не позволяет добавить в отдел служащего, если он там уже есть.

· Триггера на модификацию:

1. Создать триггер, который не позволяет установить дату окончания проекта меньше, чем дата начала.

Триггера на удаление:

1. Создать триггер, который при удалении проекта в случае, если проект не завершен, откатывает транзакцию.

Hints: select, where, in, exists, join, commit, rollback, create trigger, drop trigger

Курсоры

Хранимая процедура для расчета суммы прибыли от завершенных к настоящему времени проектов за период:

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

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

Hints: CURSOR, %NOTFOUND, FETCH