oracle pl/sql中创建视图

最新推荐文章于 2025-02-24 16:52:03 发布

diejian8780

最新推荐文章于 2025-02-24 16:52:03 发布

阅读量586

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/BradMiller/archive/2010/06/02/1750292.html

版权

本文介绍了如何在Oracle PL/SQL中创建视图，包括一个具体的创建视图的示例，涉及到了多表查询和子查询的使用，旨在帮助理解视图的创建过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

模式：

create or replace view 视图名 as ...

举例：

create or replace view vw_rightpeople
(rightpeopletypename, rightpeopletype, rightpeopleid, organizetypeno, parentorganizeno, idno, idtype, rightpeoplename, rightpeopleproperty, chairman, resopertype, resstatus, bitspno, address, zip, phoneno, code2, code3, oldpk, description, businesssystemid, createtype, createactiveid, isrestrict)
as
select '单位' rightpeopletypename,2 rightpeopletype,organizeno rightpeopleid,organizetypeno,parentorganizeno,idno,idtype,fullname rightpeoplename,type rightpeopleproperty,chairman,resopertype,resstatus,bitspno,
(select (select region.district from region where regionid=nvl(address.district,0))||street from address where businessno=organizeno and typeno=3 and rownum<2) adress,
(select to_char(zip) from address where businessno=organizeno and typeno=3 and rownum<2) zip,
(select CONTACTCONTENT from contact where businessno=organizeno and typeno=3 and rownum<2) phoneno,code2,code3,oldpk,description,businesssystemid,createtype,createactiveid,isrestrict
from orgorganize a where a.organizeno>100 and a.ORGANIZETYPENO in (41,42,43,44,46,47)
union all
select /*+ index(op.pk_orgpeople) +*/ '个人' rightpeopletypename,1 rightpeopletype,participantno rightpeopleid,null,organizeno,idno,idtype,firstname rightpeoplename,BUYERATTR rightpeopleproperty,sex,resopertype,resstatus,bitspno,
(select (select region.district from region where regionid=nvl(address.district,0))||street from address where businessno=participantno and typeno=4 and rownum<2) adress,
(select to_char(zip) from address where businessno=participantno and typeno=4 and rownum<2) zip,
(select CONTACTCONTENT from contact where businessno=participantno and typeno=4 and rownum<2) phoneno,code2,code3,oldpk,description,businesssystemid,createtype,createactiveid,isrestrict
from orgpeople op where exists (select 1 from orgorganize oo where oo.organizeno=op.organizeno and oo.organizetypeno in (41,42,43,44,46,47));

转载于:https://www.cnblogs.com/BradMiller/archive/2010/06/02/1750292.html