動的SQLのカーソル

(8.0.22)

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

 

(19c)
https://sql-oracle.com/?p=1468

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;
/

 

 

(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
;

 

(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;