不管是sql server 还是 oracle都提供了游标,其一般用途就是从查询结构中遍历数据。游标很好用,许多初学oracle或者sql server的程序员都喜欢用,然而如果滥用游标的话,对程序的性能会造成很大的影响。我认为:除非不得已,不要使用游标。下面是我的一次优化经历。
问题:
在我们的系统中,需要管理产品模型,对PDM了解的人知道,其实产品模型可以当作一个特殊的零部件来处理,所以在数据表的时候产品模型与零部件使用相同的数据表来存放数据,只不过是用一个flag来标示是零部件还是产品模型。产品模型具有版本。现在的要求的是查出最新的所有的零部件信息,查询的时候允许用户自定义查询条件。
用户自定义查询条件是从界面上通过各种选项来构造的,因此传递的是动态SQL语句,为了提高效率数据库上建立了一个存储过程专门来查询产品模型。下面是一个初学oracle不久的编程人员编写的存储过程:
1
PROCEDURE
GETPRODMODELBYSQL(p_sql
IN
VARCHAR2
,
2
cur_mi_prodmodel OUT SYS_BASE.BASECURSOR)
AS
3
sqlstr
varchar2
(
1000
);
4
idstr
varchar2
(
1000
);
5
6
7
dis_id
number
;
8
maxver
number
;
9
cursor
disid_curs
is
10
select
distinct
pt_id
11
from
mi_part
12
where
pt_flag
=
2
;
13
BEGIN
14
open
disid_curs;
15
loop
16
fetch
disid_curs
17
into
dis_id;
18
exit
when
disid_curs
%
notfound;
19
20
select
max
(pv_id)
21
into
maxver
22
from
mi_partver
23
where
pv_partid
=
dis_id;
24
25
if
maxver
is
null
then
26
idstr :
=
idstr;
27
else
28
idstr :
=
idstr
||
maxver
||
'
,
'
;
29
end
if
;
30
end
loop;
31![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
idstr :
=
rtrim
(idstr,
'
,
'
);
33
idstr :
=
ltrim
(idstr,
'
,
'
);
34
if
idstr
is
null
then
35
sqlstr :
=
p_sql
||
'
ORDER BY PV_PRODUCTID,PT_PARTNUMBER
'
;
36
else
37
sqlstr :
=
p_sql
||
'
and MI_PARTVER.pv_id in (
'
||
idstr
||
'
) ORDER BY PV_PRODUCTID,PT_PARTNUMBER
'
;
38
end
if
;
39![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
open
cur_mi_prodmodel
for
sqlstr;
41
END
GETPRODMODELBYSQL;
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这个存储过程可以正常工作,但是其速度执行起来非常慢,如果数据表中有很多数据的话,其速度很可能是直线下降,究其原因是因为使用游标来遍历然后构造查询语句。对这个存储过程进行优化:
1
PROCEDURE
GETPRODMODELBYSQL(p_sql
IN
VARCHAR2
,
2
cur_mi_prodmodel OUT SYS_BASE.BASECURSOR)
AS
3
s
varchar2
(
300
);
4
sqlstr
varchar2
(
1000
);
5![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*idstr varchar2(1000);
6
7
8
dis_id number;
9
maxver number;
10
cursor disid_curs is
11
select distinct pt_id
12
from mi_part
13
where pt_flag = 2; */
14
BEGIN
15![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*open disid_curs;
16
loop
17
fetch disid_curs
18
into dis_id;
19
exit when disid_curs% notfound;
20
21
select max(pv_id)
22
into maxver
23
from mi_partver
24
where pv_partid = dis_id;
25
26
if maxver is null then
27
idstr := idstr;
28
else
29
idstr := idstr || maxver || ',';
30
end if;
31
end loop;*/
32
s:
=
'
and MI_PARTVER.pv_id in (select a.pv_id from mi_partver a,mi_part c where a.pv_partid = c.pt_id and c.pt_flag =2 and
33
a.pv_id in (select e.pv_id from mi_partver e
34
where e.pv_id in (select max(b.pv_id) from mi_partver b where e.pv_partid = b.pv_partid)))
'
;
35![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*idstr := rtrim(idstr, ',');
36
idstr := ltrim(idstr, ',');
37
if idstr is null then
38
sqlstr := p_sql || ' ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
39
else
40
sqlstr := p_sql || ' and MI_PARTVER.pv_id in (' || idstr || ') ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
41
end if;*/
42
sqlstr :
=
p_sql
||
s
||
'
ORDER BY PV_PRODUCTID,PT_PARTNUMBER
'
;
43
open
cur_mi_prodmodel
for
sqlstr;
44
END
GETPRODMODELBYSQL;
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
6
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
14
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
16
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
32
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
36
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
42
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这里的有效语句是使用查询语句来构造查询,其结果是速度得到了数量级的提高,而且避免了一个隐患就是缓冲区溢出,因为原来的存储过程中定义了几个varchar2(1000)的变量,实际上这个变量的上界是不可以预期的,而缓冲区溢出也是很多初学oracle的程序员经常忽略甚至想不到的问题。