Oracle Database -views

oralce 经典习题系列

数据库视图

The problem

1 如何在一个表上创建视图？ 2 如果在多个表上建立视图？ 3 如何更改视图？ 4 如何查看视图的详细信息？ 5 如何更新视图的内容？ 6 如何理解视图和基本表之间的关系、用户操作的权限？ 表1

The answer

代码 –视图创建

create table pet(
  name varchar(20) primary key,
  owner varchar(20),
  species varchar(20) not null,
  sex varchar(1)  not null,
  birth date not null,
  death date,
  CONSTRAINT ck_sex CHECK (sex in('m','f'))
  );
insert into pet values ('Fluffy','Harold','cat','f','2003-10-12','2010-08-12');
insert into pet values ('Claws','Gwen','cat','m','2004-08-10','');
insert into pet values ('Buffy','null','dog','f','2009-08-11','');
insert into pet values ('Fang','Benny','dog','m','2000-05-15','');
insert into pet values ('Bowser','Diane','dog','m','2003-04-16','2009-11-12');```
insert into pet values ('Chirpy','null','bird','f','2008-05-19','');

create table Pesonpet (
  sno number(5) primary key,
  ownername varchar2(20) not null,
  gender varchar2(1) not null,
  addr varchar2(50) ,
  tel varchar2(20)
);
insert into Pesonpet values(201,'Gwen','f','Shanghai','1522511');
insert into Pesonpet values(202,'Benny','m','Beijing','1524664');
insert into Pesonpet values(203,'Diane','f','Hangzhou','1364546');
commit;
select * from pet;
--1创建单表视图
create or replace view vpet as select name ,owner ,species,sex from pet 
where death is null with check option ;
select * from vpet;
--2创建多表视图
create or replace view vpetpeason as select a.name,a.owner,a.species,b.addr,b.tel 
from pet a left join Pesonpet b on a.owner =b.ownername WITH READ ONLY;
select * from VPETPEASON;

--3修改视图vpet
create or replace view vpet as select * from pet ;
--修改视图增加视图约束
alter view vpetpeason add constraint un_tel unique(tel) disable NOVALIDATE;
commit;
--4查看
describe vpetpeason;
--5更新update ,insert ,drop 
select * from  vpet  ;
update vpet set owner ='Dack' where name ='Buffy';
commit;
insert into vpet values('Jim','Goder','java','f','2019-06-05','');
drop view vpet;
6 视图是基本表的逻辑表现，通过视图可以更好的控制人员对数据的修改，安全性更好。