ORACLE中的绑定变量

一、绑定变量的作用 :variable_name（字母，数字，或者字母数字组合）有效降低硬解析 二、绑定变量的典型用法sql中var x number;exec :x :=7876; select * from emp where empno = :x; pl/sql中，例如declare  vc_name varchar2(10); begin    execute immediate 'select ename from emp where empno= :1' into vc_name using 7876;    dbms_output.put_line(vc_name); end;所以在pl/sql中绑定变量的标准语法为： execute immediate 【使用绑定变量的语句】  using 对应绑定变量的具体输入值; PL/SQL中DML语句的绑定变量典型用法 declare v_sql1  varchar2(50); v_sql2  varchar2(50); v_num1  number; v_num2  number;begin  v_sql1 := 'insert into emp(empno,ename,sal) values(:1,:1,:1)';  execute immediate v_sql1 using 8001,'jack',1000;  v_num1 :=sql%rowcount;  -- sql%rowcount中的sql是oracle的内部游标,rowcount的意思是之前的dml sql语句影响的多少行数据  execute immediate v_sql1 using 8002,'mike',2000;  v_num2 :=sql%rowcount;  dbms_output.put_line(v_num1+v_num2);  end; 动态sql中也可以使用绑定变量，returning 这个关键字可以和带绑定变量的SQL联用，其作用是将受该SQL影响的行记录字段取出来。 三、pl/sql中批量绑定的典型用法批量绑定：一次性处理一批数据pl/sql引擎是指在Oracle在数据库中处理pl/sql代码中除了SQL语句外所有剩余部分（如变量，循环，数组，赋值）的子系统 批量绑定可以有效减少SQL引擎和pl/sql引擎的交互次数 理论上来说，pl/sql中只要执行SQL语句，就会有这两个引擎的交互，实际上这两个引擎交互产生的性能影响主要体现在以下两个方面 1、当显式游标或者参考游标需要循环执行fetch操作时，这时循环由pl/sql执行，fetch中的SQL由SQL引擎执行，这样每fetch一条记录，就需要两个引擎交互一次2、 当显式游标或者参考游标的循环内部需要执行SQL语句，跟上面一样，也是每执行一次，需要交互一次 所以当fetch一批记录，或者一次执行一批SQL，就会大大提高pl/sql的效率 批量fetch对应的语法 fetch cursorname bulk colletc into 【自定义的数组】 <limit cn_batch_size>pl/sql中批量执行一批SQL的语法forall i in 1..[自定义数组长度】  execute immediate [带绑定变量的sql] using [绑定变量输入值】 declare  cur_emp sys_refcursor;  v_sql varchar2(4000);  type namelist is table of varchar2(10);  enames namelist;  cn_batch_size constant pls_integer :=1000;begin  v_sql:= 'select ename from emp where empno> :1';  open cur_emp for v_sql using 7900;  loop    fetch cur_emp bulk collect into enames limit cn_batch_size;        for i in 1..enames.count loop      dbms_output.put_line(enames(i));      end loop;          exit when enames.count<cn_batch_size;    end loop;    close cur_emp;    end; 四、绑定变量分级（bind graduation)根据文本型绑定变量的定义长度而将这些绑定变量分成四级1、32字节（bytes)以内第一级 分配32字节2、33--128字节第二级   分配120字节3、129--2000字节第三级 分配2000字节4、2000字节以上第四级 ，按实际绑定变量大小，小于等于2000则分配2000字节，大于2000则分配4000字节 注意 oracle只对文本型绑定变量分级，数值型的统一分配22字节，另外一个重要知识点，在pl/sql中，要是文本绑定变量的定义长度发生了变化，因为child cursor 里存储了该绑定变量的长度和类型，所以这个sql就会新做硬解析。因此，为了避免不必要的硬解析， 在pl/sql中，定义文本绑定变量是，最好统一定义长度，比如varchar2(4000); 五、目标sql中的绑定变量不宜过多 六、如何得到已执行sql中绑定变量的值V$SQL_BIND_CAPTURE，如果已经被aged out 除shared pool,则可以看另外两张dba_hist_sqlstat,dba_hist_sqlbind 满足下列条件之一，含有绑定变量的目标sql中的绑定变量值会被捕获，然后可以从v$sql_bind_capture中查询到1、该sql做的是硬解析2、该SQL做的是软解析或软软解析，这是每隔15分钟，捕获一次值注意对绑定变量值的捕获只在where 语句中的绑定变量，其他比如insert中的值不会被捕获