[20171231]PLSQL使用绑定变量.txt
--//一些应用程序开发使用的绑定变量是:1,:2之列的语句,要调优这些语句必须写成如下:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//例子,这些语句无法在sqlplus下直接执行:
select * from emp where deptno = :1 and ename =:2 and hiredate =:3
--//必须转化成如下:
$ cat d.txt
begin
execute immediate q'[
select * from emp where deptno = :1 and ename =:2 and hiredate =:3
]'
using 10,'MILLER','1982-01-23 00:00:00';
end;
/
--//换一句话如果要优化最简单的方式写成上面的形式,自己没事写一个简单的脚本,实现这个功能:
2.测试脚本:
--//在toad下执行如下:
select * from emp where deptno = :1 and ename =:2 and hiredate =:3;
--//带入的参数对应是using 10,'MILLER','1982-01-23 00:00:00'.
--//查询获得sql_id=cz0pwgqy3xumj.
SCOTT@book> @ &r/bind_cap cz0pwgqy3xumj ''
C200
------------------------------------------------------------------
select * from emp where deptno = :1 and ename =:2 and hiredate =:3
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ---------------------
cz0pwgqy3xumj 0 YES :1 1 22 2018-01-02 09:19:24 NUMBER 10
YES :2 2 32 2018-01-02 09:19:24 VARCHAR2(32) MILLER
YES :3 3 7 2018-01-02 09:19:24 DATE 1982/01/23 00:00:00
--//注:使用listagg,仅仅在11g下有效.
$ cat b5.sql
spool &&1..txt
set feedback off
set head off
set verify off
select 'alter session set current_schema='||PARSING_SCHEMA_NAME||';' txt from v$sqlarea where sql_id = '&&1';
select 'alter session set statistics_level=all;' from dual;
select '' from dual;
select 'begin' txt from dual
union all
select 'execute immediate q''[' txt from dual
union all
select to_char(sql_fulltext) txt from v$sqlarea where sql_id='&&1'
union all
select ']'' using' txt from dual
union all
SELECT LISTAGG ( DECODE ( DATATYPE_STRING ,'NUMBER', value_string
,'DATE', '''' || TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss') || ''''
,'''' || value_string || '''') ,CHR (10) || ',') WITHIN GROUP (ORDER BY POSITION)
txt
FROM v$sql_bind_capture
WHERE sql_id = '&&1'
AND was_captured = 'YES'
AND DUP_POSITION IS NULL
union all
select ';' txt from dual
union all
select 'end;' txt from dual
union all
select '/' txt from dual;
select 'rollback;' txt from dual;
spool off
set feedback 6
set head on
--//执行如下:
SYSTEM@book> @ &r/b5.sql cz0pwgqy3xumj
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
begin
execute immediate q'[
select * from emp where deptno = :1 and ename =:2 and hiredate =:3
]' using
10
,'MILLER'
,'1982/01/23 00:00:00'
;
end;
/
rollback;
--//检查cz0pwgqy3xumj.txt脚本看看,是否存在问题:
$ cat cz0pwgqy3xumj.txt
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
begin
execute immediate q'[
select * from emp where deptno = :1 and ename =:2 and hiredate =:3
]' using
10
,'MILLER'
,'1982/01/23 00:00:00'
;
end;
/
rollback;
--//当然有时候如果参数cursor_sharing=force ,生成的脚本可能存在问题.要调整再执行.
