%ROWTYPE

• 复制
  复制成功

-- 9 rowtype as parameter
set enable_oracle_compatible to on;

drop table if exists t_rowtype_20221111_1;
create table t_rowtype_20221111_1(a int, b int);
insert into t_rowtype_20221111_1 values(generate_series(1, 10), generate_series(1, 10));

create table t_rowtype_20221111_2(a int);
insert into t_rowtype_20221111_2 values(generate_series(1, 10));

create table t_rowtype_20221111_3(a text, b text);
insert into t_rowtype_20221111_3 values(generate_series(1, 10), generate_series(1, 10));

drop table if exists t_rowtype_20221111_4;
drop view if exists tv_rowtype_20221111_1;
drop table if exists t_rowtype_20221111_5;
create table t_rowtype_20221111_4(a int, b varchar2(50), c number);
create or replace view tv_rowtype_20221111_1 as select a, b from t_rowtype_20221111_4;
insert into t_rowtype_20221111_4 values(generate_series(1, 10), chr(generate_series(1,10) + 96), generate_series(1, 10));
create table t_rowtype_20221111_5(key1 int, des1 varchar2(50));

drop function if exists f2f_rowtype_20221111_1;
create or replace function f2f_rowtype_20221111_1(e int, b t_rowtype_20221111_4%rowtype) return int is
res t_rowtype_20221111_4%rowtype;
begin
res := b;
select * into res from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e;
insert into t_rowtype_20221111_5 values(res.a, res.b);
return b.b;
end;
/

drop function if exists p2p_rowtype_20221111_1;
create or replace procedure p2p_rowtype_20221111_1(e int, b t_rowtype_20221111_4%rowtype) is
res t_rowtype_20221111_4%rowtype;
begin
res := b;
select * into res from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e;
insert into t_rowtype_20221111_5 values(res.a, res.b);
end;
/

drop function if exists p4p;
create or replace procedure p4p(e1 int, e2 int, f out t_rowtype_20221111_4%rowtype, g IN OUT t_rowtype_20221111_4%rowtype) is
res t_rowtype_20221111_4%rowtype;
begin
res := g;
insert into t_rowtype_20221111_5 values(res.a, 'inout :' || res.b);
select * into res from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e1;
f := res;
select * into res from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e2;
g := res;
end;
/

create or replace package pkg_rowtype_20221111_1 is
dft_rt t_rowtype_20221111_4%rowtype;
end;
/

create or replace package pkg_rowtype_20221111_2 is
dft_rt t_rowtype_20221111_4%rowtype;
CURSOR c1 is select * from t_rowtype_20221111_4;
dft_rt1 c1%rowtype;
function pkg_f_rowtype_20221111_3(a int, b c1%rowtype) return c1%rowtype;
end;
/

create or replace package body pkg_rowtype_20221111_2 is
function pkg_f_rowtype_20221111_3(a int, b c1%rowtype) return c1%rowtype is
val c1%rowtype;
begin
val := b;
select * into val from t_rowtype_20221111_4;
return val;
end;
end;
/

begin
select * into pkg_rowtype_20221111_2.dft_rt1 from t_rowtype_20221111_4;
end;
/

insert into t_rowtype_20221111_4 values(1, 'pkg_f', 2);

declare
v1 pkg_rowtype_20221111_2.c1%rowtype;
v2 pkg_rowtype_20221111_2.c1%rowtype;
a int := 3;
begin
v1 := pkg_rowtype_20221111_2.pkg_f_rowtype_20221111_3(a, v2);
end;
/

drop function if exists p3p_rowtype_20221111_2;
-- pl/sql not support using package var as default parameter
-- create or replace procedure p3p_rowtype_20221111_2(e int, a t_rowtype_20221111_4%rowtype default pkg_rowtype_20221111_1.dft_rt, b t_rowtype_20221111_4%rowtype default null) is
create or replace procedure p3p_rowtype_20221111_2(e int, b t_rowtype_20221111_4%rowtype default null) is
buf t_rowtype_20221111_4%rowtype;
begin
if b is null then
select * into buf from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e;
insert into t_rowtype_20221111_5 values(buf.a, 'null test, defualt val:' || buf.b);
else 
insert into t_rowtype_20221111_5 values(-1, 'not support null test of record type');
end if;
end;
/

declare
p1 t_rowtype_20221111_4%rowtype;
p2 t_rowtype_20221111_4%rowtype;
res int;
begin
res := f2f_rowtype_20221111_1(5, p1);
call p2p_rowtype_20221111_1(3, p1);
p2.a := 12;
p2.b := 'z';
p2.c := 12;
call p4p(6,7,p1,p2);
insert into t_rowtype_20221111_5 values(p1.a, 'OUT: ' || p1.b);
insert into t_rowtype_20221111_5 values(p2.a, 'OUT: ' || p2.b);
pkg_rowtype_20221111_1.dft_rt.a := 13;
pkg_rowtype_20221111_1.dft_rt.b := 'x';
pkg_rowtype_20221111_1.dft_rt.c := 13;
call p3p_rowtype_20221111_2(8);
end;
/
select * from t_rowtype_20221111_5 order by key1, des1;

-- 10 rowtype as return
drop function if exists f3f_rowtype_20221111_2;
create or replace function f3f_rowtype_20221111_2(e int, b int, c int) return t_rowtype_20221111_4%rowtype is
res t_rowtype_20221111_4%rowtype;
begin
select * into res from t_rowtype_20221111_4 where t_rowtype_20221111_4.a = e;
return res;
end;
/
declare
p1 t_rowtype_20221111_4%rowtype;
begin
p1 := f3f_rowtype_20221111_2(6, 2, 2);
insert into t_rowtype_20221111_5 values(p1.a, p1.b);
end;

/
select * from t_rowtype_20221111_5 order by key1, des1;