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