table()函数用法

因为开发需求需要，存储多个列的结果集，使用table()可以实现
1、创建type

create or replace type t_yqjjrgstj as object
(
  THSCODE   VARCHAR2(96),
  GSJC      VARCHAR2(120) ,
  SSHY      VARCHAR2(180),
  STOCKCODE VARCHAR2(18) ,
  KGLX      VARCHAR2(360),
  GPLX      VARCHAR2(180),
  SSXQ      VARCHAR2(60),
  H_CODE    VARCHAR2(30),
  --额外数据，挑选条件中需要
  JYSDM     VARCHAR2(36) ,--交易所代码
  EJHY      VARCHAR2(180),--证监会二级分类
  JJKGR     VARCHAR2(360)--实际控股人
)

2、实例化type

create or replace type t_jgb_yqjjrgstj as table of t_yqjjrgstj;

3、创建函数

create or replace function f_gjb_yqjjrgstj(in_date in date default sysdate)
  return t_jgb_yqjjrgstj
  PIPELINED as
  v_test_1 t_jgb_yqjjrgstj := t_jgb_yqjjrgstj();
begin
  for t in (select * from test_yqjbzl) loop
    pipe row(t_yqjjrgstj(t.thscode,
                         t.gsjc,
                         t.sshy,
                         t.stockcode,
                         t.kglx,
                         t.gplx,
                         t.ssxq,
                         t.h_code,
                         t.jysdm,
                         t.ejhy,
                         t.jjkgr));
  end loop;
  return;
end f_gjb_yqjjrgstj;

4、测试

select thscode "同花顺" from table(f_gjb_yqjjrgstj()) where SSXQ='北京';