如何从此代码中获取结果
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name
通过循环
通常的方法看起来像这样
for items in (select * from this_table) loop htp.p(items.id); end loop;
解决方法
如果你真的需要从动态表名中选择*,那么我可能会使用dbms_sql
输入记录：
create type tq84_varchar2_tab as table of varchar2(4000); /
输入结果集(这是一组记录)：
create type tq84_varchar2_tab_tab as table of tq84_varchar2_tab; /
执行select并返回结果集实例的函数：
create or replace function tq84_select_star_from_table(table_name in varchar2)
return tq84_varchar2_tab_tab
as
stmt_txt varchar2(4000);
cur number;
columns_desc dbms_sql.desc_tab;
column_cnt number;
result_set tq84_varchar2_tab_tab;
begin
stmt_txt := 'select * from ' || table_name;
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,stmt_txt,dbms_sql.native);
dbms_sql.describe_columns(cur,column_cnt,columns_desc);
dbms_sql.close_cursor(cur);
stmt_txt := 'select tq84_varchar2_tab(';
for i in 1 .. column_cnt loop
if i != 1 then
stmt_txt := stmt_txt || ',';
end if;
stmt_txt := stmt_txt || columns_desc(i).col_name;
end loop;
stmt_txt := stmt_txt || ') from ' || table_name;
-- dbms_output.put_line(stmt_txt);
execute immediate stmt_txt
bulk collect into result_set;
return result_set;
end tq84_select_star_from_table;
然后可以使用以下功能：
declare
records tq84_varchar2_tab_tab;
begin
records := tq84_select_star_from_table('user_objects');
for i in 1 .. records.count loop
dbms_output.put_line (records(i)(5) || ': ' || records(i)(1));
end loop;
end;
/
