I am trying to do some exercises, but I have blocked myself, I leave you the statement:
Activity 1
• Carry out a function called SUMA_COMPLEMENTOS that calculates the sum of the complements of an employee, we will pass a DNI as input and it will return the sum of the complements of that employee.
N_EMPLEADOS_COMPL
===================
DNI COD_COMPL
--------- ---------
1 C1
1 C2
1 C3
3 C3
3 C4
4 C1
5 C3
5 C5
N_COMPLEMENTOS
=====================
COD_COMPL EUROS_COMPL
--------- -----------
C1 100
C2 120
C3 140
C4 160
C5 180
The code I have is the following:
create or replace FUNCTION SUMA_COMPLEMENTOS(P_DNI N_EMPLEADOS.DNI%TYPE)
RETURN NUMBER
AS
SUM_COMP NUMBER;
BEGIN
SELECT SUM(COM.EUROS_COMPL) INTO SUM_COMP
FROM N_COMPLEMENTOS COM INNER JOIN N_EMPLEADOS_COMPL EMP_COM
ON COM.COD_COMPL = EMP_COM.COD_COMPL
GROUP BY EMP_COM.DNI
HAVING EMP_COM.DNI = P_DNI;
RETURN SUM_COMP;
END;
I rebuilt the database and re-inserted the data you provide as an example, and during the process I realized that in the declaration of
IN
the function, the type ofP_DNI
is incorrect, since you have named the table wrongly, puttingN_EMPLEADOS.DNI%TYPE
instead ofN_EMPLEADOS_COMPL.DNI%TYPE
. Change that and run the function again, I've tested it and it works.