EXECUTE IMMEDIATE总结

3legcat 发表于 2011-12-22 08:54

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

页: [1]

Chinaunix's Archiver

EXECUTE IMMEDIATE总结