--Oracle examples for Section 4.4
--assumes customers table is loaded
drop table result;
create table result(rvalue int);

--Example 4.4.1
declare
      i  integer;       -- local variable without an initial value
      total integer:= 0;      -- local variable with an initial value
begin
      for i in 1..100 loop
          total := total + i;
      end loop;
      insert into result (rvalue) values (total); -- insert answer into table
end;
/
select rvalue from result;


--Example 4.4.2
drop function sum_n;
create function sum_n(n integer) return integer is
      i integer; 
      total integer:= 0;  
begin
      for i in 1..n loop
          total := total + i;
      end loop;
      return total;               -- return result to SQL or PL/SQL caller
end;
/
show errors;

--Example 4.4.3
drop function namecity;
create function namecity(custid char) return char is
      custname char(20);  -- max size, gets trimmed back
      custcity char(20);
begin
    select cname, city into custname, custcity from customers
        where cid = custid;
    return rtrim(custname)||' '||rtrim(custcity)||')'; -- trim extra space
end;  
/
show errors;
select namecity(cid) from customers;

drop table points;
drop table rects;
drop type rectangle_t;
drop type point_t;

--Example 4.4.4
create type point_t as object
  (  
      x int,        -- horizontal coordinate of the point
      y int         -- vertical coordinate of the point
  );
/
show errors;
--Example 4.4.5
create type rectangle_t as object 
(
      pt1 point_t,           -- lower left-hand corner of rectangle
      pt2 point_t,           -- upper right-hand corner of rectangle
      member function inside(p point_t)  -- is point is inside rectangle?
          return int,          -- return 1 if point inside, else 0            
      member function area     -- method for area of rectangle 
          return int           -- area value will always be an integer
);
/
show errors;

--need implementation here to allow calls to work--
create type body rectangle_t as
  member function area return int is    -- all logic is in PL/SQL
  begin
      return (self.pt2.x-self.pt1.x)*(self.pt2.y-self.pt1.y); -- The area
  end;
  member function inside(p in point_t) return int is
  begin
      if (p.x >= self.pt1.x) and (p.x <= self.pt2.x) and  -- inside on x 
           (p.y >= self.pt1.y) and (p.y <= self.pt2.y) then -- inside on y 
              return 1;    -- p is inside rectangle (including the boundary)
           else
              return 0;    -- p is outside rectangle
      end if;
  end;
end;    
/
show errors;

drop function inc_age;
create function inc_age(x person_t) return person_t is 
      nper person_t:= person_t(x.ssno, x.pname, x.age); --clone x 
  begin
      nper.age := x.age + 1; --change age in LOCAL VARIABLE, not PARAMETER
      return nper; 
  end;    
/
show errors;

--Example 4.4.6
create table points of point_t (primary key (x, y));
create table rects of rectangle_t (primary key (pt1.x, pt1.y, pt2.x, pt2.y));
insert into rects values (point_t(1,2),point_t(3,4));
insert into rects values (point_t(1,1),point_t(6,6));
insert into points values (2,3);
insert into points values (1,4);
insert into points values (4,4);      

--Example 4.4.7
select value(x), x.area() from rects x;
select value(x), x.inside(point_t(4,2)) from rects x;

select distinct p.x, p.y from points p, rects r
      where r.inside(value(p)) > 0;

select value(p) from points p where not exists
      (select * from rects r where r.inside(value(p)) = 0);

update rects r set pt2 = point_t(r.pt2.x+1, r.pt2.y+1)
      where r.area() < 24

--Example 4.4.8
create or replace type body rectangle_t as
  member function area return int is    -- all logic is in PL/SQL
  begin
      return (self.pt2.x-self.pt1.x)*(self.pt2.y-self.pt1.y); -- The area
  end;
  member function inside(p in point_t) return int is
  begin
      if (p.x >= self.pt1.x) and (p.x <= self.pt2.x) and  -- inside on x 
           (p.y >= self.pt1.y) and (p.y <= self.pt2.y) then -- inside on y 
              return 1;    -- p is inside rectangle (including the boundary)
           else
              return 0;    -- p is outside rectangle
      end if;
  end;
end;    
/

--Example 4.4.9
select p.x, p.y, min(r.area()) from rects r, points p 
      where r.inside(value(p)) > 0
      group by p.x, p.y;

select value(p), value(r), r.area() from rects r, points p
      where r.area() = 
          (select min(r1.area()) from rects r1  -- minimal area rectangle...
              where r1.inside(value(p)) > 0)    -- ...containing the point p
          and r.inside(value(p)) > 0
      order by p.x, p.y;

--Example 4.4.10
drop table documents;
drop type document_t;
drop type wordset_t;
drop type wordtbl_t;
--in case types won't drop, check their dependencies
select name from user_dependencies where referenced_name ='WORDSET_T';
select name from user_dependencies where referenced_name ='WORDTBL_T';

create type wordtbl_t as table of varchar(30); -- nested table type 
/
create type wordset_t as object (  -- [key]word set object type with methods
   words    wordtbl_t,           -- attribute is nested table of [key]words
   member function wordcount return int, -- return count of words in self 
   member function subset(x in wordset_t) return int --is self a subset of x?
--   member function superset(x in wordset_t) return int --is self a superset?
);
/
show errors;

create or replace type body wordset_t as
   member function wordcount return int is
   begin
       return self.words.count; -- built-in collection method count
   end;  
   member function subset(x in wordset_t) return int is
      matches int; i int; j int; 
   begin
    for i in 1..self.words.count loop  -- loop through all words in self.words
      matches := 0;
      for j in 1..x.words.count loop   -- loop through x.words; nested table..
        if self.words(i) = x.words(j) then -- rows found by index no. 
                                       -- in PL/SQL
            matches := matches + 1;    -- found match, count it
        end if;
      end loop;                        -- end for loop on x.words
      if matches = 0 then 
         return 0;                     -- self.words not subset of x.words
      end if;      
    end loop;    
    return 1;                          -- we found all self.words in x.words
   end;
--member function superset.     -- similar to subset (reverse x and self)
end;  
/
show errors;

--Example 4.4.11
create or replace type document_t as object-- and old type, before re-creation
  (
      docid  int,                      -- unique id of document
      docname  varchar(40),            -- document title
      docauthor  person_t,             -- document author
      keywords  wordset_t              -- keyword set for document
  );
/
show errors;

create table documents of document_t  -- this is the object table of documents
  (
      primary key(docid)
  ) nested table keywords.words store as keywords_tab;  

--Example 4.4.12
select docid from documents d
      where 'boat' in (select * from table(d.keywords.words));

select sum(d.keywords.wordcount()) from documents d;      

--Example 4.4.13
select docid, docname from documents d
     where
     d.keywords.superset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip')))>0;

select docid, docname from documents d
  where d.keywords.superset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip')))>0
  and d.keywords.subset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip')))>0; 

--Example 4.4.14
--redo setup with new member functions
drop table points;
drop table rects;
drop type rectangle_t;
drop type point_t;

create type point_t as object
  (  
      x int,        -- horizontal coordinate of the point
      y int         -- vertical coordinate of the point
  );
/
show errors;
--Example 4.4.5
create type rectangle_t as object 
(
      pt1 point_t,           -- lower left-hand corner of rectangle
      pt2 point_t,           -- upper right-hand corner of rectangle
      member function inside(p point_t)  -- is point is inside rectangle?
          return int,          -- return 1 if point inside, else 0            
      member function area     -- method for area of rectangle 
          return int,           -- area value will always be an integer
      member function resize (p point_t) 
          return rectangle_t
);
/

create type body rectangle_t as
  member function area return int is    -- all logic is in PL/SQL
  begin
      return (self.pt2.x-self.pt1.x)*(self.pt2.y-self.pt1.y); -- The area
  end;
  member function inside(p in point_t) return int is
  begin
      if (p.x >= self.pt1.x) and (p.x <= self.pt2.x) and  -- inside on x 
           (p.y >= self.pt1.y) and (p.y <= self.pt2.y) then -- inside on y 
              return 1;    -- p is inside rectangle (including the boundary)
           else
              return 0;    -- p is outside rectangle
      end if;
  end;
--new member function for this example:
member function resize(p point_t) return rectangle_t is
    newrect rectangle_t := self;             -- clone self to local object
begin
   if p.x > self.pt1.x and p.y > self.pt1.y then -- check new rect. is valid
      newrect.pt2 := p;         -- OK, update pt2 for return rectangle
   end if;
   return newrect;              -- no change if pt2 was illegal
end;    
end;    
/
show errors;
create table points of point_t (primary key (x, y));
create table rects of rectangle_t (primary key (pt1.x, pt1.y, pt2.x, pt2.y));
insert into rects values (point_t(1,2),point_t(3,4));
insert into rects values (point_t(2,1),point_t(6,6));
insert into points values (2,3);
insert into points values (1,4);
insert into points values (4,4);      

--Example 4.4.15
-- display resized rectangles
select r.resize(point_t(10,10)) from rects r; 

--Example 4.4.16
update rects r 
      set r = r.resize(point_t(r.pt2.x + 1,r.pt2.y + 1))     
          where r.pt1 = point_t(2,1); 
