動的SQLのカーソル - HTN20190109の日記

MySQL

(8.0.22)

調べた限りテーブル名を動的にできない

Oracle

set serveroutput on
declare
vsql varchar2(2000);
vtabname varchar2(100);
vcol1 varchar2(100);
vcol2 varchar2(100);
type rcur is ref cursor;
cur1 rcur;

begin
vsql := '';
vtabname := 'tab1';
vcol1 := '1';
vcol2 := '';

vsql := 'select col2 from ' || vtabname || ' where col1 = ' || vcol1;

open cur1 for vsql;

loop
fetch cur1 into vcol2;
exit when cur1%notfound;

dbms_output.put_line('vcol2: ' || vcol2);
end loop;

close cur1;
end;
/

PostgreSQL

(13)
http://sak.cool.coocan.jp/w_sak3/doc/sysbrd/psql_k22.htm

do
$$
declare
vtabname varchar(100);
vcol1 varchar(100);
rec record;
cur1 refcursor;

begin
vtabname := 'tab1';
vcol1 := '1';

open cur1 for execute 'select col2 from ' || vtabname || ' where col1 = ' || vcol1;
loop
fetch cur1 into rec;
if not found then
exit;
end if;
raise info 'col2 = %' , rec.col2;
end loop;
close cur1;
end;
$$
language plpgsql
;

SQL Server

(2019)
https://www.projectgroup.info/tips/SQLServer/SQL/SQL000040.html

declare @sql nvarchar(2000);
declare @tabname nvarchar(100);
declare @col1 nvarchar(100);
declare @col2 nvarchar(100);

set @sql = N'';
set @tabname = N'tab1';
set @col1 = N'1';
set @col2 = N'';

set @sql = N'select col2 from ' + @tabname + ' where col1 = ' + @col1;
execute('declare cur1 cursor for ' + @sql);

open cur1;
fetch next from cur1 into @col2;

while @@fetch_status = 0
begin
print '@col2 = ' + @col2;
fetch next from cur1 into @col2;
end;

close cur1;
deallocate cur1;