函数 plsql 添加添加员工的工资

Question

我需要帮助！！我想使用函数添加特定部门员工的工资，但出现错误："精确提取中指定的数字小于返回的行。

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`

Answer 1

您的查询SELECT salary INTO salario_ FROM employees WHERE department_id = codigo_dep返回多行，这就是您出现错误的原因。 如果要获取具体部门中所有员工的工资总和，则应使用以下查询：

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

Answer 2

你可以运行这样的操作：

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;
/