[20121102]PLSQL中的绑定变量.txt

[20121102]PLSQL中的绑定变量.txt

    以前曾经遇到一个sql语句提交给开发,开发没有找到,最终确定是问题语句在PLSQL中,实际上PLSQL转化为大写,

加上自己没有注意.实际上SQL语句在PLSQL中,一些好像被"格式化一样",我举一个例子:

1.测试环境:

SQL> select * from v$version where rownum

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select * from dept ;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        50 TEST           TEST

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

2.建立测试例子:

create or replace procedure test_bind

as

v_deptno  number;

v_dname   varchar2(14);

v_loc     varchar2(13);

v_deptno1  number;

v_dname1   varchar2(14);

v_loc1     varchar2(13);

cursor c_dept is select deptno,dname from dept order by deptno;

begin

 open c_dept;

loop

 fetch c_dept into v_deptno,v_dname;

 exit when c_dept%NOTFOUND;

 select loc into v_loc from dept where deptno=v_deptno and dname=v_dname;

    Select loc into v_loc from dept 

where deptno=v_deptno 

and dname=v_dname;

    v_deptno1 := v_deptno;

    v_dname1  := v_dname;

    Select loc into v_loc from dept where deptno=v_deptno1 

and dname=v_dname;

Select loc 

into v_loc from dept where deptno=v_deptno

and dname=v_dname1;

Select 

loc 

into v_loc1 from dept where deptno=v_deptno1

and dname=v_dname1;

end loop;

end;

/

3.测试:

alter session set events '10046 trace name context forever,level 12';

exec test_bind

alter session set events '10046 trace name context off';

4.查看跟踪文件:

SQL ID: fq1jkwcmsx57d

Plan Hash: 2852011669

SELECT LOC

FROM

 DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        5      0.00       0.00          0          0          0           0

Execute     25      0.00       0.00          0          0          0           0

Fetch       25      0.00       0.00          0          2          0          25

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       55      0.00       0.00          0          2          0          25

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84     (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=0 us cost=1 size=18 card=1)

      1   INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73200)

--可以发现全部换成了大写,并且都转化为1条语句,分析5次,共执行了25,不管sql语句如何写,带入的参数如何,都转化为:B2和:B1.