/*
 * get_president1.sql
 * Appendix I, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script demonstrate working with a stored function.
 */

-- Unremark for debugging script.
SET PAGESIZE 999
SET SERVEROUTPUT ON SIZE 1000000

-- Select without providing optional parameters.
SELECT   get_presidents_function
FROM     dual;

-- Select with option parameters.
SELECT   get_presidents_function(1787,1800)
FROM     dual;

-- Define anonymous block PL/SQL program to test named notation.
DECLARE

  -- Define local PL/SQL record type as target for reference cursor.
  TYPE president_record IS RECORD
  ( president_id    NUMBER
  , president       VARCHAR2(60 CHAR)
  , tenure          VARCHAR2(9 CHAR)
  , party           VARCHAR2(24 CHAR));

  -- Define a local variable using the user-defined record type.
  president_rs      PRESIDENT_RECORD;

  -- Define a variable for the user-defined reference cursor.
  presidents      WORLD_LEADERS.PRESIDENT_TYPE_CURSOR;

BEGIN

  -- Assign the function to a matching left operand. 
  presidents := get_presidents_function(term_end_in => 1820);

  -- Explicit fetches are required for reference cursors.
  LOOP
    FETCH presidents
    INTO  president_rs;
    EXIT WHEN presidents%NOTFOUND;
    dbms_output.put     ('['||president_rs.president_id||']');
    dbms_output.put     ('['||president_rs.president||']');
    dbms_output.put     ('['||president_rs.tenure||']');
    dbms_output.put_line('['||president_rs.party||']');
  END LOOP;
      
END;
/
  