Cursor - FOR UPDATE

/************************************************************************
*   FOR UPDATE clause in oracle                                         *
*   Document By : Murugappan Annamalai                                  *
************************************************************************/


create table prod_details(p_id VARCHAR2(30), P_name VARCHAR2(30));


BEGIN
     --Inserting data into prod_details table
     FOR i IN 1..50 LOOP
         INSERT INTO prod_details VALUES(i,'pname'||i);
     END LOOP;
    
     commit;
END;


SELECT * FROM prod_details;


DECLARE

  CURSOR PROD_DTLS_C IS
    SELECT * FROM PROD_DETAILS T1 FOR UPDATE OF P_ID;

  V_PID     PROD_DETAILS.P_ID%TYPE;
  V_PRDNAME PROD_DETAILS.P_NAME%TYPE;

BEGIN
  OPEN PROD_DTLS_C;
    LOOP
      FETCH PROD_DTLS_C INTO V_PID, V_PRDNAME;
 
       IF PROD_DTLS_C%NOTFOUND THEN
                   EXIT;
       ELSE
                   UPDATE PROD_DETAILS P
                      SET P.P_ID = LPAD(P_ID, 10, 0)
         WHERE CURRENT OF PROD_DTLS_C;
       END IF;
 
    END LOOP;
 
  CLOSE PROD_DTLS_C;
  COMMIT;
END;


select * from PROD_DETAILS;

TRUNCATE TABLE prod_details;


BEGIN
     --Inserting data into prod_details table
     FOR i IN 1..50 LOOP
         INSERT INTO prod_details VALUES(i,'pname'||i);
     END LOOP;
    
     commit;
END;




DECLARE

  CURSOR PROD_DTLS_C IS
    SELECT * FROM PROD_DETAILS T1 FOR UPDATE OF P_ID;

  V_PID     PROD_DETAILS.P_ID%TYPE;
  V_PRDNAME PROD_DETAILS.P_NAME%TYPE;
 
BEGIN
  OPEN PROD_DTLS_C;
    LOOP
      FETCH PROD_DTLS_C INTO V_PID, V_PRDNAME;
 
       IF PROD_DTLS_C%NOTFOUND THEN
          EXIT;
       ELSE
                   UPDATE PROD_DETAILS P
                      SET P.P_ID = LPAD(P_ID, 10, 0)
         WHERE CURRENT OF PROD_DTLS_C;
       END IF;
       COMMIT;
    END LOOP;
       
  CLOSE PROD_DTLS_C;
  --COMMIT;
END;


select * from PROD_DETAILS;