1.动态查询数据数据未多行,查询后以pipelined方式输出create type row_type as object(ret_info varchar2(500));--定义行对象
create type table_type as table of row_type; --定义表对象 create or replace function getemp
return
table_type pipelined
as
v row_type;
Info varchar(400);
beginexecute immediate 'CREATE GLOBAL TEMPORARY Table TLST_TMP (
COL1 VARCHAR2(400)
)ON COMMIT PRESERVE ROWS';Info:='查询语句 ';
execute immediate('insert into TLST_TMP '+Info); for my_row in (select COL1 as empno from TLST_TMP ) loop --在这里怎么查询临时表呢
v :=row_type(my_row.empno);
pipe row(v);
end loop;
return ;
end;
create type table_type as table of row_type; --定义表对象 create or replace function getemp
return
table_type pipelined
as
v row_type;
Info varchar(400);
beginexecute immediate 'CREATE GLOBAL TEMPORARY Table TLST_TMP (
COL1 VARCHAR2(400)
)ON COMMIT PRESERVE ROWS';Info:='查询语句 ';
execute immediate('insert into TLST_TMP '+Info); for my_row in (select COL1 as empno from TLST_TMP ) loop --在这里怎么查询临时表呢
v :=row_type(my_row.empno);
pipe row(v);
end loop;
return ;
end;
或者如果还是要在函数中动态创建的话,那么可以使用动态游标declare
cur sys_refcursor;
...
begin...
open cur for 'select COL1 as empno from TLST_TMP';
loop
fetch cur into ...
...
end loop;
close cur;
...
end;