HomeOracle Page 2 - Database Interaction with PL/SQL: Nested Tables
Working with DML statements using NESTED TABLE with OBJECT TYPE - Oracle
This series has been focusing on database interactions with Oracle PL/SQL. Having just covered OBJECT TYPE in Oracle and both SQL and PL/SQL ways of working with OBJECTs in the last article, we will now learn about using NESTED TABLE with OBJECT TYPES from both SQL and PL/SQL point of views. We will also discuss FOR loops with an inline SELECT statement. This article is the fifth in this series.
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.