About Cursor's ROWCOUNT

A few days ago wrote a procedure, the job then invokes it to fetch the data from the Third-party database through Dblink. Finish the manual run once, all the normal data is also taken, but the next day to work, I found strange phenomenon (note job night 1 o'clock), my data disappeared. To this end I also investigated, who moved my data, the result is nobody. The conclusion has only one problem on the procedure.

Procedure mainly has two function 1. Fetching data from a third party database table; 2. After you have finished inserting the data, delete all of the data that you want to duplicate (note that you delete all but not one of the remaining ones), it is this two function that caused my data to be gone. Now put the SQL statement out, analyze

Create or replace procedure Test_proc is

P_lasttime_stamp date;

--the time that the cursor export_time_cur gets the final guide data
Cursor Export_time_cur is ...;

--read data from the point at which the data was last read
Cursor Export_data_cur is .....
where time_stamp> P_lasttime_stamp;
--delete a cursor for a duplicate record
Cursor Repeat_rec_cur
Is ...;

Begin

--Gets the last import time
Open export_time_cur;
If Export_time_cur%rowcont=0 Then

-Inserts a record in the timestamp table that records pull data to record the pull timestamp
Insert INTO ...

Commit
P_lasttime_stamp:=to_date (' 2000-01-01 ', ' yyyy-mm-dd '); --Start data from this date

Else

Fetch export_time_cur into P_lasttime_stamp;
End If;
Close export_time_cur;

--now read the data from P_lasttime_stamp, read it all.
For rec in Export_data_cur loop

--Perform processing and inserting
End Loop;
Commit

--Delete duplicate records
For rec in Repeat_rec_cur loop

--Performing Data deletion
End Loop;
Commit

--Update the last timestamp of the guide data
Update ... commit;

EXCEPTION
When others THEN
Raise_application_error (...);

End
/

This procedure looks perfect, but the problem is mainly in the export_time_cur, the cursor

The%rowcount property is used to return the number of records that have been extracted from the cursor to date.
The value of the%rowcount is 0 before the cursor or cursor variable is opened and the fetch (FETCH) is not performed;
After that, the value of the%rowcount is the number of records extracted from the result set, and the fetch is executed once for each successful
The value of the%rowcount is increased by one.

So now to analyze the procedure I write, I will find the problem, whenever you execute this procedure, its pull data date is

P_lasttime_stamp:=to_date (' 2000-01-01 ', ' yyyy-mm-dd ');

Then pull the data there will be a lot of duplication, if the last execution from the job to the next execution (1 O ' Day next morning), the third party database does not update a record, then the two times the data is exactly the same (now 2011 years), And the back of my procedure is to do one thing is to kill from this time pull the duplicate data, so the mistake, pull over the data will be all killed, So there's not a single piece of data in the table. So know the location of the problem can be modified. So that's the reason why I made a mistake, to remember that the next time can not be repeated, the other attributes of cursor are also listed here: (Here is the table I use the Business database table)

--1. %found Property

/** when a cursor or cursor variable is opened and before the FETCH statement is executed,
%found is null. Then, if the last FETCH statement returns a row of records,
The%found is true if the FETCH statement does not return a record,
The%found is false. Shown in the following example.

*/
--Created on 2011/4/14 by Xiao-cainiao
Declare
P_steward_name varchar2 (20);
Cursor Steward_cur is
Select S.steward_name from steward s where s.steward_id = ' 1194 ';
Begin

Open steward_cur;
If Steward_cur%found is null then
Dbms_output.put_line (' The property%found of the current cursor mycursor is null. ');
End If;
Fetch Steward_cur
into P_steward_name;
If Steward_cur%found Then

Dbms_output.put_line (' p_steward_name= ' | | | | p_steward_name);
Else
Dbms_output.put_line (' No data extracted from the cursor mycursor. ');
End If;
Close steward_cur;

End
/

--2. %isopen
/**
The%isopen property is true when the cursor or cursor variable is opened.
Otherwise the%isopen property is false.
We can use this property to determine if a cursor has been opened.
*/
--Created on 2011/4/14 by Xiao-cainiao
Declare

P_steward_name varchar2 (20);
Cursor Steward_cur is
Select S.steward_name from steward s where s.steward_id = ' 1194 ';

Begin
Open steward_cur;
If Steward_cur%isopen Then

Dbms_output.put_line (' cursor mycursor is already opened! ');

Else

Open steward_cur;

End If;

Fetch Steward_cur
into P_steward_name;

Close steward_cur;

Dbms_output.put_line (' p_steward_name ' | | | in ' | | | p_steward_name ');

End
--A comparison with the following example to notice the difference

Declare
P_steward_name varchar2 (20);
Cursor Steward_cur is
Select S.steward_name from steward s where s.steward_id = ' 1194 ';
Begin
--open steward_cur;
If Steward_cur%isopen Then
Dbms_output.put_line (' cursor mycursor is already opened! ');
Else

Open steward_cur;

End If;

Fetch Steward_cur
into P_steward_name;

Close steward_cur;

Dbms_output.put_line (' p_steward_name ' | | | in ' | | | p_steward_name ');

End

--3. %notfound
/**
The%notfound property and the%found property are just the opposite. When a cursor or cursor variable is opened but before the FETCH statement is executed, the
The%notfound is also null. Then, if the last FETCH statement returns a row of records,
The%notfound is false and%notfound is true if the FETCH statement does not return a record.
This property is useful when iterating through data from one cursor. As in the following example.
*/
Declare
P_steward_name varchar2 (20);
Cursor Steward_cur is
Select S.steward_name from steward s where s.steward_id = ' 1194 ';
V_classes VARCHAR2 (30);
Begin

V_classes: = ' Transport 98-12 ';

Open steward_cur;
Loop
Fetch Steward_cur
into P_steward_name;
Dbms_output.put_line (P_steward_name | | ' In ' | | v_classes);

Exit when Steward_cur%notfound;
End Loop;

Close steward_cur;

End
/

For this example, you can find Dbms_output.put_line (P_steward_name | | ' In ' | | v_classes); This output is 2 times, and Steward_cur has only one record, which is why. This problem also let me a long time of stuffy, until the last time in Oracle training asked teacher to untie, this is because pl/sql no real-time flush, you can use the following example to test:

Begin
For I in 1..10 loop
Dbms_output.put_line (' A ');
Dbms_lock.sleep (1);--Rest for 1 seconds
End Loop;

End
/

The

Executes under sql/plus without a line of output, but waits 10 seconds to output 10 rows at a draught.