Re: Creating Views

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 24 Aug 1999 18:00:18 GMT
Message-ID: <37c6dda1.108264085@newshost.us.oracle.com>

A copy of this was sent to Kenneth C Stahl <BluesSax_at_Unforgettable.com> (if that email address didn't require changing) On Tue, 24 Aug 1999 13:07:53 -0400, you wrote:

>Connor McDonald wrote:
>
>> You can even do mods to joins in 7.3 - as long as they are
>> key-preserved.
>
>Can you show an example?
>

8i> create table dept as select * from scott.dept;

Table created.

8i> alter table dept add constraint dept_pk primary key (deptno);

Table altered.

8i> 
8i> 
8i> create table emp as select * from scott.emp;

Table created.

8i> alter table emp add constraint emp_pk primary key (empno);

Table altered.

8i> alter table emp add constraint emp_fk foreign key(deptno) references   2 dept(deptno);

Table altered.

8i>
8i> create or replace view emp_view
  2 as
  3 select dept.deptno, dept.dname, emp.ename, emp.sal   4 from emp, dept
  5 where emp.deptno = dept.deptno
  6 /

View created.

8i>
8i> update emp_view set sal = sal*1.1
  2 /

14 rows updated.

8i>
8i> update
  2 ( select dept.deptno, dept.dname, emp.ename, emp.sal

  3      from emp, dept
  4     where emp.deptno = dept.deptno )

  5 set sal = sal * .9
  6 /

14 rows updated.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 24 1999 - 13:00:18 CDT