| While the table definitions in the previous section look fairly straightforward, it isn't immediately obvious how you might go about populating the resulting tables. Whenever you want to create an instance of a collection, you need to use its constructor, which is a system-generated function with the same name as the collection. The constructor accepts one or more elements; for varrays, the number of elements cannot exceed the maximum size of the varray. For example, adding a row to the part_c table, which contains a varray column, can be done using the following: INSERT INTO part_c (part_nbr, name, supplier_id, unit_cost, inventory_qty, restocks) VALUES ('GX5-2786-A2', 'Spacely Sprocket', 157, 75, 22, resupply_dates(TO_DATE('03-SEP-1999','DD-MON-YYYY'), TO_DATE('22-APR-2000','DD-MON-YYYY'), TO_DATE('21-MAR-2001','DD-MON-YYYY')));
In this example, the resupply_dates constructor is called with three parameters, one for each time a shipment of parts was received. If you are using a collection-savvy query tool such as Oracle's SQL*Plus, you can query the collection directly, and the tool will format the results: SELECT part_nbr, restocks FROM part_c WHERE name = 'Spacely Sprocket'; PART_NBR RESTOCKS --------------- ---------------------------------------------------- GX5-2786-A2 RESUPPLY_DATES('03-SEP-99', '22-APR-00', '21-MAR-01')
You deal with nested tables in a manner similar to varrays. The next example demonstrates how you would insert a new row into the cust_order_c table, which contains a nested table column: INSERT INTO cust_order_c (order_nbr, cust_nbr, sales_emp_id, order_dt, sale_price, order_items) VALUES (1000, 9568, 275, TO_DATE('21-MAR-2001','DD-MON-YYYY'), 15753, line_item_tbl( line_item_obj('A675-015', 25), line_item_obj('GX5-2786-A2', 1), line_item_obj('X378-9JT-2', 3)));
If you look carefully, you will notice that there are actually two different constructors called: one to create the nested table line_item_tbl, and the other to create each of three instances of the line_item_obj object type. Remember, the nested table is a table of line_item_obj objects. The end result is a single row in cust_order_c containing a collection of three line items. |
