(8.0.22)
using句のみ使用可能。into句は使用できないためユーザ変数を使用する
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,2);
insert into tab1 values(3,4);
select * from tab1;
drop procedure proc1;
delimiter //
create procedure proc1()
begin
set @val3 := 1;
prepare stmt from 'select col1,col2 into @val1,@val2 from tab1 where col1 = ?';
execute stmt using @val3;
deallocate prepare stmt;
select @val1,@val2;
end
//
delimiter ;
call proc1();
(19c)
drop table tab1 purge;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,2);
insert into tab1 values(3,4);
commit;
select * from tab1;
set serveroutput on
declare
val1 integer;
val2 integer;
val3 integer;
begin
val3 := 1;
execute immediate 'select col1,col2 from tab1 where col1 = :val3' into val1,val2 using val3 ;
dbms_output.put_line('val1=' || to_char(val1) ||',val2=' || to_char('val2'));
end;
/
(13)
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,2);
insert into tab1 values(3,4);
select * from tab1;
do
language plpgsql
$$
declare
val1 integer;
val2 integer;
val3 integer;
begin
val3 := 1;
execute 'select col1,col2 from tab1 where col1 = $1' into val1,val2 using val3 ;
raise notice 'val1=%,val2=%' ,val1, val2;
end;
$$
;
(2019)
https://sqlserver.programmer-reference.com/sp_executesql-dynamicsql/
drop table tab1;
create table tab1(col1 int,col2 int);
insert into tab1 values(1,2);
insert into tab1 values(3,4);
select * from tab1;
begin
declare @val1 integer;
declare @val2 integer;
declare @val3 integer;
set @val3 = 1;
execute sp_executesql
N'select @val1 = col1,@val2 = col2 from tab1 where col1 = @val3',
N'@val1 int OUT,@val2 int OUT,@val3 int', @val1 OUT, @val2 OUT, @val3;
print cast(@val1 as varchar) +','+ cast(@val2 as varchar)
end
