SOS...存储过程中如何调用函数返回的结果集????

[whxxr]

CREATE OR REPLACE PACKAGE ABC
as
type VarTableType is table of varchar2(20000);
function str2varList2( p_string in varchar2 ) return VarTableType;
procedure straddtable(addstr in varchar2);

END ABC;

CREATE OR REPLACE PACKAGE BODY ABC AS
function str2varList2( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || '$';
v_n varchar2(20000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, '$' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end ;

procedure straddtable(addstr in varchar2)
as
s1 varchar2(20000):='';
ss1 varchar2(20000):='';
kjnd varchar2(4):='';
begin
s1:=addstr;
--s1格式:'2008,01,00,aaa$,2008,01,00,bbb$,2008,01,00,ccc'
--在这里如何调用 STR2VARLIST2(S1)的返回结果集呀.怎么取出3条记录
-- 2008,01,00,AAA
--2008,01,00,BBB
--2008,01,00,CCC
--不知道如何写
end straddtable;
END ABC;


因为是写在一个包里,在SQL PLUS中运行出错,不知道什么原因.如果不是写在包里没有问题.

SQL> select str2varlist2('2008,01,00,aaa$2008,01,00,bbb$2008,01,00,ccc') from dual
2 /
STR2VARLIST2('2008,01,00,AAA$2008,01,00,BBB$2008,01,00,CCC')
--------------------------------------------------------------------------------
VARTABLETYPE('2008,01,00,aaa', '2008,01,00,bbb', '2008,01,00,ccc')
SQL> select abc.str2varlist2('2008,01,00,aaa$2008,01,00,bbb$2008,01,00,ccc') from dual
2 /
select abc.str2varlist2('2008,01,00,aaa$2008,01,00,bbb$2008,01,00,ccc') from dual
*
ERROR 位于第 1 行:
ORA-00902: 无效数据类型

[doer_ljy]

CREATE OR REPLACE PACKAGE C20.DOERPAG
IS
TYPE RtnRcd IS RECORD (
A VARCHAR2(100),
B VARCHAR2(100));
TYPE RtnTab IS TABLE OF RtnRcd INDEX BY BINARY_INTEGER;
FUNCTION F1(PM1 IN VARCHAR2) RETURN RtnTab;
PROCEDURE P1;
END;

CREATE OR REPLACE PACKAGE BODY C20.DOERPAG
IS
FUNCTION F1(PM1 IN VARCHAR2) RETURN RtnTab
IS
rst RtnTab;
rcd RtnRcd;
TMP1 VARCHAR2(4000);
PMIN VARCHAR2(4000);
POS NUMBER;
I NUMBER;
BEGIN
I := 0;
PMIN := PM1;
LOOP
EXIT WHEN nvl(LENGTH(PMIN),0) = 0;
POS := INSTR(PMIN,',');
IF POS > 0 THEN
TMP1 := SUBSTR(PMIN,1,POS-1);
PMIN := SUBSTR(PMIN,POS+1);
rcd.A := 'A_' || TMP1;
rcd.B := 'B_' || TMP1;
rst(I) := rcd;
I := I + 1;
ELSE
TMP1 := SUBSTR(PMIN,1);
PMIN := '';
rcd.A := 'A_' || TMP1;
rcd.B := 'B_' || TMP1;
rst(I) := rcd;
I := I + 1;
END IF;
END LOOP;
return rst;
END;
PROCEDURE P1 IS
rst RtnTab;
pm varchar2(1000);
BEGIN
pm := '111,222,333,444';
rst := F1(pm);
dbms_output.put_line(rst.count);
END;
END;