For a project I need to modify some attributes in a table based off of the value of that same attribute. I decided to go with cursors, and wrote the following:
DECLARE
totale_rente rekening.saldo%TYPE;
cursor c_boven1000 is
select r.reknummer,
r.saldo,
rt.rentepercentage,
ABS(ROUND(r.saldo * (rt.rentepercentage/100), 2 )) as teBetalen
FROM rekening r
join rekeningtype rt on rt.naam = r.rekeningtype
Where r.saldo < 0 and saldo >= -1000;
cursor c_onder1000 is
select r.reknummer,
r.saldo,
rt.rentepercentage,
ABS(ROUND(r.saldo * ((rt.rentepercentage*2)/100), 2 )) as teBetalen
From rekening r
join rekeningtype rt on rt.naam = r.rekeningtype
Where r.saldo < -1000;
TYPE rek_saldo IS TABLE OF rekening.saldo%TYPE;
TYPE rek_nummer IS TABLE OF rekening.reknummer%TYPE;
TYPE type_percentage IS TABLE OF rekeningtype.rentepercentage%TYPE;
TYPE rek_tebetalen IS TABLE OF rekening.saldo%TYPE;
rek_saldos rek_saldo;
rek_nummers rek_nummer;
type_percentages type_percentage;
rek_tebetalens rek_tebetalen;
BEGIN
OPEN c_boven1000;
FETCH c_boven1000 BULK COLLECT INTO rek_saldos, rek_nummers, type_percentages, rek_tebetalens;
CLOSE c_boven1000;
FOR x IN rek_nummers.first..rek_nummers.last LOOP
UPDATE rekening r
SET r.saldo = r.saldo - rek_tebetalens(x)
WHERE r.reknummer = rek_nummers(x);
totale_rente := totale_rente + rek_tebetalens(x);
END LOOP;
OPEN c_onder1000;
FETCH c_onder1000 BULK COLLECT INTO rek_saldos, rek_nummers, type_percentages, rek_tebetalens;
CLOSE c_onder1000;
FOR x IN rek_nummers.first..rek_nummers.last LOOP
UPDATE rekening r
SET r.saldo = r.saldo - rek_tebetalens(x)
WHERE r.reknummer = rek_nummers(x);
totale_rente := totale_rente + rek_tebetalens(x);
END LOOP;
UPDATE rekening r
SET saldo = saldo + totale_rente
WHERE r.reknummer = '2250';
END;
In this case, reknummer
is a Varchar, saldo
is a number(10,2), rentepercentage
is a number (3,2).
When executing, i got the following error:
ORA-01722: Invalid number for execute PL/SQL code.
Not sure if it is important, but this code block is inside a dynamic action on clicking a button. I have tried to find my error, but have been unable to. Could anyone assist?
The order in which you select your columns and the order of the variables is not the same.
select r.reknummer
,r.saldo
,rt.rentepercentage
into rek_saldos
,rek_nummers
,type_percentages
You can use a case statement in your cursor as such:
select r.reknummer
,r.saldo
,rt.rentepercentage
,case
when saldo >= -1000 then
abs(round(r.saldo * (rt.rentepercentage / 100), 2))
else
abs(round(r.saldo * ((rt.rentepercentage * 2) / 100), 2))
end as tebetalen
from rekening r
join rekeningtype rt
on rt.naam = r.rekeningtype
where r.saldo < 0