Database Tables - Expert Oracle Database Architecture - page 407

DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
------ ----------- ----- ---------- --------- ----- --------- ----- -----
10 ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-81 2450
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-81 5000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-82 1300
20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-80 800
20 RESEARCH 7566 JONES MANAGER 7839 02-APR-81 2975
20 RESEARCH 7788 SCOTT ANALYST 7566 09-DEC-82 3000
20 RESEARCH 7876 ADAMS CLERK 7788 12-JAN-83 1100
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-81 3000
30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 SALES 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 SALES 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 SALES 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 SALES 7900 JAMES CLERK 7698 03-DEC-81 950
14 rows selected.
We are able to cast the EMPS column as a table and it naturally did the join for us—no join conditions were
needed. In fact, since our EMP type doesn't have the DEPTNO column, there is nothing for us apparently to join on.
Oracle takes care of that nuance for us.
So, how can we update the data? Let's say we want to give department 10 a $100 bonus. We would code the
following:
EODA@ORA12CR1> update
2 table( select emps
3 from dept_and_emp
4 where deptno = 10
5 )
6 set comm = 100
7 /
3 rows updated.
Here is where the “virtually a table for every row” comes into play. In the SELECT predicate shown earlier, it
may not have been obvious that there was a table per row, especially since the joins and such aren't there; it looks a
little like magic. The UPDATE statement, however, shows that there is a table per row. We selected a discrete table to
UPDATE —this table has no name, only a query to identify it. If we use a query that does not SELECT exactly one table, we
will receive the following:
EODA@ORA12CR1> update
2 table( select emps
3 from dept_and_emp
4 where deptno = 1
5 )
6 set comm = 100
7 /
update
*
ERROR at line 1:
ORA-22908: reference to NULL table value