Creating a Procedure in Oracle

We wish to create a procedure to read data from one table and update data in another table. For this purpose we create two tables. First table is EMPLOYEES Table. The second table is SALARIES Table. The EMPLOYEES table contains Employee data including salaries. The SALARIES table contains Salary details corresponding to each employee. Suppose we want to update EMPLOYEES table using SALARY details of SALARIES table for each EMPLOYEE.

CREATE TABLE EMPLOYEES
(EMPNO NUMBER PRIMARY KEY, 
EMPNAME VARCHAR2(40),
DESIGNATION VARCHAR2(40),
SALARY NUMBER(10,2),
DEPTNO NUMBER);

Now we add some records in to the EMPLOYEES Table as follows:


INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1001, 'NIMAL', 'MANAGER',  75000, 1);

INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1002, 'SAMAN', 'ACCOUNTANT',  50000, 2);

INSERT INTO EMPLOYEES (EMPNO, EMPNAME, DESIGNATION, SALARY,DEPTNO)
VALUES (1003, 'AJITH', 'ENGINEER',  60000, 3);

Now we create SALARIES Table as follows:



CREATE TABLE SALARIES
(EMPNO NUMBER PRIMARY KEY, 
SALARY NUMBER(10,2));

Now add several data sets as follows:


INSERT INTO SALARIES (EMPNO, SALARY)
VALUES (1001, 90000);

INSERT INTO SALARIES (EMPNO, SALARY)
VALUES (1002, 80000);

INSERT INTO SALARIES (EMPNO, SALARY)
VALUES (1003, 80000);

The procedure for updating the salaries from the SALARIES table is as follows:


CREATE OR REPLACE  PROCEDURE "UPDATESAL"  is
cursor mycur is(select EMPNO, SALARY from SALARIES);
begin
for rec in mycur
loop
update EMPLOYEES
set SALARY=rec.SALARY
where EMPNO=rec.EMPNO;
end loop;
commit;
end;
/