テーブル値関数

ない模様

 


(12cR1)
https://qiita.com/fujii_masao/items/ac735db55ffda5d0ee38

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int,col2 varchar2(10));
create table tab2(col1 int,col2 varchar2(10));

declare
begin
for i in 1..5 loop
insert into tab1 values(i,chr(64+i));
insert into tab1 values(i,chr(64+i));
commit;
end loop;
for i in 1..10 loop
insert into tab2 values(i,chr(96+i));
commit;
end loop;
end;
/

select * from tab1;
select * from tab2;

drop type ttype1;
drop type type1;


create type type1 is object (col1 int, col2 varchar2(10));
/

create type ttype1 is table of type1;
/

create or replace function fun1 (p1 int)
return ttype1
is
ret ttype1 := ttype1();
i int;
begin
i := 1;
for r in (select * from tab1 where col1 = p1) loop
ret.extend;
ret(i) := type1(r.col1 , r.col2);
i := i + 1;
end loop;
return ret;
end;
/

select * from table(fun1(1));


select t2.col1,t2.col2,t1.col2
from tab2 t2 cross apply fun1(t2.col1) t1
;

select t2.col1,t2.col2,t1.col2
from tab2 t2 outer apply fun1(t2.col1) t1
;

 


(13)
https://qiita.com/fujii_masao/items/ac735db55ffda5d0ee38

drop table tab1 cascade;
drop table tab2 cascade;

create table tab1(col1 int,col2 varchar(10));
create table tab2(col1 int,col2 varchar(10));


do $$
declare
begin
for i in 1..5 loop
insert into tab1 values(i,chr(64+i));
insert into tab1 values(i,chr(64+i));
end loop;
for i in 1..10 loop
insert into tab2 values(i,chr(96+i));
end loop;
end
$$ language plpgsql;

select * from tab1;
select * from tab2;


create or replace function fun1(p1 int)
returns setof tab1 as $$
begin
return query select * from tab1 where col1 = p1;
return;
end;
$$ language plpgsql;

select * from fun1(1);


select t2.col1,t2.col2,t1.col2
from tab2 t2 inner join fun1(t2.col1) t1
on 1=1
;

select t2.col1,t2.col2,t1.col2
from tab2 t2 left join fun1(t2.col1) t1
on 1=1
;

(2019)

drop table tab1;
drop table tab2;

create table tab1(col1 int,col2 varchar(10));
create table tab2(col1 int,col2 varchar(10));


declare @i integer;
set @i = 1;
while @i <= 5
begin
insert into tab1 values(@i,char(64+@i));
insert into tab1 values(@i,char(64+@i));
set @i = @i + 1;
end
set @i = 1;
while @i <= 10
begin
insert into tab2 values(@i,char(96+@i));
set @i = @i + 1;
end

 

select * from tab1;
select * from tab2;


create or alter function fun1(@p1 int)
returns table
as
return
(
select col1,col2 from tab1 where col1 = @p1
);

select * from fun1(1);

select t2.col1,t2.col2,t1.col2
from tab2 t2 cross apply fun1(t2.col1) t1
;

select t2.col1,t2.col2,t1.col2
from tab2 t2 outer apply fun1(t2.col1) t1
;