oracle存储过程,游标及循环应用实例

最近在项目中做了一个关于系统与系统之间数据同步的业务,之前使用jndi远程调用的方式实现了这个同步功能,但总是感觉速度很慢,而且很不稳定,现将其修改成dblink+存储过程方式,特对存储过程及游标做了相应学习,此处主要记录游标及循环的不同使用方法:

 

第一种方式: FOR ... IN .....LOOP....END LOOP

CREATE OR REPLACE PROCEDURE synchronous_material
 IS  
     last_version VARCHAR2(14);  --------此处注意,别忘了长度的定义
     sync_row GZB_EPMS_material%ROWTYPE;    ---用这种方式,此处的sync_row也可不定义
  BEGIN
     SELECT last_SYNCHRONOUS_TIME INTO last_version FROM (SELECT (to_char(SYNCHRONOUS_TIME,'yyyymmddhhmiss')) AS last_SYNCHRONOUS_TIME FROM ... ORDER BY SYNCHRONOUS_TIME DESC) WHERE ROWNUM <= 1;

-----此处本来应该加EXCEPTION WHEN DATA_NOT_FOUND THEN ...;但我的这个业务不会出现找不到数据,所以省略了

IN (     ----此处的sync_row迭代变量可以不用事先在IS中定义
       SELECT ......                                            
     FROM ... WHERE OBJVERSION > last_version AND  OBJVERSION <= to_char(SYSDATE,'yyyymmddhhmiss')
     UNION
     SELECT .....     FROM ....WHERE OBJVERSION > last_version AND  OBJVERSION <= to_char(SYSDATE,'yyyymmddhhmiss')
     ) LOOP
    
       NULL;  ---此处是语句体,省略;
       
     END LOOP;
     RETURN;   
 END synchronous_material;

 

第二种方式:CURSOR

    1,CURSOR + WHILE...LOOP...END LOOP

 

         CREATE OR REPLACE PROCEDURE synchronous_material
         IS  

         last_version VARCHAR2(14); 

         CURSOR cur_sync(begin_objversion VARCHAR2,end_objversion VARCHAR2)
          IS
                   SELECT  .....  FROM ......WHERE OBJVERSION > begin_objversion AND   

            OBJVERSION <= end_objversion
             UNION
             SELECT ....FROM ......WHERE OBJVERSION > begin_objversion AND 

             OBJVERSION <=   end_objversion;    
     

        BEGIN
              SELECT last_SYNCHRONOUS_TIME INTO last_version FROM

              (SELECT (to_char (SYNCHRONOUS_TIME,'yyyymmddhhmiss'))

              AS last_SYNCHRONOUS_TIME FROM ...... ORDER BY SYNCHRONOUS_TIME DESC)

              WHERE ROWNUM <= 1;

        OPEN  cur_sync(last_version,to_char(SYSDATE,'yyyymmddhhmiss')); 
             WHILE cur_sync%FOUND LOOP
                     NULL;
             END LOOP;    
         LOSE cur_sync;
 END synchronous_material;

 

2,CURSOR +FOR 变量名 IN 游标名 LOOP....END LOOP

 

CREATE OR REPLACE PROCEDURE synchronous_material
         IS  

         last_version VARCHAR2(14); 

         CURSOR cur_sync(begin_objversion VARCHAR2,end_objversion VARCHAR2)
          IS
                   SELECT  .....  FROM ......WHERE OBJVERSION > begin_objversion AND   

            OBJVERSION <= end_objversion
             UNION
             SELECT ....FROM ......WHERE OBJVERSION > begin_objversion AND 

             OBJVERSION <=   end_objversion;       
    

sync_row GZB_EPMS_material%ROWTYPE;
 BEGIN
     SELECT last_SYNCHRONOUS_TIME INTO last_version FROM

              (SELECT (to_char (SYNCHRONOUS_TIME,'yyyymmddhhmiss'))

              AS last_SYNCHRONOUS_TIME FROM ...... ORDER BY SYNCHRONOUS_TIME DESC)

              WHERE ROWNUM <= 1;

 

     OPEN  cur_sync(last_version,to_char(SYSDATE,'yyyymmddhhmiss'));  
    
     FOR sync_row IN cur_sync(last_version,to_char(SYSDATE,'yyyymmddhhmiss')) LOOP
      NULL;
     END LOOP;
    CLOSE cur_sync;
 END synchronous_pm_t_material;