Представление создано.

Вставка значений в это представление не получится. Нельзя изменить более одной таблицы посредством связанного представления. Если мы попытаемся вставить только один столбец одной таблицы (employee_id), Oracle потребует вставки foreign key (department_id) и т.д. – для всех вариантов вставки столбцов. Изменение в одном варианте (update emp_dept set employee_id where employee_id…..) возможно, но только для строк служашего, у которого нет подчиненных.

Q6_2. Создание представления с опцией read only.

demo@10g> create or replace view EMP_V1 as

select employee_id,last_name, (salary+nvl(commission,0)) "Общая зарплата_отдела",

department_id from employee with read only;

Q6_3. Использование представлений для отработки запроса.

Использование представлений для отработки запроса нередко «спасает» студентов на контрольной работе по SQL. Рассмотрим такой запрос:

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

Посмотрим, как этот запрос можно отработать с использованием представлений.

Имя заказчика – customer.name

Год заказа – to_char(sales_order.ship_date,'yy')

Фамилия торгового представителя – employee.last_name

Сначала найдем имена заказчиков, которые оформляли заказы два или более раза:

demo@10g> Create or replace view v1 as

select customer.name name, count(customer.name) count

from customer, sales_order

where customer.customer_id=sales_order.customer_id

group by customer.name;

При создании view v1 Oracle «ругнется», если для count(customer.name) не создать алиас (например, count, как это сделано в нашем случае).

demo@10g> select * from v1;

demo@10g> create or replace view v2 as

select NAME from v1 where count>=2;

demo@10g> select * from v2;

demo@10g> select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from v2)

order by customer.name,year;

40 строк выбрано

Теперь избавимся от V2 и V1 отдельными шагами:

Сначала избавимся от V2:

demo@10g> select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from (select name from v1 where count>=2))

order by customer.name,year;

Вслед за тем подставим в запрос строки для V1:

demo@10g> select distinct customer.name, to_char(sales_order.ship_date,'yy') year,employee.last_name

from customer,sales_order,employee

where customer.customer_id=sales_order.customer_id

and customer.salesperson_id=employee.employee_id

and employee.job_id=(select job_id from job where function='SALESPERSON')

and customer.name in

(select * from (select name from

(select customer.name name, count(customer.name) count

from customer, sales_order

where customer.customer_id=sales_order.customer_id

group by customer.name)

where count>=2))

order by customer.name,year;

Q6_4. Удалим представление EMP_DEPT.

demo@10g>drop view EMP_DEPT;

Представление удалено.

Q6_5. Создадим индекс по столбцу name таблицы DEPARTMENT

demo@10g> create index i_DEPARTMENT_name on DEPARTMENT(name);

Индекс создан.

Теперь запросы к таблице DEPARTMENT могут выполняться с исплользованием этого индекса, если в условии запроса используется столбец name.

demo@10g> select * from DEPARTMENT where name like 'O%';

Q6_6. Удаление индексов

demo@10g> drop index i_DEPARTMENT_name;

Индекс удален.

Q6.7. Создадим кластер для таблиц LOCATION, DEPARTMENT

demo@10g> create cluster LOC_DEPT (location_id number(3))

storage (initial 100k next 50k);

Кластер создан.

demo@10g> create table DEPARTMENT1(

department_id number(2) not null,

name varchar2(13),

location_id number(3),

constraint department_id_pk primary key (department_id))

cluster LOC_DEPT(location_id);

Таблица создана.

В отличии от создания обычной таблицы при создании таблицы кластера указывается, к какому кластеру таблица принадлежит и по какому столбцу построен кластер.

demo@10g> create table LOCATION1(

location_id number(3) not null,

regional_group varchar2(20),

constraint location_id_pk primary key (location_id))

cluster LOC_DEPT(location_id);

Таблица создана.

Создаем индекс кластера:

demo@10g> create index idx_loc_dept on cluster loc_dept;

Индекс создан.

Заполним таблицы данными:

demo@10g>insert into DEPARTMENT1 (select * from DEPARTMENT);

11 строк создано.

demo@10g>insert into LOCATION1 (select * from LOCATION);

4 строк создано.

Теперь при отработке запросов, включающих во фразе «…from» обе таблицы кластера, Oracle будет отрабатывать эти запросы, извлекая данные из струкутуры кластера.

Q6.8. Удаление кластера, всех таблиц, принадлежащих кластеру и всех ограничений целостности, принадлежащих ему

demo@10g>drop cluster LOC_DEPT

including tables cascade constraints;

Кластер удален.

Q6.9. Создадим синоним для таблицы department:

demo@10g>create synonym DEPT for DEPARTMENT;

create synonym DEPT for DEPARTMENT

*

ошибка в строке 1:

ORA-01031: привилегий недостаточно

Сообщение об ошибке указывает об отсутствии у пользователя DEMO привилегии создавать синонимы. Для выдачи такой привилегии пользователю DEMO из другого окна SQL*Plus соединимся пользователем SYSTEM (DBA) и выполним этим пользователем команду: system@10g> create synonym demo.dpt for demo.department;. Теперь к таблице DEPARTMENT пользователю DEMO можно обращаться по более короткому имени синонима:

demo@10g>select * from DPT;

Пользователь DEMO может дать другим пользователям привилегию для работы с таблицей своей схемы:

demo@10g>grant select on EMPLOYEE to scott;

Привилегии предоставлены.

Теперь пользователь SCOTT (предварительно сединившись с Oracle, конечно) может извлекать данные из таблицы EMPLOYEE пользователя DEMO такой, например, командой: demo@10g>select * from demo.employee; , указав владельца таблицы и название таблицы. Пользователю SCOTT обращаться к таблице EMPLOYEE пользователя DEMO, каждый раз набирая имя пользователя DEMO, не совсем удобно (лишние дествия на клавиатуре). Чтобы устранить это неудобство, администратор SYSTEM выполняет команду: system@10g>create public synonym empl for demo.employee;, после которой и пользователь DEMO, и все другие пользователи, у которых есть привилегия извлекать данные из таблицы EMPLOYEE, принадлежащей пользователю DEMO, могут извлекать данные из этой таблицы, обращаясь к ней по имени EMPL:

demo@10g>select * from empl;

scott@10g>select * from empl;

Q6.10. Создадим снимок для таблицы SALES_ORDER:

demo@10g> create snapshot SALE_ORD as

select customer_id, to_char(ship_date,'YY') YEAR, sum(total)

from SALES_ORDER

group by (customer_id, to_char(ship_date,'YY'));

from SALES_ORDER

*

ошибка в строке 3:

ORA-01031: привилегий недостаточно

Пользователь SYSTEM из своей сессии выполняет команду: system@10g>grant create snapshot to demo;, после чего пользователь DEMO повторяет:

demo@10g> create snapshot SALE_ORD as

select customer_id, to_char(ship_date,'YY') YEAR, sum(total)

from SALES_ORDER

where to_char(ship_date,'YY')='89'

group by (customer_id, to_char(ship_date,'YY'))

order by customer_id;

Материализованное представление создано.

demo@10g> select * from SALE_ORD;

Q6.11. Удалим созданный снимок:

demo@10g>drop snapshot SALE_ORD;

Материализованное представление удалено.