-- John Wilkinson (MS50)
-- Databases (COMS 30103) - Dr. J.Gallagher
-- Video Shop - Assignment 2
------------------------------------------------------------------------
-- Question 3
------------------------------------------------------------------------


CREATE OR REPLACE PROCEDURE reportOverdueVideos
	(enter_date IN date)
AS

CURSOR point is  -- create cursor to handle report search

   select Member.MemberId as id,
          MemberName as name,
          VideoTitle as title,
          DueDate as due
   
   from Rental, Member
   where DueDate < enter_date and Member.MemberId = Rental.MemberId
   and Returned = 'n';
   
      
report_line point%rowtype;

BEGIN
	
	open point;
	fetch point into report_line;      -- get the first overdue tuple
	
	while point%found loop
	   insert into message_s           -- place each tuple into display table
	   values (report_line.id || '       ' ||
	           report_line.name || '       ' ||
	           report_line.title || '       ' ||
	           'Due back on: ' || report_line.due);     
       
           fetch point into report_line;   -- get next tuple
        
        end loop;
        commit;

END;          
/