怎么使用ORACLE中的绑定变量

这篇文章主要讲解了“怎么使用ORACLE中的绑定变量”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么使用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中的值不会被捕获

感谢各位的阅读，以上就是“怎么使用ORACLE中的绑定变量”的内容了，经过本文的学习后，相信大家对怎么使用ORACLE中的绑定变量这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！