Create a stored procedure to calculate age

Q. Create a stored function titled 'Age_calc'.
  • Accept the date of birth of a person as a parameter.
  • Calculate the age of the person in years, months and days e.g. 3 years, 2months, 10 days.
  • Return the age in years directly (with the help of Return statement).
  • The months and days are to be returned indirectly in the form of OUT parameters.
Answer:

Create a Function Age_calc:

CREATE OR REPLACE FUNCTION Age_calc(dat IN date,d OUT number,m OUT number) RETURN number AS y number;
BEGIN d:=sysdate-dat;
y:=d/365;
y:=trunc(y);
m:=(d-y*365)/30;
M:=trunc(m);
d:=trunc(d-y*365-m*30);
RETURN y;
END;
/


Calling Procedure:

DECLARE D varchar2(20):='r';
P1 NUMBER:=&day_of_birth;
P2 NUMBER:=&month_of_birth;
P3 NUMBER:=&year_of_birth;
BEGIN D:=to_char(p1)||'-'||to_char(p2)||'-'||to_char(p3);
P1:=AGE_CALC(to_date(d,'dd-mm-yyyy'),P2,P3);
DBMS_OUTPUT.PUT_LINE('DAYS: '||P2||' MONTHS: '||P3||' YEARS: '||P1);
END;
/


Output:

stored function
▲