CODEWITHSUNDEEP
sqloracle-databaseplsql
Khammal Mousaab
Khammal Mousaab

Reputation: 1

Function plsql add add the salaries of the employees

I need help!! I want to add the salaries of the employees of a specific department using a function but I get the error: "The number specified in exact fetch is less than the rows returned."

CREATE OR REPLACE FUNCTION SUMAR_SALARIOS (i NUMBER) RETURN NUMBER IS resultado NUMBER; BEGIN resultado := resultado + i; RETURN(resultado);
END SUMAR_SALARIOS; / SET SERVEROUTPUT ON SET VERIFY OFF
SET ECHO OFF ACCEPT codigo PROMPT ”Introduce el codigo de departamento” DECLARE
salario_ employees.salary%TYPE;   
codigo_dep number;
BEGIN
codigo_dep := &codigo; 
SELECT salary INTO salario_
FROM employees WHERE department_id = codigo_dep;
DBMS_OUTPUT.PUT_LINE(SUMAR_SALARIOS(salario_));

END;
/
UNDEFINE codigo`

Upvotes: 0

Views: 155

Answers (2)

alexs
alexs

Reputation: 406

You can run something like this:

set serveroutput on
SET VERIFY OFF
SET ECHO OFF 
ACCEPT codigo_dep NUMBER PROMPT "Introduce el codigo de departamento"

declare
    sumar_salarios number;
begin
    SELECT sum(salary) INTO sumar_salarios
    FROM mwp_alexs_1.employees WHERE department_id = &codigo_dep;

    dbms_output.put_line('sumar_salarios: '||sumar_salarios);
end;
/

Upvotes: 0

Anton Tokmakov
Anton Tokmakov

Reputation: 710

Your query SELECT salary INTO salario_ FROM employees WHERE department_id = codigo_dep returns more than one row, that's why you got a error. If you want to get a sum of salaries of all employees in a concrete department, then you should use the following query:

SELECT sum(salary) INTO salario_ FROM employees WHERE department_id = codigo_dep

Upvotes: 1

Related Questions