写了一个过程,按传入的参数作为条件检索数据,并把数插入到一个表中,如果传入参数执行时间为60秒,如果我把参数的值写在过程中,不传参数,执行时只有0.6秒,找了半天找不到原因,请大侠赐教,急急呀!!!!!
解决方案 »
- 能否用变量来代替一段语句
- 麻烦 问个sql语句的写法
- oracle8.1.7灾难恢复
- oracle Enteprise manager 10 是b/s方式吗?
- 我用10g的Enterprise Manager Console连接数据库,说没有监听程序,我并没有建立数据库。。。
- 端口问题!?为什么adsl的客户端不能访问内网的oracle服务器?高分有赠!
- 我好象配置了Net Configuration Assistant后,SQLPLUS就连接不上了,说ERROR:ORA-12560: TNS: 协议适配器错误该怎么办?
- 请教oracle中的错误号的意思---1403,100,1405????
- 在windowsme中安装ORC8.16怎么就是装不了啊?有的装好了用不了,是怎么回事啊?
- 谁有oracle的各个系统包的中文参考资料!
- 请教一个sql语句
- 在oracle的sql表中怎样增加数据
带参数过程:
CREATE OR REPLACE Procedure p_select_chart_for_po(as_sup_id in varchar2,
as_org_id in varchar2,
as_pro_chart in varchar2,
as_wh_id in varchar2,
as_buyer_group in varchar2,
as_pro_classid in varchar2) as
cursor c_chart is
SELECT 0 a,
PRO_ID,
pro_chart,
PRO_HELP_CODE,
PRO_CLASSID,
PRO_NAME,
PRO_ENNAME,
pro_spec,
pro_sup_id,
PRO_INTAX,
PRO_OUTTAX,
PRO_INPRICE,
PRO_SALEPRICE,
PRO_COST_PRICE,
pro_factory,
0 qty,
nvl(a.sum1,0) sum1,
nvl(a.sum2,0) sum2,
SPT_PACK
FROM buyer_group,
buyer_group_product,
product_class,
PRODUCT,
supplier_product,
(select distinct
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail
where mssd_org_id = as_org_id and
mssd_wh_id = as_wh_id and
substr(mssd_wh_location,1,2) in ('05','06','07') and
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = as_org_id and
wl_virtual_flag = '0' and
wl_id = as_wh_id)
group by mssd_pro_id) a
where pro_chart like nvl(as_pro_chart,pro_chart) and
spt_sup_id = as_sup_id and
spt_org_id = as_org_id and
spt_pro_id = pro_id and
pro_classid = pc_code and
bgp_id like as_buyer_group and
spt_org_id = bgp_org_id and
bgp_id = bgpp_id and
bgp_org_id = bgpp_org_id and
(case when bgpp_product_type = '1' then pro_classid else pro_id end ) = bgpp_pro_id and
spt_org_id = as_org_id and
pro_id = a.mssd_pro_id_sum(+) and
spt_status = '1' and
pro_classid = nvl(as_pro_classid,pro_classid);
BEGIN
dbms_output.put_line(to_char(sysdate));
for r_c_chart in c_chart loop
insert into temp_for_po
values (r_c_chart.a,
r_c_chart.PRO_ID,
r_c_chart.pro_chart,
r_c_chart.PRO_HELP_CODE,
r_c_chart.PRO_CLASSID,
r_c_chart.PRO_NAME,
r_c_chart.PRO_ENNAME,
r_c_chart.pro_spec,
r_c_chart.pro_sup_id,
r_c_chart.PRO_INTAX,
r_c_chart.PRO_OUTTAX,
r_c_chart.PRO_INPRICE,
r_c_chart.PRO_SALEPRICE,
r_c_chart.PRO_COST_PRICE,
r_c_chart.pro_factory,
r_c_chart.qty,
r_c_chart.sum1,
r_c_chart.sum2,
r_c_chart.SPT_PACK);
end loop;
END p_select_chart_for_po;直接写值,不传参数
CREATE OR REPLACE Procedure p_select_chart_for_polll as
cursor c_chart is
SELECT 0 a,
PRO_ID,
pro_chart,
PRO_HELP_CODE,
PRO_CLASSID,
PRO_NAME,
PRO_ENNAME,
pro_spec,
pro_sup_id,
PRO_INTAX,
PRO_OUTTAX,
PRO_INPRICE,
PRO_SALEPRICE,
PRO_COST_PRICE,
pro_factory,
0 qty,
nvl(a.sum1,0) sum1,
nvl(a.sum2,0) sum2,
SPT_PACK
FROM buyer_group,
buyer_group_product,
product_class,
PRODUCT,
supplier_product,
(select distinct
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail
where mssd_org_id = '2407' and
mssd_wh_id = '2407' and
substr(mssd_wh_location,1,2) in ('05','06','07') and
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = '2407' and
wl_virtual_flag = '0' and
wl_id = '2407')
group by mssd_pro_id) a
where pro_chart like nvl('%150-10040%',pro_chart) and
spt_sup_id = '2219' and
spt_org_id = '2407' and
spt_pro_id = pro_id and
pro_classid = pc_code and
bgp_id like '000001' and
spt_org_id = bgp_org_id and
bgp_id = bgpp_id and
bgp_org_id = bgpp_org_id and
(case when bgpp_product_type = '1' then pro_classid else pro_id end ) = bgpp_pro_id and
spt_org_id = '2407' and
pro_id = a.mssd_pro_id_sum(+) and
spt_status = '1' and
pro_classid = nvl(null,pro_classid);
BEGIN
for r_c_chart in c_chart loop
insert into temp_for_po
values (r_c_chart.a,
r_c_chart.PRO_ID,
r_c_chart.pro_chart,
r_c_chart.PRO_HELP_CODE,
r_c_chart.PRO_CLASSID,
r_c_chart.PRO_NAME,
r_c_chart.PRO_ENNAME,
r_c_chart.pro_spec,
r_c_chart.pro_sup_id,
r_c_chart.PRO_INTAX,
r_c_chart.PRO_OUTTAX,
r_c_chart.PRO_INPRICE,
r_c_chart.PRO_SALEPRICE,
r_c_chart.PRO_COST_PRICE,
r_c_chart.pro_factory,
r_c_chart.qty,
r_c_chart.sum1,
r_c_chart.sum2,
r_c_chart.SPT_PACK);
end loop;
END p_select_chart_for_polll;
insert into temp_for_po
SELECT 0 a,
PRO_ID,
pro_chart,
PRO_HELP_CODE,
PRO_CLASSID,
PRO_NAME,
PRO_ENNAME,
pro_spec,
pro_sup_id,
PRO_INTAX,
PRO_OUTTAX,
PRO_INPRICE,
PRO_SALEPRICE,
PRO_COST_PRICE,
pro_factory,
0 qty,
nvl(a.sum1,0) sum1,
nvl(a.sum2,0) sum2,
SPT_PACK
FROM buyer_group,
buyer_group_product,
product_class,
PRODUCT,
supplier_product,
(select distinct
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail
where mssd_org_id = '2407' and
mssd_wh_id = '2407' and
substr(mssd_wh_location,1,2) in ('05','06','07') and
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = '2407' and
wl_virtual_flag = '0' and
wl_id = '2407')
group by mssd_pro_id) a
where pro_chart like nvl('%150-10040%',pro_chart) and
spt_sup_id = '2219' and
spt_org_id = '2407' and
spt_pro_id = pro_id and
pro_classid = pc_code and
bgp_id like '000001' and
spt_org_id = bgp_org_id and
bgp_id = bgpp_id and
bgp_org_id = bgpp_org_id and
(case when bgpp_product_type = '1' then pro_classid else pro_id end ) = bgpp_pro_id and
spt_org_id = '2407' and
pro_id = a.mssd_pro_id_sum(+) and
spt_status = '1' and
pro_classid = nvl(null,pro_classid);
这样不就行了
insert into temp_for_po
SELECT 0 a,
PRO_ID,
pro_chart,
PRO_HELP_CODE,
PRO_CLASSID,
PRO_NAME,
PRO_ENNAME,
pro_spec,
pro_sup_id,
PRO_INTAX,
PRO_OUTTAX,
PRO_INPRICE,
PRO_SALEPRICE,
PRO_COST_PRICE,
pro_factory,
0 qty,
nvl(a.sum1,0) sum1,
nvl(a.sum2,0) sum2,
SPT_PACK
FROM buyer_group,
buyer_group_product,
product_class,
PRODUCT,
supplier_product,
(select distinct
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail
where mssd_org_id = as_org_id and
mssd_wh_id = as_wh_id and
substr(mssd_wh_location,1,2) in ('05','06','07') and
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = as_org_id and
wl_virtual_flag = '0' and
wl_id = as_wh_id)
group by mssd_pro_id) a
where pro_chart like nvl(as_pro_chart,pro_chart) and
spt_sup_id = as_sup_id and
spt_org_id = as_org_id and
spt_pro_id = pro_id and
pro_classid = pc_code and
bgp_id like as_buyer_group and
spt_org_id = bgp_org_id and
bgp_id = bgpp_id and
bgp_org_id = bgpp_org_id and
(case when bgpp_product_type = '1' then pro_classid else pro_id end ) = bgpp_pro_id and
spt_org_id = as_org_id and
pro_id = a.mssd_pro_id_sum(+) and
spt_status = '1' and
pro_classid = nvl(as_pro_classid,pro_classid); 还有,你的select 有许多地方可以优化的,写的不好
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail
where mssd_org_id = as_org_id and
mssd_wh_id = as_wh_id and
substr(mssd_wh_location,1,2) in ('05','06','07') and
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = as_org_id and
wl_virtual_flag = '0' and
wl_id = as_wh_id)
group by mssd_pro_id) a
这句里面 distinct 不需要,有了group by 了,不会出现重复的了
mssd_wh_location in (select wl_location
from warehouse_location
where wl_org_id = as_org_id and
wl_virtual_flag = '0' and
wl_id = as_wh_id)
这句可以改成exists
(select
mssd_pro_id mssd_pro_id_sum,
sum(mssd_on_hand_qty) sum1,
Sum(mssd_wait_out_qty) sum2
from material_storage_sell_detail a
where mssd_org_id = as_org_id and
mssd_wh_id = as_wh_id and
substr(mssd_wh_location,1,2) in ('05','06','07') and
exists (select 1
from warehouse_location
where wl_org_id = as_org_id and
wl_virtual_flag = '0' and
wl_id = as_wh_id
and a.mssd_wh_location=wl_location )
group by mssd_pro_id) a
第一点应该去掉distinct
第二点不能改为exists,因为我还是需要sum1和sum2的量我认为不应该是SQL的问题,因为如果SQL有问题,那么我写死参数时也不可能1秒不到就出来