プロシージャの再帰呼び出し

DB

 

4DBともにプロシージャの再帰実行は可能
ただし、MySQLはサーバパラメータの変更が必要

(8.0.29)

select @@max_sp_recursion_depth;
set session max_sp_recursion_depth=255;
select @@max_sp_recursion_depth;


drop procedure proc1;

delimiter //
create procedure proc1(
 in   n numeric
,out  m numeric
)
begin
  declare wk1 numeric;
  declare wk2 numeric;
  
  if n = 0 then
    set m = 0;
  elseif n = 1 then
    set m = 1;
  else
    call proc1(n-2,wk1);
    call proc1(n-1,wk2);
    set m = wk1+wk2;
  end if;
end
//
delimiter ;


call proc1(10,@wk1);
select @wk1;


drop procedure proc2;

delimiter //
create procedure proc2()
begin
  declare wk1 numeric;
  declare wk2 numeric;
  
  set wk1 = 0;
  while wk1 <= 20 do
    call proc1(wk1,wk2);
    select wk1,wk2;
    set wk1 = wk1 + 1;
  end while;
end
//
delimiter ;

call proc2();

 

--------------------------------

select @@max_sp_recursion_depth;
set session max_sp_recursion_depth=255;
select @@max_sp_recursion_depth;


drop procedure proc1;

delimiter //
create procedure proc1(
in   param1 numeric
)
begin
declare i int;
declare wk1 int;

if param1 = 0 then
  drop temporary table if exists rec;
  create temporary table if not exists rec(
   id    int
  ,col1  numeric
  ,col2  varchar(30)
  );
end if;

set wk1 = ( select count(*) from rec );
set i = 1;
while i <= 3 do
  insert into rec values(wk1 + i, param1 * 100 + i, param1 * 100 + i);
  set i = i + 1;
end while;


if param1 < 5 then
  call proc1(param1+1);
end if;

if param1 = 0 then
  select * from rec;
end if;

end
//
delimiter ;


call proc1(0);

 

 

 

(19c)
https://qiita.com/nkojima/items/a046b4c295ed75977626


create or replace procedure proc1(
  n in  number
, m out number
)
is
  wk1 number;
  wk2 number;
begin
  if n = 0 then
    m := 0;
  elsif n = 1 then
    m := 1;
  else
    proc1(n-2,wk1);
    proc1(n-1,wk2);
    m := wk1+wk2;
  end if;
end;
/
sho error


variable wk1 number
exec proc1(10, :wk1)
print wk1


set serveroutput on
declare
  wk1 number;
begin
  for i in 0..20 loop
    proc1(i,wk1);
    dbms_output.put_line(i||':'||wk1);
  end loop;
end;
/

 

--------------------------------

https://itsakura.com/oracle-plsql-function-ret

-- ネストした表のタイプ作成

drop type gar;
drop type grec;

create or replace type grec as object(
 id   int
,col1 number
,col2 varchar2(30)
);
/

create or replace type gar as 
table of grec;
/


set serveroutput on
create or replace procedure proc1(
 param1 in     number
,param2 out    gar
)
as
ar1 gar := gar();
ar2 gar := gar();
wk1 int;
begin

  for i in 1..3 loop
    ar1.extend;
    ar1(i) := grec(i,param1 * 100 + i, param1 * 100 + i);
  end loop;

  if param1 < 5 then
    proc1(param1+1,ar2);
    wk1 := ar1.count;
    for i in 1..ar2.count loop
      ar1.extend;
      wk1 := wk1+1;
      ar1(wk1) := ar2(i);
    end loop;
  end if;
  
  if param1 = 0 then
    for i in 1..ar1.count loop
      dbms_output.put_line('ar1(' || i || '): ' || ar1(i).col1 || ',' || ar1(i).col2 );
    end loop;
  else
    param2 := ar1;
  end if;
end;
/
show error;


declare
ar1 gar := gar();
begin
  proc1(0,ar1);
end;
/

 

 

(14)

create or replace procedure proc1(
  n in     numeric
, m out    numeric
)
language plpgsql
as $$
declare
  wk1 numeric;
  wk2 numeric;
begin
  if n = 0 then
    m := 0;
  elsif n = 1 then
    m := 1;
  else
    call proc1(n-2,wk1);
    call proc1(n-1,wk2);
    m := wk1+wk2;
  end if;
end;
$$;

 

call proc1(10, null);

 

do $$
declare
  wk1 numeric;
begin
  for i in 0..20 loop
    call proc1(i,wk1);
    raise info '%:%', i,wk1;
  end loop;
end;
$$;

 

--------------------------------

drop type if exists grec CASCADE;
create type grec as(
 id   int
,col1 numeric
,col2 varchar(30)
);

\df proc1

create or replace procedure proc1(
 param1 in     numeric
,param2 out    grec
)
language plpgsql
as $$
declare
ar1 grec;
ar2 grec[];
wk1 numeric;
begin

  for i in 1..3 loop
    ar1[i].id   := i;
    ar1[i].col1 := param1 * 100 + i;
    ar1[i].col2 := param1 * 100 + i;
  end loop;

  if param1 < 5 then
    call proc1(param1+1,ar2);
    
    wk1 := array_upper(ar1, 1);
    for i in 1..array_upper(ar2, 1) loop
      wk1 := wk1+1;
      ar1[wk1] := ar2[i];
    end loop;
  end if;
  
  if param1 = 0 then
    for i in 1..array_upper(ar1, 1) loop
      raise info 'ar1(%): %,%', i,ar1[i].col1,ar1[i].col2;
    end loop;
  else
    param2 := ar1;
  end if;
end;
$$;


\df proc1


call proc1(0, null );

 

 

  • SQL Server

(2019)

create or alter procedure proc1(
  @n  numeric
, @m  numeric out
)
as
begin
  set nocount on;
  declare @wk1 numeric;
  declare @wk2 numeric;
  declare @wk3 numeric;
  declare @wk4 numeric;  
  if @n = 0
    set @m = 0;
  else if @n = 1
    set @m = 1;
  else
    begin
      set @wk1 = @n-2;
      set @wk2 = @n-1;
      exec proc1 @wk1 ,@wk3 out;
      exec proc1 @wk2 ,@wk4 out;
      set @m = @wk3+@wk4;
  end;
end
go

declare @wk1 numeric;
exec proc1 10,@wk1 out;
print @wk1;


set nocount on
declare @wk1 numeric;
declare @wk2 numeric;
set @wk1 = 0;
while @wk1 <= 20
begin
  exec proc1 @wk1 ,@wk2 out;
  print cast(@wk1 as varchar)+':'+cast(@wk2 as varchar);
  set @wk1 = @wk1 + 1;
end;
go

 

--------------------------------

 

create or alter procedure proc1(
 @param1  numeric
)
as
begin
  set nocount on;
  declare @i int;
  declare @wk1 int;
  declare @wk2 int;

  if @param1 = 0
    begin
      drop table if exists #rec;
      create table #rec(
       id    int
      ,col1  numeric
      ,col2  varchar(30)
      );
    end;

  set @wk1 = ( select count(*) from #rec );
  set @i = 1;
  while @i <= 3
    begin
      insert into #rec values(@wk1 + @i, @param1 * 100 + @i, @param1 * 100 + @i);
      set @i = @i + 1;
    end;

  if @param1 < 5
    begin
      set @wk1 = @param1+1;
      exec proc1 @wk1;
    end;
  if @param1 = 0
    select * from #rec;
end
go

exec proc1 0;