PL/SQL Cursor Exercises: Display the name of the employee and increment percentage of salary according to their working experiences

PL/SQL Cursor: Exercise-32 with Solution

Write a PL/SQL program to display the name of the employee and increment percentage of salary according to their working experiences.

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 *
  FROM employees;
 
 
DECLARE
  CURSOR employees_cur IS
    SELECT employee_id,
           first_name,
           Trunc(Months_between(SYSDATE,hire_date) / 12) expr
    FROM   emp_temp;

incre_per NUMBER(2);
BEGIN
  dbms_output.put_line(rpad('Employee ID',15)||rpad('Name',25) || 'Increment %');
  dbms_output.Put_line('-----------------------------------------------------------');
  FOR store_emp_rec IN employees_cur 
  LOOP
    incre_per :=
    CASE
    WHEN store_emp_rec.expr > 10 THEN
      15
    WHEN store_emp_rec.expr > 5 THEN
      10
    ELSE
      8
    END;
    UPDATE emp_temp
    SET    salary = salary + (salary * incre_per / 100)
    WHERE  employee_id = store_emp_rec.employee_id;
dbms_output.put_line(rpad(store_emp_rec.employee_id,15) ||rpad(store_emp_rec.first_name,25)||  incre_per );	
  END LOOP;
END;
/ 

Sample Output:

SQL> /
Employee ID    Name                     Increment %
----------------------------------------------------
100            Steven                   15
101            Neena                    15
102            Lex                      15
103            Alexander                15
104            Bruce                    10
105            David                    15
106            Valli                    15
107            Diana                    15
108            Nancy                    15
109            Daniel                   15
110            John                     15
111            Ismael                   15
112            Jose Manuel              15
113            Luis                     10
114            Den                      15
... 

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to display the name of department and their head.
Next: Write a PL/SQL block to show the uses of a variable in explicit cursor query, and the result set is affected with the value of the variable is incremented after every fetch.