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) SQLgt / Type created.
create table orders99_o (ordnumb number(5),
orddte date, customer ref customer_ty, parts
parts_va) SQLgt / 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.custnumb311

• selecting data from varying arrays
• cannot be queried directly via a select command

2
Collectors contd.

• 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_cursorrowtype 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 SQLgt / 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 contd

• 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 tables data is stored out-of-line
  with the rest of the tables
• 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 CUSTNUMB124
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 tables columns using
  the clause in Step 2 as the
• table name in the querys 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 SQLgt
/ PART NUMBORD QUOTPRCE ---- ---------
--------- BT04 1
402.99 BZ66 1 311.95