CODEWITHSUNDEEP
sqlplsql
Venom
Venom

Reputation: 27

Using cursor to find all employees who need to have their salary updated

I have a table called employees. I am trying to create an anonymous PLSQL block, which outputs the last names, salary and hiredate of the employees in the employees table. If the employee hiredate is more than 5 years, then next to the employee record I should display the string: ‘Due for a 10% raise’ and calculate that raise and display it next to the record.
I need to use the current year (2016) to do the calcuations to determine that each employee need a raise. 

set serveroutput on
DECLARE 
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
  v_salary employees.salary%TYPE;
  v_hiredate employees.hiredate%TYPE;
  v_newsalary number(8,2); 
  CURSOR c_emp IS 
    SELECT e1.employee_id, e1.last_name, e1.salary, e1.hiredate  
    FROM employees e1, 
        (SELECT employee_id, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service 
          FROM employees
        ) e2 
    WHERE e1.employee_id =e2.employee_id and years_of_service >5; 
  BEGIN

    OPEN c_emp; 
    LOOP
      FETCH c_emp  into v_empo, v_lname, v_salary, v_hiredate
      DBMS_OUTPUT.PUT_LINE( v_empno ||'  '||v_lname || ' ' || v_salary ||'  '||v_hiredate );  
    END LOOP; 
  END;

Table

Upvotes: 0

Views: 1387

Answers (2)

XING
XING

Reputation: 9886

You program is anyways wrong. I dont think it would had ever complied. Please check below the working version:

DECLARE 
  v_empno emp.empno%TYPE;
  v_lname emp.ename%TYPE;
  v_salary emp.sal%TYPE;
  v_hiredate emp.hiredate%TYPE;
  v_newsalary number(8,2); 
  CURSOR c_emp IS 
    SELECT e1.empno, e1.ename, e1.sal, e1.hiredate 
    FROM emp e1, 
        (SELECT empno, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service 
          FROM emp
        ) e2 
    WHERE e1.empno =e2.empno 
    and years_of_service >5; 
BEGIN
    OPEN c_emp; 
    LOOP
      FETCH c_emp  
      into v_empno, v_lname, v_salary, v_hiredate;

      v_newsalary := v_salary + (v_salary*.1);

      DBMS_OUTPUT.PUT_LINE( v_empno ||'  '||v_lname || ' ' || v_salary ||'  '||v_hiredate||'    '||'Due for a 10% raise'||'  '||v_newsalary);
      exit when c_emp%NOTFOUND;  
    END LOOP; 
    close c_emp;
  END;

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

Rather than doing your entire homework assignment for you, I'll just suggest that you don't need the declare section at all as you can simply define the cursor inline with

for r in (
    select e.blah from employees e
)
loop
    do stuff here referring to r.blah
end loop;

(I always use r for cursor loops and i for numeric loops, unless there is some complicating factor.)

Also you don't need a self join to check whether hiredate < add_months(sysdate,-60) as you can just use a case expression.

Since you are just starting, I'd also advise you to choose the path of lowercase, because it's 2016 not 1974.

Upvotes: 0

Related Questions