不管是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
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
40 open cur_mi_prodmodel for sqlstr;
41 END GETPRODMODELBYSQL;
这个存储过程可以正常工作，但是其速度执行起来非常慢，如果数据表中有很多数据的话，其速度很可能是直线下降，究其原因是因为使用游标来遍历然后构造查询语句。对这个存储过程进行优化：
1PROCEDURE GETPRODMODELBYSQL(p_sql IN VARCHAR2,
2 cur_mi_prodmodel OUT SYS_BASE.BASECURSOR) AS
3 s varchar2(300);
4 sqlstr varchar2(1000);
5
/**//*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 /**//*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
33a.pv_id in (select e.pv_id from mi_partver e
34where e.pv_id in (select max(b.pv_id) from mi_partver b where e.pv_partid = b.pv_partid)))';
35 /**//*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;
这里的有效语句是使用查询语句来构造查询，其结果是速度得到了数量级的提高，而且避免了一个隐患就是缓冲区溢出，因为原来的存储过程中定义了几个varchar2(1000)的变量，实际上这个变量的上界是不可以预期的，而缓冲区溢出也是很多初学oracle的程序员经常忽略甚至想不到的问题。
posted @ 2006-12-05 21:06
释子 阅读(2479)
评论(27) 编辑 收藏 网摘 所属分类:
ORACLE技术