4.- Code the PL/SQL program that requests a department number on the screen and calculates the total sum of the salaries and commissions of that department. Then insert the corresponding tuple in the TOTALS table, previously created with the following structure: deptno number(3) total number(10,2)
--CREATE TABLE TOTALES(deptno number(3), total number(10,2));
DECLARE
cursor cur1(argu1 number) is
SELECT numde, salar, comis FROM temple WHERE numde=argu1;
registro cur1%ROWTYPE;
acu number(10,2):=0;
BEGIN
OPEN cur1(&Numero_departamento);
LOOP
FETCH cur1 INTO registro;
EXIT WHEN cur1%NOTFOUND;
acu:=acu+registro.salar+nvl(registro.comis,0);
END LOOP;
INSERT INTO TOTALES VALUES(registro.numde, acu);
CLOSE cur1;
END;
I don't understand what it is acu, or why it is registry.salar because that .
I think that with the line
acu number(10,2):=0;
a variable is being declaredacu
that is of type number with 10 digits of which 2 are decimals. And I would say that itacu
comes fromacumulado
.The notation of
.
is to access a certain column/record within that cursor. That is, withregistro.salar
I am accessing thesalar
cursor columnregistro
This line
acu:=acu+registro.salar+nvl(registro.comis,0);
is adding inacu
, the value of plus the value of the cursoracu
column , plus the result that I imagine represents some commission. What that function does is return if it is . That way you make sure it's always a number and the addition works fine.salar
registro
nvl(registro.comis, 0)
nvl
0
registro.comis
null
In the end it seems to be calculating the cumulative expense by department (salaries + commissions) or something.
acu
is a variable, in which the data of the loop is added is something equivalent to doingacu+=
We see that within acu, apart from adding the previous value, it also adds the value of thesalar
cursor columnregistro
, in additionnvl(registro.comis,0);
this is another record/ column of the cursor that with the functionNVL
in case the value isnull
replaced by a0
So that:
acu
= variableregistro
= cursorsalar
= record/column of this cursor