0
DECLARE 
    EMPLEADO EMPLOYEES.EMPLOYEE_ID%TYPE;
    SALARIO EMPLOYEES.SALARY%TYPE;
BEGIN
    select employee_id , salary INTO EMPLEADO , SALARIO
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID > = '100' AND EMPLOYEE_ID < = '120';
    IF SALARIO > 100000 THEN
    DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un excelente salario');
    elsif SALARIO > 9000 AND SALARIO < 100000 THEN
    DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un salario normal');
    ELSIF SALARIO < 9000 THEN
    DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un salario normal');
    end if;
end;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Victor Scotfield
  • 1
  • Possible duplicate of [PL/SQL ORA-01422: exact fetch returns more than requested number of rows](http://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows) – Vasan May 05 '17 at 18:38
  • What is confusing? Your query returns multiple rows and by putting the values in variables, you are assuming only one row. – Gordon Linoff May 05 '17 at 18:39
  • How would it be the right way to do it? – Victor Scotfield May 05 '17 at 18:41
  • 1
    What are you trying to do? Get a list of all employees? You don't need the variables or INTO, just the result of the SELECT. – Jacob H May 05 '17 at 18:44
  • Divide according to your salary if the employee's salary is excellent, normal or needs an increase – Victor Scotfield May 05 '17 at 18:47
  • By the way, you're saying <9000 is normal, copy paste error. – Nick May 05 '17 at 18:47
  • I have already corrected it, this is the error ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 01422. 00000 - "exact fetch returns more than requested number of rows" * Cause: The number specified in exact fetch is less than the rows returned. * Action: Rewrite the query or change number of rows requested – Victor Scotfield May 05 '17 at 18:52

1 Answers1

1

You can handle this with a cursor:

SET SERVEROUTPUT ON

DECLARE
  CURSOR CURSOR1 IS
    SELECT EMPLOYEE_ID, SALARY
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID >= '100' AND EMPLOYEE_ID <= '120';          
EMPLEADO EMPLOYEES.EMPLOYEE_ID%TYPE;
SALARIO EMPLOYEES.SALARY%TYPE;
BEGIN
  FOR REC IN CURSOR1 LOOP
    EMPLEADO := REC.EMPLOYEE_ID;
    SALARIO := REC.SALARY;
    IF SALARIO > 100000 THEN
       DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un excelente salario');
    ELSIF SALARIO > 9000 AND SALARIO < 100000 THEN
       DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un salario normal');
    ELSIF SALARIO < 9000 THEN
       DBMS_OUTPUT.PUT_LINE('El salario actual del empleado '||empleado||' es de '||salario||'.'|| 'Es un salario normal');
    END IF;
    EMPLEADO := NULL;
    SALARIO := NULL;
  END LOOP;
END;

As Nicholas V. pointed out, you are indicating that a salary < 9000 is also normal - this is likely a typo.

Jake
  • 604
  • 3
  • 9
  • 33