[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 ,生成的脚本可能存在问题.要调整再执行.