CODEWITHSUNDEEP
sqloracle-databaseplsql
ZHE.ZHAO
ZHE.ZHAO

Reputation: 385

--PL/SQL--cursor returns only the first line, then the loop is over

I have a table named XXALD_INT_IN_VEHICLE_ITEM, in which there are 11 lines.

I define a cursor:

    CURSOR LINE_VEH IS
        SELECT  *
        FROM    XXALD_INT_IN_VEHICLE_ITEM
    FOR UPDATE
        ;

I take the lines one by one, and handle it, then update a field of this line(error_code).

OPEN LINE_VEH;
LOOP
    FETCH LINE_VEH INTO line;
        EXIT WHEN LINE_VEH%NOTFOUND;

    if line.ALD_PROSYS_OR_ATT_N is null then
        v_error := v_error ||'ALD_PROD_SYSTEM|';
    end if;

    ...

    update XXALD_INT_IN_VEHICLE_ITEM 
        set ERROR_CODE= v_error
    where current of LINE_VEH;      
    commit;

END LOOP;
CLOSE LINE_VEH;

The problem is, why after the traitement of only the first line, the loop finishes? The other 10 lines are not fetched.

Upvotes: 0

Views: 817

Answers (2)

user3446787
user3446787

Reputation: 27

    CREATE TABLE EMP AS
SELECT * FROM EMPLOYEES WHERE ROWNUM <=11;
COMMIT;
SELECT * FROM EMP;


    DECLARE
  CURSOR LINE_VEH
  IS
    SELECT * FROM EMP FOR UPDATE;
  line LINE_VEH%ROWTYPE;
  v_error NUMBER;
  --I take the lines one by one, and handle it, then update a field of this line(error_code).
BEGIN
  OPEN LINE_VEH;
  LOOP
    FETCH LINE_VEH INTO line;
    EXIT  WHEN LINE_VEH%NOTFOUND;
    IF line.DEPARTMENT_ID =200 THEN
      v_error              := 300;
    END IF;
    UPDATE EMP SET DEPARTMENT_ID= v_error WHERE CURRENT OF LINE_VEH;
    --COMMIT;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;

/

FOR UPDATE cursor autometically commits. so I have commented out the commit part. This updates all the rows that satisfies the condition.

Upvotes: 0

Lajos Veres
Lajos Veres

Reputation: 13725

Can you move the commit outside the loop? I think it conflicts with the for update cursor. (for update is only living in one transaction.)

Upvotes: 1

Related Questions