How to update data with cursor in Oracle?

How to Update Data by using Cursor?

With simple update statement we can update only simple data but with cursor we can update data with reference.

Sample Code:

======================================
SQL> CREATE TABLE  EMP
2  (
3  EMP_CODE        NUMBER,
4  EMP_NAME        VARCHAR2(100),
5  EMP_SALARY      NUMBER,
6  USER_NAME       VARCHAR2(100),
7  DATE_TIME       DATE
8  );

Table created.

SQL> INSERT INTO EMP
2  VALUES (101,’ROBERT’,50000,’ALENA’,SYSDATE);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP
2  VALUES (102,’VENKAT’,60000,’ALENA’,SYSDATE);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP
2  VALUES (103,’JHON’,70000,’ALENA’,SYSDATE);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP
2  VALUES (104,’MANI’,80000,’ALENA’,SYSDATE);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP
2  VALUES (105,’ALBERT’,90000,’ALENA’,SYSDATE);
1 row created.

SQL> CREATE TABLE  EMP_SAL
2  (
3  EMP_CODE        NUMBER,
4  EMP_SALARY      NUMBER
5  );
Table created.

SQL> INSERT INTO EMP_SAL
2  VALUES(101,0);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP_SAL
2  VALUES(102,0);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP_SAL
2  VALUES(103,0);
1 row created.

SQL> /
1 row created.
SQL> INSERT INTO EMP_SAL
2  VALUES(104,0);
1 row created.

SQL> /
1 row created.

SQL> INSERT INTO EMP_SAL
2  VALUES(105,0);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM EMP_SAL;
EMP_CODE EMP_SALARY
———- ———-
101          0
101          0
102          0
102          0
103          0
103          0
104          0
104          0
105          0
9 rows selected.
SQL>

SQL> DECLARE
2  CURSOR C1 IS SELECT EMP_CODE,EMP_SALARY FROM EMP;
3  BEGIN
4  FOR I IN C1
5  LOOP
6  UPDATE EMP_SAL
7  SET    EMP_SALARY = I. EMP_SALARY
8  WHERE  EMP_CODE   = I.EMP_CODE;
9  END LOOP;
10  END;
11  /
PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP_SAL;
EMP_CODE EMP_SALARY
———- ———-
101      50000
101      50000
102      60000
102      60000
103      70000
103      70000
104      80000
104      80000
105      90000
9 rows selected.
SQL>
======================================