将一堆字段提取到包中定义的自定义对象中

发布于 2024-10-21 06:57:30 字数 527 浏览 1 评论 0原文

假设我有一个包 A ，

type type_bla is record (id number, ...);

在同一个包主体中我还有一个查询，它获取构造对象所需的所有字段。如果我有一个存储的对象，我可以这样做：

select type_bla(t1.id, t2.foo, t1.bar ...)
into instance_of_type_bla
from table t
inner join table2 t2 ON ...

但是由于我在包中定义了一个自定义类型 - 它没有构造函数，所以我不得不将其更改为：

select t1.id, t2.foo, t1.bar ...
into instance_of_type_bla.id, instance_of_type_bla.foo ...
from table t
inner join table2 t2 ON ...

填充此类对象的更优雅的方式吗？

原文

Let's suppose I have a package A that has

type type_bla is record (id number, ...);

Also in the same package body I have a query, that fetches all the fields needed to construct the object. If I had a stored object I could do:

select type_bla(t1.id, t2.foo, t1.bar ...)
into instance_of_type_bla
from table t
inner join table2 t2 ON ...

But since I have a custom type defined in the package - it has not a constructor, so I obliged to change it to:

select t1.id, t2.foo, t1.bar ...
into instance_of_type_bla.id, instance_of_type_bla.foo ...
from table t
inner join table2 t2 ON ...

Is it more elegant way to fill such sort of objects?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟酒忠诚 2024-10-28 06:57:30

您应该能够简单地将数据直接选择到记录中，就像声明 %ROWTYPE 记录一样。

我将声明一个包 PKG_FOO，其中 GET_REC 函数填充自定义记录

SQL> create or replace package pkg_foo
  2  as
  3    type my_rec is record( col1 number, col2 number, col3 varchar2(10) );
  4    function get_rec
  5      return my_rec;
  6  end;
  7  /

Package created.

SQL> create or replace package body pkg_foo
  2  as
  3    function get_rec
  4      return my_rec
  5    is
  6      l_rec my_rec;
  7    begin
  8      select 1, 2, 'Justin'
  9        into l_rec
 10        from dual;
 11      return l_rec;
 12    end;
 13  end;
 14  /

Package body created.

，只是为了表明它有效

SQL> declare
  2    l_rec pkg_foo.my_rec;
  3  begin
  4    l_rec := pkg_foo.get_rec;
  5    p.l( 'Col1 = ' || l_rec.col1 );
  6    p.l( 'Col2 = ' || l_rec.col2 );
  7    p.l( 'Col3 = ' || l_rec.col3 );
  8  end;
  9  /
Col1 = 1
Col2 = 2
Col3 = Justin

You should be able to simply select the data directly into the record just like you would if you declared a %ROWTYPE record.

I'll declare a package PKG_FOO where the GET_REC function populates the custom record

SQL> create or replace package pkg_foo
  2  as
  3    type my_rec is record( col1 number, col2 number, col3 varchar2(10) );
  4    function get_rec
  5      return my_rec;
  6  end;
  7  /

Package created.

SQL> create or replace package body pkg_foo
  2  as
  3    function get_rec
  4      return my_rec
  5    is
  6      l_rec my_rec;
  7    begin
  8      select 1, 2, 'Justin'
  9        into l_rec
 10        from dual;
 11      return l_rec;
 12    end;
 13  end;
 14  /

Package body created.

And just to show that it works

SQL> declare
  2    l_rec pkg_foo.my_rec;
  3  begin
  4    l_rec := pkg_foo.get_rec;
  5    p.l( 'Col1 = ' || l_rec.col1 );
  6    p.l( 'Col2 = ' || l_rec.col2 );
  7    p.l( 'Col3 = ' || l_rec.col3 );
  8  end;
  9  /
Col1 = 1
Col2 = 2
Col3 = Justin

回复收藏 0 原文

~没有更多了~