Oracle中使用Execute immediate语句和using关键字实现动态SQL

前言：

日常在使用Oracle数据库编写Procedure时会遇到需要执行动态SQL的场景，类似于Java中的形参。这时就需要用到Execute immediate语句，他能帮我们很好的执行动态SQL语句，但是性能方面会比静态SQL稍弱一些。

知识点总结：

• returning子句只能用于insert、update、delete。
• insert、update 是提取 操作后 的数据，delete 是提取 操作前 的数据。
• returning into 在 动态sql内部和外面都要写，内部变量需要加上标识符：，外面不用，且外面的 returning 后面不加字段直接 into。
• returning 不支持 record

语法：

Execute immediate 动态SQL语句 into 出参1，出参2.. using 形参1，形参2.. 
Execute immediate 动态SQL语句 using 形参1，形参2.. returning into 出参1，出参2..

创建测试环境：

create table test1 (id number(2));
create table test1 (id number(2));
--随机生成数字
begin
  for i in 1..50 loop
    insert into test1 values(round(dbms_random.value()*10));
  end loop;
  commit;
end;

begin
  for i in 1..100 loop
    insert into test2 values(round(dbms_random.value()*10));
  end loop;
  commit；
end;

示例一：

--目标：根据test1表的ID，查询在test2表出现的次数并输出。
declare
  flag number;
begin
  --循环获取test1表的数据
  --可以在SQL后面用||拼接形参，也可以使用 :参数名 作为占位符，类似java中的占位符？
  for test1 in (select distinct id from test1 order by id) loop
    execute immediate 'select count(1) from test2 where id = :1'
    --returning into只能作用于insert，，update，delete上而select则不行，应该用into
      into flag
        --注意占位符和using后的参数绑定是按照前后顺序的
      using test1.id;
    dbms_output.put_line('ID为'||test1.id||'的数量为：'||flag);
  end loop;
end;

输出结果

ID为0的数量为：5
ID为1的数量为：10
ID为2的数量为：11
ID为3的数量为：15
ID为4的数量为：12
ID为5的数量为：7
ID为6的数量为：8
ID为7的数量为：15
ID为8的数量为：9
ID为9的数量为：10
ID为10的数量为：5

示例二：

--目标：根据传入的ID删除test2表的数并输出删除条数。
declare
  type v_table_id is table of test1.id%type;
  v_id v_table_id;
  flag number := 1;
begin
  execute immediate 'delete test1 where id =' || flag ||
                    'returning id into :v_id'
    returning bulk collect into v_id;
    rollback;
    dbms_output.put_line('总删除数：'||v_id.count);
    for i in 1..v_id.count loop
      dbms_output.put_line('删除值'||i||'：'||v_id(i));
      end loop;
end;