oracle数组sql查询,Oracle数组使用以及PLSQL操作记录

----------------------------固定长度数组

declare

type intarray is varray(30) of varchar2(100);

var_arr intarray := intarray('a','b','c','d');

begin

for i in 1..var_arr.count

loop

dbms_output.put_line(var_arr(i));

end loop;

end;

---------------------------未知长度数组

declare

type vararray is table of varchar2(50) index by binary_integer;

var_arr vararray;

begin

for i in 1..15 loop

var_arr(i) := i+10;

end loop;

dbms_output.put_line('Count: '|| var_arr.count);

dbms_output.put_line(var_arr(11));

end;

------------------------自定义1

declare

var_phone varchar2(100);

idex   int;

nextIdex int;

iphone varchar2(100);

begin

var_phone := '10000,10001,10002,10003,10004';

dbms_output.put_line(var_phone);

idex := 0;

nextIdex := 1;

while nextIdex > 0 loop

nextIdex := instr(var_phone, ',', idex+1);

dbms_output.put_line(idex || ' --- ' || nextIdex);

if nextIdex > 0 then

iphone := substr(var_phone, idex+1, nextIdex-idex-1);

else

iphone := substr(var_phone, idex+1);

end if;

idex := nextIdex;

dbms_output.put_line(iphone);

end loop;

end;

-------------------  自定义2  使用数组

declare

var_phone varchar2(100) := '1000';--'10000,10001,10002,10003,10004';

idex   int;

nextIdex int;

type charArray is table of varchar2(100) index by binary_integer;

phone_arr charArray;

arrCount int := 1;

begin

dbms_output.put_line(var_phone);

idex := 0;

nextIdex := 1;

while nextIdex > 0 loop

nextIdex := instr(var_phone, ',', idex+1);

dbms_output.put_line(idex || ' --- ' || nextIdex);

if nextIdex > 0 then

phone_arr(arrCount) := substr(var_phone, idex+1, nextIdex-idex-1);

arrCount := arrCount + 1;

else

phone_arr(arrCount) := substr(var_phone, idex+1);

end if;

idex := nextIdex;

--dbms_output.put_line();

end loop;

for i in 1..phone_arr.count loop

dbms_output.put_line(phone_arr(i));

end loop;

end;

------------------------------------------&gt>

转载于:http://johnny-gz.iteye.com/blog/1751754