Nested Table

What is the Nested Table
Ans : A nested table is collection of rows, represented as a column within the table. A nested table is collection of rows, represented as a column within the table. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table.

CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;

Size specification is required in NESTED table as varrays ?
Ans : NO

What is the TABLE command in NESTED table collection ?
Ans : To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.

Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.

Examples:
v Select the collection elements using table command as follows
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC2 Sozhinganallur Chennai
v Update one element in a collection
UPDATE TABLE(SELECT place FROM employee WHERE name ='Neal') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai

v Delete one element in a collection
DELETE from TABLE(SELECT place FROM employee WHERE name ='Neal')
WHERE column_value = 'CDC5'
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ Sozhinganallur Chennai

v Insert one element in a collection
INSERT INTO TABLE(SELECT place FROM employee WHERE name ='Neal') VALUES ('CDC5')
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai


Que: What is the Associative Array
Ans: The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.
In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”
The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY key_type [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];
Þ type_name is a type specifier used later to declare collections
Þ element_type is any PL/SQL datatype
Þ key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
Þ

Please give us some example ,how to use the associative array
Ans:

v Declare and populate a associative array using the key_type BINARY_INTEGER

DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (1) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(1) is ' pno (1));
pno (100) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(100) is ' pno (100));
END;

Output:
phone_no(1) is 022 667 943 2666
phone_no(100) is 022 667 943 2666
As you can see from the above example the elements (cells) of the array are created automatically by Oracle.
v Declare and populate a associative array using the key_type VARCHAR2

DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (‘office’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(office) is ' pno (‘office’));
pno (‘home’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(home) is ' pno (‘office’));
END;

Output:
phone_no(office) is 022 667 943 2666
phone_no(home) is 022 667 943 2666

v Select records from table and store it in the associative array as follows:

The employee table contains the following data:

1. Select * from employee

ENO NAME PLACE
------------- ------------- -------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai


2. Inserting the above records in another table using associative array as follows

Create table employ(eno number, name varchar2(60),place varchar2(60));

DECLARE CURSOR all_emps IS SELECT * FROM employee ORDER BY name; TYPE emp_table IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; emps emp_table; emps_max BINARY_INTEGER;BEGIN emps_max := 0;
FOR emp IN all_emps LOOP emps_max := emps_max + 1; emps(emps_max).eno := emp. eno; emps(emps_max).name := emp. name; emps(emps_max).place := emp. place;

Insert into employ values (emps(emps_max).eno, emps(emps_max).name, emps(emps_max).place);Dbms_output.put_line(‘ENO ENAME PLACE ‘);
Dbms_output.put_line(‘--------------------------------------------------------- ‘);

Dbms_output.put_line(emps(emps_max).eno ’ ‘emps(emps_max).name ’ ‘ emps(emps_max).place);
END LOOP;
END;
Output:
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai

Select * from employ
This Query returns the following

ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai

Thus the records in employee table is inserted into the employ table.