| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating Views
Thomas Kyte wrote:
> 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.
>
Ok, I see now. This works for updates, but not for inserts or deletes.
If 'sal' were ambiguous (existed in both tables) I suppose you'd just have to give a fully qualified name for the column. I'd also think that if you tried to update a derived column that Oracle would barf. So, the technique is useful in very tightly defined situations, but there would also be instances which would preclude such usage.
Ken
 |
 |