/*
 * get_presidents_procedure.sql
 * Appendix I, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script demonstrate the creation of a stored procedure.
 */


SET PAGESIZE 999
SET SERVEROUTPUT ON SIZE 1000000


CREATE OR REPLACE PROCEDURE get_presidents_procedure
( term_start_in   IN     NUMBER
, term_end_in     IN     NUMBER
, country_in      IN     VARCHAR2
, president_ids   IN OUT WORLD_LEADERS.PRESIDENT_ID_TABLE
, president_names IN OUT WORLD_LEADERS.PRESIDENT_NAME_TABLE
, tenures         IN OUT WORLD_LEADERS.TENURE_TABLE
, parties         IN OUT WORLD_LEADERS.PARTY_TABLE) IS
  
BEGIN
  
  -- Define a Bulk Collect into parallel associative arrays (PL/SQL tables)
  SELECT   president_id pres_number
  ,        first_name||' '
  ||       DECODE(middle_name,NULL,NULL,middle_name||' ')
  ||       last_name "Preisdent"
  ,        term_start||'-'||term_end tenure
  ,        party
  BULK COLLECT
  INTO     president_ids
  ,        president_names
  ,        tenures
  ,        parties
  FROM     president
  WHERE    country = country_in
  AND      term_start BETWEEN term_start_in AND term_end_in
  OR       term_end BETWEEN term_start_in AND term_end_in;
 
END get_presidents_procedure;
/
