oracle 数组的用法,oracle存储过程中数组的使用

create or replace package ArrayTestPKG1 is

type tt_type is table of varchar(32) INDEX BY BINARY_INTEGER;  --- 定义数组

type table2 is table of tableA.columnA%type index by binary_integer;

function toArray(Liststr in varchar, V1 out tt_type) return number;

Procedure test;

END ArrayTestPKG1;

/

show errors;

create or replace package body ArrayTestPKG1 is

function toArray(Liststr in varchar, V1 out tt_type) return number is

TmpStr varchar(32);

Str    varchar(4000);

j      number;

begin

Str := Liststr;

j   := 0;

IF Instr(Liststr, ',', 1, 1) = 0 THEN

V1(j) := Liststr;

j := j + 1;

else

While Instr(str, ',', 1, 1) > 0 Loop

TmpStr := Substr(str, 1, Instr(str, ',', 1, 1) - 1);

V1(j) := TmpStr;

str := SubStr(Str, Instr(str, ',', 1, 1) + 1, length(str));

j := j + 1;

end loop;

if not str is null then

--将最后一个保存

V1(j) := str;

j := j + 1;

end if;

end if;

return J;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('sqlerrm:'||sqlerrm);

end;

--计算某一天所有上下文的消费数据

Procedure test Is

t1 tt_type;

v_char varchar(150):='';

v_cnt  number:=0;

Begin

dbms_output.put_line('start!!!');

v_char:='aaaa,bbbb,cccc,dddd,eee,ffff';

v_cnt := toArray(v_char,t1);

dbms_output.put_line('v_char:'||v_char);

dbms_output.put_line('v_cnt:'||v_cnt);

dbms_output.put_line('t1:'||t1(3));

dbms_output.put_line('end!!!');

End;

begin

-- Initialization

Null;

end ArrayTestPKG1;

/

show errors;

SQL> exec ArrayTestPKG1.test;

start!!!

v_char:aaaa,bbbb,cccc,dddd,eee,ffff

v_cnt:6

t1:dddd

end!!!

PL/SQL procedure successfully completed.