[20171231]PLSQL使用绑定变量.txt

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2149586/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2149586/