oracle设置返回值类型,Oracle创建返回值类型是TABLE函数报错，sql server下正确

Oracle创建返回值类型是TABLE函数报错，sql server下正确

CREATE OR REPLACE FUNCTION GET_GS_PORC (dxm_n varchar2,PorC varchar2,bhbc varchar2)

RETURN TABLE( PK varchar2(100)) IS

TreeTable TABLE( PK varchar2(100));

--len NUMBER;

i NUMBER;

cc NUMBER;

dxm_n_new varchar2(100);

begin

if PorC= '1 ' then--返回父项

cc:=length(dxm_n)/3;--层次

if bhbc= '1 ' then

i:=cc;

else

i:=cc - 1;

end if;

while i> =0 LOOP

dxm_n_new:=substr(dxm_n,1,3*i);

INSERT into TreeTable

SELECT gs_id from jh_gs_m where dxm_n=dxm_n_new;

i:=i - 1;

end loop;

else --返回子项

if bhbc= '1 ' then

INSERT into TreeTable

SELECT gs_id from jh_gs_m where dxm_n like dxm_n|| '% ';

else

INSERT into TreeTable

SELECT gs_id from jh_gs_m where dxm_n like dxm_n|| '% ' and dxm_n <> dxm_n;

end if;

end if;

RETURN TreeTable;

目前编译就在第二行报错，哪位高人指点一下。

------解决方法--------------------------------------------------------

改了一下:

CREATE OR REPLACE TYPE obj_tbl AS OBJECT( PK varchar2(100));

/

CREATE OR REPLACE TYPE tbl_Ref AS TABLE OF obj_tbl;

/

CREATE OR REPLACE FUNCTION GET_GS_PORC (dxm_n varchar2,PorC varchar2,bhbc varchar2)

RETURN tbl_Ref IS

TreeTable tbl_Ref := tbl_Ref();

--len NUMBER;

i NUMBER;

cc NUMBER;

dxm_n_new varchar2(100);

begin

if PorC= '1 ' then--返回父项