Chinaunix

标题: EXECUTE IMMEDIATE总结 [打印本页]
作者: 3legcat    时间: 2011-12-22 08:54
标题: EXECUTE IMMEDIATE总结
oracle的execute immediate可以动态执行sql语句。下面总结一下这一语句的用法：

1. execute immediate不会自动提交DML事务执行，需要显式提交或者作为execute immediate语句的一部分。（参考如下代码）
  1. SQL> create table PEOPLE_T(
  2.   2 id number,
  3.   3 name varchar(50)
  4.   4 );
  5.  
  6. Table created
  7. SQL> set serveroutput on;
  8. SQL> declare
  9.   2 cnt integer;
  10.   3 begin
  11.   4 execute immediate 'insert into people_t values(1, ''zhangsan'')';
  12.   5 rollback;
  13.   6 select count(*) into cnt from people_t;
  14.   7 dbms_output.put_line(cnt);
  15.   8 end;
  16.   9 /
  17.  
  18. 0
  19.  
  20. PL/SQL procedure successfully completed
  21.  
  22. SQL>
上面的代码和结果可以证明，execute immediate并不自动提交DML操作。下面的代码中，我们手动提交：
  1. SQL> declare
  2.   2 cnt integer;
  3.   3 begin
  4.   4 execute immediate 'insert into people_t values (2, ''Lisi'')';
  5.   5 commit;
  6.   6 select count(*) into cnt from people_t;
  7.   7 dbms_output.put_line(cnt || ' records after commit');
  8.   8 end;
  9.   9 /
  10.  
  11. 1 records after commit
  12.  
  13. PL/SQL procedure successfully completed
可以看出手动提交后，DML语句的记录才插进表里。当然也可以用下面的方式动态执行提交语句，但是与手动提交没有太大的实际意义：
  1. SQL> declare
  2.   2 cnt integer;
  3.   3 begin
  4.   4 execute immediate 'insert into people_t values (3, ''Wangwu'')';
  5.   5 execute immediate 'commit';
  6.   6 select count(*) into cnt from people_t;
  7.   7 dbms_output.put_line(cnt || ' records after commit');
  8.   8 end;
  9.   9 /
  10.  
  11. 2 records after commit
  12.  
  13. PL/SQL procedure successfully completed
  14.  
  15. SQL>
2. 当执行sql语句时，结尾不需要分号；当执行pl/sql块时，需要用分号结尾。
  
   下面的代码展示了错误的语法：
  1. SQL> begin
  2.   2 execute immediate 'insert into people_t values (1, ''Zhangsan'');';
  3.   3 end;
  4.   4 /
  5.  
  6. begin
  7.   execute immediate 'insert into people_t values (1, ''Zhangsan'');';
  8. end;
  9.  
  10. ORA-00911: invalid character
  11. ORA-06512: at line 3
  12.  
  13. SQL>
  1. SQL> begin
  2.   2 execute immediate 'begin insert into people_t values (1, ''Zhangsan''); end';
  3.   3 end;
  4.   4 /
  5.  
  6. begin
  7.   execute immediate 'begin insert into people_t values (1, ''Zhangsan''); end';
  8. end;
  9.  
  10. ORA-06550: line 1, column 54:
  11. PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

  13.    ; <an identifier> <a double-quoted delimited-identifier>
  14. The symbol ";" was substituted for "end-of-file" to continue.
  15. ORA-06512: at line 3
  16.  
  17. SQL>






欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2