Update inside cursor loop.
CrazeNov 14 2007 — edited Nov 15 2007Hi I encounter problem when update records inside cursor. When I run the procedure, it update the records in the cursor but I need the cursor to refresh eg. cursor records contain a but once first update a will updated but infact once you refresh the cursor it contain another records.Below are the procedure and the expected result I need.I need to update the null value with previous row data when execute one procedure. Below procedure able to update it but I need to run for several times.
Actual table
------------------------
TABLE 1
ID STEP_BEGIN_TIME CURR_STEP LAST_STEP
1 T3EM 11/3/2007 12:10:47 AM 003.000 003.000
2 T3EM 11/3/2007 12:25:47 AM 003.000 003.000
3 T3EM 11/3/2007 1:05:32 AM 003.000 003.000
4 T3PX 11/3/2007 1:19:03 AM 050.000 090.000
5 T3PX 11/3/2007 1:22:30 AM 050.000 090.000
6 T3PX 11/3/2007 1:26:20 AM 050.000 090.000
7 T3PX 11/3/2007 1:29:48 AM
8 T3EM 11/3/2007 1:31:15 AM 003.000 005.000
9 T3EM 11/3/2007 1:31:32 AM 002.000 002.000
10 T3PX 11/3/2007 1:33:17 AM
11 T3EM 11/3/2007 2:38:21 AM 002.000 002.000
12 T3EM 11/3/2007 3:42:21 AM 002.000 002.000
13 T3EM 11/3/2007 4:43:12 AM 003.000 003.000
14 T3EM 11/3/2007 5:53:09 AM 003.000 003.000
15 T3EM 11/3/2007 6:24:31 AM
16 T3EM 11/3/2007 6:49:00 AM
17 T3EM 11/3/2007 7:12:01 AM
Expected result
-----------------------------
TABLE 1
ID STEP_BEGIN_TIME CURR_STEP LAST_STEP
1 T3EM 11/3/2007 12:10:47 AM 003.000 003.000
2 T3EM 11/3/2007 12:25:47 AM 003.000 003.000
3 T3EM 11/3/2007 1:05:32 AM 003.000 003.000
4 T3PX 11/3/2007 1:19:03 AM 050.000 090.000
5 T3PX 11/3/2007 1:22:30 AM 050.000 090.000
6 T3PX 11/3/2007 1:26:20 AM 050.000 090.000
7 T3PX 11/3/2007 1:29:48 AM 050.000 X 090.000 X
8 T3EM 11/3/2007 1:31:15 AM 003.000 005.000
9 T3EM 11/3/2007 1:31:32 AM 002.000 002.000
10 T3PX 11/3/2007 1:33:17 AM 002.000 X 002.000 X
11 T3EM 11/3/2007 2:38:21 AM 002.000 002.000
12 T3EM 11/3/2007 3:42:21 AM 002.000 002.000
13 T3EM 11/3/2007 4:43:12 AM 003.000 003.000
14 T3EM 11/3/2007 5:53:09 AM 003.000 003.000
15 T3EM 11/3/2007 6:24:31 AM 003.000 X 003.000 X
16 T3EM 11/3/2007 6:49:00 AM 003.000 X 003.000 X
17 T3EM 11/3/2007 7:12:01 AM 003.000 X 003.000 X
Procedure
---------------------
CREATE OR REPLACE PROCEDURE UPDATE_P AS
CURSOR C_P IS
SELECT T1.ID,
T1.BEGIN_TIME,
T2.CURR_STEP AS CURR_STEP2,
T2.LAST_STEP AS LAST_STEP2
FROM TABLE1 T1, TABLE1 T2
WHERE T1.ID = LOTRUN2.ID
AND T1.BEGIN_TIME = LOTRUN2.RELEASE_TIME
AND T1.BEGIN_TIME >= TRUNC(SYSDATE)-1
AND T1.CURR_STEP IS NULL
AND T1.AREA = 'FAB'
AND T2.CURR_PRCDSTACK2_CURR_STEP IS NOT NULL;
R_P C_P%ROWTYPE;
BEGIN
OPEN C_P;
FETCH C_P
INTO R_P;
IF (C_P%ROWCOUNT > 0) THEN
BEGIN
LOOP
UPDATE TABLE1
SET CURR_STEP=R_P.CURR_STEP2,
LAST_STEP=R_P.LAST_STEP2
WHERE ID = R_P.LOTID
AND BEGIN_TIME = R_P.BEGIN_TIME;
COMMIT;
FETCH C_P
INTO R_P;
EXIT WHEN C_P%NOTFOUND;
END LOOP;
END;
END IF;
END;