12.txt

Oracle database 10g基础教程（第二版） 源码

create view computeSalary(employee_number, name, salary, salaryNew)
as
select empno, ename, sal, sal + sal * 0.1
from emp;
 
 
desc computeSalary
select *
from computeSalary
order by employee_number;


desc user_views


select text
from user_views
where view_name = 'COMPUTESALARY';




create view computeSalary(employee_number, name, job, salary, salaryNew)
as
select empno, ename, job, sal, sal + sal * 0.1
from emp;





create or replace 
view computeSalary(employee_number, name, job, salary, salaryNew)
as
select empno, ename, job, sal, sal + sal * 0.1
from emp;
desc computeSalary


drop view computeSalary;
desc computeSalary



create view emp_salary_greater_than_1500
as
select *
from emp
where sal > 1500;
select count(*)
from emp_salary_greater_than_1500;



create view job_total
as
select job, count(*) total
from emp
group by job
order by job;
select *
from job_total;




create view emp_dept
as
select
  e.empno,
  e.ename,
  e.job,
  e.mgr,
  e.sal,
  d.dname,
  d.loc
from
  emp e,
  dept d
where
  e.deptno = d.deptno
with read only;




connect scott/tiger
create table t(
id number,
data varchar2(200)
);



create view view_t
as
select id view_id, data view_data
from t;



insert into t
values(1, 'ABC');
select * 
from view_t;



alter table t
modify(
  id number,
  data varchar2(255)
);
alter table t
add(
  data2 clob
);
desc t


select object_name, status
from user_objects
where object_name = 'VIEW_T';




select *
from view_t;
select object_name, status
from user_objects
where object_name = 'VIEW_T';



alter view view_t
compile
/




drop table t;
select *
from view_t;



create view invalid_view
as
select *
from table_that_does_not_exist;
create force view invalid_view
as
select *
from table_that_does_not_exist;







create view vw_emp30
as
select *
from emp
where deptno = 30
with check option constraint check_emp30;





create table human(
humanID number,
humanName varchar2(20),
age number);




insert into human
values(1, 'Athos', 25);
insert into human
values(2, 'Aramis', 35);
insert into human
values(3, 'Porthos', 55);


select *
from human;



create view youngPerson
as
select *
from human
where age <= 40
with check option;



update youngPerson
set age = 38
where humanID = 2;
select *
from youngPerson;

update youngPerson
set age = 42
where humanID = 2;



create or replace trigger update_youngPerson
instead of update 
on youngPerson
begin
  update human
  set humanID = :new.humanID,
      humanName = :new.humanName,
      age = :new.age
  where humanID = :old.humanID;
end;
/








select dname, count(*), to_char((count(*)/total_emp.cnt)*100, '99.99')||'%' pct
from dept,
     emp,
     (select count(*) cnt from emp) total_emp
where dept.deptno = emp.deptno
group by dname, total_emp.cnt;



set pagesize 20
select ename, hiredate
from emp
order by hiredate;



select ename, hiredate
from emp
where rownum < 6
order by hiredate;





select ename, hiredate
from (select ename, hiredate
      from emp
      order by hiredate)
where rownum < 6;





desc emp
select count(*)
from emp;



create table my_emp
as
select *
from emp;
insert into my_emp
select *
from my_emp;



select count(*)
from my_emp;



set autotrace on
set timing on
select empno, count(*) total_emp
from my_emp
group by empno;



set timing off
create materialized view my_emp_mv
build immediate
refresh on commit
enable query rewrite
as
select empno, count(*) total_emp
from my_emp
group by empno;



set linesize 180
set timing on
select *
from my_emp_mv;