EXECUTE IMMEDIATE在plsql中 [英] EXECUTE IMMEDIATE in plsql

问题描述

如何从此程式码取得结果

  EXECUTE IMMEDIATE'SELECT * FROM'|| table_name 
  

通过 for循环

通常的方法如下

 用于（select * from this_table）
 loop 
 htp.p（items.id）; 
 end loop; 
  

解决方案

如果您到 select * from 动态表名，那么我可能会使用 dbms_sql / p>

输入记录：

 创建类型tq84_varchar2_tab作为varchar2（4000）的表; 
 / 
  

输入结果集数组）：

 创建类型tq84_varchar2_tab_tab作为tq84_varchar2_tab的表; 
 / 
  

执行select并返回结果的实例的函数设置：

 创建或替换函数tq84_select_star_from_table（varchar2中的table_name）
 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那么
 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）; 
 
执行立即stmt_txt 
批量收集到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; 
 / 
  

How to get a result from this code

EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name

through for loop

The usual method looks like this

for items in (select * from this_table)
loop
htp.p(items.id);
end loop;

解决方案

If you really need to select * from dynamic table name, then I'd probably go with dbms_sql

Type for a record:

create type tq84_varchar2_tab as table of varchar2(4000);
/

Type for a result set (which is an array of records):

create type tq84_varchar2_tab_tab as table of tq84_varchar2_tab;
/

The function that does the select and returns an instance of the result set:

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;

The function can then be used with something like:

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;
/

这篇关于EXECUTE IMMEDIATE在plsql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！