Агрегатные и аналитические функции. Создание других объектов базы данных (Лабораторная работа № 4), страница 3

ORDER BY department_id, salary;

Агрегированные данные вычисляются при помощи подзапросов.

8.  Выполните запрос:

SELECT department_id, last_name, salary , SUM(salary) OVER (PARTITION BY department_id ORDER BY salary,last_name) AS cum_sal, ROUND(100*RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id), 1 ) pct_dept, ROUND(100*RATIO_TO_REPORT(salary) OVER () , 1 ) AS pct_overall FROM employee ORDER BY department_id, salary;

Получаем такой же точно результат, какой был в предыдущем запросе, который выполнялся без аналитических функций. RATIO_TO_REPORT(salary) – краткая и удобная функция получения процентных долей как внутри отделов OVER (PARTITION BY department_id), так и по всей фирме OVER ().

Часть 2

Цель:  научиться создавать объекты базы данных.

Задания:

1.  Создайте представление, для выборки данных из таблиц employee и department, в котором-бы выбирались сотрудники отдела продаж.

2.  Создайте представление с опцией READ ONLY.

3.  Удалите представление emp_dept.

4.  Создайте индекс для таблицы employee по полю last_name.

5.  Удалите индекс, созданный в прошлом задании.

6.  Создайте синоним job1 для таблицы job.

7.  Создайте функцию, которая бы возвращала имя и фамилию руководителя сотрудника.

8.  Создайте процедуру для внесения данных в таблицу employee.

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

Технология работы:

1.  Создайте представление emp_dept, выполнив запрос:  

CREATE OR REPLACE VIEW emp_dept AS

SELECT e.employee_id, e.last_name, department_id, d.name AS department

FROM employee e join department d USING (department_id) WHERE d.name='Отдел продаж';

Выберите данные из созданного представления:

select * from emp_dept;

Вставка значений в это представление не получится. Нельзя изменить более одной таблицы посредством связанного представления. Если мы попытаемся вставить только один столбец одной таблицы (employee_id), Oracle потребует вставки foreign key 68

(department_id) и т.д. для всех вариантов вставки столбцов. Изменение в одном варианте (UPDATE emp_dept SET employee_id WHERE employee_id…..) возможно, но только для строк служащего, у которого нет подчиненных.

2.  Создайте представления с опцией READ ONLY, запрещающей insert, update, delete с базовой таблицей, на основе которой создано представление, через это представление. Выполните запрос:

CREATE OR REPLACE VIEW emp_v1 AS SELECT employee_id,last_name, (salary+NVL(commission, 0)) AS "Зарплата_отдела", department_id FROM employee WITH READ ONLY;

Выберите данные из созданного представления:

select * from emp_v1;

3.  Для удаления представления, выполните запрос:

DROP VIEW emp_dept;

4.  При помощи команды create index создайте индекс для таблицы employee:

CREATE INDEX i_employee ON employee(last_name);

Выберите данные из таблицы для сотрудника с фамилией Мороз:

select * from employee where last_name='Мороз';

Посмотрите план этого запроса, перейдя на вкладке Explain:

5.  Удалите индекс при помощи команды drop:

DROP INDEX i_employee ;

Запросите данные из таблицы employee по сотруднику Мороз:

select * from employee where last_name='Мороз';

Посмотрите план этого запроса, перейдя на вкладке Explain:

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

6.  Создайте синоним при помощи запроса: CREATE SYNONYM job1 FOR job;

Запросите данные из таблицы employee, используя созданный синоним: select * from job1;

7.  Создайте функцию, которая находит по id сотрудника имя менеджера:

create or replace function manager_name(p_employee_id in number) return varchar2