Database Reference
In-Depth Information
EODA@ORA12CR1> update
2 table( select emps
3 from dept_and_emp
4 where deptno > 1
5 )
6 set comm = 100
7 /
table( select emps
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
If we return fewer than one row (one nested table instance), the update fails. Normally an update of zero rows
is OK, but not in this case—it returns an error the same as if we left the table name off a regular table update. If we
return more than one row (more than one nested table instance), the update fails. Normally an update of many rows
is perfectly OK. This shows that Oracle considers each row in the
DEPT_AND_EMP
table to point to another table, not just
another set of rows as the relational model does.
This is the semantic difference between a nested table and a parent/child relational table. In the nested table
model, there is one table per parent row. In the relational model, there is one set of rows per parent row. This
difference can make nested tables somewhat cumbersome to use at times. Consider this model we are using, which
provides a very nice view of the data from the perspective of a single department. It is a terrible model if we want to
ask questions like “What department does
KING
work for?”, “How many accountants do we have working for us?”, and
so on. These questions are best asked of the
EMP
relational table, but in this nested table model we can only access
the
EMP
data via the
DEPT
data. We must always join; we cannot query the
EMP
data alone. Well, we can't do it in a
supported, documented method, but we can use a trick (more on this trick later). If we needed to update every row
in the
EMPS_NT
, we would have to do four updates: one each for the rows in
DEPT_AND_EMP
to update the virtual table
associated with each row.
Another thing to consider is that when we updated the employee data for department
10
, we were semantically
updating the
EMPS
column in the
DEPT_AND_EMP
table. We understand that physically there are two tables involved,
but semantically there is only one. Even though we updated no data in the
DEPT
table, the row that contains the nested
table we did modify is locked from update by other sessions. In a traditional parent/child table relationship, this
would not be the case.
These are the reasons why I tend to stay away from nested tables as a persistent storage mechanism. It is the
rare
child table that is not queried stand-alone. In the preceding example, the
EMP
table should be a strong entity. It stands
alone, so it needs to be queried alone. I find this to be the case almost all of the time. I tend to use nested tables via
views on relational tables.
So, now that we have seen how to update a nested table instance, inserting and deleting are pretty
straightforward. Let's add a row to the nested table instance department
10
and remove a row from department
20
:
EODA@ORA12CR1> insert into table
2 ( select emps from dept_and_emp where deptno = 10 )
3 values
4 ( 1234, 'NewEmp', 'CLERK', 7782, sysdate, 1200, null );
1 row created.
EODA@ORA12CR1> delete from table
2 ( select emps from dept_and_emp where deptno = 20 )
3 where ename = 'SCOTT';
1 row deleted.
