[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 ,生成的指令碼可能存在問題.要調整再執行.