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


SET PAGESIZE 999
SET SERVEROUTPUT ON SIZE 1000000


CREATE OR REPLACE FUNCTION get_presidents_function
( term_start_in   IN     NUMBER := 1787
, term_end_in     IN     NUMBER := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))
, country_in      IN     VARCHAR2 := 'USA')
RETURN WORLD_LEADERS.PRESIDENT_TYPE_CURSOR IS

  presidents      WORLD_LEADERS.PRESIDENT_TYPE_CURSOR;

BEGIN
  
  -- Collect data for the reference cursor.
  OPEN presidents FOR
    SELECT   president_id "#"
    ,        first_name||' '
    ||       DECODE(middle_name,NULL,NULL,middle_name||' ')
    ||       last_name "Preisdent"
    ,        term_start||' '||term_end "Tenure"
    ,        party "Party"
    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;

  RETURN presidents;
 
END get_presidents_function;
/
