--26/Jan wouter DS

CREATE OR REPLACE package IVerkoopMgt
AS
	--declaratie van de record datatypes
	TYPE rec_order_summary
	IS RECORD
	(orderID number(4),
	 klantID number(4),
	 datum date,
	 leveringsAdres varchar2(30),
	 totaal number(7,2) );
		
	TYPE rec_account_summary
	IS RECORD
	( accID number(4),
	  klantID number(4),
	  lastOrder date,
	  debet number(7,2) );
	  
	--declaratie van de tabel datatypes
	
	TYPE table_order_summary
	IS TABLE
	OF rec_order_summary
	index by binary_integer;
	
	TYPE table_account_summary
	IS TABLE
	OF rec_account_summary
	index by binary_integer;
	
	--declaratie van de functies
	  
	function getOrder(p_klantid number)
	return table_order_summary;
	
	function getAccount(p_klantid number)
	return table_account_summary;

END IVerkoopMgt;

CREATE OR REPLACE package body IVerkoopMgt
AS
		
	v_teller number default 1;
	
	v_table_order_summary table_order_summary;
	v_table_account_summary table_account_summary;
	
	CURSOR c_order IS
	select * FROM order;
	
	CURSOR c_account IS
	select * FROM account;
	
	function getOrder(
	p_klantid number )
	return table_order_summary
	IS
	
	BEGIN
		FOR r_order IN c_order LOOP
				
			IF r_order.klantid = p_klantid THEN
				v_table_order_summary(v_teller) := r_order;
				v_teller := v_teller + 1;
			END IF;
		END LOOP;
			
		v_teller := 1;	
		RETURN v_table_order_summary;
			
	END getOrder;
	
	function getAccount(
	p_klantid number)
	return table_account_summary
	IS
	BEGIN
		FOR r_account IN c_account LOOP
			IF r_account.klantid = p_klantid THEN
				v_table_account_summary(v_teller) := r_account;
				v_teller := v_teller + 1;
			END IF;
		END LOOP;
		
		v_teller := 1;
		
		RETURN v_table_account_summary;					
			
		
	END getAccount;


END IVerkoopMgt;