如果忘记密码,就用默认账号,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与‘’用法不对。