バインド変数取得

DB

(5.6)

drop table tab1;
create table tab1(col1 int,col2 varchar(100));

insert into tab1 values(1,'A');
select * from tab1;

-- 1.select文

prepare stmt1 from 'select /* TEST1 */ A.*,? from tab1 A where A.col1 = ? and A.col2 = ?';

set @val1 = '1';
set @val2 = 'A';
set @val3 = 999999;

execute stmt1 using @val3,@val1,@val2;
deallocate prepare stmt1;

-- 2.insert文
prepare stmt1 from 'insert /* TEST2 */ into tab1 values(?,?)';

set @val1 = '2';
set @val2 = 'B';

execute stmt1 using @val1,@val2;
deallocate prepare stmt1;

-- 3.update文
prepare stmt1 from 'update /* TEST3 */ tab1 set col2 = ? where col1 = ?';

set @val1 = '1';
set @val2 = 'C';

execute stmt1 using @val2,@val1;
deallocate prepare stmt1;


-- 4.delete文
prepare stmt1 from 'delete /* TEST4 */ from tab1 where col1 = ? and col2 = ?';

set @val1 = '1';
set @val2 = 'C';

execute stmt1 using @val1,@val2;
deallocate prepare stmt1;


--確認結果
一般クエリログやスロークエリログには
バインド変数部分が実際の値に置き換えられて記録される

 

(12cR1)

 

drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(100));

insert into tab1 values(1,'A');
commit;
select * from tab1;

-- 1.select文

variable val1 varchar2(100)
variable val2 varchar2(100)
variable val3 number

execute :val1 := '1'
execute :val2 := 'A'
execute :val3 := 999999

print :val1
print :val2
print :val3

select /* TEST1 */ A.*,:val3 from tab1 A where A.col1 = :val1 and A.col2 = :val2;


select sql_id,sql_text
from v$sql
where sql_text like '%TEST1%'
;

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

select sql_id,child_number,name,position,datatype_string,last_captured,value_string
from v$sql_bind_capture
where sql_id = 'gzz5d9jn5c4gw'
order by child_number,position
;

-- 2.insert文

variable val1 varchar2(100)
variable val2 varchar2(100)

execute :val1 := '2'
execute :val2 := 'B'

print :val1
print :val2

 

insert /* TEST2 */ into tab1 values(:val1,:val2);
commit;

select sql_id,sql_text
from v$sql
where sql_text like '%TEST2%'
;

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

select sql_id,child_number,name,position,datatype_string,last_captured,value_string
from v$sql_bind_capture
where sql_id = '95cujjvbugc84'
order by child_number,position
;

-- 3.update文

variable val1 varchar2(100)
variable val2 varchar2(100)

execute :val1 := '1'
execute :val2 := 'C'

print :val1
print :val2

update /* TEST3 */ tab1 set col2 = :val2 where col1 = :val1;
commit;

select sql_id,sql_text
from v$sql
where sql_text like '%TEST3%'
;

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

select sql_id,child_number,name,position,datatype_string,last_captured,value_string
from v$sql_bind_capture
where sql_id = '2gk6fn2uzs7cy'
order by child_number,position
;


-- 4.delete文

variable val1 varchar2(100)
variable val2 varchar2(100)

execute :val1 := '1'
execute :val2 := 'C'

print :val1
print :val2

delete /* TEST4 */ tab1 where col1 = :val1 and col2 = :val2;
commit;

select sql_id,sql_text
from v$sql
where sql_text like '%TEST4%'
;

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

select sql_id,child_number,name,position,datatype_string,last_captured,value_string
from v$sql_bind_capture
where sql_id = 'gsxu3rvfxwt2u'
order by child_number,position
;

--確認結果
V$SQL_BIND_CAPTURE
バインド変数がSQL文のWHERE句またはHAVING句で使用されている場合にのみ、このビューでバインド値が表示されます

 

(9.4)

drop table tab1 cascade;
create table tab1(col1 int,col2 varchar(100));

insert into tab1 values(1,'A');
select * from tab1;

-- 1.select文

prepare stmt1(integer,integer,varchar)
as select /* TEST1 */ A.*,$1 from tab1 A where A.col1 = $2 and A.col2 = $3;

execute stmt1(999999,'1','A');
deallocate prepare stmt1;


-- 2.insert文
prepare stmt1(integer,varchar)
as insert /* TEST2 */ into tab1 values($1,$2);

execute stmt1('2','B');
deallocate prepare stmt1;

-- 3.update文
prepare stmt1(varchar,integer)
as update /* TEST3 */ tab1 set col2 = $1 where col1 = $2;

execute stmt1('C','1');
deallocate prepare stmt1;

-- 4.delete文

prepare stmt1(integer,varchar)
as delete /* TEST4 */ from tab1 where col1 = $1 and col2 = $2;

execute stmt1('1','C');
deallocate prepare stmt1;

 

--確認結果
SQL文とバインド変数がセットでスロークエリログに記録される模様

 


(2017)

drop table tab1;
create table tab1(col1 int,col2 varchar(100));

insert into tab1 values(1,'A');
select * from tab1;


-- 1.select文

dbcc freeproccache
go

declare @P1 int
declare @sql nvarchar(max)
declare @param nvarchar(2000)
declare @val1 int
declare @val2 varchar(100)
declare @val3 varchar(100)


set @sql = 'select /* TEST1 */ A.*,@val1 from tab1 A where A.col1 = @val2 and A.col2 = @val3'
set @param = N'@val1 int, @val2 varchar(100), @val3 varchar(100)'
exec sys.sp_prepare @P1 output, @param, @sql;

set @val1 = 999999
set @val2 = '1'
set @val3 = 'A'


exec sys.sp_execute @P1, @val1, @val2, @val3;

exec sys.sp_unprepare @P1;

--SQL文と実行計画の取得
select text,query_plan from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
where text like '%TEST1%'
;

-- 2.insert文

dbcc freeproccache
go

declare @P1 int
declare @sql nvarchar(max)
declare @param nvarchar(2000)
declare @val1 int
declare @val2 varchar(100)


set @sql = 'insert /* TEST2 */ into tab1 values(@val1,@val2)'
set @param = N'@val1 int, @val2 varchar(100)'
exec sys.sp_prepare @P1 output, @param, @sql;

set @val1 = '2'
set @val2 = 'B'


exec sys.sp_execute @P1, @val1, @val2;

exec sys.sp_unprepare @P1;

--SQL文と実行計画の取得
select text,query_plan from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
where text like '%TEST2%'
;

-- 3.update文

dbcc freeproccache
go

declare @P1 int
declare @sql nvarchar(max)
declare @param nvarchar(2000)
declare @val1 varchar(100)
declare @val2 int


set @sql = 'update /* TEST3 */ tab1 set col2 = @val1 where col1 = @val2'
set @param = N'@val1 varchar(100), @val2 int'
exec sys.sp_prepare @P1 output, @param, @sql;

set @val1 = 'C'
set @val2 = '1'


exec sys.sp_execute @P1, @val1, @val2;

exec sys.sp_unprepare @P1;

--SQL文と実行計画の取得
select text,query_plan from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
where text like '%TEST3%'
;

-- 4.delete文

dbcc freeproccache
go

declare @P1 int
declare @sql nvarchar(max)
declare @param nvarchar(2000)
declare @val1 int
declare @val2 varchar(100)


set @sql = 'delete /* TEST4 */ from tab1 where col1 = @val1 and col2 = @val2'
set @param = N'@val1 int, @val2 varchar(100)'
exec sys.sp_prepare @P1 output, @param, @sql;

set @val1 = '1'
set @val2 = 'C'


exec sys.sp_execute @P1, @val1, @val2;

exec sys.sp_unprepare @P1;

--SQL文と実行計画の取得
select text,query_plan from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
where text like '%TEST4%'
;

--確認結果
SSMSの「実際の実行プランを含める」を選択して表示される実行プランの中のParameterListで確認できる
dm_exec_query_plan.query_planでは確認できない