¸ÞÀÎ ¢º USER: PO7
>CREATE USER tester IDENTIFIED BY tester;
>GRANT CONNECT TO tester;
¢º USER: SCOTT
>CREATE VIEW staff
AS SELECT empno, ename, job, mgr, deptno FROM emp;
>GRANT SELECT ON staff TO tester;
>CREATE VIEW staff_dept
as select empno, ename, dname, loc from emp, dept
where emp.deptno=dept.deptno;
>SELECT * FROM staff_dept;
>CREATE VIEW vemp (eno, enm, job, sal, cls)
as select empno, ename, job, sal, deptno from emp;
>CREATE VIEW status (deptno, tot_men, max_sal, min_sal, avg_sal)
as select deptno, count(*), max(sal), min(sal), avg(sal) from emp
group by deptno;
>SELECT empno, ename, job, sal - avg_sal from emp, status
where emp.deptno=status.deptno;
>CREATE VIEW emp_dept
as select empno, ename, emp.deptno, dname, loc from emp, dept
where emp.deptno=dept.deptno;
>CREATE VIEW emp_sal
as select empno, ename, job, sal, comm from emp;
> UPDATE emp_dept set ename=' ';
14 rows updated.
> UPDATE emp_dept set dname=' ';
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
> ROLLBACK WORK;
> DELETE FROM emp_dept where deptno=10;
3 rows deleted.
> ROLLBACK WORK;
>CREATE VIEW dept_emp
as select empno, ename, dept.deptno, dname, loc from emp, dept
where emp.deptno=dept.deptno;
> DELETE FROM dept_emp where deptno=10;
¡ß ºÐÇÒ ¿µ¿ª
View
> CREATE TABLE d1_emp
as select * from emp where deptno=10;
> ALTER TABLE d1_emp add constraint c_d1 check(deptno in(10));
> CREATE TABLE d2_emp
as select * from emp where deptno=20;
> ALTER TABLE d2_emp add constraint c_d2 check(deptno in(20));
> CREATE TABLE d3_emp
as select * from emp where deptno=30;
> ALTER TABLE d3_emp add constraint c_d3 check(deptno
in(30));
> CREATE VIEW d_emp as
select * from d1_emp union all
select * from d2_emp union all
select * from d3_emp;
> SELECT * FROM d_emp;
> INSERT INTO d1_emp values
(7999, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 3999, 1999,
10);
> UPDATE d1_emp set sal=3000 where empno=7999;
> DELETE FROM d1_emp where empno=7999;