oracle动态sql

使用execute immediate语句可以处理包括ddl（create、alter和drop）、DCL（grant、revoke）、DML（insert、update、delete）以及单行select语句。

execute immediate语句：
      execute immediate dynamic_string
      [into {define_variable[,define_variable]…|record}]
      [using [in|out|in out] bind_argument[,[in|out|in out]bind_argument]…]
      [{returning|return} into bind_argument[, bind_argument]…]

Define_variable用于指定存放单行查询结果的变量；

using inbind_argument用于指定存放传递给动态sql值的变量，即在dynamic中存在占位符时使用；

using outbind_argument用于指定存放动态sql返回值的变量。

示例：使用executeimmediate执行简单ddl语句
    begin

execute immediate 'create table ma_org(org_codevarchar2(20),org_name varchar2(254))';
       execute immediate 'drop table ma_org';
    end;

实例：使用executeimmediate执行dcl语句
    begin
       execute immediate 'grant insert on ma_org to scott'
    end;

示例：使用execute immediate处理dml语句。

如果dml语句包含占位符，那么在execute immediate语句之后必须要带有using子句；如果dml语句带有returning子句，那么在execute immediate语句之后必须带有returning into子句，并且此时只能处理作用的单行上的dml语句，如果dml语句作用在多行上，则必须使用bulk子句。

示例：

declare
         orgcode varchar2(10);
         orgname varchar2(254);
    begin
         orgcode := 1200;
         execute immediate 'select org_name fromma_org 
         where org_code = :X'
         into orgname
         using orgcode; 
         dbms_output.put_line(orgname);
    end;

示例：

declare
         orgcode varchar2(10);
         orgname varchar2(254);
         rname varchar2(254);
    begin
         orgcode := '1200';
         orgname := '天津市分行';
         execute immediate 'update ma_org set org_name=:X 
         where org_code = :Y returning org_name into :rname'
         using orgname, orgcode
         returning into rname; 
         dbms_output.put_line(orgname);
    end;

使用动态游标处理多行查询类动态sql语句。

示例：

declare
         type myrefcursor is ref cursor;
         ref cursor my ref cursor;
         rec_ma_org ma_org%rowtype;
    begin
         open ref cursor for select * from ma_org;
              loop
                  fetch ref cursor into rec_ma_org;
                   exit when ref cursor%not found;    
                  dbms_output.put_line(rec_ma_org.org_code||','||rec_ma_org.org_name);
               endloop;
         close refcursor;
    end;

oracle通过使用bulk collect into子句处理动态sql中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用bulk子句时，集合类型可以是plsql所支持的索引表、嵌套表和varray，但集合元素必须使用sql数据类型。在oracle9i以后，有三种语句支持bulk子句，execute immediate，fetch和forall。

在execute immediate中使用bulk collect into处理多行查询返回结果:

示例：

declare
         type org_table_type is table of ma_org%rowtype;
         org_table org_table_type;
         v_orgcode varchar2(20);
    begin
         v_orgcode := '%00%';
         execute immediate 'select * from ma_org where org_code like:v_orgcode' bulk collect into org_table
         using v_orgcode;
         for i in 1..org_table.count
             loop    
                dbms_output.put_line(org_table(i).org_code||','||org_table(i).org_name);
             end loop; 
    end;

在forall语句中使用bulkinto语句。

示例：

declare
         type type_org_code is table of ma_org.org_code%type;
         type type_org_name is table of ma_org.org_name%type;
         v_orgcode type_org_code;
         v_orgname type_org_name;
    begin
         v_orgcode := type_org_code('1100','1200'); 
         forall i in 1..v_orgcode.count
            execute immediate 'update ma_org set org_name = org_code||org_namewhere org_code = :p1 returning org_name into :p2'
            using v_orgcode(i)
            returning bulk collect into v_orgname; 
         for i in v_orgname.first..v_orgname.last
            loop
               dbms_output.put_line(v_orgname(i));
         end loop;
    end;