(8.0.26)
配列はない模様のため、テンポラリテーブルを使用する
drop procedure proc1;
delimiter //
create procedure proc1()
begin
drop temporary table if exists ar1;
create temporary table if not exists ar1(col1 int,col2 varchar(1000) );
insert into ar1 values(100,'AAA');
insert into ar1 values(200,'BBB');
select * from ar1;
end
//
delimiter ;
call proc1();
(19c)
https://itsakura.com/oracle-plsql-array
https://oracle.programmer-reference.com/plsql-record-type/
set serveroutput on
create or replace procedure proc1
is
type type1 is record(
col1 int
,col2 varchar2(1000)
);
type type2 is table of type1;
ar1 type2 := type2();
begin
ar1.extend;
ar1(ar1.count).col1 := 100;
ar1(ar1.count).col2 := 'AAA';
ar1.extend;
ar1(ar1.count).col1 := 200;
ar1(ar1.count).col2 := 'BBB';
for i in ar1.first..ar1.last loop
dbms_output.put_line(ar1(i).col1 ||','|| ar1(i).col2);
end loop;
end;
/
exec proc1;
(14)
https://www.ne.jp/asahi/hishidama/home/tech/postgres/plpgsql/array.html
drop type if exists type1;
create type type1 as(col1 int,col2 varchar(1000) );
create or replace procedure proc1()
language plpgsql
as $$
declare
rec type1;
ar1 type1[];
begin
rec.col1 := 100;
rec.col2 := 'AAA';
ar1 := array_append(ar1,rec);
rec.col1 := 200;
rec.col2 := 'BBB';
ar1 := array_append(ar1,rec);
foreach rec in array ar1 loop
raise info 'rec = %', rec;
end loop;
end;
$$;
call proc1();
(2019)
https://bayashita.com/p/entry/show/58
配列はない模様のため、テーブル変数を使用する
create or alter procedure proc1
as
begin
set nocount on;
declare @ar1 table(
col1 int
,col2 varchar(1000)
);
insert into @ar1 values(100,'AAA');
insert into @ar1 values(200,'BBB');
select * from @ar1;
end
go
exec proc1;
