1创建测试表及数据

1.1创建一个测试表有3个字段

-- Create table
create table A_HQ
(
  f_gp   VARCHAR2(7),
  s_riq  VARCHAR2(10),
  n_span NUMBER(13,4)
)

1.2输入测试数据若干

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-02', 9061.8600);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-03', 9138.4700);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-04', 9201.4300);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-05', 9372.0100);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-06', 9329.9400);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-20', 9135.0800);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-19', 9083.9300);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-18', 9000.4200);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-17', 9068.2600);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-16', 9150.1500);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-13', 9144.6500);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-12', 9188.3900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-11', 9150.3700);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-10', 9374.3000);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-09', 9496.8200);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-27', 9028.1900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-26', 8942.4800);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-25', 8962.4900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-24', 9118.2000);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-23', 9148.5700);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-04', 9273.6800);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-03', 9205.9300);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-11-30', 9042.4700);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-11', 8942.3000);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-10', 9114.0200);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-09', 9138.0900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-08', 9275.1300);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-07', 9268.8500);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-18', 9096.2800);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-17', 9081.0600);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-16', 9039.1000);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-15', 9067.2600);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-14', 8998.2700);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-25', 9343.2100);

insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-24', 9274.8900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-23', 9291.1200);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-22', 9174.7900);
insert into A_HQ (F_GP, S_RIQ, N_SPAN) values ('399005', '2020-12-21', 9292.6800);

2创建行类型和表类型

2.1创建行类型

create or replace type hqing as object
(
       f_gp varchar2(6),
       s_riq varchar2(10),
       n_span number
)

2.2创建表类型

create or replace type hqtable is table of hqing

3创建返回表的函数

3.1创建返回行情表的函数

create or replace function get_price_table (s_date1 in varchar2,e_date2 in varchar2)
return hqtable is
hqt hqtable := hqtable();
begin
   select hqing(f_gp,s_riq,n_span) bulk collect into hqt
       from a_hq
       where s_riq between s_date1 and e_date2 
       order by s_riq;
           
   return hqt;
end get_price_table;

3.2调用函数

select * from table(get_price_table('2020-12-01','2020-12-25'))

3.3返回结果

399005  2020-12-03  9205.93
399005  2020-12-04  9273.68
399005  2020-12-07  9268.85
399005  2020-12-08  9275.13
399005  2020-12-09  9138.09
399005  2020-12-10  9114.02
399005  2020-12-11  8942.3
399005  2020-12-14  8998.27
399005  2020-12-15  9067.26
399005  2020-12-16  9039.1
399005  2020-12-17  9081.06
399005  2020-12-18  9096.28
399005  2020-12-21  9292.68
399005  2020-12-22  9174.79
399005  2020-12-23  9291.12
399005  2020-12-24  9274.89
399005  2020-12-25  9343.21