plsql开发动态sql执行没有效果的问题

Question

我写的测试代码如下：CREATE TABLE testlkkj AS SELECT * FROM All_Tables;
SELECT * FROM testlkkj WHERE table_name = 'MGMT_PDP_METADATA';
CREATE OR REPLACE PROCEDURE proc_test001
AS
       sql_tx VARCHAR2(3000);
       tb_name VARCHAR2(200) := 'MGMT_PDP_METADATA';
BEGIN
  sql_tx :=  --'delete from testlkkj where table_name = :v1';
            'insert into testlkkj select * from all_tables where table_name = :v2';
  EXECUTE IMMEDIATE sql_tx USING tb_name;
  IF SQL%NOTFOUND OR SQL%NOTFOUND IS NULL 
    THEN dbms_output.put_line('没有语句被执行');
  END IF;
  COMMIT;
END proc_test001;BEGIN 
  proc_test001;
END;预想中，执行之后testlkkj表中会新增一条记录，但执行后，打印出“没有语句被执行”，查看testlkkj表中的记录，确实没有新增。但当将sql_tx变成上面注释掉的delete语句后，执行后testlkkj确实删除了一条数据。求问，这个存储过程中究竟存在什么问题，使insert语句未能成功执行但delete语句又能正常执行。

Answer 1

insert into testlkkj .....
是不是少个关键字values

Answer 2

不是~ insert into tb1 select * from tb2 这个不需要values啊

Answer 3

动态sql的权限相关问题，我在我本地测试了你的sql，因为你执行这个存储过程的用户，没有查询 MGMT_PDP_METADATA 这张表的权限。   我再本地授权了一下，执行，就有记录了。

Answer 4

  sql_tx :=     'insert into testlkkj select * from all_tables where table_name ='''||:v2||''';:v2是传入变量么？ 需要放外面，你可以把动态执行语句打印出来看看到底执行的什么语句。

Answer 5

sql_tx :=     'insert into testlkkj select * from all_tables where table_name ='||:v2;

Answer 6

动态语句访问 all_objects 类对象时，必须显式的授权；

Answer 7

 我在相同的用户下，执行如下语句：
DECLARE tb_name VARCHAR(20) := 'MGMT_PDP_METADATA';
        sql_tx VARCHAR2(1000);
BEGIN 
  sql_tx := 'insert into testlkkj select * from all_tables where table_name = :1';
  --EXECUTE IMMEDIATE 'insert into testlkkj select * from all_tables where table_name = :1' USING tb_name;
  EXECUTE IMMEDIATE sql_tx USING tb_name;  
  IF SQL%NOTFOUND OR SQL%NOTFOUND IS NULL THEN
    dbms_output.put_line('没有语句被执行');
  END IF;
  COMMIT;
END;
能够成功执行的啊。  同样都是动态sql ，一个是存储过程 一个不是，就会产生这样的差别吗

Answer 8

--在10G测试了一下，没问题SQL> select * from  testlkkj;未选定行SQL> CREATE OR REPLACE PROCEDURE proc_test001
  2  AS
  3         sql_tx VARCHAR2(3000);
  4         tb_name VARCHAR2(200) := 'EMP';
  5  BEGIN
  6    sql_tx :=  --'delete from testlkkj where table_name = :v1';
  7              'insert into testlkkj select * from all_tables where table_name = :v2';
  8    EXECUTE IMMEDIATE sql_tx USING tb_name;
  9    IF SQL%NOTFOUND OR SQL%NOTFOUND IS NULL
 10      THEN dbms_output.put_line('没有语句被执行');
 11    END IF;
 12    COMMIT;
 13  END proc_test001;
 14  /过程已创建。SQL>  select * from  testlkkj;未选定行SQL> BEGIN
  2    proc_test001;
  3  END;
  4  /PL/SQL 过程已成功完成。SQL> select * from  testlkkj;OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------ -------------
SCOTT                          EMP                            SYCH_NEW

Answer 9

楼主，编译一下你的存储过程能不能通过先。或者能不能试试将
END proc_test001;
改成
END;
/

Answer 10

他是动态的 SQL ，如果没有其他语法问题，是可以编译通过的， 如果把这个 insert 语句拿出来，极有可能编译不过去。