Using Collections
===================
Simple Example with VARRAY
create or replace type colors as varray (10) of varchar2(10);
set serveroutput on size 1000000;
DECLARE
rainbow colors;
BEGIN
rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');
for i in rainbow.first..rainbow.last loop
DBMS_OUTPUT.put_line(rainbow(i));
end loop;
END;
/
create table my_rainbows_per_day
(
r_date number(8),
rainbow colors
);
---INSERT DATA---
insert into my_rainbows_per_day values ( '20111024',Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet'));
insert into my_rainbows_per_day values ( '20111025',Colors('Red','Orange','Yellow','Green'));
insert into my_rainbows_per_day values ( '20111026',Colors('Red','Orange','Yellow','Green'));
--- Read data using first and last methods
set serveroutput on size 1000000
DECLARE
cursor c is
select r_date ,rainbow
from MY_RAINBOWS_PER_DAY;
c_r c%rowtype;
BEGIN
FOR c_r IN c LOOP
DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);
for i in c_r.rainbow.first..c_r.rainbow.last loop
DBMS_OUTPUT.put_line(c_r.rainbow(i));
end loop;
END LOOP;
END;
/
--- Read data using first and next methods
set serveroutput on size 1000000
DECLARE
cursor c is
select r_date ,rainbow
from MY_RAINBOWS_PER_DAY;
c_r c%rowtype;
i integer;
BEGIN
FOR c_r IN c LOOP
DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);
i:=c_r.rainbow.first;
while i is not null loop
DBMS_OUTPUT.put_line(c_r.rainbow(i));
i:=c_r.rainbow.next(i);
end loop;
END LOOP;
END;
/
---- Read data using the TABLE expression
select * from TABLE( select r.rainbow from my_rainbows_per_day r where r.r_date=20111024);
---------------------------------------------------------------------------------------------
