Oracle的动态SQL

1、概述

       为了了解动态SQL，需要先明白Oracle对SQL语句的处理过程（请参考《Oracle对SQL语句处理过程》），静态SQL是在解析（Parse）阶段就确定的SQL，而动态的SQL则是在编译阶段不能确定的，只有在关联绑定变量之后才能确定的SQL。为什么要使用动态的SQL？我认为主要有两点：

       ① 增加灵活性。动态SQL可以使用using子句实现变量绑定，可以通过改变绑定变量的值改变语句条件。

       ② 提高查询效率。动态SQL使用绑定变量，可以让SQL在绑定变量不同值的情况下使用相同的执行计划。

2、语法               

        Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：

        1)  动态select语句

Execute immediate '动态Select SQL语句' [bulk collect] into 输出参数列表 using 绑定参数列表;

         或

OPEN 游标名称 FOR '动态sql语句' USING 绑定参数列表;

         如：

declare
   v_name t_student.name%TYPE;
   v_sex t_student.sex%TYPE;
   p_id number;
begin
  p_id := 100003;
  execute immediate 'select name, sex from t_student where gid = :1 ' into v_name, v_sex
      using p_id ; --动态SQL为查询语句
  dbms_output.put_line('编号为：'||p_id||', 学生姓名：'||v_name||', 性别：'||v_sex);
end;

declare
   TYPE student_cur_type IS REF CURSOR;
   p_id number;
   v_name t_student.name%TYPE;
   cur_name student_cur_type;
begin
  p_id := 100003;
  open cur_name for 'select name from t_student where gid = :1' using p_id;
  
  loop
     fetch cur_name into v_name;
     exit when cur_name%NOTFOUND;
     dbms_output.put_line('学生姓名:'||v_name);
  end loop;
  
  close cur_name;
end;

         2) 动态Insert/Update/Delete语句

Execute immediate '动态Insert/Update/Delete SQL语句' using 绑定参数列表 returning [bulk collect] into 输出参数列表;

            如：

declare
   TYPE name_tbl_type IS TABLE OF t_student.name%type INDEX BY BINARY_INTEGER;
   v_name_tbl name_tbl_type;
begin
  execute immediate 'update t_student set name = :1 returning name into :2' USING 'ZHUGE' RETURNING bulk collect INTO v_name_tbl; --动态SQL为查询语句
  commit;
  
  FOR i IN 1..v_name_tbl.count LOOP
     dbms_output.put_line('学生姓名:'||v_name_tbl(i));
  END LOOP;
end;