в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:24Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/PL SQL/Table of Date - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия