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_cntnumber:=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.
页:
[1]