Advantage of a collection derived table

By contrast, if you were to use collection variables, you might be required to allocate multiple variables and multiple cursors. For example, consider the following schema:

EXEC SQL create row type parent_type(name char(255), id int,
   children list(person not null));
EXEC SQL create grade12_parents(class_id int, 
   parents set(parent_type not null));

You can query the collection derived table as a virtual table as shown in the following SELECT statement:

EXEC SQL select name into :host_var1 
   from table((select children from table((select parents
   from grade12_parents where class_id = 1))
   p_table where p_table.id = 1001)) c_table
   where c_table.name like ’Mer%’;

To perform the same query with collection variables, you need to execute the following statements:

EXEC SQL client collection hv1;
EXEC SQL client collection hv2;
EXEC SQL int parent_id;

EXEC SQL char host_var1[256];
⋮

EXEC SQL allocate collection hv1;
EXEC SQL allocate collection hv2;

EXEC SQL select parents into :hv1 from grade12_parents
   where class_id = 1;
EXEC SQL declare cur1 cursor for select id, children 
   from table(:hv1);
EXEC SQL open cur1;
for(;;)
{
   EXEC SQL fetch cur1 into :parent_id, :hv2;
   if(parent_id = 1001)
      break;
}
EXEC SQL declare cur2 cursor for select name from
   table(:hv2));
EXEC SQL open cur2;
for(;;)
{
   EXEC SQL fetch cur2 into :host_var1;
   /* user needs to implement ’like’ function */
   if(like_function(host_var1, "Mer%"))
      break;
}