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);
beginif 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;目前编译就在第二行报错,哪位高人指点一下。
RETURN TABLE( PK varchar2(100)) IS TreeTable TABLE( PK varchar2(100));
--len NUMBER;
i NUMBER;
cc NUMBER;
dxm_n_new varchar2(100);
beginif 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 type obj_emp as object(empno number(4),ename varchar2(10));
/
create type tab_emp as table of obj_emp;
/
create or replace function f_return_table
return tab_emp
as
l_tab_emp tab_emp := tab_emp();
begin
select obj_emp(empno,ename) bulk collect into l_tab_emp from emp;
return l_tab_emp;
end;
/
select * from table(cast(f_return_table() as tab_emp));
/
这样不对,应该要先定义一个table类型
/
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);
beginif 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;
Error: PLS-00103: 出现符号 "CREATE"
Line: 3
Text: CREATE OR REPLACE TYPE tbl_Ref AS TABLE OF obj_tbl;
两个
CREATE OR REPLACE TYPE obj_tbl AS OBJECT( PK varchar2(100));
/
CREATE OR REPLACE TYPE tbl_Ref AS TABLE OF obj_tbl;
/
我这边执行都没有问题你把它们分开一个一个执行
drop type obj_tbl;
/
再执行
CREATE TYPE obj_tbl AS OBJECT( PK varchar2(100));
/接着
drop type tbl_Ref;
/
再执行
CREATE OR REPLACE TYPE tbl_Ref AS TABLE OF obj_tbl;
/