PL/SQL Cursor Exercises: Increase salary of employees in the department 50 using WHERE CURRENT OF clause

PL/SQL Cursor: Exercise-14 with Solution

Create a PL/SQL block to increase salary of employees in the department 50 using WHERE CURRENT OF clause.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DROP TABLE emp_temp;

CREATE TABLE emp_temp AS
  SELECT employee_id,
         first_name,
         last_name,
		 department_id,
         salary
  FROM   employees;

DECLARE
    CURSOR employee_cur IS
      SELECT employee_id,
             salary
      FROM   emp_temp
      WHERE  department_id = 50
      FOR UPDATE;
    incr_sal NUMBER;
BEGIN
    FOR employee_rec IN employee_cur LOOP
        IF employee_rec.salary < 15000 THEN
          incr_sal := .15;
        ELSE
          incr_sal := .10;
        END IF;

        UPDATE emp_temp
        SET    salary = salary + salary * incr_sal
        WHERE  CURRENT OF employee_cur;
    END LOOP;
END;
/ 

Sample Output:

SQL> /

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to create an explicit cursor with for loop.
Next: Write a program in PL/SQL to FETCH single record and single column from a table.