[大数据]Oracle数据库之动态SQL（七）

1、动态SQL概述

• 在编译时就是确定的完整SQL语句，叫做静态SQL；在运行时才确定的完整的SQL语句，叫做动态SQL。

• 早期绑定与晚期绑定：早期绑定是在编译时完成绑定，编译时间长，执行时间短；晚期绑定是在运行时完成绑定，编译时间短，运行时间长。

• PL/SQL块中为什么不能直接执行DDL或DCL语句？因为DDL或DCL语句在执行时，需要验证用户操作权限

• Oracle中有两种动态SQL技术：使用DBMS_SQL包和本地动态SQL。

  1. 在Oracle 8i以前，只能使用DBMS_SQL包来执行动态SQL。
  2. 在Oracle 8i之后，可以使用本地动态SQL来执行动态SQL，通过本地动态SQL，可以直接将动态SQL语句放在PL/SQL中运行，与DBMS_SQL包相比，本地动态SQL更简单、运行速度更快、性能更高。

• 注：虽然动态SQL技术很灵活，但是会影响性能，我们只能把它用在静态SQL不可能完成任务的情况下。我们在使用本地动态SQL时，最好是将绑定变量技术与本地动态SQL技术结合使用，这样可以提高性能。

2、绑定变量

• 硬解析：语句格式会变化，每次都要重新进行编译，占用的资源就多。

• 软解析：在语句中采用绑定变量方式，第一次解析后，存储在SGA缓存中，其它后续次不需要再进行对语句进行编译，占用的资源就少。

• 绑定变量：是在SQL语句中的变量(或参数)，它在运行时会被有效的字面值替换(或绑定)，以保证语句能够成功执行。主要就是用来将相似的SQL语句成为一个SQL语句，避免了硬解析的重复执行，提高了性能和并发访问量。

• 使用绑定变量：

  1. 在SQL*Plus中使用绑定变量：
     ?

  SQL> variable deptno number
  SQL> exec :deptno := 10
  SQL> select * from emp where deptno = :deptno;
  

  2. 在PL/SQL中使用绑定变量：
     ?

  create or replace procedure dsal(p_empno in number)
  as
  begin
  	update emp set sal=sal*2 where empno = p_empno;
  	commit;
  end;
  -- 注：在PL/SQL的存储过程和函数中，每个对PL/SQL变量的引用实际上都是绑定变量
  

  3. 在动态SQL中使用绑定变量：不能用绑定变量替换实际的数据库对象名，只能替换字面量。
     ?

  -- 测试使用绑定变量的效率是否比不使用绑定变量的效率高
  -- 1、不使用绑定变量
  declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time; --  dbms_utility.get_time：得到开始时间
  begin
      for i in 1..1000 loop
          open l_rc for 'select object_name from all_objects where object_id='||i;
          fetch l_rc into l_dummy;
          close l_rc;
      end loop;
      -- 用当前时间-开始时间得到过程时间
      dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'秒...'); -- 约38秒
  end;
  -- 2、使用绑定变量
  declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1..1000 loop
          open l_rc for 'select object_name from all_objects where object_id=:x' using i;
          fetch l_rc into l_dummy;
          close l_rc;
      end loop;
      dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'秒...'); -- 约0.15秒
  end;
  

  4. Java应用程序中的绑定变量：JDBC中的PreparedStatement允许我们使用两种方式传递SQL语句，一个是用绑定变量，一个是使用字符串连接方式的Statement。
     ?

  -- 使用绑定变量方式的语法（仅供参考）：
  增加：insert into emp values(?,?,?,?);
  查询：select * from emp where empno=?;
  ...
  

• 不适合使用绑定变量的情况：隔一段时间才执行一次的SQL语句；数据仓库的情况下；在对建有索引的字段，且字段的集非常大时。

3、实现本地动态SQL的语句

• 本地动态SQL是通过execute immediate来立即执行指定的SQL语句，以及增强已有的open for语句来执行多行查询来实现的。

• execute immediate语句使用示例：
  ?

  -- 语法（仅供参考）
  execute immediate dynamic_string [into ...] [using ...] [returning|return into ...];
  -- 注：本地动态SQL支持所有的SQL数据类型，不能在using子句绑定PL/SQL特定的数据类型，into子句可以包含PL/SQL记录。
  

  declare
      str varchar2(4000);
  begin
      str := 'delete from emp1 where empno=7788';
      execute immediate str;
  exception
      when others then
          null;
  end;
  

• open for语句使用示例：
  ?

  open {cursor_variable | :host_cursor_variable} for dynamic_string
  	[using bind_argument[,bind_argument]...];
  -- 注：一旦用该语句打开了查询，那么提取行、关闭游标变量、检测游标属性的语法就与静态游标变量一样了。using子句中的绑定参数只在游标打开时才被赋值。
  

  -- 使用open语句打开一个动态查询
  declare
      parts_table varchar2(20);
      where_in varchar2(2000);
      type query_curtype is ref cursor;
      dyncur query_curtype;
  begin
      open dyncur for 'select * from'||&parts_table||'where'||&where_in;
  end;
  

4、用本地动态SQL技术执行动态SQL的方法

1. 无绑定变量的非SQL查询语句：
   ?

   -- 使用本地动态SQL技术执行动态DDL语句的方法：
   begin
       execute immediate 'create index emp_ind_1 on emp(sal,hiredate)';
   end;
   
   -- 使用本地动态SQL技术执行不带绑定变量的动态update语句的方法：
   declare
       v_sqlstring varchar2(100);
   begin
       v_sqlstring := 'update emp set sal=999 where empno=7788';
       execute immediate v_sqlstring;
   end;
   

2. 带固定数目绑定变量的非SQL查询语句：
   ?

   -- 将上例的DML语句更改为带绑定参数的动态SQL，然后用本地动态SQL技术执行
   declare
       v_sqlstring varchar2(100);
   begin
       v_sqlstring := 'update emp set sal=:salary where empno=:eno';
       execute immediate v_sqlstring using 999,7788;
   end;
   
   -- 使用动态的update语句来更新某位员工的薪水，同时返回更新后的薪水信息。
   declare
       v_sqlstring varchar2(100);
       v_sql emp.sal%type;
       v_ename emp.ename%type;
   begin
       v_sqlstring := 'update emp set sal=999 where empno=7777 returning ename,sal into :1,:2';
       execute immediate v_sqlstring returning into v_ename,v_sal;
       dbms_output.put_line('员工'||v_ename||'的薪水为：'||v_sal);
   end;
   

3. 带固定数目列和绑定变量的SQL查询语句：
   ?

   -- 演示对有固定数目的列和绑定变量，并且返回单行数据的select查询语句
   declare
       v_sqlstring varchar2(100);
       v_ename emp.ename%type;
       v_sal emp.sal%type;
   begin
       v_sqlstring := 'select ename,sal from emp where empno=:eno';
       execute immediate v_sqlstring into v_ename,v_sal using 7788;
       dbms_output.put_line('雇员'||v_ename||'的工资为'||v_sal);
   end;
   

    -- 演示有固定数目的列和绑定变量，并且返回多行数据的select查询语句情况下，用带动态字符串的open语句执行动态SQL的方法
      declare
          type emp_cur is ref cursor;
          l_emp_cur emp_cur;
          l_emp_rec emp%rowtype;
      begin
          open l_emp_cur for 'select * from emp where deptno = :x' using 30;
          loop
              fetch l_emp_cur into l_emp_rec;
              exit when l_emp_cur%notfound;
              dbms_output.put_line(l_emp_rec.ename);
          end loop;
      end;
   
      -- 演示有固定数目的列和绑定变量，并且返回多行数据的select查询语句情况下，
      -- 用带using和bulk collect into子句的execute immediate语句执行动态SQL的方法
      declare
          type emp_table_type is table of emp%rowtype index by binary_integer;
          emp_table emp_table_type;
      begin
          execute immediate 'select * from emp where deptno = :x' bulk collect into emp_table using 30;
          for i in 1..emp_table.count loop
              dbms_output.put_line(emp_table(i).ename);
          end loop;
      end;
   

4. 列数目和绑定变量数据不确定的SQL查询语句：
   ?

   -- 该情况相对很少，不推荐使用哦
   open l_cursor for 'select'||l_column_list||'from emp';