PL/SQL function help

I have this EMPLOYEE table with the following fields.

SQL> desc employee;
Name Null? Type
------------------------------- -------- ----
FNAME NOT NULL VARCHAR2(15)
MINIT NOT NULL CHAR(1)
LNAME NOT NULL VARCHAR2(15)
SSN NOT NULL CHAR(9)
BDATE DATE
ADDRESS NOT NULL VARCHAR2(30)
SEX CHAR(1)
SALARY NUMBER(5)
SUPERSSN CHAR(9)
DNO NUMBER(1)


Need to create a function called AGE to calculate an age (integer value) based on the birthdate. Would be something like

CREATE OR REPLACE FUNCTION AGE (...) RETURN......

END AGE;

Can anyone help? I know I have to use SYSDATE-BIRTHDATE, but how to make the conversion in order to get the age as in integer value?


Thanks,
SKY

create or replace function CALC_AGE (i_SSN in varchar2) return NUMBER
is
birthdate date;
ret_age number;
begin
select BDATE into birthdate from employee where ssn = i_SSN;
select round((sysdate - birthdate)/365) into ret_age from dual;
return ret_age;
end CALC_AGE;

Use functions TRUNC and MONTHS_BETWEEN:
SELECT lname,
TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12)
FROM employee;

Rotem_fo,

After created the function you recommended, I tried a query, but it did not give me any numbers

SQL> select fname, lname, calc_age(bdate) "AGE"
2 from employee;

FNAME LNAME AGE
--------------- --------------- ---------
Franklin Wong
Alicia Zelaya
Jennifer Wallace
Ramesh Narayan
Joyce English
Ahmad Jabbar
James Borg
John Smith

8 rows selected.


Did I make any mistake? On your code, you had

create or replace function CALC_AGE (i_SSN in varchar2) return NUMBER
is
.......
.........

Do we usually use PK in the table for the function parameter? I am new to this, can you explain?

Thanks a lot.
SKY

I guess my intend was to use AGE (Bdate), not ssn!

Hello,

Have just modified rotem_fo's function.

Hope this will solve your problem.

create or replace function CALC_AGE (ag in date) return NUMBER
is
begin
return round((sysdate - ag)/365);
end CALC_AGE;

Then

SQL>select ename,CALC_AGE (age) from emp;

ENAME CALC_AGE(AGE)
---------- ----------------------
SMITH 20
ALLEN 20
WARD 20
JONES 20
MARTIN 20
BLAKE 20
CLARK 20
SCOTT 14
KING 19
TURNER 20
ADAMS 14
JAMES 19
FORD 19
MILLER 19