Collectors

1. Collectors • Varying array • allows you to store repeating attributes of a record in a single row • example: all part numbers can be stored in ORDERS table as a varray 1* create or replace type parts99_va as varray(5) of varchar2(4) SQL> / Type created. create table orders99_o (ordnumb number(5), orddte date, customer ref customer_ty, parts parts_va) SQL> / Table created. • inserting records into a varying array insert into orders99_o select 12491, to_date('09/02/94', 'mm/dd/yy'), ref(c), parts_va('BT04','BZ66') from customer_o c where c.custnumb=311; • selecting data from varying arrays • cannot be queried directly via a select command

2. Collectors cont’d. • selecting data from varying arrays set serveroutput on 1 declare 2 cursor order_cursor is 3 select o.customer.custname,o.orddte, o.parts from orders99_o o; 4 order_rec order_cursor%rowtype; 5 begin 6 for order_rec in order_cursor 7 loop 8 dbms_output.put_line('Customer Name: '||order_rec.custname||' '||'Date: '||order_rec.orddte 9 dbms_output.put_line('-----------------------------------------'); 10 for i in 1..order_rec.parts.count 11 loop 12 dbms_output.put_line(order_rec.parts(i)); 13 end loop; 14 end loop; 15* end; SQL> / Customer Name: SALLY ADAMS Date: 02-SEP-94 ----------------------------------------- AX12 Customer Name: DON CHARLES Date: 02-SEP-94 ----------------------------------------- BT04 BZ66

3. Nested tables • Limitations of varying arrays • can only contain one column • must declare the maximum number of members at the time of definition • cannot be used in select statement of SQL • Nested table • a table represented as a column within another table • no limit on the number of entries per row • Example: create a nested table within ORDERS corresponding to line items • (ordlne) • Step 1: Create a type corresponding to ORDLNE CREATE OR REPLACE TYPE ORDLNE_TY AS OBJECT (PARTNUMB VARCHAR2(4), NUMBORD NUMBER(3), QUOTPRCE NUMBER(7,2) ) / • Step 2: Use the above data type as the basis for a nested table CREATE TYPE ORDLNE_NT AS TABLE OF ORDLNE_TY; /

4. Nested tables cont’d • Step 3: Create the ORDERS table using the ORDNLE_NT data type CREATE TABLE ORDERS (ORDNUMB NUMBER(6), ORDDTE DATE, CUSTOMER REF CUSTOMER_TY, ORDLNE ORDLNE_NT) NESTED TABLE ORDLNE STORE AS ORDLNE_NT_TAB; / • The above command creates a nested table called ORDLNE_NT_TAB • to store line item data within orders • The nested table’s data is stored “out-of-line” with the rest of the table’s • data • Although the nested table is stored as a separate table, you cannot access • it directly • Inserting data in nested table INSERT INTO ORDERS SELECT 12489, TO_DATE('09/02/94', 'MM/DD/YY'), REF(C),ORDLNE_NT(ORDLNE_TY('AX12',1,14.95)) FROM CUSTOMER_O C WHERE CUSTNUMB=124;

5. Querying nested tables • Since a nested table is a column within a table, querying a nested table • requires a special key word THE • Example: select all line items of orders placed by DON CHARLES • Step 1: select the nested table column from the main table select ordlne from orders o where o.customer.custname='DON CHARLES' • Step 2: enclose this query within the THE function • Step 3: query the nested table’s columns using the clause in Step 2 as the • table name in the query’s from clause 1 select nt.partnumb, nt.numbord, nt.quotprce 2* from the (select ordlne from orders o where o.customer.custname='DON CHARLES') NT SQL> / PART NUMBORD QUOTPRCE ---- --------- --------- BT04 1 402.99 BZ66 1 311.95