oracle视图基础语句:
------创建表示例用于展示视图
create table organization (
orgNO varchar2(20) not null,
orgName varchar2(20) not null,
orgType char(1) not null,
openDate date,
legalPersion varchar(20),
primary key( orgNo )
)
insert into organization values('1','1','1',SYSDATE,'1');
insert into organization values('2','2','2',SYSDATE,'2');
视图创建:
--语法:create or replace view view_name as subquery;
--创建一个包含全表内容的视图
create or replace view view_all as select * from ORGANIZATION;
--创建一个指定字段的视图(type,name为指定的别名)
create or replace view view_type_name(type,name) as select orgtype,orgname from ORGANIZATION;
--创建一个指定字段的视图(子查询带条件)
create or replace view view_type_name_date(type,name,no) as select orgtype,orgname,orgno from ORGANIZATION o where o.orgtype = 2;
视图查询:
--查询所有视图(user_views)的名称和视图语句
select view_name,text from user_views;
--查询指定视图名称的视图详情
select * from user_views where view_name = 'VIEW_ALL';
视图修改:
--alter语法一般用于约束的修改
视图删除:
--删除:drop view view_name
drop view view_type_name_date