Collections in the Database

create or replace type BookList as varray(10) of number(4);

create table class_material (
  cid     number(4),
  title   varchar2(30),
  reading BookList
);

insert into class_material values (467, 'Advanced database programming', BookList(1, 3, 12));
insert into class_material values (363, 'Network Administration', BookList(2,4));
insert into class_material values (489, 'Advanced C++ programming', BookList(5,6,7,9));

select * 
  from class_material;

select * 
  from class_material CM
 where 6 in (select * from table(CM.reading));

  				  
create or replace procedure PrintRequired(p_CourseID in class_material.cid%type)
is
  v_Books class_material.reading%type; 
begin
  select reading
    into v_Books
    from class_material
   where cid = p_CourseID;
  for i in 1..v_Books.count loop
    dbms_output.put_line(i || ': ' || v_Books(i));
  end loop;
end PrintRequired;

create or replace type StudentList as table of number(6);
create table lib_catalog (
  catid       number(4),
  num_copies  number,
  num_out     number,
  checked_out StudentList
) nested table checked_out store as co_tab;

insert into lib_catalog values (1000, 20, 3, StudentList(1234, 567, 34243));
insert into lib_catalog values (1001, 20, 2, StudentList(1234, 54243));
insert into lib_catalog values (2001, 10, 1, StudentList(9243));

select catid
  from lib_catalog LC
 where 1234 in ( select * from table(LC.checked_out) );
 
insert into table(select checked_out from lib_catalog where catid=2001)
 values (7654);

select catid as "ID", 
       num_copies "Copies", 
       num_copies - (select count(*) from table(LC.checked_out) ) "In stock" 
  from lib_catalog LC;