asked
Calculate age stored procedure
I am creating a stored procedure that calculates the age of an employee when given the SSN. The table employee_age has 4 columns.(ssn,bdate,age,date_calculated) I am very close. My SP compiles but the age I am calculating is not going into the age column of the employee_age table and neither is the sysdate which is suppose to go into the date_calculated column. Any ideas why they aren't being put into the employee_age table? BTW the employee-age table already has SSN and bdate values. The age and date_calculated columns are empty. CREATE OR REPLACE PROCEDURE agecalc ( p_empSSN IN employee_Age.ssn%type) AS v_empbdate employee_Age.bdate%type; v_empdatec employee_age.date_calculated%type; v_empage employee_Age.age%type; BEGIN SELECT bdate into v_empbdate from employee_age WHERE ssn = p_empSSN; v_empdatec := TRUNC(SYSDATE); v_empage := Floor(( TRUNC(SYSDATE) - TRUNC(v_empbdate) )/365.25); COMMIT; END agecalc; / show errors exec agecalc ('123456789')
My syntax might be wrong - I'm not an Oracle developer - but you need an UPDATE statement in your proc, after you calculate v_empage. UPDATE employee_age SET age = v_empage, date_calculated = TRUNC(SYSDATE) WHERE ssn = p_empSSN; But why not do it all in one go? Something like this? CREATE OR REPLACE PROCEDURE agecalc ( p_empSSN IN employee_Age.ssn%type) AS BEGIN UPDATE employee_age SET age = Floor(( TRUNC(SYSDATE) - TRUNC(v_empbdate) )/365.25) date_calculated = TRUNC(SYSDATE) WHERE ssn = p_empSSN; COMMIT; END agecalc Take it for what it is - an attempt from someone who uses SQL every day but haven't used Oracle since a very basic database course 15 years ago.
Yup you were correct I forgot the update statement. I hate when the simplest things sometimes slip my mind. Thanks for you help @magnus ahlkvist
@sqlLearner - if this has got you working as requested can you pop back and tick the answer so others know this fixed you up, just in case they have the same issue. thanks .
