i have created a stored procedure in Oracle 9i by using the following code.It's aim is to take designation as argument and calculates the avg salary and then inserts the result into another table.
create or replace procedure my_proc( desig IN varchar)
cursor s_cursor is
select avg(salary) from Teachers where designation=desig;
fetch s_cursor into s_value;
insert into Salary values(s_value);
when i'm executing this code it says the procedure created with compilation errors.
but the procedure does't working.
LittleFoots suggestion is far superior to my own
But just to throw another coin in the fountain - why not create this as a view? It may be a bit of overkill but the total will update live with data changes.