ITPub博客

首页 > 数据库 > Oracle > Creating Test Script With Bind Variable

Creating Test Script With Bind Variable

原创 Oracle 作者:lfree 时间:2015-08-14 17:40:13 0 删除 编辑

[20150814] Creating Test Script With Bind Variable.txt

--原链接:
http://blog.itpub.net/267265/viewspace-1700792/
http://blog.itpub.net/267265/viewspace-1401633/
http://blog.itpub.net/267265/viewspace-764543/

--我在原来的基础上做了许多修正。纯粹是工作需要,解决一些优化问题。

--我们的生产系统我设置会话cursor_sharing=force,主要程序存在大量没有使用绑定变量的情况,这样一些常数参数也变成了参数,
--今天建立一个新的脚本,把里面:"SYS_B_NN" 替换为 常量, 欢迎大家测试:

--注意我日期类型选择环境变量
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--这样直接使用字符串表示时间就没有问题。


-------------------------------------------------------------------------------------------------------
--
-- File name:   build_bind_vars3.sql
--
-- Purpose:     Build SQL*Plus test script with variable definitions
--
-- Author:      Jack Augustin and Kerry Osborne
--
-- Description: This script creates a file which can be executed in SQL*Plus. It creates bind variables,
--              sets the bind variables to the values stored in V$SQL_BIND_CAPTURE, and then executes
--              the statement. The sql_id is used for the file name and is also placed in the statement
--              as a comment. Note that numeric bind variable names are not permited in SQL*Plus, so if
--              the statement has numberic bind variable names, they have an 'N' prepended to them. Also
--              note that CHAR variables are converted to VARCHAR2.
--
-- Usage:       This scripts prompts for two values.
--
--              sql_id:   this is the sql_id of the statement you want to duplicate
--
--              child_no: this is the child cursor number from v$sql
--                        (the default is 0 second)
--
-- http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/
-- modify : add date convert varchar2(32) 20130622
-- modify : bug replace char to varchar2(32) 20150114
-- modify : replace sql_fulltext chr(13) to '' 20150114
-- modify : add 'set sqlblanklines on' and 'set sqlblanklines off' 20150616
-- modify : use distinct to delete duplicate  20150616
-- modify : do not use distinct to delete duplicate , add where dup_position is null 20150814
-- modify : delete some comment code.
-------------------------------------------------------------------------------------------------------
--
set verify off
set sqlblanklines on
set trimspool on
set trimout on
set feedback off;
set linesize 4000;
set pagesize 50000;
set timing off;
set head off
--
accept sql_id char prompt "Enter SQL ID ==> " default &1
accept child_no char prompt "Enter Child Number ==> " default 0
var isdigits number
var v_sql_fulltext clob
--
--
col sql_fulltext for a4000 word_wrap
spool &&sql_id\.sql

--
--Check for numeric bind variable names
--

begin
select case regexp_substr(replace(name,':',''),'[[:digit:]]') when replace(name,':','') then 1 end into :isdigits
from
V$SQL_BIND_CAPTURE
where
sql_id='&&sql_id'
and child_number = &&child_no
and rownum < 2;
end;
/

--
-- Create variable statements
--
select 'variable ' ||
   case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' ||
decode(datatype_string,'DATE','VARCHAR2(32)',replace(datatype_string,'CHAR(','VARCHAR2(')) txt
from
V$SQL_BIND_CAPTURE
where
sql_id='&&sql_id'
and child_number = &&child_no
and dup_position is null;
--
-- Set variable values from V$SQL_BIND_CAPTURE
--
select 'begin' txt from dual;
select
   case :isdigits when 1 then replace(name,':',':N') else name end ||
   ' := ' ||
   case datatype_string when 'NUMBER' then null else '''' end ||
   case datatype_string when 'DATE' then to_char(to_date(value_string,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss')  else nvl(value_string,'00') end ||
   case datatype_string when 'NUMBER' then null else '''' end ||
   ';' txt
from
   V$SQL_BIND_CAPTURE
where
   sql_id='&&sql_id'
   and child_number = &&child_no
   and dup_position is null;
select 'end;' txt from dual;
select '/' txt from dual;

--
-- Generate statement
--
select 'set termout off' txt from dual;
select 'set sqlblanklines on' txt from dual;
select 'alter session set current_schema='||PARSING_SCHEMA_NAME||';' txt from v$sqlarea where sql_id = '&&sql_id';
select 'alter session set statistics_level=all;' from dual;
select '' from dual;

--begin
--select replace(sql_fulltext,chr(13),'') into :v_sql_fulltext from (
--select regexp_replace(sql_fulltext,'(select |SELECT )','select /* test &&sql_id */ /*+ gather_plan_statistics */ ',1,1) sql_fulltext from (
--select case :isdigits when 1 then replace(sql_fulltext,':',':N') else sql_fulltext end ||';' sql_fulltext
--from v$sqlarea
--where sql_id = '&&sql_id'));
--end;
--/

--replace :"SYS_B_NN" using constant value
SET SERVEROUTPUT ON
declare
v_sql_fulltext clob;
v_sql clob;
begin

select replace(sql_fulltext,chr(13),'') into v_sql_fulltext from (
select regexp_replace(sql_fulltext,'(select |SELECT )','select /* test &&sql_id */ /*+ gather_plan_statistics */ ',1,1) sql_fulltext from (
select case :isdigits when 1 then replace(sql_fulltext,':',':N') else sql_fulltext end ||';' sql_fulltext
from v$sqlarea
where sql_id = '&&sql_id'));

FOR cursor_1 IN (
select
   case :isdigits when 1 then replace(name,':',':"N') else replace(name,':',':"') end||'"'  c1,
   case datatype_string when 'NUMBER' then null else '''' end ||
   case datatype_string when 'DATE' then to_char(to_date(value_string,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss')  else nvl(value_string,'00') end ||
   case datatype_string when 'NUMBER' then null else '''' end  c2
from
   V$SQL_BIND_CAPTURE
where
   sql_id='&&sql_id'
   and child_number = &&child_no
   and name like ':SYS\_B\_%' escape '\'
   and dup_position is null  order by name desc )
LOOP
    BEGIN
        select replace(v_sql_fulltext,cursor_1.c1,cursor_1.c2) into v_sql_fulltext from dual;
    END;
END LOOP;
    dbms_output.put_line(v_sql_fulltext);
end;
/
SET SERVEROUTPUT Off
--

select 'set termout on' txt from dual;
select 'set sqlblanklines off' txt from dual;
select '@zws '''' ''''' txt from dual;
select '--@dpc '''' ''''' txt from dual;
select 'rollback;' txt from dual;

spool off;
undef sql_id
undef child_no
set feedback on;
set head on

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292139