create or replace package body PKG_ATF as
procedure GetZYWardList(s_date in varchar2, --开始时间
e_date in varchar2, --结束时间
group_no in varchar2, --库房编号
page_type in varchar2, --处方类型列表
b_data out r_recordset --返回的数据集
) is
begin
open b_data for
select distinct --a.ordered_by as "编码"
b.ward_code as "编码",b.ward_name as "病区名称"
from drug_dispense_rec a,view_his_ward_dict b
where a.ordered_by = b.dept_code
--and c.ward_code = b.dept_code
and a.dispensing_date_time >=to_date(s_date,'yyyy-mm-dd')
and a.dispensing_date_time <=to_date(e_date,'yyyy-mm-dd')
and a.dispensary = '0803' --库房编号
--and a.CHARGE_INDICATOR='1' --已经计价
order by b.ward_name;
end GetZYWardList;
一天的数据量也就4000-5000条,但是取数据时很慢,通过sql2008通过oracle客户端访问oracle的视图取数据
单从语句上看,日期写成between and是否好点?
用普通视图有个缺点,来源表里建的索引失去作用了,如果来源表里的数据量大,建议不用视图直接取表,或者把视图改成物化视图,充分利用出索引的优点
视图里不要出现order by,在视图外order by
create index for ward_code column in table DEPT_VS_WARD & dept_code column in table DEPT_DICT;
2, create index for ordered_by column in table drug_dispense_rec;
3, create or replace package body PKG_ATF as
procedure GetZYWardList(s_date in varchar2, --开始时间
e_date in varchar2, --结束时间
group_no in varchar2, --库房编号
page_type in varchar2, --处方类型列表
b_data out r_recordset --返回的数据集
) is
begin
open b_data for
select distinct --a.ordered_by as "编码"
b.ward_code as "编码",replace(c.dept_name,'护理单元','') as "病区名称"
from drug_dispense_rec a,DEPT_VS_WARD b,DEPT_DICT c
where a.ordered_by = b.dept_code
and c.ward_code = b.dept_code
and a.dispensing_date_time >=to_date(s_date,'yyyy-mm-dd')
and a.dispensing_date_time <=to_date(e_date,'yyyy-mm-dd')
and a.dispensary = '0803' --库房编号
--and a.CHARGE_INDICATOR='1' --已经计价
order by b.ward_name;
end GetZYWardList;