除非迫不得已不要用游标

        不管是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;

这个存储过程可以正常工作，但是其速度执行起来非常慢，如果数据表中有很多数据的话，其速度很可能是直线下降，究其原因是因为使用游标来遍历然后构造查询语句。对这个存储过程进行优化：

 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     /**/ /*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
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            /**/ /*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的程序员经常忽略甚至想不到的问题。