因为开发需求需要,存储多个列的结果集,使用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='北京';