ない模様
(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
;
