You need to perform data analysis between different records on the Oracle query results. You can find a syntax similar to a programming language on the Internet.
DECLARE
// Declare Variables
BEGIN
// Specific operation
If then
//..
End if;
Loop
//..
End Loop;
END;
Specifically
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; -- get the data of the current record
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;
It can be programmed to operate the record set like other languages such as c ++. After one afternoon, statistics on different records of the result set are finally realized. later I found out that this is called the Stored Procedure Haha. The original SQL can also be programmed.