PL/SQL Function returns to many values

Maiwand

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

I´m working with this table.

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.

result picture

Jorge Campos

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.

edited at
0

Comments

0 comments
Login to comment

Related

The function returns invalid values

Python function wihich takes a list of values and returns how many strings that list contains at the top level

TukeyHSD returns too many values in R

Powershell function returns intermediate values

Print function returns two values

checkPrime function returns incorrect values

counter function returns wrong values

Function which returns multiple values

Anonymous function returns too many input arguments

Too many values to unpack in lambda function

Defining many function values elegantly in haskell

R predict() function returning too many values

fetching values with apostrophe on PLSQL

Running many python threads and grabbing the returns values of each

python - for loop over list - returns error "too many values to unpack"

Oracle Nested Select returns ORA-00913 "too many values"

Python scraping XHR returns ValueError: Too many values to unpack

React useParams not showing params at all, returns object with many values

bring many years (oracle plsql)

A function that returns different column values based on input

Graphite Returns All values, rather than function

Oracle: Create a function that returns a set of values?

Same function with same inputs returns different values

Order function returns different values as vector

Elixir doctest fails for function that returns random values

Function returns all values as null, even though they are not

return function returns array values using Closure

How to document a function that returns specific values?

Returns multiple values from a PHP function