`
wuhuizhong
  • 浏览: 681148 次
  • 性别: Icon_minigender_1
  • 来自: 中山
社区版块
存档分类
最新评论

Sqltype Table of Records - Operations and Java

 
阅读更多

You Asked

Hello Sir,
          I want to pass a Sqltype table of records to Java.
1)How to do populate values for such a record in plsql.
say from a select statement.
2) How to insert values in a table from such a table of records
3) How will java interpret that.

I see some examples on your site about passing a scalar sqltype like table of numbers or 
table of varchar But no example on passing table of records to Java.
Can u pls give one 

and we said...

I've never done a collection of objects in java -- and I won't be :)

I would use result sets -- period.  Least amount of code on all parts.  Consider:

create or replace type myScalarType as object
( x int,
  y varchar2(20),
  z date
)
/
create or replace type myTableType as table of myScalarType
/


create or replace function non_pipelined( p_like in varchar2 )
return myTableType
as
    l_data myTableType;
begin

    select myScalarType( user_id, username, created  )
      BULK COLLECT into l_data
      from all_users
     where username like p_like;

    return l_data;
end;
/

create or replace function is_pipelined( p_like in varchar2 )
return myTableType
pipelined
as
begin
    for x in ( select myScalarType( user_id, username, created  ) y
                 from all_users
                where username like p_like )
    loop
        pipe row(x.y);
    end loop;
    return;
end;
/

select * from TABLE( non_pipelined( '%A%' ) );
select * from TABLE( is_pipelined( '%A%' ) );


Just will just run the QUERY to run the procedure and a ResultSet to manipulate the 
output. 

 

you have a record.

it has three components.

it'll have three components today, tomorrow, forever (unless you drop and recreate it).

Just like a table -- if you have a table with three columns -- you have, well, three columns.


So, you could:

  6      select myScalarType( user_id, username, NULL  )
  7        BULK COLLECT into l_data

and then the person querying that would query

select x, y from TABLE.....


or you could:

  6      for x in ( select myScalarType( user_id, username, NULL  ) y
  7                   from all_users
  8                  where username like p_like )
  9      loop
 10          pipe row(x.y);
 11      end loop;



or you could create yet another type that has your two attributes. 

分享到:
评论

相关推荐

    微软内部资料-SQL性能优化3

    Another type of table lock is a schema stability lock (Sch-S) and is compatible with all table locks except the schema modification lock (Sch-M). The schema modification lock (Sch-M) is incompatible ...

    EhLib 6.3 Build 6.3.176 Russian version. Full source included.

    This component is intended for totaling sums and amounts of records in a TDataSet with dynamic changes. Component keeps a list of TDBSum objects, which contains types of group operations (goSum or ...

    EhLib 8.0 Build 8.0.023 Pro Edition FullSource for D7-XE8

    This component is intended for totaling sums and amounts of records in a TDataSet with dynamic changes. Component keeps a list of TDBSum objects, which contains types of group operations (goSum or ...

    ehlib_vcl_src_9_3.26

    This component is intended for totaling sums and amounts of records in a TDataSet with dynamic changes. Component keeps a list of TDBSum objects, which contains types of group operations (goSum or ...

    EhLib5.0.13 最新的ehlib源码

    This component is intended for totaling sums and amounts of records in a TDataSet with dynamic changes. Component keeps a list of TDBSum objects, which contains types of group operations (goSum or ...

    EhLib 9.1.024

    This component is intended for totaling sums and amounts of records in a TDataSet with dynamic changes. Component keeps a list of TDBSum objects, which contains types of group operations (goSum or ...

    微软内部资料-SQL性能优化5

    If there is no clustered index, there is a sysindexes row for the table with an indid value of 0, and that row will keep track of the address of the first IAM for the table. The IAM is a giant bitmap...

    rx第三方控件

    TABLE OF CONTENTS ----------------- Latest Changes Overview History License Agreement Installation Demonstration Programs Source Files Using GIF Images Copyright Notes NEW FOR VERSION 2.75 ---------...

    RxLib控件包内含RxGIF,全部源码及DEMO

    This version is the result of long unactivity of RX Library authors and some imperfections and bugs of other RX adaptations to Delphi 6. The authors of this version disclaim all warranties as to ...

    php.ini-development

    of the INI constants (On, Off, True, False, Yes, No and None) or an expression ; (e.g. E_ALL & ~E_NOTICE), a quoted string ("bar"), or a reference to a ; previously set variable or directive (e.g. ${...

    BobBuilder_app

    or log base k of N, now for the typical values of k which are above 200 for example the b+tree should outperform any binary tree because it will use less operations. However I have found the following...

    arcgis工具

    当查询ArcInfo coverages, shape文件, INFO表以及dBASE表时,SQL表达式中的字段名必须用双引号扩起。如:“AREA”,如果查询的是个人地理数据库数据,则需要将字段名包含在方括号内,如:[AREA],如果查询的是ArcSDE...

    Doctrine ORM for PHP.pdf

    Table of Contents Introduction....................................................................................................13 Code Examples.........................................................

Global site tag (gtag.js) - Google Analytics