I cant understand why my sql function returns the whole table with the same value.
The task is the following:
Create a PL / SQL CalculationAge (Person) function that returns the difference between the year of death and the year of birth, if the person is already deceased, and the difference between the current year and the year of birth if the person is still alive. The current year should be determined using the DBMS system time. Example: CalculateAlter ('Andrea') should return 24 as the result, and CalculateAlter ('Albert') should return 49."
Im working with the table below.
Geboren=birth year
Gestorben=year of dead
Geschlecht=gender
gelebt=lived
The Code of the function is the following:
create or replace FUNCTION BerechneAlter(n_Name varchar)
RETURN Integer
is
age_result integer;
v_gestorben integer;
v_geboren integer;
Begin
select gestorben, geboren into v_gestorben, v_geboren
from Family
where Family.name = n_Name;
if v_gestorben is Null
then age_result := (2018 - v_geboren);
else
age_result := v_gestorben - v_geboren;
end if;
return age_result;
end;
At the moment the function returns the right value but it shouldn´t do it for every single line of the table.
That`s because you are running it in a context of a query, it is running for every row and calculating just Andrea. In order to return just one result you need to run it just once:
select BerechneAlter('Andrea') from dual;
And if you want it to calculate for every row in the table you use:
select BerechneAlter(name) from family;
dual
is a system table in Oracle to be used to return just one result/value in a query
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments