Oracle数据库对象

--视图


create view v_myview
  as
select * from emp where sal>2000;


select * from v_myview;


select * from user_views;


create view v_emp20
  as
select * from emp where deptno=20;


select view_name,text_length,text from user_views;


select * from v_emp20; 


create or replace view v_myview 
  as
select d.deptno,d.dname,d.loc,
  count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
  nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;


select * from v_myview;


create or replace view v_myview 
  (部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资)
  as
select d.deptno,d.dname,d.loc,
  count(e.empno) count,nvl(round(avg(sal),2),0) avg,nvl(sum(sal),0) sum,
  nvl(max(sal),0) max,nvl(min(sal),0) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;


select * from v_myview;


create or replace view v_emp20
  as
select empno,ename,job,sal,deptno from emp where deptno=20;


select * from v_emp20;


insert into v_emp20(empno,ename,job,sal,deptno)values(6688,'工大','CLERK',1900,20);


SELECT * FROM V_EMP20;


select * from emp;


update v_emp20 set ename='MLDNJAVA',JOB='MANAGE',SAL=2300 where empno=6688;


select * from v_emp20;


select * from emp where deptno=20;


delete from v_emp20 where empno=6688;
commit;


select * from v_emp20;


select * from emp where deptno=20;


create or replace view v_myview 
  as
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and d.deptno=20;


select * from v_myview;


insert into v_myview(empno,ename,job,sal,deptno,dname,loc) 
values(6688,'工大','CLERK',2000,50,'教学','北京');


update v_myview set ename='史密斯',sal=5000,dname='教学' where empno=7369;


delete from v_myview where empno=7369;


select * from v_myview;


select * from emp;
select * from dept;


delete from v_myview where deptno=20;


select * from emp;
select * from dept;
select * from v_myview;


create or replace view v_emp20
  as
select * from emp where deptno=20;


select view_name,text_length,text from user_views;


select * from v_emp20;


select * from emp;
SELECT * FROM DEPT;


desc emp;


insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7369,'SMITH','CLERK',800,20,7902,to_date('1980-12-17','yyyy-mm-dd'));


insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7566,'JONES','MANAGER',2975,20,7839,to_date('1981-04-02','yyyy-mm-dd'));


insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7788,'SCOTT','ANALYST',3000,20,7566,to_date('1987-04-19','yyyy-mm-dd'));


insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7876,'ADAMS','CLERK',1100,20,7788,to_date('1987-05-23','yyyy-mm-dd'));


insert into emp(empno,ename,job,sal,deptno,mgr,hiredate)
values(7902,'FORD','ANALYST',3000,20,7566,to_date('1981-12-03','yyyy-mm-dd'));
COMMIT;
rollback;


create or replace view v_emp20
  as
select * from emp where deptno=20;


select view_name,text_length,text from user_views;


select * from v_emp20;


update v_emp20 set deptno=40 where empno=7369;


select * from v_emp20;


select * from emp where empno=7369;


rollback;


create or replace view v_emp20
  as
select * from emp where deptno=20
with check option constraint v_emp20_ck;


update v_emp20 set ename='史密斯',comm=300 where empno=7369;


select * from V_emp20;


create or replace view v_emp20
  as
select * from emp where deptno=20
with read only;


select view_name,text_length,text,read_only from user_views;


update v_emp20 set ename='SMITH',comm=null where empno=7369;


drop view v_myview;
drop view emp20;
select view_name,text_length,text,read_only from user_views;
select * from emp;


--序列


create sequence myseq;


select * from user_sequences;


select myseq.nextval from dual;
select myseq.currval from dual;


drop table member purge;
create table member(
  mid number,
  name varchar2(50) not null,
  constraint pk_mid primary key(mid)
);


select * from tab;


insert into member(mid,name) values(myseq.nextval,'软件1102');


select * from member;


drop sequence myseq;


select * from user_sequences;


drop sequence myseq;
create sequence myseq increment by 3;


select * from user_sequences;


select myseq.nextval from dual;


drop sequence myseq;
create sequence myseq 
  increment by 3
  start with 30;


select myseq.nextval from dual;


drop sequence myseq;
create sequence myseq 
  cache 100;
  
select * from user_sequences;


drop sequence myseq;
create sequence myseq 
  nocache;
  
drop sequence myseq;
create sequence myseq
  start with 1
  increment by 2
  maxvalue 10
  minvalue 1
  cycle
  cache 3;
  
select myseq.nextval from dual;


drop sequence myseq;
create sequence myseq;


select * from user_sequences;


alter sequence myseq
  increment by 10
  maxvalue 98765
  cache 100;
  
select myseq.nextval from dual;


--同义词


--Oracle伪劣


select rowid,deptno,dname,loc from dept;


select rowid,
  dbms_rowid.rowid_object(rowid) 数据对象号,
  dbms_rowid.rowid_relative_fno(rowid) 相对文件号,
  dbms_rowid.rowid_block_number(rowid) 数据块号，
  dbms_rowid.rowid_row_number(rowid) 数据行号,
  deptno,dname,loc
from dept;


drop table mydept purge;
create table mydept as select * from DEPT;


insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
insert into mydept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');
COMMIT;


select rowid,deptno,dname,loc from mydept;


select deptno,dname,loc,min(rowid)
from mydept
group by deptno,dname,loc;


delete from mydept
where rowid not in(
  select min(rowid)
  from mydept
  group by deptno
);


select * from mydept;


select rownum,empno,ename,job,sal,hiredate from emp;


select rownum,empno,ename,job,sal,hiredate from emp where deptno=30;


select avg(sal) from emp;


select e.empno,e.ename,e.sal,e.job,e.hiredate
from emp e
where e.sal>(
  select avg(sal) from emp);
  
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc
from emp e,dept d
where e.sal>(
  select avg(sal) from emp)
  and e.deptno=d.deptno;
  
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
  select avg(sal) from emp)
  and e.deptno=d.deptno
  and e.sal between s.losal and s.hisal;
  
select rownum rn,e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade
from emp e,dept d,salgrade s
where e.sal>(
  select avg(sal) from emp)
  and e.deptno=d.deptno
  and e.sal between s.losal and s.hisal;
  
select * from emp where rownum=1;


select * from(
  select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn 
  from emp where rownum<=5) temp
where temp.rn>0;


select * from(
  select empno,ename,job,hiredate,sal,mgr,deptno,rownum rn 
  from emp where rownum<=10) temp
where temp.rn>5;


--索引


select * from emp where sal>1500;


create index emp_sal_ind on emp(sal);


select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;


select * from user_ind_columns where index_name='EMP_SAL_IND';


create index emp_hiredate_ind_desc on emp(hiredate);


select * from scott.emp
where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-21','yyyy-mm-dd')
order by hiredate desc;


create index emp_ename_ind on emp(lower(ename));


select * from emp where lower(ename)='smith';


create bitmap index emp_deptno_ind on emp(deptno);


select * from emp where deptno=10;


select * from emp where deptno=10 or deptno=20;


select index_name,index_type,table_owner,table_name,uniqueness,status from user_indexes;


drop index emp_sal_ind;


drop index emp_deptno_ind;


drop index emp_ename_ind;