variable val1 varchar2(100)
execute :val1 := '123'
print :val1
select * from tab1 where col1 = :val1;
PREPARE stmt1 FROM 'SELECT * from tab1 where col1=?';
SET @a = 2;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
https://www.postgresql.jp/document/9.4/html/sql-prepare.html
create table tab10(col1 int, col2 text, col3 bool, col4 numeric);
PREPARE pstmt1 (int, text, bool, numeric) AS
INSERT INTO tab10 VALUES($1, $2, $3, $4);
EXECUTE pstmt1(1, 'Hunter Valley', 't', 200.00);
DEALLOCATE pstmt1;
select * from tab10;
(2014)
https://www.sqlindia.com/difference-between-sp_execute-sp_executesql-exec/
drop table tab1;
create table tab1(col1 int,col2 varchar(100));
insert into tab1 values(1,'A');
insert into tab1 values(2,'B');
select * from tab1;
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;
set @val1 = 888888
set @val2 = '2'
set @val3 = 'B'
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%'
;
