关于Oracle 动态sql

小小编

2 年前

静态SQL是前置编译绑定，动态SQL是后期执行时才编译绑定。

场景：

动态SQL适用于表名及查询字段名未知的情况。在已知查询字段名及表名的情况下，使用动态SQL(字符串拼接方式)会增加硬解析的开销，在这种情况下，建议使用静态SQL，这样可以提高执行效率。在过程过程用拼凑的动态sql效率并不高。

因此，真实业务下适用动态sql的场景非常少，使用时也必须注意表结构的变动等因素，应该尽量在业务开发中使用动态sql。本人一般使用动态sql做数据采集；

用execute immediate实现动态sql：

----ddl
begin
  execute immediate \'create table l_test(name varchar2(8))\';
end;

带参数：

declare
  ----带参数
  v_name  l_test.name%type;
  v_insql varchar(100);

begin
  v_name  := \'张三\';
  v_insql := \'insert into l_test(name)values(:1)\';
  execute immediate v_insql
    using v_name;
  commit;
end;

返回一行记录：

declare
  ----带参数
  v_name  l_test.name%type;
  v_sql varchar(100);

  v_rec l_test%rowtype;

begin
  v_name := \'张三\';
  v_sql  := \'select * from l_test t where t.name=:v_n\';
  execute immediate v_sql
    into v_rec
    using v_name;
  dbms_output.put_line(v_rec.name);
end;

返回多行记录：

declare
  --v_name l_test.name%type;
  v_sql  varchar(100);

  type t_rec IS TABLE OF l_test%ROWTYPE;
  v_arry_test t_rec;

begin
  v_name := \'张三\';
  v_sql  := \'select * from l_test t\';
  execute immediate v_sql bulk collect
    into v_arry_test;

  for i in 1 .. v_arry_test.count loop
    dbms_output.put_line(v_arry_test(i).name);
  end loop;

end;

结合游标：

declare
  v_name l_test.name%type;
  v_sql  varchar(100);
  type cur_type is ref cursor;
  v_cur cur_type;
  type t_rec IS TABLE OF l_test%ROWTYPE;
  v_arry_test t_rec;

begin
  v_name := \'张三\';
  v_sql  := \'select * from l_test t where name=:v_n\';

  open v_cur for \'\' || v_sql
    using v_name;
  fetch v_cur bulk collect
    into v_arry_test;
  close v_cur;
  for i in 1 .. v_arry_test.count loop
    dbms_output.put_line(v_arry_test(i).name);
  end loop;
end;