PL/SQL function help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: PL/SQL function help

  1. #1
    Join Date
    Sep 2000
    Posts
    3

    Question

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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;

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    Use functions TRUNC and MONTHS_BETWEEN:
    SELECT lname,
    TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12)
    FROM employee;

  4. #4
    Join Date
    Sep 2000
    Posts
    3

    Question Need more help

    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

  5. #5
    Join Date
    Sep 2000
    Posts
    3

    Question Clarify

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

  6. #6
    Join Date
    Apr 2001
    Posts
    24
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width