这样的存储过程 还需要什么优化？

Question

/*
  行业匹配 存储过程  
  匹配完成之后 调用发布存储过程 将匹配好的数据发布到t_smentrelatbase表中
*/
create or replace procedure proc_hypp
as
  v_count int := 0; --数组下标
  v_split int := 2;
  v_1 int := 0;
  i int := 0;
  j int := 0;  TYPE type_hyms IS TABLE of varchar2(100) index by BINARY_INTEGER;
  TYPE type_array is TABLE of type_hyms index by BINARY_INTEGER;
  hyms type_array;
  hy type_hyms;
  --行业游标
  CURSOR cur_hy IS SELECT hy,decode(hyjb,null,'0',hyjb) jb,hyms FROM T_SMHY where flag='1' order by jb desc;
  --企业游标
  CURSOR cur_qy IS SELECT qyuid,nvl2(qyms,qymc||' '||qyms,qymc) FROM T_SMHYQY;  
  
  v_hy T_SMHY.HY%TYPE; -- 行业
  v_jb T_SMHY.HYJB%TYPE; --级别
  v_hyms T_SMHY.HYMS%TYPE;--行业描述
  v_hycount int := 0;--行业数量
  v_qy T_SMHYQY.QYMS%TYPE;--企业
  v_qyuid T_SMHYQY.QYUID%TYPE;--企业UID
  v_match_count int := 0; --匹配计数器
  v_cur_count int := 0; 
  
  update_erro EXCEPTION;
 
begin
  select count(*) into v_hycount from T_SMHY where flag='1';
  if (v_hycount=0) then
    return;
  end if;
  
  --打开 游标
  if(cur_hy%ISOPEN) then
     null;
  else
    open cur_hy;
  end if;
        
       loop
            v_count :=v_count+1;
            fetch cur_hy into v_hy,v_jb,v_hyms;
            EXIT WHEN cur_hy%NOTFOUND;
            hyms(v_count)(1) := trim(v_hy);
            v_split := 2;
            --符号替换
            loop
              select instr(v_hyms,',') into v_1 from dual;
              if v_1=0 then
                exit;
              else
                v_hyms := replace(v_hyms,',','、');
              end if;  
            end loop;
            
            loop
              select instr(v_hyms,'，') into v_1 from dual;
              if v_1=0 then
                exit;
              else
                v_hyms := replace(v_hyms,'，','、');
              end if;
            end loop;    
            
            --分割到二维数组 hyms 里面
            loop
              select instr(v_hyms,'、') into v_1 from dual;
              if v_1=0 then
                hyms(v_count)(v_split) := trim(v_hyms); 
                exit;
              else
                hyms(v_count)(v_split) := trim(substr(v_hyms,1,v_1-1));
                v_hyms := substr(v_hyms,v_1+1);                
                v_split := v_split+1;
              end if;
            end loop;
        end loop;        
  --关闭游标      
  if(cur_hy%ISOPEN) then
     close cur_hy;
  else
     null;
  end if; 
      
       -- 匹配
        if(cur_qy%ISOPEN) then
           null;
        else     
           open cur_qy;
        end if;        
        
        loop                             
                fetch cur_qy into v_qyuid,v_qy;
                v_cur_count :=v_cur_count+1;                
                EXIT WHEN cur_qy%NOTFOUND;  --无记录 退出
                          
                    for i in 1..hyms.count loop                   
                      for j in 1..hyms(i).count loop                                                                   
                        select instr(v_qy,hyms(i)(j)) into v_1 from dual;
                        if v_1>0 then                          
                           v_match_count := v_match_count + 1 ;--匹配记录                              
                           update T_SMHYQY set HY=hyms(i)(1),HYMS=hyms(i)(j),SJ=sysdate where QYUID=v_qyuid;     
                          goto end_of_loop;
                        end if;
                      end loop;
                    end loop;               
                    
                <<end_of_loop>>
                    null;
                                    
               if v_cur_count > 10000  or  v_match_count >2000 then --游标移动10000条记录                    
                   v_cur_count:=0;
                   commit;
                end if;              
        end loop;      
        commit;--最后不足1000条记录的数据一次性提交        
        --关闭游标
        if(cur_qy%ISOPEN) then
            close cur_qy;
        else
            null;
        end if;        
        --更新行业表
        
        update T_SMHY set FLAG='0' where FLAG='1';
        commit;
           
        EXCEPTION
               WHEN NO_DATA_FOUND THEN 
                    NULL;
               WHEN update_erro THEN
                    ROLLBACK;
               WHEN OTHERS THEN
                    ROLLBACK;end proc_hypp; 这样的存储过程 还需要什么优化？

Answer 1

符号替换那段
if else不就可以了么。
不一定要用cursor

Answer 2

个人觉得 企业游标没有必要声明因为你的
 loop                              
                fetch cur_qy into v_qyuid,v_qy; 
                v_cur_count :=v_cur_count+1;                 
                EXIT WHEN cur_qy%NOTFOUND;  --无记录 退出 
                           
                    for i in 1..hyms.count loop                    
                      for j in 1..hyms(i).count loop                                                                    
                        select instr(v_qy,hyms(i)(j)) into v_1 from dual; 
                        if v_1>0 then                           
                           v_match_count := v_match_count + 1 ;--匹配记录                               
                           update T_SMHYQY set HY=hyms(i)(1),HYMS=hyms(i)(j),SJ=sysdate where QYUID=v_qyuid;      
                          goto end_of_loop; 
                        end if; 
                      end loop; 
                    end loop;                
                     
                 < <end_of_loop>> 
                    null; 
                                     
               if v_cur_count > 10000  or  v_match_count >2000 then --游标移动10000条记录                     
                   v_cur_count:=0; 
                   commit; 
                end if;               
        end loop;       
        commit;--最后不足1000条记录的数据一次性提交这个循环就是对 T_SMHYQY 进行更新
何不用 megert into table on xx when matched update  when not matched insert into 这样的命令 如果数据量很大的话 你在这个循环体内部update后并没有commit; 因为是单条记录 ^_^ 不过建议数据量很大的情况下 设置一个计数i  if i>10000 then  commit; i=0 end if 这样可以清楚redo缓冲区 
望高手指点 呵呵 楼主最好程序上加注释 耗费劲看着

Answer 3

谢谢楼上两位的建议，呵呵，我补充一下表结构说明：
t_smentrelatbase 匹配之后的企业关联表 ，简称关联表 
T_SMHY 行业表  （HY 行业关联字，不可能有重复值，HYJB对应的行业匹配级别，HYMS对应的行业描述，FLAG 是否匹配的标注，示例内容：
HY     HYMS,                       HYJB        FLAG
餐饮   酒店，酒楼，面馆、米粉、    2     1匹配/0不匹配）
T_SMHYQY 企业表   (QYMC 企业名称,QYMS企业描述,HY 行业,HYMS 行业描述   示例内容：
QYMC          QYMS                        HY      HYMS  
新华大酒店    提供住宿，休闲.......等等   NULL  NULL
) 
匹配 过程
根据企业名称 、企业描述 和行业表中的行业，行业描述 比较，先根据 企业名称&&企业描述 比较行业表中的行业字段，如果能够匹配上，则update T_SMHYQY企业表 ，HY=行业，HYMS=行业，
 如果匹配不上，则匹配行业表中的 HYMS行业描述字段，以"、" 隔开，能够匹配上，则update T_SMHYQY企业表 ，HY=行业（该例子中对应的是餐饮），HYMS=行业描述（酒店），
 
示例中匹配之后的结果：QYMC          QYMS                        HY      HYMS  
新华大酒店    提供住宿，休闲.......等等   餐饮  酒店 匹配过程就是这样一个目的