问个oracle存储过程

Question

要求：编写PL/SQL块，将所有部门名称及其员工个数和平均工资，在屏幕 输出两张表如下：departments(department_id,department_name)
employee（employee_id,employee_salary,employee_name, department_id)求问这样的存储过程对么？create or replace procedure c1
as 
begin
select department name, avg(salary),count(employee_id)
from employee,department
where employee.department_id =department.department_id
group by employee.department_idend;

Answer 1

select department name, avg(salary),count(employee_id)
from employee,department
where employee.department_id =department.department_id
group by department.department

Answer 2

靠，这论坛真烂，自己回复的都不能编辑，上面的改成
select department_name, avg(salary),count(employee_id)
from employee,department
where employee.department_id =department.department_id
group by department.department_name

Answer 3

Oracle 存储过程返回数据集 要用到游标。。

Answer 4

你修改后的语句是对的，可以查出结果。但这与你的要求不符合，因为你要把查询的结果输出到屏幕上，你现在做的仅能在数据库中查询出来。你要定义一个游标用于保存你查询出来的结果，还要定义一个游标变量逐行的保存信息。最后，你要调用dbms_output.put_line的包，把游标变量中的值逐个的输出到屏幕上。

Answer 5

那这么改对不对？
create or replace procedure p1
as 
begin
@department_name char(20);
@avg float;
@count int;CURSOR c1 as
select department_name, avg(salary),count(employee_id)
from employee,department
where employee.department_id =department.department_id
group by employee.department_idbegin
open c1
fetch c1 into @department_name,@avg,@count
while c1%found
loop
dbms_output.put_line( @department_name || @avg || @count );
fetch c1 into @department_name,@avg,@count
end loop;end;
end;
end;

Answer 6

CURSOR c1 as
select department_name, avg(salary),count(employee_id)
from employee,department
where employee.department_id =department.department_id
group by employee.department_id后面是不是应该要加个;号呢？