Database Interaction with PL/SQL: Nested Tables - Working with DML statements using NESTED TABLE with OBJECT TYPE
(Page 2 of 4 )
The following SELECT statement gives the respective details of NESTED TABLE available in a single row of parent table:
select a.CompanyName,a.NoOfYears from
table(select experiences from employees where name='jag') a
You can also replace the word ‘table’ with another keyword ‘the’ as follows:
select a.CompanyName,a.NoOfYears from
the(select experiences from employees where name='jag') a
Any of the above two examples performs two operations. First, it reads an ‘employees’ row with the given name. Then, it reads the elements stored in that row’s nested table. For better readability, I used ‘table’ instead of ‘the’. You can even replace the field names with ‘*’ as follows:
select a.* from
table(select experiences from employees where name='jag') a
By now, we already know how to retrieve information from NESTED TABLEs. Now we need to know how to insert rows to the NESTED TABLE (not to the main table ‘employees’)? This means I wanted to add another ‘experience’ row to the same employee ‘jag’. The following statement does it.
INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
VALUES
(
t_experience('efg company','Professor',2)
);
If you compare the above example with previous SELECT statements, you should be able to understand and there is nothing new from any angle. Actually it is not necessary to use ‘t_experience’ in the above statement, you can also insert it as follows:
INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
VALUES
(
'efg company','Professor',2
);
But for better readability, I suggest you to use the TYPE name along with values. You can even modify the existing rows using UPDATE statement as follows:
UPDATE
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
set NoOfYears=5
where CompanyName='abc company';
You can even use DELETE statement on NESTED TABLE as follows:
DELETE FROM
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
where CompanyName='abc company';
If you don’t use any OBJECT type in the NESTED TABLE (like a NESTED TABLE of varchar2, like in part-3), you can use a keyword ‘column_value’ for UPDATE or DELETE statements. It is necessary because the NESTED TABLE does not have any specific columns to address.
Next: Working with PL/SQL using NESTED TABLE with OBJECT TYPE >>
More Oracle Articles
More By Jagadish Chatarji
