Oracle 支持两种类型 SQL: 静态 SQL 和动态 SQL 。在静态 SQL 中， SQL 语句在编译时就已经全部确定，语法和语义的引用问题也是在编译时全部解析确定。在动态 SQL 中， SQL 语句是由字符串组成的，在运行时动态执行。动态 PL/SQL 也是如此。

 

DBMS_SQL 与本地动态 SQL 比较

1、  动态 SQL 语句最长度限制在 32K 以内。比这个长的语句就只能用 DBMS_SQL 来处理 .

2、  还有很多。。省略

一个最简单的例子 :

SQL>  declare

  2      dyn_tab_name varchar2(20):='t_temp';

  3      dyn_string varchar2(150);

  4   begin

  5     dyn_string:='create table '||dyn_tab_name||' (colA number not null)';

  6     execute immediate dyn_string;

  7  end;

  8  /

使用 DBMS_SQL 的过程包括以下五个步骤

1、    以字符串的形式构造动态 SQL 语句

2、    专用明游标句柄并为该句柄打开游标

3、    解板动态构造的 SQL 语句，把该 SQL 语句与游标句柄关联。

4、    执行 SQL 语句

5、    关闭游标

如

SQL> declare

  2          dyn_tab_name varchar2(20):='t_temp';

  3          dyn_string varchar2(150);

  4          c   integer;

  5          ret_code integer;

  6      begin

  7        dyn_string:='create table '||dyn_tab_name||'( colA number not null)';

  8        c:=dbms_sql.open_cursor;

  9       dbms_sql.parse(c,dyn_string,dbms_sql.v7);

 10       ret_code:=dbms_sql.execute(c);

 11       dbms_sql.close_cursor(c);

 12     end;

 13  /

 

本地动态 SQL 语句

关于动态 DDL 、 DML 和会话控制的本地动态 SQL 语句如下所示

1、  Execute immediate <dyn_string>

2、  Execute immediate <dum_string> using < 参数 >

3、  Execute immediate <dyn_string>into <index-by 、 array 、嵌套表 >using< 参数 >

4、  Open cursor for <dyn_string>

5、  Open cursor form <dyn_string>  using < 参数 >

6、  Close <cursor>

7、  Execute immediate ‘begin…..end’ using < 参数 >

动态 DDL

动态 DDL 处理的是使用运行时为数据库对象提供的值的 DDL 语句。动态 DDL 一个典型的用法就是，创建仅在运行时表名和列名才可用的一个动态表。可以使用以下 DDL 语句 :

1 、 Execute immediate <dyn_string>

2 、 Execute immediate <dum_string> using < 参数 >

 

一个使用动态 DDL 创建表的存储过程

create or replace procedure dyn_ddl_demo(tablename varchar2,errnum  outnumber,errtext outvarchar2)
authidcurrent_user
is
  dyn_string varchar2( 1000 );
  dyn_tablename varchar2( 30 );
begin
  dyn_tablename:=tablename;
  dyn_string:= 'create table ' ||dyn_tablename||
  '(order_id number(10) primary key,
    order_date date not null,
    total_qty number,
    total_price number(15,2)
   )' ;
   dbms_output.put_line( 'dyn_string--->' ||dyn_string);
   executeimmediate dyn_string;
   errnum:= 0 ;
exception
   whenothersthen
      errnum:=sqlcode;
      errtext:= 'ERR: Create table ' ||dyn_tablename|| '---' ||sqlerrm;
end dyn_ddl_demo;

 

动态 SELECTS

查询可以返回一行记录或多行记录。多行记录的在《本地批绑定》里再介绍

单行 Selects

Execute immediate <dyn_string> into < 变量 > using < 参数 >

如 :

create or replace procedure dyn_selects(
vid number,
errnum outnumber,
errtext outvarchar2)
is
  sql_string varchar2( 500 );
  id number;
  uname varchar2( 200 );
  sex varchar2( 2 );
 
  upd_string varchar2( 500 );
  vname varchar2( 200 );
  vsex varchar2( 2 );
  vvid number;
begin
  sql_string := 'select * from t_user where id=:vid' ;
  executeimmediate sql_string into id,uname,sex using vid;
  dbms_output.put_line(id|| '----' ||uname|| '----' ||sex);
 
  upd_string:= 'update t_user set name=:vname,sex=:vsex where id=:vvid' ;
  vname:= ' 傻瓜 ' ;
  vsex:= 'xx' ;
  vvid:= 3 ;
 
  executeimmediate upd_string using vname,vsex,vvid;
  errnum:= 0 ;
exception
  whenothersthen
     errnum:=sqlcode;
     errtext:=sqlerrm;
end dyn_selects;

动态 INSERT 、 UPDATE 和 DELETE

除了 DDL 语句和 DML 的单 SELECT 语句以外，还能动态地构造 INSERT 、 UPDATE 和 DELETE 语句并在运行时执行它们。可以使用以下语句执行 insert,update,delete 语句

       Execute immediate <dyn_string>

    Execute immediate <dyn_string> into < 变量 > using < 参数 >