Oracle PL/SQL/PL SQL/Table of Date

Storing and retreiving a nested table with non-sequential keys

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE DateTab AS

 2    TABLE OF DATE;
 3  /

Type created. SQL> CREATE TABLE MyDate (

 2    key        VARCHAR2(100) PRIMARY KEY,
 3    date_list  DateTab)
 4    NESTED TABLE date_list STORE AS dates_tab;

Table created. SQL> SQL> create or replace PROCEDURE Print(p_Dates IN DateTab) IS

 2      v_Index BINARY_INTEGER := p_Dates.FIRST;
 3  BEGIN
 4      WHILE v_Index <= p_Dates.LAST LOOP
 5        DBMS_OUTPUT.PUT("  " || v_Index || ": ");
 6        DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_Dates(v_Index), "DD-MON-YYYY"));
 7        v_Index := p_Dates.NEXT(v_Index);
 8      END LOOP;
 9  END Print;
10  /

Procedure created. SQL> show error No errors. SQL> SQL> DECLARE

 2    v_Dates DateTab := DateTab(TO_DATE("04-JUL-1776", "DD-MON-YYYY"),
 3                               TO_DATE("12-APR-1861", "DD-MON-YYYY"),
 4                               TO_DATE("05-JUN-1968", "DD-MON-YYYY"),
 5                               TO_DATE("26-JAN-1986", "DD-MON-YYYY"),
 6                               TO_DATE("01-JAN-2001", "DD-MON-YYYY"));
 7
 8  BEGIN
 9    v_Dates.DELETE(2);
10
11    DBMS_OUTPUT.PUT_LINE("Initial value of the table:");
12    Print(v_Dates);
13
14    INSERT INTO MyDate (key, date_list) VALUES ("Dates in American History", v_Dates);
15
16    SELECT date_list INTO v_Dates FROM MyDate WHERE key = "Dates in American History";
17
18    DBMS_OUTPUT.PUT_LINE("Table after INSERT and SELECT:");
19    Print(v_Dates);
20  END;
21  /

Initial value of the table: 1: 04-JUL-1776 3: 05-JUN-1968 4: 26-JAN-1986 5: 01-JAN-2001 Table after INSERT and SELECT: 1: 04-JUL-1776 2: 05-JUN-1968 3: 26-JAN-1986 4: 01-JAN-2001 PL/SQL procedure successfully completed. SQL> SQL> drop table MyDate; Table dropped. SQL> SQL> SQL>

</source>