ORACLE 存储过程 遍历查询记录

             需要对查询结果进行不同记录间的 数据分析, 网上找了下, 找到一种类似编程语言的语法

    DECLARE
        //声明变量
           
    BEGIN
         //具体操作
         if then
             //..
         end if;
         
         loop
             //..
         end Loop;
    END;

   

具体为

DECLARE
       TYPE c1 IS REF CURSOR;
       dateLast date ;
       dateNow date ;
       cBQHM t_rfid_passcar_i.Bqhm%TYPE;-- BQHM
       cLastBQHM t_rfid_passcar_i.Bqhm%TYPE;-- BQHM
       
       bHaveLast BOOLEAN := FALSE;
       temp_cursor c1;
       iContrast NUMBER(10) := 0;
       iIndex NUMBER(10) := 0;
       
BEGIN
     OPEN temp_cursor
          FOR
           SELECT BQHM, JRSJ FRom t_rfid_passcar_i where BQHM IN  
                  (select BQHM from t_rfid_passcar_i group by BQHM having count(*)>1)
                    order by "BQHM" ASC, "JRSJ" ASC;
     LOOP
         FETCH temp_cursor INTO cBQHM, dateNow;--获得当前记录的数据
         EXIT WHEN temp_cursor%NOTFOUND;
         IF bHaveLast THEN
            BEGIN
                 iContrast := ROUND(to_number(dateNow - dateLast) * 24 * 60 * 60) ;
                 IF cLastBQHM = cBQHM THEN
                   IF iContrast < (60 * 20) THEN
                      iIndex := iIndex + 1;
                      dbms_output.put_line(iIndex || '  ' || cBQHM || '  ' || iContrast || '  ' || dateLast || '  '|| dateNow || '   ' );
                   END IF;
                 END IF;
            END;
         END IF;    
         --dbms_output.put_line('-----------' || iIndex || cBQHM || '  ' || iContrast || '  ' || dateLast || '  '|| dateNow || '   ' );
         dateLast := dateNow;
         cLastBQHM := cBQHM;
         bHaveLast := TRUE;
         --dbms_output.put_line(to_char(dateNow, 'yyyy-mm-dd hh24:mi:ss'));
     END LOOP;
     CLOSE temp_cursor;
END;

    可以像c++等其他语言那样编程对记录集进行操作,  写了一下午,终于 实现了对结果集 不同记录的统计.  后来才发现这叫存储过程 哈哈, 原来sql也可以编程.

                                                                                                                                                                                                                  2012-7-23 17:35:00