drop package eval;
drop table Part_to_Part;
drop table Part;

create table Part(
	id		number primary key,
	name		varchar2(10),
	kind		varchar2(10),
	assemblyCost	number,
	assemblyMass	number
);

create table Part_to_Part (
	id_parent	number references Part,
	id_child	number references Part not null,
	amount		number not null,
	check (amount > 0)
);


insert into Part values (1, 'A', 'detail', 50, 10);
insert into Part values (2, 'B', 'detail', 10,  1);
insert into Part values (3, 'C', 'detail', 20,  2);
insert into Part values (4, 'D', 'detail', 10, 15);
insert into Part values (5, 'E', 'detail',  5, 10);
insert into Part values (6, 'F', 'detail',  5, 10);

insert into Part values (7,  'assembly1', 'agregate', 150, 1);
insert into Part values (8,  'assembly2', 'agregate', 15, 22);
insert into Part values (9,  'assembly3', 'agregate', 15, 14);
insert into Part values (10, 'assembly4', 'agregate', 11, 12);

insert into Part values (11, 'top1',  'agregate', 11, 12);
insert into Part values (12, 'top2',  'agregate', 11, 12);
insert into Part values (13, 'whole', 'agregate', 11, 12);

insert into Part_to_Part values (7, 1, 5);
insert into Part_to_Part values (8, 2, 2);
insert into Part_to_Part values (8, 3, 7);
insert into Part_to_Part values (9, 4, 5);
insert into Part_to_Part values (9, 5, 3);
insert into Part_to_Part values (10, 6, 8);
insert into Part_to_Part values (10, 3, 1);
insert into Part_to_Part values (11, 7, 8);
insert into Part_to_Part values (11,10, 1);
insert into Part_to_Part values (12, 8, 8);
insert into Part_to_Part values (12, 9, 1);
insert into Part_to_Part values (13,11, 8);
insert into Part_to_Part values (13,12, 1);
insert into Part_to_Part values (13, 1, 1);
insert into Part_to_Part values (null, 13, 1);



/* Takie małe hakerstwo - funkcja, która ewaluuje */
/* wyrażenia arytmetyczne                         */

create or replace package eval
as
     function uate(expression in varchar2) return number;
end;
/


create or replace package body eval
as
  g_theCursor        integer default dbms_sql.open_cursor;

  function uate(expression in varchar2) return number
  is
      l_rowsprocessed number  default 0;
      l_retval       number;
  begin
      dbms_sql.parse(g_theCursor,
                    'begin :ret_val := ' || expression || '; end;',
                     dbms_sql.native );
  
      dbms_sql.bind_variable
            (g_theCursor,':ret_val',l_retval );

      l_rowsprocessed := dbms_sql.execute(g_theCursor);
      dbms_sql.variable_value
          ( g_theCursor, ':ret_val', l_retval );
  
      return l_retval;
  end uate;
  
  
end eval;
/


