[20121102]PLSQL中的繫結變數.txt

[20121102]PLSQL中的繫結變數.txt

    以前曾經遇到一個sql語句提交給開發,開發沒有找到,最終確定是問題語句在PLSQL中,實際上PLSQL轉化為大寫,

加上自己沒有注意.實際上SQL語句在PLSQL中,一些好像被"格式化一樣",我舉一個例子:

1.測試環境:

SQL> select * from v$version where rownum<=1;

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.