Storing and retreiving a nested table with non-sequential keys : Table of Date « PL SQL « Oracle PL / SQL






Storing and retreiving a nested table with non-sequential keys

 
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>

 








Related examples in the same category