怎么使用ORACLE中的绑定变量

共计 2568 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要讲解了“怎么使用 ORACLE 中的绑定变量”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么使用 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 中的绑定变量这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！