[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.
