使用光标查找需要更新工资的所有员工 [英] Using cursor to find all employees who need to have their salary updated

问题描述

我有一张叫员工的表.我正在尝试创建一个匿名PLSQL块，该块在employees表中输出雇员的姓氏，薪水和雇用日期.如果员工的聘用日期超过5年，那么我应该在员工记录旁边显示字符串:"Due for 10％加薪"，并计算该加薪并将其显示在记录旁边.
我需要使用当年(2016)进行计算，以确定每个员工都需要加薪.

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; 

推荐答案

您的程序始终是错误的.我不认为它会遵守.请检查以下工作版本:

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; 

这篇关于使用光标查找需要更新工资的所有员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！