oracle视图

oracle视图分为：关系视图，内嵌视图，对象视图，物化视图

1.关系视图

--视图：整合不同表的数据；封装复杂查询；
--关系视图
--创建员工表用于测试
create table employee(
       employee_id number primary key,
       first_name varchar2(4),
       last_name varchar2(4),
       province varchar2(10),
       city varchar2(10),
       salary number
);

select * from employee for update;

--1.创建视图：查看员工姓名和所在地
create view vw_employee
as
select first_name || last_name as employee_name,province || '-' || city as location from employee;
--查看视图
select * from vw_employee;
--修改视图
create or replace view vw_employee
as
select first_name || last_name as employee_name,province || '-' || city as location,salary from employee;
--删除视图
drop view vw_employee;
--创建员工3月份零售额信息表
create table employee_sales(
       id number primary key,
       sal2_month varchar2(2),
       total_price number,
       sale_by number
);

--为employee_sales表创建外键关联employee表
alter table employee_sales add constraint fk_employee_sales_employee foreign key(sale_by) references employee(employee_id);
--2.联接视图：创建员工3月份的工资视图
create or replace view vw_employee_salary
as
select e.first_name || e.last_name as employee_name,es.sal2_month,(e.salary+es.total_price) as salary from employee e,employee_sales es where e.employee_id=es.sale_by;
--查看视图
select * from vw_employee_salary;
--创建员工零售额详情表
create table employee_sale_details(
       sale_details_id number primary key,
       goods_name varchar2(10),
       price number,
       quantity number,
       sale_month varchar(5),
       sale_by number
);
--添加约束
alter table employee_sale_details add constraint fk_details_employee foreign key(sale_by) references employee(employee_id);
--3.创建视图
create or replace view vw_employee_details
as
select e.sale_by,sum(e.price*e.quantity) as total_price from employee_sale_details e group by e.sale_by;
--查看视图
select * from vw_employee_details;
--4.表和视图进行联接创建新视图：
create or replace view vw_employee_salary
as
select e.first_name || e.last_name as employee_name,(e.salary+ved.total_price*0.1) as salary from employee e,vw_employee_details ved where e.employee_id=ved.sale_by;
--编译视图:查看视图的状态
select object_name,status from user_objects where object_name='VW_EMPLOYEE' and object_type='VIEW';--valid
--修改基础表结构
alter table employee add age number;

--5.强制创建不存在的表的视图
create or replace force view vw_book
as
select book_name,book_author from book;

--创建book表
create table book(
       book_id number primary key,
       book_name varchar2(10),
       book_author varchar2(10)
);

select * from vw_book;
select * from vw_employee_salary;
select * from vw_employee_salary;
select * from vw_employee;
select * from employee;
select * from employee_sales;
select * from employee_sale_details for update;
select * from dba_tables where table_name='EMPLOYEE';

--查看表中可以编辑的列
select * from user_updatable_columns where table_name='EMPLOYEE';
--修改book表结构
select * from book for update;
alter table book add publish_date date;
alter table book modify press varchar2(100);
--【with check option的用法：可以保证视图修改前后数据的一致性】
--创建视图：只能查看ID为5的书
create or replace view vw_book
as
select * from book where book_id=5;
--查看视图
select * from vw_book;
--通过视图修改数据
update vw_book set book_id=6 where book_id=5;
--问题：在此查看视图时查不到数据
select * from vw_book;
--解决：通过with check option重新创建视图，重新上边的操作，通过视图修改数据时会报错哦！
create or replace view vw_book
as
select * from book where book_id=5
with check option;

2.内嵌视图

--内嵌式图：类似于子查询，具有临时性，可以在任何嵌入的sql中嵌入，不显示创建
--查询员工的月度零售额
select e.employee_id id,e.first_name || e.last_name as name,(e.salary+s.total_price*0.1) as total_price 
from employee e,(select sale_by,sum(price*quantity) total_price from employee_sale_details group by sale_by) s
where e.employee_id=s.sale_by order by total_price desc;

--为所有的员工工资增加300
update (select salary from employee) set salary=salary+300;

select * from employee;

3.对象视图

--对象视图：关系视图是对关系表进行查询，那么自然对象视图是对对象进行查询

--创建对象
create type employee2 is object
(employee_id number,employee_name varchar2(10),location varchar2(50),salary number);
--创建对象视图（字段一一对应于基础表）
create or replace view ov_employee2 of employee2 with object oid(employee_id)--对象中的id
as
select employee_id,last_name || first_name,province || '-' || city,salary from employee;
--查看视图
select * from ov_employee2;
--对比对象视图和一般视图的区别:一般视图view_type和oid_text为空
select * from user_views where view_name='OV_EMPLOYEE2' or view_name='VW_EMPLOYEE_SALARY';

4.物化视图

--物化视图:会在数据库中进行存储，占用资源;适合更新不频繁且复杂的数据进行查询，也就是偶尔调用一次