/*
 * reading_string_index.sql
 * Appendix I, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script demonstrate reading an associative array string index
 * alphabetically in ascending order.
 */

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

DECLARE

  -- Define control variables.
  current VARCHAR2(60 CHAR);
  element INTEGER;

  -- Define an associative array of numbers.
  TYPE president_table IS TABLE OF NUMBER
    INDEX BY VARCHAR2(60 CHAR);

  -- Declare an associative array variable.
  presidents PRESIDENT_TABLE;

  -- Define a dynamic cursor.
  CURSOR get_presidents
  ( term_start_in NUMBER
  , term_end_in   NUMBER
  , country_in    VARCHAR2) IS
    SELECT   president_id
    ,        last_name||', '
    ||       first_name
    ||       DECODE(middle_name,NULL,NULL,' '||middle_name) president
    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
    ORDER BY 1;

BEGIN

  -- Swap index and name.
  FOR i IN get_presidents(1787,2009,'USA') LOOP
      presidents(i.president) := TO_CHAR(i.president_id);
  END LOOP;

  -- Start with first alphabetically index column.
  FOR i IN 1..presidents.COUNT LOOP
  
    IF i = 1 THEN
      current := presidents.FIRST;
      element := presidents(current);
    ELSE
      IF presidents.NEXT(current) IS NOT NULL THEN
        current := presidents.NEXT(current);
        element := presidents(current);
      ELSE
        EXIT;
      END IF;     
    END IF;

    -- Print current value and index. 
    IF element < 10 THEN
      DBMS_OUTPUT.PUT_LINE('Old Index ['||element||']  is ['||current||']');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Old Index ['||element||'] is ['||current||']');
    END IF;

  END LOOP;

END;
/
