/*
 * create_world_leaders.sql
 * Chapter 13, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script builds the PRESIDENT table, seeds the table with
 * scalar variable types, creates the WORLD_LEADERS package, and
 * provides anonymous block PL/SQL to test package procedures.
 */

-- Unremark for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT   table_name
            FROM     user_tables
            WHERE    table_name = 'PRESIDENT' ) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE president';
  END LOOP;
  
  FOR i IN (SELECT   sequence_name
            FROM     user_sequences
            WHERE    sequence_name = 'PRESIDENT_S1' ) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE president_s1';
  END LOOP;
END;
/

-- Create table for dynamic web page display.
CREATE TABLE president
( president_id      NUMBER
, last_name         VARCHAR2(20 CHAR)
, first_name        VARCHAR2(20 CHAR)
, middle_name       VARCHAR2(20 CHAR)
, term_start        NUMBER
, term_end          NUMBER
, country           VARCHAR2(3 CHAR)
, party             VARCHAR2(24 CHAR)
, CONSTRAINT pk_p1  PRIMARY KEY (president_id));

-- Create sequence for primary (surrogate) key.
CREATE SEQUENCE president_s1;

-- ------------------------------------------------------------------
-- Seed data for 42 US Presidents.
-- ------------------------------------------------------------------

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Washington','George',''
       , 1789,1797 
       ,'USA','Federalist');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Adams','John',''
       , 1797,1801
       ,'USA','Federalist');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Jefferson','Thomas',''
       , 1801,1809
       ,'USA','Democratic-Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Madison','James',''
       , 1809,1817 
       ,'USA','Democratic-Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Monroe','James',''
       , 1817,1825 
       ,'USA','Democratic-Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Adams','John','Quincy'
       , 1825,1829 
       ,'USA','Democratic-Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Jackson','Andrew',''
       , 1829,1837 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Van Buren','Martin',''
       , 1837,1841 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Harrison','William','Henry'
       , 1841,1841 
       ,'USA','Whig');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Tyler','John',''
       , 1841,1845 
       ,'USA','Whig');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Polk','James',''
       , 1845,1849 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Taylor','Zachary',''
       , 1849,1850 
       ,'USA','Whig');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Fillmore','Millard',''
       , 1850,1853 
       ,'USA','Whig');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Pierce','Franklin',''
       , 1853,1857 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Buchanan','James',''
       , 1857,1861 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Lincoln','Abraham',''
       , 1861,1864 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Johnson','Andrew',''
       , 1865,1869 
       ,'USA','Union');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Grant','Ulysses','S.'
       , 1869,1877 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Hayes','Rutherford','B.'
       , 1877,1881 
       ,'USA'
       ,'Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Garfield','James',''
       , 1881,1881 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Arthur','Chester',''
       , 1881,1885 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Cleveland','Grover',''
       , 1885,1889 
       ,'USA','Democratic');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Harrison','Benjamin',''
       , 1889,1893 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Cleveland','Grover',''
       , 1893,1897 
       ,'USA','Democratic');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'McKinley','William',''
       , 1897,1901 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Roosevelt','Theodore',''
       , 1901,1909 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Taft','William','H.'
       , 1909,1913 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Wilson','Woodrow',''
       , 1913,1921 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Coolidge','Calvin',''
       , 1923,1929 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Hoover','Herbert',''
       , 1929,1933 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Roosevelt','Franklin','D.'
       , 1933,1945 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Truman','Harry','S.'
       , 1945,1953 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Eisenhower','Dwight','David'
       , 1953,1961 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Kennedy','John','Fitzgerald'
       , 1961,1963 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Johnson','Lyndon','B.'
       , 1963,1969 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Nixon','Richard','M.'
       , 1969,1974 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Ford','Gerald','R.'
       , 1974,1977 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Carter','Jimmy',''
       , 1977,1981 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Reagan','Ronald',''
       , 1981,1989 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Bush','George','H.W.'
       , 1989,1993 
       ,'USA','Republican');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Clinton','William','Jefferson'
       , 1993,2001 
       ,'USA','Democrat');

INSERT INTO president
VALUES ( president_s1.nextval
       ,'Bush','George','W.'
       , 2001,2009 
       ,'USA','Republican');

-- Commit the entries to the PRESIDENT table.
COMMIT;

-- ------------------------------------------------------------------
-- User defined scalar type collections.
-- ------------------------------------------------------------------

-- Define a VARRAY of NUMBER data types.
CREATE OR REPLACE TYPE president_id_varray
  AS VARRAY(100) OF NUMBER;
/

-- Define three VARRAY of VARCHAR2 data types of varying size.
CREATE OR REPLACE TYPE president_name_varray
  AS VARRAY(100) OF VARCHAR2(60 CHAR);
/
CREATE OR REPLACE TYPE tenure_varray
  AS VARRAY(100) OF VARCHAR2(9 CHAR);
/
CREATE OR REPLACE TYPE party_varray
  AS VARRAY(100) OF VARCHAR2(24 CHAR);
/

-- Define a VARRAY of NUMBER data types.
CREATE OR REPLACE TYPE president_id_ntable
  AS TABLE OF NUMBER;
/

-- Define three VARRAY of VARCHAR2 data types of varying size.
CREATE OR REPLACE TYPE president_name_ntable
  AS TABLE OF VARCHAR2(60 CHAR);
/
CREATE OR REPLACE TYPE tenure_ntable
  AS TABLE OF VARCHAR2(9 CHAR);
/
CREATE OR REPLACE TYPE party_ntable
  AS TABLE OF VARCHAR2(24 CHAR);
/

-- ------------------------------------------------------------------
-- Package specification with overloaded procedures.
-- ------------------------------------------------------------------

-- Create a package specification or definition.
CREATE OR REPLACE PACKAGE world_leaders AS

  -- Define an associative array (PL/SQL Table)  of numbers.
  TYPE president_id_table IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  -- Define three associative arrays (PL/SQL Table)  of VARCHAR2 by size.
  TYPE president_name_table IS TABLE OF VARCHAR2(60 CHAR)
    INDEX BY BINARY_INTEGER;
  TYPE tenure_table IS TABLE OF VARCHAR2(9 CHAR)
    INDEX BY BINARY_INTEGER;
  TYPE party_table IS TABLE OF VARCHAR2(24 CHAR)
    INDEX BY BINARY_INTEGER;

  -- Define procedure specification to return parallel associative arrays.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_TABLE
  , president_names IN OUT PRESIDENT_NAME_TABLE
  , tenures         IN OUT TENURE_TABLE
  , parties         IN OUT PARTY_TABLE);
  
  -- Define procedure specification to return VARRAYs.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_VARRAY
  , president_names IN OUT PRESIDENT_NAME_VARRAY
  , tenures         IN OUT TENURE_VARRAY
  , parties         IN OUT PARTY_VARRAY);
  
  -- Define procedure specification to return parallel nested_tables.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_NTABLE
  , president_names IN OUT PRESIDENT_NAME_NTABLE
  , tenures         IN OUT TENURE_NTABLE
  , parties         IN OUT PARTY_NTABLE);
  
  -- Define a PL/SQL Reference Cursor.
  TYPE president_type_cursor IS REF CURSOR;

  -- Define procedure specification to return reference cursor.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , presidents      IN OUT PRESIDENT_TYPE_CURSOR);
  
END world_leaders;
/

-- Display any compilation errors.
SHOW ERRORS

-- ------------------------------------------------------------------
-- Package body with overloaded procedures.
-- ------------------------------------------------------------------

-- Create a package body or implementation.
CREATE OR REPLACE PACKAGE BODY world_leaders AS

  -- Implement a procedure body to return parallel associative arrays.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_TABLE
  , president_names IN OUT PRESIDENT_NAME_TABLE
  , tenures         IN OUT TENURE_TABLE
  , parties         IN OUT PARTY_TABLE) AS
  
  BEGIN
  
    -- Define a Bulk Collect into parallel associative arrays (PL/SQL tables)
    SELECT   president_id pres_number
    ,        first_name||' '||middle_name||' '||last_name pres_name
    ,        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;

  -- Implement a procedure body to return parallel VARRAYs.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_VARRAY
  , president_names IN OUT PRESIDENT_NAME_VARRAY
  , tenures         IN OUT TENURE_VARRAY
  , parties         IN OUT PARTY_VARRAY) AS
  
  BEGIN
  
    SELECT   president_id pres_number
    ,        first_name||' '||middle_name||' '||last_name pres_name
    ,        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;

  -- Implement a procedure body to return parallel nested tables.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , president_ids   IN OUT PRESIDENT_ID_NTABLE
  , president_names IN OUT PRESIDENT_NAME_NTABLE
  , tenures         IN OUT TENURE_NTABLE
  , parties         IN OUT PARTY_NTABLE) AS
  
  BEGIN
  
    SELECT   president_id pres_number
    ,        first_name||' '||middle_name||' '||last_name pres_name
    ,        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;

  -- Implement a procedure body to return a reference cursor.
  PROCEDURE get_presidents
  ( term_start_in   IN     NUMBER
  , term_end_in     IN     NUMBER
  , country_in      IN     VARCHAR2
  , presidents      IN OUT PRESIDENT_TYPE_CURSOR ) AS

  BEGIN

    -- Collect data for the reference cursor.
    OPEN presidents FOR
      SELECT   president_id "#"
      ,        first_name||' '||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;
 
  END get_presidents;
  
END world_leaders;
/

-- Display any compilation errors.
SHOW ERRORS

-- ------------------------------------------------------------------
-- Anonymous Block Testing Programs.
-- ------------------------------------------------------------------

-- Define anonymous block PL/SQL Program to test PL/SQL table  procedure.
DECLARE

  -- Declare local variables.
  t_start           NUMBER := '1914';
  t_end             NUMBER := '1943';
  t_country         VARCHAR2(3 CHAR) := 'USA';
  president_ids     WORLD_LEADERS.PRESIDENT_ID_TABLE;
  president_names   WORLD_LEADERS.PRESIDENT_NAME_TABLE;
  tenures           WORLD_LEADERS.TENURE_TABLE;
  parties           WORLD_LEADERS.PARTY_TABLE;

BEGIN

  -- Call the overloaded procedure.  
  world_leaders.get_presidents
  ( t_start
  , t_end
  , t_country
  , president_ids
  , president_names
  , tenures
  , parties);

  -- Read the contents of one of the arrays.  
  FOR i IN 1..president_names.COUNT LOOP
    dbms_output.put_line('Testing ['||president_names(i)||']');
  END LOOP;  

END;
/

-- Define anonymous block PL/SQL Program to test Varray procedure.
DECLARE

  -- Declare local variables.
  t_start           NUMBER := '1914';
  t_end             NUMBER := '1943';
  t_country         VARCHAR2(3 CHAR) := 'USA';
  president_ids     PRESIDENT_ID_VARRAY;
  president_names   PRESIDENT_NAME_VARRAY;
  tenures           TENURE_VARRAY;
  parties           PARTY_VARRAY;

BEGIN

  -- Call the overloaded procedure.  
  world_leaders.get_presidents
  ( t_start
  , t_end
  , t_country
  , president_ids
  , president_names
  , tenures
  , parties);

  -- Read the contents of one of the arrays.  
  FOR i IN 1..president_names.COUNT LOOP
    dbms_output.put_line('Testing ['||president_names(i)||']');
  END LOOP;  

END;
/

-- Define anonymous block PL/SQL Program to test nested table procedure.
DECLARE

  -- Declare local variables.
  t_start           NUMBER := '1914';
  t_end             NUMBER := '1943';
  t_country         VARCHAR2(3 CHAR) := 'USA';
  president_ids     PRESIDENT_ID_NTABLE;
  president_names   PRESIDENT_NAME_NTABLE;
  tenures           TENURE_NTABLE;
  parties           PARTY_NTABLE;

BEGIN

  -- Call the overloaded procedure.  
  world_leaders.get_presidents
  ( t_start
  , t_end
  , t_country
  , president_ids
  , president_names
  , tenures
  , parties);

  -- Read the contents of one of the arrays.  
  FOR i IN 1..president_names.COUNT LOOP
    dbms_output.put_line('Testing ['||president_names(i)||']');
  END LOOP;  

END;
/

-- Define anonymous block PL/SQL Program to test PL/SQL reference cursor.
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 variable of the PL/SQL record.
  presidents PRESIDENT_RECORD;

  -- Declare local variables.
  t_start           NUMBER := '1914';
  t_end             NUMBER := '1943';
  t_country         VARCHAR2(3 CHAR) := 'USA';
  president_info    WORLD_LEADERS.PRESIDENT_TYPE_CURSOR;

BEGIN

  -- Call the overloaded procedure.  
  world_leaders.get_presidents
  ( t_start
  , t_end
  , t_country
  , president_info);

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