EXECUTE IMMEDIATE总结

3legcat

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:
    
12. 
    
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>