I would like to create a PL/SQL Function that calculates the age of any person from an existing table "Family tree" (Familienbaum), based on their Name. The Table has the needed Values Name, BirthYear (Geburtsjahr), YearOfDeath (Sterbejahr), etc.

Now I want to calculate the age of the person in two ways:

1. If the Person has a YearOfDeath, it should subtract the BirthYear from the YearofDeath to calculate
2. If the Person has no YearOfDeath, it should subtract the BirthYear from the Current System Year of the Oracle SQL Server

So far I have tried using the SELECT INTO clause to declare the variables needed for the calculation from the table Family Tree (Familienbaum):

CREATE OR REPLACE FUNCTION BerechneAlter(Person VARCHAR2)
RETURN INTEGER IS 
BEGIN
SELECT Name, Sterbejahr, Geburtsjahr FROM Familienbaum
WHERE Person = Name;
RETURN (CASE 
        WHEN Sterbejahr IS NULL THEN (year(curDate()) - Geburtsjahr)
        WHEN Sterbejahr IS NOT NULL THEN (Sterbejahr - Geburtsjahr)
        END);
END BerechneAlter;

The SELECT INTO clause is giving me a lot of problems, does anyone know what needs to be changed in the syntax?

I also tried using cursors, but it seems more complicated than needed:

create or replace FUNCTION BerechneAlter(Person VARCHAR2)
RETURN INTEGER IS 
Sterbejahr INTEGER; 
Geburtsjahr INTEGER;

CURSOR SJ IS SELECT familienbaum.sterbejahr FROM familienbaum WHERE familienbaum.name=Person;
CURSOR GJ IS SELECT familienbaum.geburtsjahr FROM familienbaum WHERE familienbaum.name=Person;

BEGIN

OPEN SJ; 
FETCH SJ INTO Sterbejahr;
CLOSE SJ;

OPEN GJ;
FETCH GJ INTO Geburtsjahr;
CLOSE GJ;

RETURN (CASE
        WHEN Sterbejahr IS NULL THEN (2022 - Geburtsjahr)
        WHEN Sterbejahr IS NOT NULL THEN (Sterbejahr - Geburtsjahr)
        END);
END BerechneAlter;

CodePudding user response：

If you are using a SQL SELECT statement within an anonymous block or function or procedure, etc (in PL/SQL - between the BEGIN and the END keywords) you must select INTO something so that PL/SQL can utilize a variable to hold your result from the query. It is important to note here that if you are selecting multiple columns, (which you are by "SELECT Name, Sterbejahr, Geburtsjahr"), you must specify multiple variables or a record to insert the results of your query into.

for example:

SELECT 1 
INTO v_dummy 
FROM dual;

SELECT 1, 2 
INTO v_dummy, v_dummy2 
FROM dual;

It is also worth pointing out that if your SELECT , ... FROM.... will return multiple rows, PL/SQL will throw an error. You should only expect to retrieve 1 row of data from a SELECT INTO.

In your case, it would look something like this (note - I haven't confirmed that your logic is correct, also note that I don't know your datatypes so you'll have to work on that bit too):

CREATE OR REPLACE FUNCTION BerechneAlter(p_person VARCHAR2)
RETURN INTEGER IS 

v_name VARCHAR2(100);
v_sterbejahr VARCHAR2(100);
v_geburtsjahr VARCHAR2(100)

BEGIN

SELECT Name, Sterbejahr, Geburtsjahr 
INTO  v_name, v_sterbejahr, v_geburtsjahr 
FROM Familienbaum
WHERE Name = p_person;

RETURN (CASE 
        WHEN v_sterbejahr IS NULL THEN (year(curDate()) - v_geburtsjahr)
        WHEN v_sterbejahr IS NOT NULL THEN (v_sterbejahr - v_geburtsjahr)
        END);
END BerechneAlter;

CodePudding user response：

I think the function's logic is overcomplicated. You can get data and calculate age in an SQL statement, so only one var is needed.

create or replace function get_age(p_name varchar2) return number
is
  l_aelter number;
begin

  select nvl(sterbejahr, to_char(sysdate, 'YYYY')) - geburtsjahr aelter
    into l_aelter
    from familienbaum
   where name = p_name;

 return l_aelter;
end;

If using plsql is not required, you might wanted to use a standalone SQL statement to perform all the calculations you need:

select nvl(sterbejahr, to_char(sysdate, 'YYYY')) - geburtsjahr aelter
  from familienbaum
 where name = p_name;