ITPub博客

首页 > 数据库 > Oracle > ORACLE中的绑定变量

ORACLE中的绑定变量

原创 Oracle 作者:james210 时间:2019-12-02 15:22:40 0 删除 编辑

一、绑定变量的作用

: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中的值不会被捕获





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26031282/viewspace-2665928/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORACLE的游标共享
请登录后发表评论 登录
全部评论

注册时间:2011-11-07

  • 博文量
    16
  • 访问量
    11639