plsql - Function in SQL about compilation error -

i'm new sql, want create function return professors age pro_dob.

here code.

create or replace function pro_age (pro_dob in date) return number current_age number; begin select pro_dob,pno current_age from(select pro_dob,pno professors) pno =pro_dob;  current_age:=trunc((sysdate - to_date('pro_dob', 'mm/dd/yyyy'))/365.25); return current_age; end; / 

it show me compilation error after create it.

and how can execute it?

"i want create function return professors age pro_dob"

your implementation of rules seems little confused:

where pno =pro_dob 

is real logic want professor's date of birth using id (pno) , calculate age?

if so, function should this:

create or replace function pro_age     (p_id in professors.pno%type)     return number     current_age number; begin     select trunc(months_between(sysdate, pro_dob)/12)     current_age      professors     pno = p_id;      return current_age; end; / 

the date arithmetic uses built-in function months_between() calculate number of months between dob , current date. find out more. dividing 12 gives number of years , trunc() ensures integer, because age expressed whole years.

note variables in into clause need match columns in query's projection (number , datatype).

"how can execute it?"

functions return value must assign something. run anonymous pl/sql block in sql*plus:

sql> var age number sql> accept professor_id prompt "please enter pno professor: "  please enter pno professor: 123 sql> begin   2      :age := pro_age(&professor_id);   3  end;   4  /  pl/sql procedure completed.  sql> print age         age ----------         32  sql> 

---

alternatively write function calculates age passed date of birth

create or replace function calc_age     (p_dob in date)     return number begin     return trunc(months_between(sysdate, p_dob)/12); end; / 

this function include in query so:

select pno                                   , pro_dob        , calc_age(pro_dob) pro_age professors /