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--返回父项
