如何在EXECUTE IMMEDIATE查询中添加表名? [英] How to add a table name in EXECUTE IMMEDIATE query?

问题描述

我有一个关于立即执行"的问题. 我在下一个plsql语句中动态更改了表名

I have one question about "EXECUTE IMMEDIATE". I have dynamicly changed table name in next plsql statement

DECLARE

TYPE CurTyp IS REF CURSOR;
cur CurTyp;
str1 VARCHAR2(30);
str2 VARCHAR2(30);
table_name  VARCHAR2(30);

BEGIN

select data
  into table_name
  from ref
 where o_id = 111
   and a_id = 222;



OPEN cur FOR
'select  name, sname  from :1 b,myobjects a where a.obj_id = b.obj_id'
USING table_name;
LOOP
FETCH cur INTO str1, str2;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(str1||str2);
END LOOP;
CLOSE cur;
END

是否可以将下一个立即执行查询的结果读取到光标?

Is it possible to read the result of next Execute Immediate query to cursor?

'select  name, sname  from :1 b,myobjects a where a.obj_id = b.obj_id'
    USING table_name;

或者也许有什么方法可以做到这一点?

Or maybe is there any way to do this?

谢谢.

推荐答案

您可以使用ref_cursor 参见 http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

You can use ref_cursor see http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

示例:

CREATE OR REPLACE PROCEDURE query_invoice(
       month VARCHAR2, 
       year VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(200);
    inv_num NUMBER;
    inv_cust VARCHAR2(20);
    inv_amt NUMBER;
BEGIN
    query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year 
      || ' WHERE invnum = :id';
    OPEN c FOR query_str USING inv_num;
    LOOP
        FETCH c INTO inv_num, inv_cust, inv_amt;
        EXIT WHEN c%NOTFOUND;
        -- process row here
    END LOOP;
    CLOSE c;
END;
/

但是正如@jonearles所说，您不能将表名作为参数插入

but as @jonearles said, you can't insert the table names as params

这篇关于如何在EXECUTE IMMEDIATE查询中添加表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！