Oracle视图详解-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangludcsdn/article/details/79081930

如果忘记密码，就用默认账号，system /manager登陆

select username,password from dba_users;用于找回自己账户和密码

几个sqlplus的关键字

ed：打开编辑器

desc：描述表结构

1视图的定义：视图是建立在已有表的基础上，掉电不丢失

2视图的优点：1）控制数据访问：假如我有一张员工表，员工表里面除了基本信息外，还有对员工的的留用状态栏，这个个ceo可以对其进行操作，但是系统管理员就不能看到这一栏，就建立一张子表，独立于主表，拥有表的部分信息，对系统管理员显示这张子表，但是员工的增删查改不能由ceo去做，所以就要保证修改子表，主表与之对应变化，所以就有了oracle的视图对象。

2）简化查询：基于多个表的查询

select test02.id,test02.name,test02.birthday,test01.DEPARMENT from test01,test02 where test01.test02_id=test02.id

3）避免查询相同的操作

3操作

1）创建视图

在plsql软件开一个命令窗口

出现的错是因为我的表名写错了，上图是更正后的

2）修改

update testview02
set name='张三' where name='张璐'

create or replace view testview02
as 
select id,name,birthday,filename from test02,annex with read only

3）删除

 delete FROM testview02 WHERE position='实习生'

4.复杂视图

多表视图，有新列要取别名，用group by。使用了group by就不可以对表进行增删查改

5.Top-N分析

例如：查询某条件下最前的10条数据，或者最后的10条数据，用于分页。
rownum伪列

6.视图能力提升

CREATE OR REPLACE VIEW VIEW_TO_DO_LIST AS
SELECT ID,
          CHANGETIME SUBMIT_DATE,
          UNITNAME REPRESENT_NAME,
          LICENSECODE REPRESENT_NUMBER,
          '单位信息发生变更,点击“单位信息维护” 查看详情'
             APPLY_TYPE,
          DEPARTMENT
     FROM UNIT_CHANGE_LOG      
     WHERE    TO_DATE(TO_CHAR(SYSDATE,'yyyymmdd'),'yyyymmdd')  -   TO_DATE

(TO_CHAR(CHANGETIME,'yyyymmdd'),'yyyymmdd') <=   7
   UNION ALL
  select 
        to_char(rownum) id,
          TO_DATE (TO_CHAR (T.SUBMIT_DATE), 'yyyy-mm-dd hh24:mi:ss '),
          T.UNITNAME REPRESENT_NAME,
          T.LICENSECODE REPRESENT_NUMBER,
          DECODE (T.APPLY_TYPE,
                  '1501', '新签用工信息',
                  '1502', '续签合同',
                  '1503', '变更合同',
                  '1504', '合同终止/解除')
          || '，请备案确认('||t.PENDINGCOUNT||'人)'
             APPLY_TYPE,
          T.DEPARTMENT
 from PENDING_UNIT_COUNT t
  UNION ALL
  SELECT T1.ID ID,
   TO_DATE (TO_CHAR (T1.REGISTERDATE), 'yyyy-mm-dd hh24:mi:ss ') SUBMIT_DATE,
       T1.COMPANY_NAME REPRESENT_NAME,
       T1.COMPANY_CODE  REPRESENT_NUMBER,
       '法人信息发生变更' APPLY_TYPE,
       T1.COMPANY_AREA  DEPARTMENT 
     

  FROM DEV_YN_LABOUR.COMPANY_MODIFY T1, APPLY_EVENT A
 WHERE   
 T1.ID = A.ID
       AND A.SUBMIT_STATUS = '1'
       AND NOT EXISTS
              (SELECT R.APPLY_EVENT_ID
                 FROM AUDIT_RESULT R
                WHERE T1.ID  = R.APPLY_EVENT_ID)


comment on table VIEW_TO_DO_LIST is '待办事项列表';
comment on column VIEW_TO_DO_LIST.ID is '主键';
comment on column VIEW_TO_DO_LIST.SUBMIT_DATE is '提交日期';
comment on column VIEW_TO_DO_LIST.REPRESENT_NAME is '单位名称';
comment on column VIEW_TO_DO_LIST.REPRESENT_NUMBER is '单位编号';
comment on column VIEW_TO_DO_LIST.APPLY_TYPE is '事件类型';
comment on column VIEW_TO_DO_LIST.DEPARTMENT is '主管部门编号';

这个是改正后的视图代码了，改正前有很多bug，查找bug是一个巨大的工程，这个视图包含三个sql，我们一个一个的运行，首先我们对每个sql单独提出来在命令窗口运行。

union是可以合并多个sql语句里面的数据，列名，列的数量，类型以第一个sql语句里面的为主，所以我们要将以后的sql语句都取一个别名，同时还要注意字段的数据类型转换，

在改sql中遇到的知识点：

随机id用到一个sys_guid()

WITH data AS (SELECT SYS_GUID() uuid FROM DUAL WHERE ROWNUM >= 1)
  SELECT uuid, uuid FROM data;

例子2

CREATE OR REPLACE VIEW DEV_YN_LABOUR.VIEW_CONTRACT_STATUS AS
select t2.id,t2.result,t2.AAC001,t2.CONTRACTNUM,t2.unitcode,t2.aca112,t2.aae030,t2.aae031,t2.department,t2.t1,
p.aac003,p.aac002
from
(
  SELECT t.id,
  CASE
  WHEN (to_date(to_Char(SYSDATE, 'yyyymmdd'), 'yyyymmdd') - to_date(to_Char(AAE031, 'yyyymmdd'),'yyyymmdd'))>60 THEN '超期违法'
  WHEN (to_date(to_Char(SYSDATE, 'yyyymmdd'), 'yyyymmdd') - to_date(to_Char(AAE031, 'yyyymmdd'),'yyyymmdd'))>30 THEN '已超期30天'
  WHEN (to_date(to_Char(SYSDATE, 'yyyymmdd'), 'yyyymmdd') - to_date(to_Char(AAE031, 'yyyymmdd'),'yyyymmdd'))>0 THEN '已超期'
  WHEN (to_date(to_Char(SYSDATE, 'yyyymmdd'), 'yyyymmdd') - to_date(to_Char(AAE031, 'yyyymmdd'),'yyyymmdd'))>-30 THEN '即将超期'
  ELSE '未超期' END result,t.AAC001,t.CONTRACTNUM,t.unitcode,t.aca112,t.aae030,t.aae031,u.department,
  to_date(to_Char(SYSDATE, 'yyyymmdd'), 'yyyymmdd') - to_date(to_Char(AAE031, 'yyyymmdd'),'yyyymmdd') t1
    FROM dev_yn_labor_center.CONTRACT_INFO t,dev_yn_labor_center.unit_base_info u

  where t.unitcode=u.unitcode and t.AAE031 is not null
)
t2, dev_yn_labor_center.person_info p where t2.aac001=p.aac001
union all
select t3.id,t3.result,t3.AAC001,t3.CONTRACTNUM,t3.unitcode,t3.aca112,t3.aae030,t3.aae031,t3.department,t3.t1,
p.aac003,p.aac002
from (
  select f.id,
  to_char(decode(f.FACTUALSTATUS,'1','代签合同','4','超期违法','其他')) result,f.AAC001,null as CONTRACTNUM,f.unitcode,'' aca112,null as aae030,null as aae031,u.department,null as t1

  from dev_yn_labor_center.FACTUAL_RELATION f,dev_yn_labor_center.unit_base_info u
  where f.unitcode=u.unitcode and to_date(to_char(f.FACTUALENDDATE ,'yyyymmdd'),'yyyymmdd')-to_date(to_char(f.FACTUALSTARTDATE,'yyyymmdd'),'yyyymmdd')=30
) 
t3,dev_yn_labor_center.person_info p where t3.aac001=p.aac001
comment on table DEV_YN_LABOUR.VIEW_CONTRACT_STATUS is '到期视图';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.id is 'ID';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.result is '超期结果';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.AAC001 is '人员编号';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.CONTRACTNUM is '合同编号';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.unitcode is '单位编号';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.aca112 is '岗位名称';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.aae030 is '合同期限起始日期';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.aae031 is '合同期限终止日期';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.department is '主管部门';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.t1 is '合同与当前日期差';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.aac003 is '人员姓名';
comment on column DEV_YN_LABOUR.VIEW_CONTRACT_STATUS.aac002 is '人员身份证';

1.这个里面注意case then的使用，详见https://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

2.注意第一个sql里面有的字段，如果第二个sql里面没有可以置空，null适用与日期、number等，''适用于字符串。

3.能select具体列就不能select *，起初select *导致一直报表达式数据类型不一致，打印了两个sql调用的表，一比对，类型是一样的，后来改代码，将*改为具体的列才发现是null与‘’用法不对。