---------------------------------------------------------------
-- Grade Book Database; Create Tables Script
-- Chapter 2; Oracle 9i Programming -- A Primer
--            by R. Sunderraman
---------------------------------------------------------------

create type address_type as object (
  street varchar2(30),
  city   varchar2(30),
  state  varchar2(20),
  zip    number(5)
);
/
create type phones_varray_type as varray(3) of char(12);
/
create type person_type as object (
  name     varchar2(30),
  address  address_type,
  phones   phones_varray_type);
/
create table o_employees (
  eno      number(4) not null primary key, 
  person   person_type,
  hdate    date
);
create table o_parts(
  pno      number(5) not null primary key,
  pname    varchar2(30),
  qoh      integer check(qoh >= 0),
  price    number(6,2) check(price >= 0.0),
  olevel   integer);

create table o_customers (
  cno      number(5) not null primary key,
  person   person_type
);

create type odetails_type as object (
  pno      number(5),
  qty      integer,
  --member function cost(pno in number, qty in number) return number,
  member function cost return number,
  pragma restrict_references(cost,WNDS)
);
/ 
show errors
create type body odetails_type as
--member function cost(pno in number, qty in number) return number is
member function cost return number is
  p o_parts.price%type;
begin
  select price into p from o_parts where pno = self.pno;
  return p * self.qty;
end;
end;
/
show errors
create type odetails_ntable_type as 
  table of odetails_type;
/
show errors
create type o_order_type as object (
  ono      number(5),
  odetails odetails_ntable_type,
  cno      number(5),
  eno      number(4),
  received date,
  shipped  date,
  member function total_cost return number,
  pragma restrict_references(total_cost,WNDS)
)
/
show errors
create or replace type body o_order_type as
  member function total_cost return number is
    i          integer;
    item       odetails_type;
    total      number := 0;
    item_cost  number;
  begin
    for i in 1..self.odetails.count  loop
      item := self.odetails(i);
      item_cost := item.cost();
      total := total + item_cost ;
    end loop;
    return total;
  end;
end;
/
show errors
create table o_orders of o_order_type (
  primary key (ono)
)
nested table odetails store as odetails_tab;

