Styjun

I got an PL/SQL script to insert data from another table, but the result was not as i expected. The insert in BBB table only save the first row of d_rec loops. When i tried to print/output of d-Rec in another sql, it give me all the rows that saved in yyy table.

i already tried using cursor, but the result still the same.

DECLARE
GROUP_SEQ NUMBER;
TRANS_SEQ NUMBER;
ID NUMBER;
PP NUMBER := 1;
TRANS_ID NUMBER;
TRANS_SUM NUMBER;
UNIT VARCHAR(6);
DEBET NUMBER;
CREDIT NUMBER;

BEGIN
FOR v_rec in (SELECT unit from xxx)
LOOP
 FOR d_rec in (SELECT ID, TRANS_SUM, UNIT from yyy where unit = v_rec.unit )
 LOOP

 UPDATE YYY_SEQUENCE 
 SET SEQ_COUNT = SEQ_COUNT + 1
 WHERE SEQ_NAME = 'GROUP_SEQ'
 RETURNING SEQ_COUNT 
 INTO GROUP_SEQ;
 COMMIT;

 INSERT INTO AAA (ID, SEQ) VALUES (ID, GROUP_SEQ);
 WHILE PP <= 4
 LOOP
 UPDATE YYY_SEQUENCE 
 SET SEQ_COUNT = SEQ_COUNT + 1
 WHERE SEQ_NAME = 'TRANS_SEQ'
 RETURNING SEQ_COUNT 
 INTO TRANS_SEQ;
 COMMIT;

 IF (PP < 3) THEN
 DEBET := TRANS_SUM;
 CREDIT := 0;
 ELSE 
 DEBET := 0;
 CREDIT := TRANS_SUM;
 END IF;
 PP := PP +1;
 INSERT INTO BBB(ID, SEQ, UNIT, DEBET, CREDIT) VALUES (ID, TRANS_SEQ, UNIT, DEBET, CREDIT);
 COMMIT;
 END LOOP;
 END LOOP;
PP :=1;
END LOOP;
END

The First insert give me all the rows in d_rec data. But the second insert only give me the first row of d_rec data.

You are resetting the PP counter after the d_rec loop, inside the v_rec loop; which means that once it's been incremented to 4 for the first d_rec for a unit, subsequent d_rec iterations skip over the innermost loop - because PP <= 4 is never true for those.

A quick fix would be to move that reset in a loop:

...
 END LOOP;
 -- reset PP after this loop
 PP :=1;
 END LOOP;
 -- instead of after this loop
 --PP :=1;
END LOOP;
END

But it would probably be clear if you reset immediately before using it:

...
 INSERT INTO AAA (ID, SEQ) VALUES (ID, GROUP_SEQ);
 -- reset here
 PP := 1;
 WHILE PP <= 4
 LOOP
 PP := PP +1;

 ...

 IF (PP < 3) THEN
 DEBET := TRANS_SUM;
 CREDIT := 0;
 ELSE 
 DEBET := 0;
 CREDIT := TRANS_SUM;
 END IF;

 INSERT INTO BBB(ID, SEQ, UNIT, DEBET, CREDIT) VALUES (ID, TRANS_SEQ, UNIT, DEBET, CREDIT);
 COMMIT;
 END LOOP;
 END LOOP;
END LOOP;
END

Or get rid of the explicit variable altogether - so it doesn't need to be declared, reset or incremented - and use a for loop instead:

...
 INSERT INTO AAA (ID, SEQ) VALUES (ID, GROUP_SEQ);
 FOR PP IN 1..4
 LOOP
 ...

 IF (PP < 3) THEN
 DEBET := TRANS_SUM;
 CREDIT := 0;
 ELSE 
 DEBET := 0;
 CREDIT := TRANS_SUM;
 END IF;

 INSERT INTO BBB(ID, SEQ, UNIT, DEBET, CREDIT) VALUES (ID, TRANS_SEQ, UNIT, DEBET, CREDIT);
 COMMIT;
 END LOOP;
 END LOOP;
END LOOP;
END

Incidentally, all those commits look wrong - they make it impossible to restart on failure but also have a detrimental effect on performance. And you could probably simplify this to fewer loops.