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
