时间戳

首页 > 解决方案 > 使用 PL/SQL 的总工资支出

plsql - 使用 PL/SQL 的总工资支出

问题描述

我必须从 Employee 表中显示部门名称和部门的总工资支出。

EMPLOYEE: 
Column name     Data type       Constraints
EMP_ID          NUMBER(5)       PK
EMP_NAME        VARCHAR2(25)    NOT NULL
SALARY          NUMBER(10,2)     
DEPT            VARCHAR2(25)

EMP_ID   EMP_NAME   SALARY  DEPT
101      Tom        54000   MECH
102      William    43000   CSE
103      John       34560   MECH
104      Smith      56000   CSE
105      Steve      23450   IT
Sample Output:
Department-wise salary expenditure:
IT department, total salary is  23450
CSE department, total salary is 99000
MECH department, total salary is 88560

我有这个代码 -

set serveroutput on;
declare 
v_emp_rec employee%rowtype;
cursor op_cursor is select distinct(dept), sum(salary) from employee;
begin
dbms_output.put_line('Department-wise salary expenditure:');
open op_cursor;
loop
fetch op_cursor into v_emp_rec;
exit when op_cursor%notfound;
dbms_output.put_line(v_emp_rec.dept || ' department,' || ' total salary is ' || v_emp_rec.salary);
end loop;
close op_cursor;
end;
/

我不知道sum(salary)在那个地方使用是否正确，请查看此代码并提供帮助。提前致谢！

标签： plsql

解决方案

您应该按部门分组，然后进行总和。代替：

cursor op_cursor is select distinct(dept), sum(salary) from employee;

做：

cursor op_cursor is select dept, sum(salary) as salary from employee group by dept;

另外我认为你应该像这样循环光标：

FOR v_emp_rec IN op_cursor 
  LOOP
    dbms_output.put_line(v_emp_rec.dept || ' department,' || ' total salary is ' || v_emp_rec.salary);

  END LOOP;

推荐阅读