I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as

  • emp (eid, ename, age, salary)
  • works (eid, did, pct_time)
  • dept (did, dname, budget, managerid)

The procedure is compiling with errors. What am I doing wrong? Please see the procedure definition below.

create or replace procedure get_avgSalary(ddept_id in dept.did%type) 
  cursor c1 is
    select avg(e.salary) 
    from emp e, works w, dept d 
    where e.eid=w.eid and d.did = w.did 
    group by w.did;
  avg_sal c1%rowtype;
  for avg_sal in c1 loop
    dbms_output.put_line('Average salary of the employees in department ' || ddept_id ||' is '|| avg_sal.salary);
  end loop;

I have to use a cursor. It is part of my assignment.

  • 2
    Why are you using a loop? That looks like it could be easily accomplished in a single query. – Max Vernon Jun 26 '15 at 18:17

You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body :

.... cursor c1 is select avg(e.salary) AS salary ...

Side notes. It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable compared to SQL89 style (and recommended by Oracle as well). Finally, using cursor in this case seems quite questionable to me...

| improve this answer | |
  • I did give it an alias and it compiles fine but it doesn't print the average salary. – gigi Jun 26 '15 at 21:34

I think you're going about this the wrong way. First off, I don't think you need a procedure/function to solve your problem. Second, I definitely don't see how a cursor should come in to it.

Try the following window query using the OVER() clause:

SELECT avg(e.salary) OVER(PARTITION BY d.dname) FROM emp e, works w, dept d 
WHERE e.eid=w.eid and d.did = w.did;
| improve this answer | |
  • I need to do this using a cursor. Everything is working but it does not print out the average salary. I'm a beginner to this and I have't learned the over() clause. – gigi Jun 26 '15 at 18:56
  • I have to use a cursor it part of my assignment. – gigi Jun 26 '15 at 21:32
  • 4
    Firstly, this is not a forum for solving homework assignments, but for solving actual engineering problems. Second, if your teacher has assigned you to perform a task in a sub-optimal way, they aren't teaching you very well. – Chris Jun 26 '15 at 21:34
  • Every Select statement (and also any other DML or DDL statment) creates a cursor in Oracle. You can always access the default attribute, e.g. SQL%ROWCOUNT. Looks like your teacher does not know the difference between an implicit and an explicit cursor. – Wernfried Domscheit Jun 29 '15 at 13:52
  • 1
    Homework questions are not automatically off-topic on this site. A good enough question is a good enough question regardless. – Paul White 9 Jul 4 '15 at 5:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.